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.

[Harriswe (Wendy)]

@MultiTech_Visions

select(Orders[Order_Key], 
  in([_thisrow].[Product_Key], [Order_Products])
)

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.

[MultiTech_Visions]

@Harriswe

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.

I’m surprised this timed out.

It should be noted, creating many-to-many relationships like this is not natively supported by the platform

• As such, it’s prone to poor performance effects - which become magnified with larger data sets and increased complexity.

@Harriswe

Product has many Controls and a Control can be for many Products relationship

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:

Looping with actions

Kinda burnt out and just wanna get this out. I’ll try to circle back later and provide some documentation. https://www.appsheet.com/samples/Examples-of-loops-using-actions?appGuidString=7b9ae746-1f51-4ecb-8c39-b322e1c12da6

Looping with Actions (Sample App BUILD Video)

There have been many posts about this topic, I thought I would throw one out there showing the creation of a sample app… from start to finish. This way you can follow along if you want, and hopefully end up at the same place I did at the end of the video. (^_^) Here is a link to the app produced in this video (if you want to skip to the end) https://www.appsheet.com/samples/How-to-loop-through-a-list-and-create-child-records-for-each-item-in-that-list?appGuidString=87db6a15-2904-41c6-b6a9-dbf9…

Manupulating Array value (List in Appsheet) using LOOP actions

Being inspired by @steve s LOOP actions tips and tricks, to add arbitrary number of childs records, I came up with ideas to manupilate values in array, which means list values in Appsheet. User select and/or add values to Enumlist type fields then convert those values as we want. In this simplified sample app, we select and/or values to enumlist, which represents the name. Then we add the string (text) at the end of each listed values to generate the converted list values. In oth…

[Harriswe (Wendy)]

@MultiTech_Visions

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.

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.

[Harriswe (Wendy)]

@MultiTech_Visions

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?

[tsuji_koichi]

@Harriswe

ow 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?

[Harriswe (Wendy)]

Hi @tsuji_koichi

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.

[Steve (Steve Coile)]

The text displayed there was recently localized: you can change it in the app editor in UX >> Localize.

[Harriswe (Wendy)]

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.

[DiastroniX]

@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

[Marc_Dillon]

Can you provide a mock-up sketch of what sort of chart you’re wanting?

The general constraint for Appsheet charting is:

1. X axis is divided by records (rows).
2. Y axis shows one or more values from other columns.

Charting an EnumList probably isn’t going to work out great in most cases.

[DiastroniX]

@Marc_Dillon I create excel file with example what i need:

Exmpl

My app not a public (all tables are in different files) and i don’t create this in appsheet (Importrange doesn’t work )

[Marc_Dillon]

So in your case, you’d create the pie chart from your Products Table, and use the [Count…] column as “Chart column”.

[DiastroniX]

@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?