Many-to-Many Relationships

[MultiTech_Visions]

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.

[Jonathan_S (Jonathan)]

Awesome,

Thank you

[Bahbus (Dave Papajcik)]

Yup yup. Been using this method forever.

[Wally_Young]

Love this thanks so much! Helped me finally figure out how to accomplish this with my app!

Quick question/observation: this currently only allows adding new orders but doesn’t allow adding new products since the Products table is set to read-only. My app has a requirement to support adding new Products and then to associate the product with one or more Projects (similar to your Orders table). Similarly, by creating/adding new Projects, I need to associate one or more Products to one or more Projects. With this requirement, I believe this would still require creating an intermediary table that connects the Products and Projects tables together - storing Product IDs and Project IDs in the table.

[MultiTech_Visions]

@Wally_Young

storing Product IDs and Project IDs in the table

This would be a good thing to store in it’s own table, yes.

[Jake_Naude]

Thanks @MultiTech_Visions… Excellent content as always… Cheers…

[Wally_Young]

So I’m a little stuck right now trying to figure out how to accomplish what I’m trying to do. Here’s my needs:

1. I need to be able to pick/select multiple Projects for a single Product. From the Products edit screen, how can I include an Enumlist to provide all active Projects, and then based on which ones are checked, store those in the intermediary table that connects the Products and Projects (let’s call it Project Details)? I’ve tried adding a “Projects” column to the Products table as an Enumlist with a base type of Text, and the Valid If = SORTED(Projects[Project Name]). This gives me the list of all projects in the Projects table to pick from, and when saved, it does save the project names in this field. However I’m not clear how to show all of these selected Projects when viewing from the Projects table to display a single project and which selected products.

2. From the Projects table, when viewing/editing a project, I need to be able to A) view which products are associated with this project, and B) to edit/update the selected products to check or uncheck products.

So basically I need to be able to add/edit bi-directionally - either when viewing/editing products to pick which projects it’s tied to, or when viewing/editing a project to pick which products are associated to it.