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?
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 â 
â
2 Likes
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?
Youâd manually add a Virtual List type column or Type Ref
1 Like
This is an item I have been struggling with also. Do you by chance have a sample app with this setup?
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)
1 Like
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?
No its a virtual column of Type = List. Similar to this one I created:
1 Like
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.
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.
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.
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.
