Many-to-Many Relationships

MultiTech
Participant V

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.

3X_2_9_29606bed27e8460363c6e120893989b93336802a.gif

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 (^_^)

3X_d_5_d51363a862e7ab883241c312ac5d7f271579cdd3.gif


There is two ways in which you can go about this:

  1. Using a SELECT()
  2. 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:

  1. 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

  2. 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.

  1. You need an action, on both sides of the reference, to update the Enumlist column on that side
  2. You also need a Ref action to run the update action on the other side of the ref, for the records you updated
  3. 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

  1. 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
  2. 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)
  3. You need to add 2 extra physical columns, and 1 virtual, to your app (Add changes, Remove changes, Display)
  4. 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

  1. 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:

  1. An ID column (because every record needs an ID)
  2. A ref column to one side of the connection
  3. 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.

  1. [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

  2. 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

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

  1. 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
  2. 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)
  3. 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

  1. 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

39 30 7,169
30 REPLIES 30

Jonathan_S
Participant V

Awesome,

Thank you

Bahbus
Participant V

Yup yup. Been using this method forever.

Wally_Young
Participant II

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:

  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.

Jake_Naude
Participant V

Thanks @MultiTech_Visionsโ€ฆ Excellent content as alwaysโ€ฆ Cheersโ€ฆ

Harriswe
Participant V

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.

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.

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.

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

Which part of App you see App owners email address and you are refering to?

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.

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:

  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.

@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(...) ) )

MultiTech
Participant V

In the future - please make a new post (referencing this one) for further discussions on this topic

- This way this post remains the โ€œinformation postโ€, and others are the โ€œdiscussion postsโ€


3X_4_d_4d21f7e92a430268be69d01bde89bff9593288d5.gif


@Phong_Lam itโ€™s inside the app

3X_1_e_1efd34d5df404fe97f6afa168c72003ed8af9e7b.gif

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 ?

@Phong_Lam you canโ€™t do that with this method

  • If you think about it: where would these totals for each product be stored?
  • Youโ€™re simply selecting a bunch of products from a listโ€ฆ whereโ€™s the place to store the values for these?

You need a separate place to store the data pair (product + qty)

  • and that means you need a separate table, one record for each selected product.

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.)

Albajaa
Participant I

Awesome! Thanks a lot, saved me a lot of time.

Phong_Lam1
Participant V

Hi all, How can I calculate the order( in is app is Total)


If each product have Itโ€™s quantity, and the price given by app user (in form). Thanks

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?

Top Labels in this Space