All Collections
Advanced
Using Relational Data
Using Relational Data
Patrick avatar
Written by Patrick
Updated over a week ago

In this article, we will explain how to organize your data efficiently using a relational approach, using a hypothetical dog walking app example.

Understanding Relational Data

In a relational data structure, information is organized into separate tables that are related to each other. Each table represents a specific type of data, such as Dogs or Dog Walks, and contains relevant information about that particular entity.

Click here to read the full article on Relational Database Theory


How To Use Relational Data In Frontly

Step 1: Add a 'Relationship' in the Data Relations tab of your Google Sheets settings:

Sheets

In this example, I establish my 'Dogs' sheet as the primary sheet, with the 'Walks' sheet as the 'child'. This order is important because of the way this data is related.

Columns

Once you've selected your Sheets with the primary table first and the secondary table next, you will select the Columns that relate these two tables.

In my spreadsheet, a Walk has a 'dog_id' field. A walk can have one dog selected, which points to the ID of a dog in the Dogs spreadsheet.

In contrast, a Dog does not have any fields related to Walks. Dogs can exist without Walks, but a Walk can't exist unless it has a Dog associated with it (or there would be no dog to walk!). The field 'id' on my Dogs spreadsheet will match a dog_id in my Walks sheet.

Custom ID

The Custom ID field can be set to anything, but should probably relate to the 'singular' tense word for your primary 'parent' sheet. In my case, it is 'dog'. You will see this in the next step.

Display Name

Finally, Display Name determines which field is used as the primary display for when we have a list of dogs displayed in a Walk editing form.

After this, you can hit save and see the results!



Step 2: Add a Table to a page

Without my relational data setup, this is what I see in the Walks table. A plain (boring) dog_id field.

With our relational data setup complete, when we click on the Columns settings for the Table, we'll see our new related fields appear in the list (turned off by default):

I would like to display the Dog Name and Dog Breed fields in my table, so I'll just turn them on, and click and drag them to rearrange them as well as turning off the 'Dog ID' field:

Now, my table looks like this. Way better:

Once I click on a record, I'll see the editing form.

Before the relational setup, I'd see a form with a dog_id field like this:

This data is integral, but I'd really rather see a list of the dogs to select from. Fortunately, this is automated by the relational data setup, and my form field now looks like this:

That's it! Now you can select the dog right there by name, and watch it update in real-time. Behind the scenes, it's actually just updating the dog_id field.

Warning:

Currently, to accomplish this relational data system, Frontly has to load both of your related sheets in entirety behind-the-scenes to have all the related fields available. Users won't see this in the app, but technically if you know where to look, that data is available, so if this data is sensitive, this might not be the right choice for you.

Good luck, and feel free to reach out to support via chat if you get stuck!

Did this answer your question?