Good afternoon
Iโve made an app that allows me to gather performance indicators on employees across a range of sites. Here are some example tables:
Contacts Table
SiteID | Site | Suburb | Employee | Employee Mobile | Employee Avail. | Employee Email | Region | Manager | Manager Email |
---|---|---|---|---|---|---|---|---|---|
123 | Site Jelly | Suburb A | Julie | M, T, W, Th, F | Alpha | Tracey | tracey@email.com | ||
456 | Site Oreo | Suburb B | Wendy, Luke |
M, T, W W, Th, F |
Alpha | Tracey | tracey@email.com | ||
789 | Site Jelly | Suburb C | Charlie | Th, F | Delta | Linda | linda@email.com |
Employee Records
Date | Site | Employee | Work Overview | Workload | Projects | Comments |
---|---|---|---|---|---|---|
01/01/20 | 123 | Julie | Completed monthly sales reports | High | None at this time. | |
01/01/20 | 789 | Charlie | Completed onboarding | Average | Sales initiative |
These tables are referencing each other via the Site cell (i.e. SiteID is being used as the Key and Site as the Label).
I may also need to change the Key and Label used for the Contacts table as not every site name is unique. (i.e. I could possibly use a Virtual Column for these purposes?).*
Iโd like to be able to set it so Mangers are only able to view Contacts and create/view Records for Sites/Employees for their Regions (i.e. Tracey can create Records for Alpha Region, Linda can create Records for Delta Region).
Managers change frequently so it would potentially need to be based off of what data it entered into [Region] and [Manager].
Any advise is appreciated - please feel free to ask for further information.
Kind regards
So youโre gonna need 2 items here. A slice to use so they can only see their contacts and a formula to control what region they can pick.
The slice formula should be something like
IN([Region],Select(Contacts[Region],[Manager Email]))=UserEmail()
Assuming that [Contacts]Region is an ENUM youโre going to need to use something like this for the Valid_IF and Suggested_Items formulas:
Select(Contacts[Region],[Manager Email]=UserEmail())
Hi Simon @1minManager
Thank you for getting back to me
Users of the app actually have a Site auto selected for them via a geotag as an Initial Value (e.g. Site Jelly, Suburb A), before the Employee field does the same (i.e. if the Initial Value for Site or is wrong, they can manually select).
I would like to be able to have it so if Tracey were to create a new Employee Record, she would not have Site Jelly, Suburb C in the Delta region set as an initial value, nor would she be able to select it.
Likewise if Linda was to create a new record, she would only have Site Jelly, Suburb C in the Delta region selected as an Initial Value or be able to select it.
If the region manager for the Delta region changes in the Contacts table as below (i.e. Lucy > Linda), Lucy would then only have Site Jelly, Suburb C in the Delta region selected as an Initial Value or be able to select it.
SiteID | Site | Suburb | Employee | Employee Mobile | Employee Avail. | Employee Email | Region | Manager | Manager Email |
---|---|---|---|---|---|---|---|---|---|
123 | Site Jelly | Suburb A | Julie | M, T, W, Th, F | Alpha | Tracey | tracey@email.com | ||
456 | Site Oreo | Suburb B | Wendy, Luke |
M, T, W W, Th, F |
Alpha | Tracey | tracey@email.com | ||
789 | Site Jelly | Suburb C | Charlie | Th, F | Delta | Lucy | lucy@email.com |
Would I be able to achieve this in the same way or is another approach needed?
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |