Tricky Slice of people based on info in another table

[Florian_Noizet]

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…

[Marc_Dillon]

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() ) )

[Florian_Noizet]

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.

[Marc_Dillon]

Florian_Noizet:

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.

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.

Florian_Noizet:

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 ?

help.appsheet.com

Valid_If Column Constraint

How to check if a given form input is valid based on its type.

[Florian_Noizet]

Hi Marc.
Sorry, I uploaded the wrong screenshot. Here is what I have with the settings I applied.

I evnetually get the multiple choice.

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

Settings of my column are

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

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)

[Marc_Dillon]

Florian_Noizet:

But when I clic on the ‘+’, the list is empty.
…
I dont quite get why I get the warning, since the base type details show the correct Role table

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]

Florian_Noizet:

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 ?

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

Florian_Noizet:

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)

Current_User (Slice) - How to conform your app around WHO is using the app Tips & Tricks 💡

One of the core basic functions, included in just about EVERY app I make, is the ability for the app to know WHO is using the app. If the app knows who’s using it, then I can easily control many different aspects of the app: Add/edit/delete permissions How data should be filtered What views are shown What buttons, or Actions, are visible Which workflows should fire off Which columns should be shown or editable etc. Requirements To accomplish this functionality in your app, you need the follo…

[Florian_Noizet]

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

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…

[Florian_Noizet]

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.

Here are the settings

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

[Marc_Dillon]

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

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

[Florian_Noizet]

Marc_Dillon:

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

Yes it is.

Marc_Dillon:

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

I got 3 people with this Role in my dataset yet

[Florian_Noizet]

Marc_Dillon:

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.

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 ^^