I’m posting this tip because I didn’t find anything like it here and it took me a long time to get it. I needed to read all the records in a table to generate a result.
I made this script and I hope it helps. Feel free to help with improvements.
First step is to find out the real name of the fields in your table. To do this, place your table in Thing list 1.
Then enter the following code:
JSON.stringify(get_objects_from_ids(properties.thinglist1.get(0,properties.thinglist1.length()).map((x) => {return x.get("_id")})));
Put the result of this step in a text field and you will see something like this:
[{"data_date":"2020-01-01T03:00:00.000Z","id_veic_text":"sample:custom.veiculos","km_number":10000,"obs_text":"mercadocar","trocadeoleo_boolean":true,"Created By":"1597346501878x930217692918956900","Created Date....
Note that the fields I created as obs are actually called obs_text and km is km_number.
So to access these values you can use the following code:
var obj = get_objects_from_ids(properties.thinglist1.get(0,properties.thinglist1.length()).map((x) => {return x.get("_id")}));
obj[0]["km_number"];
this will bring up the first record.
And to go through the entire table you can use something like this:
var x, kmtot;
for (x in obj) {
kmtot += obj[x]["km_number"];
}
kmtot;
However, note that some json fields return an ID, such as “Created by”. To access this value use the following syntax:
userobj = get_objects_from_ids([obj[x]["Created By"]]);
userobj[0]["email"];
Or
get_objects_from_ids([obj[x]["Created By"]])[0]["email"];
I also had a problem when a numeric field is zero. So I created the following check:
if (isNaN(obj[x]["km_number"])){
km_number = 0;
} else{
km_number = obj[x]["km_number"];
}
Remember to place conditions in your table as if it were the where clause in an sql command.
I hope I have helped and whoever has something to add please comment.