LiveCloud arrays and Datagrid

[efrain.c]

Hi sid,

If I understand your question correctly, you want all the records from two tables that have the same value for a common key. The following batchQuery should do the trick. Let me know if you have any questions or if I misunderstood your question.

Code:

local tTableName1, tTableName2, tTableID1, tTableID2, tProductCode, tDataA, tOutputA
local tTarget, tMode, tResultFormat, tCollapseArray
local tCount, tDataGridA, tKeys

//GET TABLEIDS FOR TABLES NEED TO CONSTRUCT QUERY
put cdb_tableID(tTableName1) into tTableID1
put cdb_tableID(tTableName2) into tTableID2

//CONSTRUCT QUERY FOR TABLE 1
put "ProductCode" into tDataA[tTableID1][1]["key"]
put "=" into tDataA[tTableID1][1]["operator"]
put tProductCode into tDataA[tTableID1][1]["value"]

//CONSTRUCT QUERY FOR TABLE 2
put "ProductCode" into tDataA[tTableID2][1]["key"]
put "=" into tDataA[tTableID2][1]["operator"]
put tProductCode into tDataA[tTableID2][1]["value"]

put "cloud" into tTarget
put "batch" into tMode
put "recordData" into tResultFormat
put false into tCollapseArray

//QUERY BOTH TABLES FOR EVERY RECORD THAT CONTAINS THE GIVEN tProductCode IN THE ProductCode KEY
put cdb_batchQuery(tDataA, tTarget, tMode, tResultFormat, tCollapseArray) into tOutputA

put 0 into tCount

//CLEAR DATAGRID
set the dgData of group "datagrid" to empty

//REPEAT FOR EACH TABLE IN THE OUTPUT ARRAY
repeat for each key xTableID in tOutputA
    //GET ALL KEYS FOR THE TABLE WE ARE CURRENTLY PROCESSING
    put cdb_tableKeys(xTableID) into tKeys
   
    //REPEAT FOR EACH RECORD FOR THE CURRENT TABLE
    //THE OUTPUT ARRAY ALSO CONTAINS THE INDEX (1 IN THIS CASE) SO WE NEED TO REFERENCE IT TO ACCESS THE RECORDIDS
    repeat for each key xRecordID in tOutputA[xTableID][1]
         //INCREMENT tCount FOR EVERY RECORD (NEEDED TO FORMAT THE ARRAY FOR THE DATAGRID)
         add 1 to tCount
         
         //DISPLAY THE RECORDID IF WANTED
         //COMMENT OUT OR REMOVE IF NOT
         put xRecordID into tDataGridA[tCount]["recordID"]
         
         //REPEAT FOR EVERY KEY IN THE TABLE TO DISPLAY ALL COLUMNS OF THE RECORDS
         repeat for each item xKeyName in tKeys
              put tOutputA[xTableID][1][xRecordID][xKeyName] into tDataGridA[tCount][xKeyName]
         end repeat
    end repeat
end repeat

//POPULATE DATAGRID
set the dgData of group "datagrid" to tDataGridA