JSON from multiple tables to one table

Ask for Help
1

Ok, I’m needing some help as I’m not sure how to approach this. Basically, I want to create a helper table that will be used to make a HTML file. The helper table will need data from three tables, namely Animals, Medical and Neonate. I’m thinking that based on the Animal_ID from the Animals table (which the button is coming from so easy enough to get data from that table), it will then get all rows in both the medical and neonate tables relating to that animal_ID but I’m not sure how to do that. I thought I could make a JSON column in the two tables and simply look that up from the helper table… which works but then I can’t extract the data from the JSON file as it is no longer a JSON file but a lookup…

Maybe I’m thinking about this all wrong so please feel free to point me in the right direction. I went with a helper table as I figured I would need to have a column for every extraction of data from JSON file… but maybe that isn’t right. Maybe in the Animal table, the table which the button runs from, I should do this… I’m not sure.

The end goal is a HTML file that displays three sets of data in relation to the animal - the general info in the animal table (easy), any rows of data relating to that animal in medical and same again for neonate.

I’m no expert - I can’t code and I don’t know any languages, but I’m learning… But I just can’t get my head around this so would appreciate any help.

1 Like
2

Hi @SageCat!

Many ideas here, so let’s start by clarifying some concepts and see where that leads :wink:

First of all, that’s usually a good approach to create a table for each purpose, so well done!

In Glide, you can easily bring data from another table based on a common value using a column called Relation. There’s also a Query column for more advanced use cases, especially when you want to apply filters.

You just have to add a new Relation column in your Animals table and set it up to match rows from Medical. Then proceed in the same way for data in Neonate (one relation for each targeted table).
Here’s the doc (with a step-by-step walkthrough): Relations | Glide Docs

You can then use these relations to display data in Layout and/or apply calculations, aggregations and so on in the Data pane (for example, counting the number of rows with a Rollup [Rollup Column | Glide Docs], looking for a value [Lookup | Glide Docs]…).

So, do you want the user to be able to download an HTML template document?
Or is it just about creating a screen in Glide to display these data coming from multiple tables as mentioned previously?

Both are possible, and the answer will depend on your aim here (either by using computed columns such as Template, I imagine, or directly in the Layout with Collections components targeting the Relation column).

Technicaly, a JSON file is just a formated string.
Indeed, if you apply a Lookup, the result in Glide isn’t always a string but an array (with a pill design displayed). By using a Single Value column or a Template column, you can cast the array into a string like this:
Array to text

After that, it’s quite simple to get information from the JSON using - for example - a Query JSON column:

Query JSON
Query JSON666×331 14.7 KB

We’re all in the same boat, constantly learning :stuck_out_tongue:

3 Likes
3

Morning Nicolas

Thanks for your great email. I have already got relations set up in each of the three tables where necessary, so that I can show in the app all the medical or neonatal data in relation to the selected animal. I just wasn’t sure how to get that info to the helper table as a JSON (which I assume I need to have it has to get it to HTML).

The end goal of the HTML is to use it with HTML2PDF so I can print the data. I’m trying to develop an app for rescues to be as workable as possible in the free version (because I know rescues are always scrambling for money) so that they can run in free for as long as possible but the inability to print is a big thing… so I was hoping I could use the data from Glide to make a HTML version of the screen in Glide so it can print… and that’s where I ran into trouble :joy_cat:

ITs getting the JSON file from each table to the helper table so I can then extract out to make the HTML file. I’m planning that the record is only in the helper table to generate the HTML and then it would be deleted - I hope this all makes sense to you.

4

image
image1679×126 17.2 KB

Above is what I get. The rel to Animals works as I’m only getting a single match, but medical and neonate is multiple match and whilst the rel to them works, I then can’t work out how to get at that data so I can show it in HTML. Lookup doesn’t work as I no longer sees that column as an option to select, I assume because it is multiple matches (because relAnimals still shows but that is singular). Hope this makes sense.

5

Have you tried a Joined List?

6

Ahhh… ok, so what I’ve done in the helper file is this which appear to be working… Thanks you!

Now to work out getting the data from medical and neonate into a table, given the amount of data would be different for each animal…

image

7

Actually, a thought… would I be better off having three helper tables, each which would temporarily contain the data for each animal - one for animal (one row), one for medical (X rows) and one for neonate (X rows)…

Or is that just going around in circles? I just thought that way maybe it might be easier to show the tables which will have unknown number of rows of data.