Building and marrying variable data

[Jalz]

Hi All,

 

I’ve got a dilemma, which I think a multidimensional variable would solve but I’m not too sure how to implement it. I’m using Virtual Lists for the first time and I’ve split my VL data into Columns – 1 to 8. $$Column1 stores the primary key of an object, in this case a customer, $$Column2 would store the customer name, $$Column3 would store the sales for that customer in month a. I would then do another find loop through and store sales for each customer in month b in $$Column5. Again I would doanother find for month c and so on... I'm sure you get the point. That seems to be fairly straightforward as I have customer table related to sales just loop though records (sort the records via customer id) and build variables based on related data.

 

The issue I have encountered is that not all customers have sales associated in a given month, so when I do the finds the sales data in $$Column2 could be on line 6 for customer x, but could also be on line 4 for the same customer as, data for other customers may not be present.

 

The way my list works is, loops through the data, line 1, is for record 1,etc.

 

Anyone know of a way to marry this data, so I can be accurate that Line 6 in $$Column3, $$Column4, $$Column5 always belongs to customer x regardless of how many records for a customer is found. I need to do my find in the sales layout as I have other things going on and then sort via customer name.

 

Can anyone suggest with examples how if its possible to overcome this or point me in the direction of some resources.

 

Thanks in advance

 

Jalz

[eos]

See if the attached file gives you some inspiration …

CrossTabBySQL_eos.fmp12.zip

[Jalz]

Hi eos,

 

Thank you so much, I'll direct it to see how it works

 

All the best

 

Jalz