AppSheet by default supports One-to-many relationships:
- One record (say an Order) that has many related records (order details)
- An Employee with many Project_Assignments
- A Client has many Projects
- An Machine has many service tickets
This is accomplished via the “Ref” column type
-------------------------------------------------------------------------------
But there is another relationship connection type that allows you to connect many records to many records
- Let’s say you have a table of records (say Products) where each record can be related to many records in another table (say orders).
- This means when you look at a product, you can see a bunch of related orders - and when you look at an order, you will see a bunch of related products.
AppSheet doesn’t support this type of relationship by default, though it is possible to implement this functionality
- It just takes a little setup is all (^_^)
There is two ways in which you can go about this:
- Using a SELECT()
- Using a JOIN table
-----------------------------------------------------------------------------------------
Using Select() for Many-to-Many References
WARNING: This is inherently inefficient, and should be used with caution - If you expect your system to continue in complexity, or if you’re planning on having more than 5,000 records, they you may want to go the Join table route.
- Here’s a sample app showing how this all works together
- This app consists of two primary tables - which we want to many-to-many connect together
- Employees & Projects
- Each Employee record contains a list of Projects that employee is assigned to
- Each Project record contains a list of Employees assigned to that Project
- When someone changes an Employees list of Projects, the corresponding Projects need to be updated so their list of Employees are correct
Columns
This is accomplished through the combination of adding new physical columns, making some actions to update things, and including a virtual column to display the resulting list:
-
You need two columns to store:
-
What items have been ADDED to the list
-
What items have been REMOVED from the list
-
Then the idea is to search through the database with an action and find:
- All the records on the opposing table that this record SHOULD belong to
- All the records this records SHOULD NOT belong to
-
Then we run an update over these actions, remove what has been removed & adding what has been added.
- We’ll store that value inside the Enumlist on the record
-
-
Then we need a nice way to view that list in the app, so we make a VC to DISPLAY the list of items
This setup must be implemented on both ends
Actions
You also need to create a set of actions to maintain the reference lists when you change things.
- You need an action, on both sides of the reference, to update the Enumlist column on that side
- You also need a Ref action to run the update action on the other side of the ref, for the records you updated
- It’s also helpful to have a composite action, which you can use as the “Save Event” action
- Inside this stack you can place you Ref action
- This future proofs your build, should you want to add something else into the save event later
The Core Principles Of This Setup
- The Enumlist on each record holds the ACTUAL list of connected things.
- When we change something, we’ll run an update on the corresponding records - making sure their lists are always updated.
- From that physical Enumlist column, we’ll then create a virtual Display column - so we can see the corresponding records in an interactive way (i.e. we can click on them and go to the records).
- By relying on the Enumlist to hold the list of ref-connected records, we can lighten the load on the system
- Removing the need for a SELECT() inside a VC
- Which will cause your sync time to increase over time, as your database grows and the number of records increases
Drawbacks of this Implementation
- You can’t use Quick Edits
- As you need the actions to update the records to run, and it only runs on the form save event - You can’t update the list values outside the system
- At least… you need to make sure if you do, that both sides are maintained with your update
- External updates won’t execute the update (though there may be ways to make things work) - You need to add 2 extra physical columns, and 1 virtual, to your app (Add changes, Remove changes, Display)
- There’s no ongoing record of changes; so if your list of items gets corrupted somehow, there’s no way to restore it (other than data source version history).
Benefits of this Implementation
- The heavy lifting is stored inside the Enumlist column, so you don’t have a SELECT() inside a VC
=======================================================
=======================================================
Using A JOIN Table for Many-to-Many References
- Here’s a sample app showing how this all works together
- This app consists of two primary tables - which we want to many-to-many connect together
- Same scenario as above
- The difference here is that all the connections are stored inside the Join table records
- This implementation makes use of Action-Looping
- This implementation makes use of temporary variables - aka “Hot Linking”
Join Table
The first thing you’ll need is a new table, which doesn’t need anything much to it in the beginning:
- An ID column (because every record needs an ID)
- A ref column to one side of the connection
- A ref column to the OTHER side of the connection
That’s it, you can add other bits if you wish - but those are the essential parts to make this work.
Columns
When you set this up in your app, the reference columns will create corresponding reverse references on each of the tables you’re connecting together.
-
[Related Whatevers]
- These contain the join record IDs, but we really need the ID values in the record for the other side of the reference
-
Create a derivative virtual column, using a list-dereference to pull out the IDs of the other side of the reference
-
For example: from Projects we’d have a reverse reference of:
[Related Employee_Project_Assignments] -
From this we can list dereference all the assigned employees for this project:
[Related Employee_Project_Assignments][Employee_Link]
-
This setup must be implemented on both ends
Actions
- This implementation makes use of Action-Looping
When you save the form, a loop kicks off to create each of the join records (or delete the join record if an items was removed), and update the corresponding opposite sides of the join records.
- You need to implement a loop to create the Join records (for each item you’ve added to the list)
- You need to add in a ref delete action as well (to remove any items you’ve removed from the list)
- But this isn’t just a straight delete, you need to:
- Flag the records to be deleted
- Run an update throughout the system, removing these connections and updating any corresponding lists
- THEN you can delete the record
- But this isn’t just a straight delete, you need to:
The Core Principles Of This Setup
- The Join table records are the actual place where the connections are stored.
- All the heavy lifting is done through the data inside the records
Drawbacks of this Implementation
- You can’t use Quick Edits
- As you need the actions to update the records to run, and it only runs on the form save event - You can’t update the list values outside the system
- At least… you need to make sure if you do, that both sides are maintained with your update
- External updates won’t execute the update (though there may be ways to make things work) - You need to make use of action-looping
- If you’re not confident with this method, this is a good way to get there! (^_^)
Benefits of this Implementation
- All the ref connections are stored in the Join table records, so if you need to fix corrupted data you can
---------------------------------------------------------------------------------------------------------------
Even though many-to-many references aren’t natively supported by AppSheet, it’s still possible - and even possible in such a way that will scale to tens-of-thousands of connections while maintaining efficiency in your system
