Add rows in a table based on json coming from a api call

Ask for Help
1

I’m trying to find the right solution for this use case.
Data about a company and its board members can be found by use of “call api” to a specific endpoint.

The resulting json has a structure like
company
– company_number : 100
– board
— boardmember
----- Name: John Doe
----- Address: The earth
— boardmember
----- Name: Alex Johnson
----- Address: The moon

I would like to let the users manually add position to the board members - therefore I’m thinking of putting all the boardmembers into a new table like

| Name         | Address   | Position |
|--------------|-----------|----------|
| John Doe     | The earth | CEO      |
| Alex Johnson | The moon  | CFO      |

I would then be able to show all the boardmembers in a collection (and filter by company_number)

When the Positions has been added to the boardmembers by the user then I expect that I would write all the data to new table with all boardmembers for all companies whereas the table shown above should be a helper table. But this is potentially just a bad idea that complicates the setup unnecessarily as I don’t really see an easy way to add multiple rows in a new table.

How would you solve such a case? Any ideas? Will I need to introduce Make.com (I would actually love to it all in Glide)

NB: this must be done (automated) for multiple companies

2

I think adding rows in a new table is the right approach.

The only way I can think of to do it solely in Glide requires using the Call API action.

  • Write a JavaScript function to work with the original JSON, which will output multiple mutations that can be used in the Call API action.

  • Trigger the Call API action with the mutations.

If you don’t have access to Call API, probably Make.com is the way to go, with the same approach of creating multiple mutations (through an iteration) and then use a single HTTP module to add multiple rows.

2 Likes
3

Yes, @ThinhDinh is correct. Build a payload in your Helper Table to add the new rows and use the Call API action to add them. It’s actually fairly straight forward, I’ve been doing it for a while.

1 Like
4

@Darren_Murphy @ThinhDinh Thanks a lot for your input.
I admit this is not easy for me to do as I can probably only at my best can adjust some existing javascript to do something similar.

As you have done it before, Darren, would you be able do a small example to show how this is actually done? I think that it is a pattern that would be great for others to understand: Based on json data in one cell how can you create multiple rows in another table.

Would be so much appriciated :slightly_smiling_face:

1 Like
5

Here are a couple of Loom videos that I made a few months back. These demonstrate setting values in multiple rows, but the same technique can be applied to add rows.

The first video uses templates and joined lists. The second one uses the new JSON Columns.

I guess what isn’t covered in the above is how you would transform an API JSON response into something you could feed into the API call to add new rows. It’s kind difficult to show something generic for that as every situation will be slightly different.

1 Like
6

@Darren_Murphy Thanks you showing a way.
I have a question - from where did you get JSON array column. Doesn’t seem to be there for me.
1:31 into second video

7

ah yes, that column has been deprecated since I made that video, and the JSON Object column now handles arrays.

8

@Darren_Murphy
Another question in relation to using Call API

I use a Query JSON column - and it results in a mutations array of one element - and therefore start bracket [ and end bracket ] are missing.

And I therefore get a bad request when I do a Call API.

Is this really to be expected even when the JSON is valid?

{
“appID”: “eBrEBeungKTfhE4UijRR”,
“mutations”: {
“kind”: “add-row-to-table”,
“tableName”: “native-table-ujUoRWQ0TSOEAsrbpXGd”,
“columnValues”: {
“UYJya”: “CVR-3-4004007869”,
“zv7Ts”: “A V”,
“9iUYf”: “Direktion”,
“tkABp”: “KV”,
“M7jF4”: “Mr”,
“tulMY”: 12345678,
“xXF5I”: “ABC-1-12345678”,
“fRlqc”: “”,
“ZtH4N”: “ddd”
}

The definition of a request to add-rows

curl --request POST ‘https://api.glideapp.io/api/function/mutateTables’
–header ‘Content-Type: application/json’
–header ‘Authorization: Bearer *********************’
–data-raw ‘{
“appID”: “eDDDDeungDDfhD4DijDD”,
“mutations”: [
{
“kind”: “add-row-to-table”,
“tableName”: “native-table-ujDoDDDDTSOEAsrbpXGd”,
“columnValues”: {
“tkABp”: “Kilde”,
“UYJya”: “PersonLassoId”,
“zv7Ts”: “Navn”,
“9iUYf”: “RolleType”,
“M7jF4”: “Rolle”,
“xXF5I”: “VirkLassoId”,
“tulMY”: “CVR”,
“fRlqc”: “Adresse1”,
“ZtH4N”: “Adresse2”
}
}
]
}’

}
}

9

Mutations should be an array (square brackets [])

1 Like