AnsweredAssumed Answered

Looping through 2 Value lists (1 set, 1 dynamic)

Question asked by user28897 on Aug 15, 2018
Latest reply on Aug 27, 2018 by user28897

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.


Thanks y'all!