JSON from multiple tables to one table

[SageCat]

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.

[Nicolas_Joseph]

Hi @SageCat!

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

@SageCat

three tables, namely Animals, Medical and Neonate

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

---

@SageCat

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.

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]…).

---

@SageCat

The end goal is a HTML file that displays three sets of data in relation to the animal

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

---

@SageCat

I can’t extract the data from the JSON file as it is no longer a JSON file but a lookup

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:

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

---

@SageCat

I’m no expert - I can’t code and I don’t know any languages, but I’m learning…

We’re all in the same boat, constantly learning

[SageCat]

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

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.

[SageCat]

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.

[Jeff_Hager]

Have you tried a Joined List?

[SageCat]

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…

[SageCat]

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.

[Nicolas_Joseph]

OK, it’s easier to understand now with your explanations

Your objective is to have a HTML content in a column.
And this HTML content have to be dynamic (the number of values is not always the same).

As @Jeff_Hager mentioned, you could use Joined List to your advantage.
But the trick is to use formated HTML content. So instead of getting a, b, c, you’ll get <li>a</li><li>b</li><li>c</li> for a bullet point list (for example).

Here how it works

• In your Medial table, add a Template column. Set it up like this:
  

  

• In your main table, after you established Relation columns, use a Joined List targeting the column you just added (no separator needed):
  

  

• Optional: with Template colmuns, you can already add your HTML tags (in can also be done later on, I only show it to you here):
  

  

• Add a Template column to wrap all in a unique column:
  

  

The code I used, however not especialy relevant unless you adapt it to your own case:

Title

<p>A sample text</p>

<h2>List of Fruits</h2>
<ul>{{Fruits}}</ul>

<h2>List of Colors</h2>
<ul>{{Colors}}</ul>

<img src="https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/sagecat/144/85273_2.png" />

• <p> to add a paragrah (some text)
• <h1>, <h2> to <h6> are levels for titles
• <ul> is for creating a list of elements
• <li> (in the first Template column) lets you identify an element of the list
• <img> is for addind an image, your Glide’s avatar here

---

The result

At this point, the content is dynamic. You’ll still have to set up the layout to make it… nice to look.
But the technical part is up and running!

[SageCat]

Thanks for this! So, before I head down that path, I just want to confirm that I could use this to display HTML tables, of which I have no idea how many rows? It would be something similar to the following which, which appears in Glide ok but want to get the same data to HTML so can be printed.

[ThinhDinh]

Yes, that’s basically the premise of HTML tables.

[SageCat]

Thanks for that. I will give it a whirl! Fingers crossed!

[SageCat]

ok, I kinda get how it is all working but my issue is that when I make joined list in my helper_HTML table targeting the template column in the medical table (lets say the date), I’m getting ALL the data for date, not just the data for the animal selected. I’m not sure what I’ve done wrong?

[Darren_Murphy]

Instead of targeting the Medical table directly, create a Query column, apply appropriate filters, and target that.

[SageCat]

Firstly, I want to thank you all for your patience with me… I feel I’m close but I’m missing some vital step. I’ve done as you suggested, Darren, in the Helper_HTML table, which looks like this:

So now, yes, I’m getting just the data for that animal but now i’ve lost the ability to format each date as Date and instead it is doing Date Date Date Date Date… what have I missed?

FYI, with the exception of the query_medical column, which queries the medical table, the other columns query the relevant columns within this Helper_HTML table.

[Darren_Murphy]

Yeah that all looks wrong.

Just taking a step back a bit, and referring to your earlier screen shot:

@SageCat

If you want to replicate the above, then in your Medical table you should have a Template column that looks something like the below:

<tr>
  <td>{date}</td>
  <td>{practitioner}</td>
  <td>{reason}</td>
  <td>{details}</td>
</tr>

In that Template column, you would use replacements to dynamically add the values for each of {date}, {practitioner}, {reason} & {details}.
And then back in your Helper table, you take a Joined List of that Template column via your Query.
That will give you all your table rows.
The final step is to insert all those rows into a table body with another template column like so:

<table>
  <tbody>
  {rows}
  </tbody>
</table>

In the above, you use your Joined List of rows as a replacement for {rows}

[SageCat]

Thanks for this. I will give it another go as I felt like I was going around in circles… I’m gonna get this! And when I do it will be like Oh, yeah, that makes 100% sense!

[Darren_Murphy]

One thing I should point out is that when you take the Joined List of rows via the Query, it’s very important that you remove the default comma separator, otherwise you’ll wind up with broken HTML.

I think this was mentioned in one of the earlier replies, but pointing it out again in case you missed it.

[SageCat]

@Darren_Murphy

Just taking a step back a bit, and referring to your earlier screen shot:

@SageCat

If you want to replicate the above, then in your Medical table you should have a Template column that looks something like the below:

<tr>
  <td>{date}</td>
  <td>{practitioner}</td>
  <td>{reason}</td>
  <td>{details}</td>
</tr>

In that Template column, you would use replacements to dynamically add the values for each of {date}, {practitioner}, {reason} & {details}.
And then back in your Helper table, you take a Joined List of that Template column via your Query.
That will give you all your table rows.
The final step is to insert all those rows into a table body with another template column like so:

<table>
  <tbody>
  {rows}
  </tbody>
</table>

In the above, you use your Joined List of rows as a replacement for {rows}

Wow! This is working! Thank you, thank you, thank you! I can now build on this and make a truly worthwhile HTML page.

I have one more question which relates to this, however… So to print this PDF using HTML2PDF, I have created a button on the animal page (which shows everything that will ultimately be duplicated in the HTML). If the user wants to print the PDF, they click on this button. For testing, I have this button copying the animalID to the Helper Table so that the HTML can be built… but I would then also need it to then send the HTML to HTML2PDF site that will then print the HTML… which makes me then wonder how I can get a button to perform two actions in the Free platform? I did think, oh, I can add that animalID to the Helper Table when the originally select the animal from the animal collection but I can’t see how to add that either in the Item Click section.

Does anyone have any ideas how I can have both these actions happen so the PDF could be generated using the Free platform (because I assume in a Paid platform it would be possible to have multiple actions assigned?)

[SageCat]

I’m having a play with workflows to see if that might be the answer…

[ThinhDinh]

@SageCat

I have one more question which relates to this, however… So to print this PDF using HTML2PDF, I have created a button on the animal page (which shows everything that will ultimately be duplicated in the HTML). If the user wants to print the PDF, they click on this button. For testing, I have this button copying the animalID to the Helper Table so that the HTML can be built… but I would then also need it to then send the HTML to HTML2PDF site that will then print the HTML… which makes me then wonder how I can get a button to perform two actions in the Free platform? I did think, oh, I can add that animalID to the Helper Table when the originally select the animal from the animal collection but I can’t see how to add that either in the Item Click section.

I haven’t followed closely how you build this, but wouldn’t any workflows allow you to specify 2+ actions?