LiveCloud Forums
LiveCloud arrays and Datagrid - Printable Version

+- LiveCloud Forums (https://forums.livecloud.io)
+-- Forum: General (https://forums.livecloud.io/forumdisplay.php?fid=1)
+--- Forum: General (https://forums.livecloud.io/forumdisplay.php?fid=3)
+--- Thread: LiveCloud arrays and Datagrid (/showthread.php?tid=234)

Pages: 1 2

LiveCloud arrays and Datagrid - Bizbuzz - 11-21-2019

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.

RE: LiveCloud arrays and Datagrid - efrain.c - 11-21-2019

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.

RE: LiveCloud arrays and Datagrid - sid - 12-14-2019

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
[attachment=45]

RE: LiveCloud arrays and Datagrid - sid - 12-16-2019

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

RE: LiveCloud arrays and Datagrid - efrain.c - 12-17-2019

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.

RE: LiveCloud arrays and Datagrid - sid - 12-17-2019

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

RE: LiveCloud arrays and Datagrid - sid - 12-18-2019

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/6wtjs8bsbvevg2i/logs_13e9dd99-3999-4c47-9e5a-53392c1ab8f0.csv?dl=0

Thanks again for the help

RE: LiveCloud arrays and Datagrid - sid - 12-19-2019

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

RE: LiveCloud arrays and Datagrid - sid - 02-14-2020

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?

RE: LiveCloud arrays and Datagrid - efrain.c - 02-14-2020

(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