I've been using arrays and Datagrid tables for some time now, and figured it would be awesome to use Livecloud as a database for obvious reasons.
But I am experiencing issues with the Datagrid not formatting it's height properly. I have several objects on every row and a height of 70. But when setting the dgData of a datagrid to a Livecloud array it sets the height to default.
It seems that the very long id's like 0e0e3c72-24d0-4801-88ba-a3718e1c3fbe are somehow causing this issue. Because when I replace these id's with numbers (1,2,3...) and then set the dgData of the datagrid all is fine with the formatting.
However this causes some coding headaches regarding editing fields and updating in the Datagrid etc as I have to keep track of ID's.
Anyone else working with Livecloud arrays and datagrids? I've just started but thought I would seek some advice right away.
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:
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.
Hi Efrain
Thank you very much man
I used that bit of code to populate the datagrid successfully and did a dance of joy, I had to modify it slightly to work with the datagrid helper plugin.
Anyway, Im new to programming and am trying to use livecloud to build a retail system I have dreamed about. A lot has to do with queries between dates so I modified your code to include a code snippet that LCM produced . It works on LCM and I get the records between the 2 dates and i was happy...but I cannot get it to repopulate the datagrid with the results. Here is the code...Im hoping you can tell me where I went wrong:
Code: on mouseUp
put empty into field "searchAllField"
put empty into field "foundLogsLabel"
put "Search all fields by typing here" into field "searchAllFieldsLabel"
local tKeys, tOutputA, tCount, tDataA, tInputA
--code snippet
set the dgData of group "DataGridLogs" to empty
put cdb_TableID("logs") into tTableID
//put cdb_tableKeys("logs") into tKeys
put "date" into tInputA[tTableID]["query2"]["key"]
put "<=" into tInputA[tTableID]["query2"]["operator"]
put "12/13/19" into tInputA[tTableID]["query2"]["value"]
put "date" into tInputA[tTableID]["query1"]["key"]
put ">=" into tInputA[tTableID]["query1"]["operator"]
put "12/01/19" into tInputA[tTableID]["query1"]["value"]
put cdb_batchQuery(tInputA,"cloud","logicalAND","recordData") into tOutputA
--
//put cdb_read("logs", "*", "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 tTableID
put tOutputA[xRecordID][xKeyName] into tDataA[tCount][xKeyName]
end repeat
end repeat
set the dgData of group "DataGridLogs" to tDataA
set the dghProp["saved data"] of group "DataGridLogs" to tDataA
//set the dgData of group "DataGridLogs" to tOutputA
//set the dghProp["saved data"] of group "DataGridLogs" to tOutputA
end mouseUp
I tried messing with it , by changing the tTableID to tKeys and hoping it will work, but nothing, some of the commented code I uncommented to see if putting the data into tOutputA will work
I know the query works on LCM.
WIll appreciate the help
I tried to run a a simple query as well and post it to the datagrid, but it doesnt seem to work either
Code: on mouseUp
put empty into field "searchAllField"
put empty into field "foundLogsLabel"
put "Search all fields by typing here" into field "searchAllFieldsLabel"
local tKeys, tOutputA, tCount, tDataA, tOperator, tValue, tTable, tTarget, tResultFormat, tkey
set the dgData of group "DataGridLogs" to empty
put cdb_tableKeys("logs") into tKeys
put "time" into tKey
put "=" into tOperator
put text of field "timeSearch" into tValue
put "logs" into tTable
put "cloud" into tTarget
put "recordData" into tResultFormat
put cdb_Query(tKey,tOperator,tValue,tTable,tTarget,tResultFormat) 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 "DataGridLogs" to tDataA
set the dghProp["saved data"] of group "DataGridLogs" to tDataA
end mouseUp
if I alter this line :
put "recordData" into tResultFormat
to
put "recordList" tResultFormat
it errors..also i tried putting 'into' : put "recordList" into tResultFormat (the query documentation leaves out the 'into'
still nothing happens (I suspect its the recordList format we need)
It would be awesome if you guys can provide guidance into using the query snippets within LCM to post data into the datagrid ( Its ok to post all the columns because we just 'hide' the ones we do not want users to see.
I also attach a screenshot , everytime I run this query the Cdb engine thinks iI'm offline and not connected to the internet...this may be because I live in a rural town in South Africa, but i doubt it
will really appreciate help on this
Thanks guys
Hi sid,
The code I provided will only work for a read. This is because the output for a batchQuery is slightly different than the output for a read. A batchQuery has two more layers in the output array, the tableID and the query index. The code below will massage the output of a batchQuery into the format for a datagrid.
Code: local tInputA, tOutputA, tTableID, tKeys, tCount, tDataA, tRecordID
put cdb_TableID("testTable") into tTableID
put cdb_tableKeys("testTable") into tKeys
put "date" into tInputA[tTableID]["query2"]["key"]
put "<=" into tInputA[tTableID]["query2"]["operator"]
put "12/13/19" into tInputA[tTableID]["query2"]["value"]
put "date" into tInputA[tTableID]["query1"]["key"]
put ">=" into tInputA[tTableID]["query1"]["operator"]
put "12/01/19" into tInputA[tTableID]["query1"]["value"]
put cdb_batchQuery(tInputA,"cloud","logicalAND","recordData") into tOutputA
put 0 into tCount
repeat for each key xRecordID in tOutputA[tTableID][1]
add 1 to tCount
put tOutputA[tTableID][1][xRecordID]["cdb"]["cdbRecordID"] into tRecordID
repeat for each item xKeyName in tKeys
put tOutputA[tTableID][1][xRecordID][xKeyName] into tDataA[tCount][xKeyName]
end repeat
end repeat
cdb_tableKeys returns all the keys in a table. We repeat through each of these keys to get the data for all of the columns of a record like you want. As for tResultFormat, we want to use "recordData" in this case. "recordData" gives the entire record while "resultList" only gives the recordID of the records. We will fix the documentation to include the "into", thanks for pointing that out.
Hope this helps! Let me know if you have any question.
Hi Efrain
Thank you very much for the assist, I greatly appreciate it.
will give this a shot , I now understand where I went wrong
BTW, Thank you guys for setting up the africa Datacentre. Its great. A huge difference in speed
Hi Efrain
I ran the query successfully and it returned a response, but here's the thing
It returned records from the 10th december to the 13th december into the datagrid, not from the 1st december to the 13th.
Basically the query is to search the records from the 1st to 13th December
so I tried putting in the following -convert- code to covert the variable into a date
put text of field "dateFromField" into tToDate
convert tToDate to short date
put text of field "dateToField" into tFromDate
convert tFromDate to short date
put cdb_TableID("logs") into tTableID
put cdb_tableKeys("logs") into tKeys
put "date" into tInputA[tTableID]["query2"]["key"]
put "<=" into tInputA[tTableID]["query2"]["operator"]
put tFromDate into tInputA[tTableID]["query2"]["value"]
put "date" into tInputA[tTableID]["query1"]["key"]
put ">=" into tInputA[tTableID]["query1"]["operator"]
put tToDate into tInputA[tTableID]["query1"]["value"]
etc etc
and I ran it again, but it still left out records on the 1st, 3rd, 6th etc
here's a sample of the database records can be downloaded with this link:
https://www.dropbox.com/s/6wtjs8bsbvevg2...0.csv?dl=0
Thanks again for the help
I got it....The issue was the query operator,
for anyone trying to search records look at query operators in the documentation ..graaah
Heres the code to search records between 2 dates and display it into the datagrid (You will have to adjust to suit your keys)
Important Note: If you just use ">=" and "<=" this will leave out dates between 01 and 09 if you run a query from lets say the 1 december to the 20 december...use "date>=" and "date<="
on mouseUp pButtonNumber
local tInputA, tOutputA, tTableID, tKeys, tCount, tDataA, tRecordID, tFromDate, tToDate,
put text of field "dateFromField" into tToDate
convert tToDate to short date
put text of field "dateToField" into tFromDate
convert tFromDate to short date
put cdb_TableID("logs") into tTableID
put cdb_tableKeys("logs") into tKeys
put "date" into tInputA[tTableID]["query2"]["key"]
put "date<=" into tInputA[tTableID]["query2"]["operator"]
put tFromDate into tInputA[tTableID]["query2"]["value"]
put "date" into tInputA[tTableID]["query1"]["key"]
put "date>=" into tInputA[tTableID]["query1"]["operator"]
put tToDate into tInputA[tTableID]["query1"]["value"]
put cdb_batchQuery(tInputA,"cloud","logicalAND","recordData") into tOutputA
put 0 into tCount
repeat for each key xRecordID in tOutputA[tTableID][1]
add 1 to tCount
put tOutputA[tTableID][1][xRecordID]["cdb"]["cdbRecordID"] into tRecordID
repeat for each item xKeyName in tKeys
put tOutputA[tTableID][1][xRecordID][xKeyName] into tDataA[tCount][xKeyName]
end repeat
end repeat
set the dgData of group "DataGridLogs" to tDataA
set the dghProp["saved data"] of group "DataGridLogs" to tDataA
end mouseUp
Hi Guys
I have a new problem , The above code to put in Data into the datgrid works great , but it leaves out the cdbRecordID as a key.
I created a hidden cdbRecordID as a hidden colomn in the datagrid so i can use it to edit records from a button within the datagrid.
the repeat script Efrain came up with at the top of the page gives me all the keys and their records correctly, but leaves out the cdbRecord ID
Could you help with this please?
(02-14-2020, 02:56 PM)sid Wrote: Hi Guys
I have a new problem , The above code to put in Data into the datgrid works great , but it leaves out the cdbRecordID as a key.
I created a hidden cdbRecordID as a hidden colomn in the datagrid so i can use it to edit records from a button within the datagrid.
the repeat script Efrain came up with at the top of the page gives me all the keys and their records correctly, but leaves out the cdbRecord ID
Could you help with this please?
Hi sid,
I took the repeat code from your previous post and slightly modified it to include the recordID
Code: repeat for each key xRecordID in tOutputA[tTableID][1]
add 1 to tCount
put tOutputA[tTableID][1][xRecordID]["cdb"]["cdbRecordID"] into tRecordID
put tRecordID into tDataA[tCount]["cdbRecordID"] //ADDED THIS LINE TO INCLUDE THE recordID
repeat for each item xKeyName in tKeys
put tOutputA[tTableID][1][xRecordID][xKeyName] into tDataA[tCount][xKeyName]
end repeat
end repeat
|
