AppSheet be default supports One-to-many relationships:
- you have one record (say an Order) that has many related records (order details).
But there is another relationship connection type that allows you to connect many records to many records
- 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 formula magic
I’ve put together a simple sample app that demonstrates how the formulas work to produce this interaction:
https://www.appsheet.com/samples/A-Sample-App-To-Demonstrate-How-To-Do-ManyToMany-Relationships?appGuidString=9fc05730-3794-4445-9e08-12fbacbfbf21
Here’s a run through of how it works:
The Secret
select(Orders[Order_Key],
in([_thisrow].[Product_Key], [Order_Products])
)
The column [Order_Products] = an enumlist of Product keys on the Orders table
- This SELECT() formula checks to see if the Product Key (for the row that’s being evaluated) is inside the list for the row that’s being calculated.