AppSheet by default supports One-to-many relationships:
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
AppSheet doesn’t support this type of relationship by default, though it is possible to implement this functionality
There is two ways in which you can go about this:
-----------------------------------------------------------------------------------------
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.
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:
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.
The Core Principles Of This Setup
Drawbacks of this Implementation
Benefits of this Implementation
=======================================================
=======================================================
Using A JOIN Table for Many-to-Many References
Join Table
The first thing you'll need is a new table, which doesn't need anything much to it in the beginning:
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.
This setup must be implemented on both ends
Actions
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.
The Core Principles Of This Setup
Drawbacks of this Implementation
Benefits of this Implementation
---------------------------------------------------------------------------------------------------------------
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
Awesome,
Thank you
Yup yup. Been using this method forever.
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.
This would be a good thing to store in it’s own table, yes.
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:
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.
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.
Thanks @MultiTech_Visions… Excellent content as always… Cheers…
Hi @MultiTech_Visions I had done virtually the same kind of query to maintain a Product has many Controls and a Control can be for many Products relationship. It worked for a short while and then developed a time-out syncing error.
Unable to fetch app definition. Error:Request cancelled: took too long to process: Computing a formula/n
Product table has a maximum record count of 4, 645, which has a sever-side security filter to restrict that number to only the products the user has created to: 50 records.
Control table has a maximum record count of 56 records, security filter down to 20 based on AddedBy.
So not huge numbers by database standards (MySql), yet the solution resulted in the sync error, which only went away after deleting the query listed below.
Product table had a virtual column Inherited controls that used the following query, like the one suggested:
Select(
Control[ControlUUID],
IN( [_THISROW].[ProductUUID], [Products] ),
FALSE )
On the Control table side, it has an enum-list of type Ref called Products of ProductUUIDs that identifies Products the control should apply to. See screenshot below. Note: this is not a virtual column.
So now I am at a bit of a loss as to how to represent this relationship. I have considered an intermediary table: Product_Has_Many_Controls but how do you update such a table, when there are no Loop constructs (While, For) in AppSheet?
I.e: how do you create / update a record in an intermediary table from a list of ProductUUIDs identified in the Control table?
Any suggestions or recommendations greatly appreciated.
I’m surprised this timed out.
I wouldn’t do things this way, I would create many controls for products - then select the control when selecting which specifics should be applied - sticking with a one-to-many relationship.
PS:
Unfortunately manually selecting controls for a product is not the requirement. Consider a Compliance Team, who want to the specify controls for products with certain characteristics. For example: Credit Cards offered to Retail clients. This approach ensures conformity across ‘like’ credit card offerings and new credit card products with ‘similar’ characteristics inherit the controls automatically.
This ensures consistency, which would be difficult to achieve if each control had to be manually assigned to a product every time a new product was created. Easy for operational-risk to creep in and for a control to be missed. Furthermore the Designers creating the products should not be allowed to amend the controls set-by the Compliance team. This is segregation of duty requirement.
I fully understand why the solution currently outlined does not scale. Thank you for the looping actions tips. I’ll take a look and hopefully will find a solution.
I implemented the looping with action approach based on the examples sent, with an intermediary table to maintain the many-to-many relationship, between controls and products. I can see how the same could have achieved by manipulating arrays. Avery good video with the sample app. Kudos to @Steve
Just one last question, how do you hide or change the app owner email to be a general email for the company, rather than an individual’s email?
Which part of App you see App owners email address and you are refering to?
On start-up the app mentions the app creator. It used to give out my work email and say this person@1stidea.com may receive data on your locale app usage etc.
Has that changed? See screenshot below.
The text displayed there was recently localized: you can change it in the app editor in UX >> Localize.
Many thanks @Steve , brilliant news. Although I do most of the development work, I didn’t really want to broadcast my email address. Fame has its limitations
Happy Easter to all the Appsheet community.
@MultiTech_Visions many thanks,
I have question (anybody ) :
If i need create chart(view, percentage), using the example data from this post:
Chart: products and orders in numbers
Product1 has 3 orders
Product2 has 1 order
…
What i need?
Thank you
Can you provide a mock-up sketch of what sort of chart you’re wanting?
The general constraint for Appsheet charting is:
Charting an EnumList probably isn’t going to work out great in most cases.
@Marc_Dillon I create excel file with example what i need:
My app not a public (all tables are in different files) and i don’t create this in appsheet (Importrange doesn’t work )
So in your case, you’d create the pie chart from your Products Table, and use the [Count…] column as “Chart column”.
@Marc_Dillon Hm, in my case: I don’t know how to: CountProductsInOrder I think we need create a new virtual colomn, but what i write in expression?
COUNT( FILTER( Table, IN(...) ) )
Thanks for replying, but in the app It’s does not have quantity, for example, If I choose product 1 with 3 item, and product 2 with 4 items ?
I’ve got a few options out there that I’ve put together over the years:
I’ve had requests for updates to both of these apps; they’re on my list. (But with as many projects as I have… it could be awhile.)
Awesome! Thanks a lot, saved me a lot of time.
Hi all, How can I calculate the order( in is app is Total)
Thankyou soooo much!!! I was stucked on this for two days now and couldn't find a solution anywhere... So today I've found an asnwer by Marc Dyllon that finally lead me here" ❤️ In databases this is so easy and common, that I just found mylself confused that I couldnt work with this here in Appsheet. Thanks again, It finally worked here!
@MultiTech wrote: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?appG...
this link opens an invalid page.
thanks for the post!!!!!!
You're correct - the whole thing needed a re-write
--------------------------------------------------------------------------------------
I've updated the post to include better instructions on the two methods for achieving many-to-many references - including links to updated sample apps and explainer videos.
It seems wild to me that these somewhat convoluted techniques are the best practice methods for many-to-many since it is such an incredibly standard relationship. (Why) is this the case? Are there plans to improve this in the future? Am I missing something?