Many to many relation for the same table

[DroidTeo]

Hello all! 

I have a table users, each user can cover different position: 

• team leader
• coordinator
• normal user

and a coordinator can be manager of 1 or N normal users
and a team leader can manager 1 or N coordinator

So, I have this users table wihtin the following columns: user type, team leaders and coordinators.

Inside appSheet I configured the columns as below: 

But it doesn't work.. Inside the application I have only the label but not input field.. 
How can I do this? 

Thanks a lot!

[dbaum]

If each user can have only a single manager, try the following approach:

• [User Type] column: Enum type with predefined values (i.e., Team Leader, Coordinator)
• [Manager] column: Ref type with a Valid_If expression that returns a list of team leaders for a row whose user is a coordinator and a list of coordinators for a row whose user is neither a team leader nor a coordinator

[DroidTeo]

@dbaum unfortunately no.. it is possible to have several managers

[SkrOYC]

---

@DroidTeo wrote:

it is possible to have several managers

---

EnumList basetype Ref then.

Now, it's not that I'm recommending you to do it, just saying that's its possible.

Make sure you know what you are doing

[MultiTech]

---

@SkrOYC wrote:

EnumList basetype Ref then

---

I've got a few sample apps that show some many-to-many setups.

• Using a JOIN table
• Using a SELECT() statement (efficiently)

[DroidTeo · Accepted solution]

In the end, I've chosen to use a dedicated join table between Users and Users, hiding one of the 2 columns of type "List", automatically created and that's it..
Here the join table

[MultiTech]

I see you forgot to add an ID column to your table.... 😛

[DroidTeo]

The ID for the join table is the combination of the 2 foreign keys User and Team Leader