Tricky Slice of people based on info in another table

Hello everybody,

I’ve been pulling my hair for a long time now, and haven’t found the adequate help among the existing topics here. So I’m asking for help on my specific problem.

  • Table “Person” : list of all people in the company (many columns, among which key and email)
  • Table “Role” : I have a list of existing roles in my company (2 columns : Key and name of each role)
  • Table “Person Role” : Since 1 person can have several role, and 1 role can be populated on many people, I created a list of all combination between people and their role(s) (3 column : link key, person key and role key)

My first need :
I want to be able to assign each person to his/her Manager

when I fill the column “Person”[Manager], I need to display only the real Managers of my company. So I guess I need a filter or a slice for that.
Basically, I need to lookup in the table “Person role” and show only the Person Key where [Role]=“Manager”
So easy with SQL, but I’m hitting a wall here

My second need
Also, I want to be able to show his/her full team to each Manager. Therefore I need to be able to select all the people for with the assigned manager is the current user.
I guess I need to link with the USEREMAIL, but in my user table, the information stored for Manager is the Key. How can I do ?

Any help would be much appreciated I going crazy with all this…

Whilst having a 3 table setup for this situation may meet a standard of relational databases, you are certainly making it harder on yourself to do these sort of things in Appsheet. You could set it up with just 2 tables, Person and Role, and include a EnumList column ,with base-type Ref, in Person table to choose the Roles for each Person. With this way, you can get the managers by:

FILTER( Person ,IN( “Manager” , [Roles][Role] ) )

Also in the Person table I would include a Ref column which points back to the Person table, to hold each Person’s manager. At which point you’d just need to manually setup a REF_ROWS Virtual Column (since they aren’t automatic for self-referenced situations like this). And that’ll be the list of team members per each manager.

Just some thoughts, just another option.


Otherwise, with your current setup, to get a list of managers you could do:

FILTER( Person , ISNOTBLANK( SELECT( [Related Person Roles][key] , [Role].[Role] = “Manager” ) ) )

or perhaps:

FILTER( Person , IN( “xxx” , [Related Person Roles][Role] ) )

…where “xxx” is the key value for the manager Role.


To get the Person record that matches the logged in user, you can use:

ANY( FILTER( Person , [Email] = USEREMAIL() ) )

2 Likes

Thanks Marc for your quick answer.
I must be doing something wrong. As advised, I set the role column in my person table (enumlist with base-type Ref)

However, when I go to the app, I get a single choice drowpdown list. I can’t get it to let me chose multiple roles.


What did I do wrong ?

I also had the Manager ref column in my Person table. However, when I do this, I get all person in my company.
Where do i put the filter formula you gave me so I only get Manager listed ? In which setting ?

Thank you so much for your help.

1 Like

I’d assume you’re looking at the wrong column (or an old app version). I’ve never before seen Appsheet show an EnumList as an Enum before.

3 Likes

Hi Marc.
Sorry, I uploaded the wrong screenshot. Here is what I have with the settings I applied.
image
I evnetually get the multiple choice.

But when I clic on the ‘+’, the list is empty. :slightly_frowning_face:

Settings of my column are


I dont quite get why I get the warning, since the base type details show the correct Role table :thinking:

About the Valid_If constraint, according to my table structure, I’ve tried this formula :

But to no avail, the liste still contains all peoples from my person table, and not only Managers
Here is my Role table structure (if it helps)


What do I do wrong ?

Last question :
I also want to limit some action due to Role the user has.
Do I do that with this kind of formula (FILTER) or is it better to create a slice of Person table for each role, and then compare USEREMAIL() to email from the adequate slices ?
(some access can be allowed for several roles, for example : managers, HR people and app admin)

1 Like

You have to manually set a Valid_if for Enum(List) columns in this case, they won’t auto-pull the list like a basic Ref column does. Simply:

Role[Key]

I can’t tell. Use Test from expression assistant to ensure expressions are producing the correct results.

1 Like

I just tried your solution as shown here in the settings

But for a user, which has no role yet, the list stays terribly empty
And for an existing user which already have a role, I see that i get a multiple checkbox list.
But I only see the existing selection, others are not shown :frowning:

As for testing my formula, it seems to get me the correct results. At least, when I use Test from expression assistant, I only get 3 keys (since there are only three managers yet in my dataset).


I’m lost here…

Hi Marc.
OK, first thing is now working : roles are displayed in my Enumlist.
I don’t really know how I managed it (since I did many tweaks here and there in the app), but it is all good. I get my role, with multiple choice checkboxes.

Unfortunately, I’m still stuck with the Manager thing… still not displaying what I need.

image

Here are the settings



Any help appreciated on what I did wrong in the settings.

1 Like

“MC” is the key value for the “Manager” Role, yes?

Perhaps you do not have any Person records set as Managers?

Yes it is.

I got 3 people with this Role in my dataset yet

I’m thinking of it now : couldn’t I put an enum column (base-type ref) for the Manager column as I did with the Role column ?
I’d have a single choice dropdown listing the adquate person. No ?

Or would this render useless my virtual column that calculate the related persons (member of each manager’s team)

I hope it is understandable ^^