Is it possible to link across multiple tables?


#1

I have tables that I’d like to link through. For instance, I may have a tables for car manufacturers, models, and owners. The car manufacturers are linked to models, and models are linked to owners. Is there a way to see all owners for each manufacturer and see all manufacturers for an owner?


#2

Hi Dennis,

If I understand your question correctly, I think you’re asking about many-to-many relationships. Each manufacturer could have multiple owners who’ve purchased models of cars from them, and each owner could have purchased multiple models of cars from different manufacturers. To represent this relationship, you need to have a junction table, which in this case is the models table, which links to both the owners and manufacturers.

You can get information from the Owners table to the Manufacturers table using rollup fields. Here’s a table I’ve mocked up following your instructions:

This Models table has links to the Manufacturers and to the Owners.

Now, let’s look at the Manufacturers table:

The rollup field on the right is set up to look at a particular linked field in your table and draw information from the other table to which the field has been linked. It’s set up like so:

This means that this field looks at the Models field here, then goes to look at the table to which it’s been linked (which in this case is also titled “Models”), then returns the value found in a specified field in the Models table (in this case, the “Owners” field). The ARRAYUNIQUE aggregation function is there to ensure that you don’t have repeats of owners’ names (e.g., in this example, Andrew owns two different Lamborghini models, but his name only shows up once).

You can do something similar in the Owners table as well:

Hope this answers your question!


#3

You are correct, I am attempting to implement a many-to-many relationship. This is exactly what I needed. Thank you!

This brings up additional questions related to having relationships across more than three tables and other functionality available with many-to-many relationships. Is there documentation available on these types of features?