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.

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

1 Like

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.

This looks really useful - do you have this in a demo somewhere?

No, I actually recommend you to stay away from the Data API.
Use backend workflows if needed, they are a stable API :wink:

You saved my day, man!

Any idea how to reference a List of Things within an object?

Nevermind… had the wrong privacy settings, now its showing.

Hi @marcioavilareis

I am also trying to get records from my DB and use it in run Javascript action but cant’ make it.
I am using the get_objects_from_id function but I get an error.
Here is the code

> Add each parentFolder to the list until reaching the root parent
> 
> function getFoldersPath(currentFolder) {
>   const previousParents = [];
>   while (template's selectedFolder's unique id:formatted as JSON-safe !== Current User's Organisation's Folder:filtered:first item's unique id:formatted as JSON-safe) {
>     previousParents.push(currentFolder);
>     currentFolder = get_objects_from_ids([currentFolder]["Unique id"]]);
>   }
>   return previousParents.reverse();
> }
> 
> var result = getFoldersPath(template's selectedFolder's unique id:formatted as JSON-safe)
> console.log(result)
> 
> // Return value to bubble
> bubble_fn_path(result)