Many-to-Many Relationships on an Interactive Dashboard

[generativegeorg]

As I understand it there are a few ways people go about doing many-to-many relationships. You can have enumlists with associated history columns and actions, a slices & a lookup table, or refs on both sides with actions. 

I have Departments, Roles, and Personnel. A Department may have many Roles, a Role may have many associated Departments & Personnel, and a Person may have many Roles.

Can someone explain the reasoning and performance/user implications behind using these different techniques within appsheets?

Is there a good example / video of an interactive dashboard where the sub-views relate via many-to-many relationships? For that matter something that shows the use-cases for each style of m2m?

Are there plans to make this more 'natively' supported?

[1minManager]

This is a bit of an opened question and hard to answer without being clear of your use-case for it.  But ill try 🙂

So I would create 3 tables

Table 1 = Department with an ENUMLIST of [Roles] and a Ref column of [Related Personnels]

Table 2 = Personnel with an ENUMLIST of [Roles] and a Ref column of [Related Departments]

Table 3 = Roles each one being a single record of [Role] and 2 Ref columns to [Related Departments] and [Related Personnels]

I'd do it this way because Departments and Personnel are tangible whereas roles are more nebulous.  But really it depens on what you want to do with the data and how you might want to export or display it.  I've built quite a few apps where you have to do something like this.  So it is already "natively supported".

---

---

Performance wise it will depend on how many rows of data you have in all 3 tables.  But unless the total is more than a few thousand I wouldn't worry about it.

Simon, 1minManager.com

Appsheet developer for hire and reward ‌😉‌

[generativegeorg]

How are you generating the 'related' columns without additional ref columns 'redundant' to the enumlist... are these enumlists with a basetype of Ref? This doesn't seem to generate the 'Related' columns... Does this require actions in the background?

[generativegeorg]

Here's an example project for context
https://www.appsheet.com/Template/AppDef?appName=Many-to-Many-289611030&utm_source=share_app_link

[1minManager]

Says app not found

[generativegeorg]

https://www.appsheet.com/Template/AppDef?appName=Many-to-Many-289611030&utm_source=share_app_link

https://www.appsheet.com/start/cf55e731-a624-426e-b6ab-d2de47345146

[1minManager]

You'd manually add a Virtual List type column or Type Ref

[Markus_Malessa]

This is an item I have been struggling with also. Do you by chance have a sample app with this setup?

[1minManager]

Not really.  But if you add a virtual column and then add an App formula to that column that produces a list of the key column values from another table then it automatically becomes a List/Ref column.  Try it by added a formula of

Select(TableName[KeyColumn],TRUE)

[Markus_Malessa]

In your scenario are your [Ref Table1] and [Ref Table2] in Table3 still REF type columns though? And assuming I understand you correctly, you have a column in Table1 as EnumList BaseType Ref pointing to Table3 as the reference table name? With this type of setup does Appsheet automatically create the entries in Table3 or do you still need to setup looping actions?

[1minManager]

---

@Markus_Malessa wrote:

you have a column in Table1 as EnumList BaseType Ref pointing to Table3 as the reference table name?

---

No its a virtual column of Type = List.  Similar to this one I created:

[Markus_Malessa]

Ok, so in your case you have tables for Staff, Jobs, Related_Staff_Jobs, presumably a standard many-to-many relationship setup such as 2 regular tables with a bridge table that only stores the key pairs between Staff and Jobs?

So in this case since your [Related Jobs] column in your Staff table is a VC, what process do you use to add your key pairs into the Related_Staff_Jobs table? I apologize for the continued questions, I am just wanting to make sure I understand your scenario better and that I am not missing a simpler process I can implement on my apps to handle this kind of workflow with many-to-many relationships since I have a lot of them.

[1minManager]

This app has a very simply Grandfather / Father / Son hierarchy.  This virtual column is just to pull in a subset of the Jobs table (All Jobs Edit Only being a slice).  For a structure like what @generativegeorg described there is no link as such between table 1 & 2.  Just a common list of Roles and some virtual columns that show a subet of records from the other table.

[generativegeorg]

I found some examples but I'm shocked there isn't a more simple way. Also still not super clear how much of this happens on the frontend vs backend and what that means if you go to look at a different record and it hasn't pulled down yet from sync.

(thanks Multitech Visions, et al). 

https://www.youtube.com/watch?v=aDXb0YIMzbs

https://www.youtube.com/watch?v=e7geQtdlvrg

Using refs

https://www.appsheet.com/templates/BRUTE-FORCE-Method-for-implementing-ManytoMany-References?appGuid...

https://www.appsheet.com/templates/App-that-shows-an-EnumList-of-references-to-another-table?appGuid...

Using a join/bridge/lookup table

https://www.appsheet.com/templates/JOIN-TABLE-Method-for-implementing-ManytoMany-References?appGuidS...

https://www.appsheet.com/templates/Create-orders-that-have-many-items-Track-items-that-have-many-ord...

Unfortunately all of these methods are a huge PITA to set up.

[Markus_Malessa]

Those are excellent examples to follow and I have personally implemented the looping process for the join/bridge table concept. However, as you already identified yourself this lacks 'native implementation' in as far as that many other frameworks automatically implement the creation of key pairs in the join/bridge table vs Appsheet you have to implement a 'workaround'. I have explored Budibase a little bit (does involve more coding in my opinion) and they do have a multiselect pointing to the M-2-M relationship table and automatically creates the entries.

[1minManager]

---

@Markus_Malessa wrote:

With this type of setup does Appsheet automatically create the entries in Table3 or do you still need to setup looping actions?

---

To keep it simple I'd suggest manually adding all the required roles in table 3 and the the ENUMLIST columns in Table 1 and 2 will reference this list of roles.

You COULD allow the users to create new roles within the above 2 ENUMLIST columns.  Buts it means you'll need a bot/action to also add it to table 3.  But also generally allowing app users to add to an ENUMLIST is a bad idea.  You'll get loads of duplicates such as:

• OpsRoom
• Ops Room
• Operations Room
• Opsrm
• OpsRoom.

Better to give them a fixed list.