LiveCloud arrays and Datagrid

[sid]

Thanks Efrain

Ok, this is all coming together and making sense

Appreciate the assist

[Papa Cann]

Hi Bizbuzz,

I haven't used the datagrid much, this might've been my first time using it, but after tinkering for a bit I have the following script to populate a datagrid from a Livecloud array:

Hello will this code populate the datagrid with the data from livecloud?

I have an expense table with expenseName, expenseAmount
when the card is opened I want all the expenseNames and amounts to be loaded in the datagrid.

Code:

on mouseUp
    local tKeys, tOutputA, tCount, tDataA
   
    set the dgData of group "datagrid" to empty
   
    put cdb_tableKeys("testTable") into tKeys
    put cdb_read("testTable", "*", "cloud") into tOutputA
   
    put 0 into tCount
    repeat for each key xRecordID in tOutputA
         add 1 to tCount
         
         put tOutputA[xRecordID]["cdb"]["cdbRecordID"] into tDataA[tCount]["recordID"]
         repeat for each item xKeyName in tKeys
              put tOutputA[xRecordID][xKeyName] into tDataA[tCount][xKeyName]
         end repeat
    end repeat
   
    set the dgData of group "datagrid" to tDataA
end mouseUp

I set the row height to 70 and the column names to the table keys through the property inspector before running the code above. The row height stayed the same after running the code. I might be missing something. If you have a sample stack, I can take a look at it.

---

The code has not been commented making it very hard to understand.

Efrain cann you please comment the code?

Code:

on mouseUp
    local tKeys, tOutputA, tCount, tDataA
   
    set the dgData of group "datagrid" to empty
   
    put cdb_tableKeys("testTable") into tKeys
    put cdb_read("testTable", "*", "cloud") into tOutputA
   
    put 0 into tCount
    repeat for each key xRecordID in tOutputA
         add 1 to tCount
         
         put tOutputA[xRecordID]["cdb"]["cdbRecordID"] into tDataA[tCount]["recordID"]
         repeat for each item xKeyName in tKeys
              put tOutputA[xRecordID][xKeyName] into tDataA[tCount][xKeyName]
         end repeat
    end repeat
   
    set the dgData of group "datagrid" to tDataA
end mouseUp

I set the row height to 70 and the column names to the table keys through the property inspector before running the code above. The row height stayed the same after running the code. I might be missing something. If you have a sample stack, I can take a look at it.

[efrain.c]

Code:

on mouseUp
  local tKeys, tOutputA, tCount, tDataA
 
  //CLEAR datagrid
  set the dgData of group "datagrid" to empty
 
  //GET ALL THE KEYS OF THE TABLE
  put cdb_tableKeys("testTable") into tKeys
 
  //GET ALL RECORDS OF THE TABLE
  put cdb_read("testTable", "*", "cloud") into tOutputA
 
  put 0 into tCount
  //REPEAT FOR EVERY RECORD SO WE CAN CREATE AN ARRAY FORMATTED FOR datagrid
  repeat for each key xRecordID in tOutputA
     add 1 to tCount
     
     //GRAB THE RECORDID SO WE CAN DISPLAY IT
     put xRecordID into tDataA[tCount]["recordID"]
     
     //REPEAT FOR EVERY KEY IN THE TABLE SO WE CAN DISPLAY ALL COLUMNS OF THE RECORDS
     repeat for each item xKeyName in tKeys
        put tOutputA[xRecordID][xKeyName] into tDataA[tCount][xKeyName]
     end repeat
  end repeat
 
  //POPULATE datagrid
  set the dgData of group "datagrid" to tDataA
end mouseUp

I added comments to the code. I hope it's easier to understand. Let me know if you have any questions.

For your case, place the code in the preOpenCard. If your expense table only has the two keys expenseName and expenseAmount then you shouldn't need to modify the code. If you have more keys but only want to display those two then replace:

put cdb_tableKeys("testTable") into tKeys

with

put "expenseName,expenseAmount" into tKeys

If you don't want to display the recordID remove or comment out:

put xRecordID into tDataA[tCount]["recordID"]

[sid]

Hi Guys

One more question regarding DataGrids and livecloud .

If we want to combine 2 queries from 2 seperate tables where there is an identical key on each table...how would one do this?

so you would end up with basically 2 livecloud arrays that have been put into 2 separate local variables (originating from 2 separate queries on 2 different tables) . the 2 arrays have a common key, such as productode...so you want to put the common key 'ProductCode' and the other keys from both the table queries into one datagrid.

this will be a cool example and very useful to everyone

Would appreciate the assist. Thanks in advance Efrain

Sid

[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

[sid]

Hi Efrain

Thanks man. You understood the question perfectly.

this will help a lot

[sid]

Hi Guys

3 code sample requests that will be invaluable for people reading this forum


Request 1 :Could you help with a code sample to create new records from a datagrid into livecloud please? So Basically you just take each column in the datagrid and create a record in the livecloud table.



each row of the datagrid will correspond to a livecode key.


Request 2 :Also batch updating records that are in a datagrid where 1 key is is common to records in the table you are updating (in the absence of the cdbRecordID row) for example, you have a key called "code" which you want to use to figure out the records that need updating.

Request 3: Batch Updating tables from a grid where the cdbRecordID exists  and is a named column in the datagrid. So here you just want to update records that already exist in a datagrid .

Basically its the opposite of reading data and putting it into a datagrid. The Ying to the Yang...

Will appreciate the assist. Request 1 being the most important

Sid