Forum Academy Marketplace Showcase Pricing Features

Toolbox Server Script - Reading all Table records

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.
image
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.

3 Likes

Great! Thank you for sharing!

I don’t understand why @Bubble doesn’t have the get_object_from_id and the get_objects_from_ids documented anywhere. These functions are crucial for plugin developers.

It does: https://bubble.io/reference#API.get_api
Enjoy your newfound powers :wink:

@vini_brito I must be missing something…I don’t see those two functions mentioned anywhere in the api docs…

Ah, it’s just the API you can interact with, the function you’d have to build yourself.
Think of an address to arrive, but the route is up to you to come up with.

It’s not a specific function to call, nor specific to the plugin environment.