Relation between tables with multiple columns keys

I am building an app where the user would first have to create an “Asset” record.
from the asset he will be able to create multiple “Cycle” records related to the same asset.
And finally, he will be able to create multiple “Transaction” records for each cycle.
Eventually, each “Cycle” will display a summary of all transactions related to the cycle and the asset.
After that, every asset will display a summary of all cycles related to the asset.

As my relation is based on more than one column - I was not able to understand how to build the relations between the tables.

I hope my question is clear…

Thanks in advance!

I am very new to GlideApps. But from what I understood, you link table by adding a relationship column. For ex: in the Cycles table, add a new column called “Asset Relation” (you can name this anything you want), set the type to relation, then specify that the “Asset” column in this table should be matched to the “Assets” column in Assets table.

From here you can lookup additional values from the Assets table by using a “Lookup” column type. You can also use this relationship column directly in UI for example the “Choice” component.

Hope this helps.

1 Like

Have you looked into using a Query Column to build your relations?

Yes I did. But did not use it (in my case) because the UI showed a “This query will be faster as a relation. Learn more.” warning.

Is a query better than using a relation and additional lookup columns?

Maybe I misunderstood the question. You asked about multiple keys. If you have to use multiple columns as keys to build a relation, you can use a query instead. If you are only using one column then use a relation.

In place of a query, you can create a template column to join all of your keys together and then use the template column in a relation, but now that we have query, I’d probably go that route instead.

Which one is better depends on the situation. Relation is best if you have one piece of criteria that you are using to create a match between tables. Query is best when you have multiple criteria, complex filtering that can’t be done with a relation, or you want your queried data to be sorted a certain way for doing further logic within the table. I like query because I can filter ranges of dates. Something that is not remotely easy to accomplish with a relation.

3 Likes

I have actually started using Glide today, so I just heard about this for the first time and started researching it :slight_smile:

Sounds like it will be useful when I get to the summary part. Thank you!

The thing is, I am unable to create the basic relation between records in my “Cycles” table and records in my “Assets” table because the relation is based on 2 columns.
I am not sure if query is also the answer for that, but if it is - I would appreciate some guidance because I was not able to do it.

Columns in “Assets” table (currently): Assets, Image
Columns in “Cycles” table (currently): Cycle number, Asset, Cycle Status
Columns in “Transactions” table (currently): Asset, Cycle number, Date, Income, Transaction Status

Thank you :slight_smile:

Why not? You have two fields you need to match up. Query does that very well. Unless you want to create template columns in each table and link the template columns with a relation.

What two columns? Isn’t Asset the only common value between those two tables?

It is.
I guess I have to research this more.
Thank you for the prompt response and guidance. It is extremely helpful.

1 Like

That warning will disappear as soon as you add a second filter condition, or a single filter condition that couldn’t be used with a relation.

Queries and relations are similar in that both establish links between one row and one or many other rows. A query is more flexible, because it allows you to apply multiple filter conditions, sorting and limiting. That doesn’t necessarily make it better. As Jeff says, it depends on the situation. Experiment a bit, try playing around with both column types, and get used to the way they work. The more you use them and become familiar with them, the easier you’ll be able to recognise which is appropriate for any given situation.

3 Likes