Okay let me see if I can properly address this question.
I have a table of properties. In this table I have property names and its associated pCode which is just an internal abbreviation for each property. I have also defined a Valuelist named pList in the file based on all values in pCode. I then have a global field checkbox set using values from pList. This is because I want to dynamically generate charts using a web viewer parsing data to Google Charts for only the pCode values I checked.
The data I want to pass to Google Charts comes from another table though that isn't formatted the way I need. Let me explain this... My table basically has a bunch of summary data based on month/yr totals. For example:
Field Name: MthYr | pCode | number of nights rented that month | number of guests per stay | etc.
record1: 1/2017 | ABC | 15 | 2 | etc.
record2: 1/2017 | DEF | 19 | 2 | etc.
record3: 2/2017 | ABC | 12 | 3.5 | etc.
record4: 3/2017 | GHI | 7 | 1.7 | etc.
Google Combo Charts however needs the data as below because it summarizes all properties for each month to bar each properties data on that month.
notations * - indicates no value and # indicates a random value to fill space.
Also the 'other checkbox properties' are dynamically added or subtracted by which pCodes are checked to compare in the chart
['Month', 'ABC', 'DEF', 'GHI', 'other checkbox properties',....],
['1/2017', 15, 19, - , ....],
['2/2017', 12, -, -, ....],
['3/2017', -, -, 7, ....],
['4/2017', #, #, #, ....],
['12/2017', #, #, #, ....],
So here are my thoughts to derive the data this way. Since the months are always set 1-12,
set a variable $months ; Value: List (1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12)
set another variable $pCode; Value: List (pList)
set a third variable $row to capture the loop values
ad some how loop through each month
set $month +1
and executeSQL where month = $month and
some sub loop with executeSQL where month = $month AND $pCode = pCode
set $row to values returned from executeSQL
set $pCode +1
exit sub loop
exit main loop
Another way I thought might be to generate a calculated Value list that takes the months 1-12 and recursively adds each checked pCode so I would have something like this in one field:
... all the way through 12.
Then I could create a calculated field on month/yr totals table that generates 1ABC etc. to use as a matching field to between the two tables.
Then would I only have to loop through once?
Ah my head is going to explode.