Dependent dropdown using a Many To Many recursive relationship

[DroidTeo]

Hello all! 

I have a structure as below: 

Users table
another table to manage the N to N relation from Users to Users -> this relationship is "Team Leader". 
A user can be Team Leader of several other users, and a user can have 1 or more Team Leader. 

Then, inside a table "Report" I have the user that work on this report + the team leader. 
Here a simplified presenation:

I would like to show inside the drop-down at report level, only the team leader linked to the user assigned to the report. 
So I've tried (inside the valid if field) this exepression (translated by AppSheet using the natural language):

(The value of column 'Inspector') is one of the values in the list (The list of values of column 'Engineer'
....from rows of table 'TeamLeader Engineers'
....where this condition is true: ((The value of column 'Team Leader') is equal to (The value of column 'TechnicalManager')))

using the expression notation:

IN([Inspector], SELECT(TeamLeader Engineers[Engineer], [Team Leader] = [_THIS]))

where Inspector -> the Ref field inside the Report table towards the users table
TeamLeader Engineers  -> the N-N table 
TeamLeader Engineers [Engineers] -> is the ref towards the users table (inspector)
[_THIS] -> the selected Team leader

But it doesn't work.. Why? 
How can I retrieved the team leader linked to the selected inspector? 

PS inside the Users table I have 2 lists: 
1) Related TeamLeaderInspector By Inspector
and 
2) Related TeamLeaderInspector By Team Leader

[WillowMobileSys]

---

@DroidTeo wrote:

where Inspector -> the Ref field inside the Report table towards the users table
Related TeamLeaderInspector By Inspector -> the N-N table 
TeamLeader -> the column Team Leader inside the N-N table 

But it doesn't work.. Why? 
How can I retrieved the team leader linked to the selected inspector? 

---

[Related TeamLeaderInspector By Inspector] is a LIST of Ref values.  Even if there was only a single item, it is still represented as a LIST of 1.

The expression doesn't work because it the "dot" notation will only work on column with a single Ref value - not a LIST

How can you fix this?

Your references in the post to the TeamLeader associated with the Inspector implies that you only expect a single Team Leader.  Could you not represent the Team Leader as just a Ref column for that Inspector instead of a LIST?

If not, then you will need to create an appropriate SELECT() expression that replaces the "dot" notation expression you indicated. 

A data structure note

You are facing some challenges with many-to-many relationships because, in my opinion, you are overusing the Users table.   I would recommend including separate Tables one for Inspectors and one for Team Leaders.  Then you can include columns for the specialized characteristics for each of those roles in their respective tables as well as attach any needed child tables such as Team Members, etc.  This would be more efficient and more manageable as the app grows and eliminate the confusion that surrounds the many-to-many relationships.

[DroidTeo]

@WillowMobileSys yes, I've tried using SELECT, but I cannot (yet) filter only the team leader of the selected inspector. 

Then, regarding the structure, I'm not sure that it is a good idea to have an additional table, because even if I'm going to take this way, in the end I need to manage another N-N table in order to recover the teams.. 

[WillowMobileSys]

---

@DroidTeo wrote:

I've tried using SELECT, but I cannot (yet) filter only the team leader of the selected inspector. 

---

If you only have a single Team Leader per Inspector then on your Inspector row you should have a Team Leader column defined as Ref and assign the appropriate Team Leader.  Then you can access the Team Leader details by an expression like [Inspector].[Team Leader].[other team leader column]

---

@DroidTeo wrote:

even if I'm going to take this way, in the end I need to manage another N-N table in order to recover the teams.. 

---

I am not sure why this would be the case since I don't know your data structure.  My thought is you would have a Team Leaders table AND a child Team Members table.  Each row in these tables would have a Ref column to the Users table BUT you avoid trying to manage REF lists all over the app.  And believe me, you don't want to do that!