Display info from 2 different records next to each other

[jenschmidt]

Happy Friday!!

I want to display info from two different records next to each other, as if they are on the same record.

Example of the data:
TABLE_1.TX ID TABLE_2.Line TABLE_2.Prov ID
23515696

23515697 1 100038

23515698 1 130088
23515699 2 601


There are three scenarios that can happen -
1.) A transaction (noted by TABLE_1.TX ID) can have no records in TABLE_2

2.) A transaction can have one record in TABLE_2

3.) A transaction can have two records in TABLE_2

When scenario three happens, I want to show the Prov ID from line 2 on line 1, as in the following;

TABLE_1.TX ID TABLE_2.Prov ID TABLE_2.Prov ID (Ln 2)
23515696

23515697 100038

23515698 130088 601

Currently, I am using the following formula:
WhilePrintingRecords;
if {TABLE_1.TX_ID}={TABLE_2.TX_ID}
and {TABLE_2.LINE}=2
then Previous({TABLE_2.PROV_ID})

This works, but with the Previous function, I'm reduced in flexibility regarding sorting and possibly other formatting. I'm not sure how this will be used in the end, I've just been asked for help with the solution, but I want to make it as flexible as possible. I was thinking that some variable declarations might be needed, but I can't quite figure out how.

Any other ideas, or is this the way you would do it too?
Thanks!


jennifer.giemza@uwmf.wisc.edu

 

[jenschmidt]

Sorry for the multiple threads!!! I made a mistake in the example data on this thread. Please ignore this one and look at the other thread by the same name -
thread 767-919260

jennifer.giemza@uwmf.wisc.edu