Filtering Data for Users/Managers of Specific Locations

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

Simon@1minManager.com

2 Likes

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?