Database Access Control Based on UserName and Hierarchy Column

[Anonymous]

I am looking or help with creating access by hierarchy in my Power Apps screens.

I have a 'Member List' and I have a 'Training and Competencies List'.

The Member List has a 'UnqID' (Text) field for each member and a 'MemberHierarchy' (Text) field.

An employee has one of four hierarchy assigned: 'Employee', 'Park', 'Region', 'National'. 

I have a free floating UserName field in the header of the First screen. The UserName is being pulled from the active directory with User().Name.

I would like the information displayed on each screen to default to each persons allowed access based on their UserName pulled, as compared to the 'exact same' UserName and UnqID in the Member list (I manually loaded them all), and the UnqID in the 'MemberHierarchy'. The User should only be able to see data specific to their hierarchy level.

First Screen OnVisible Should be what to achieve this task?

I've got User().FullName in a Text field collecting the UserName.

On several screens I have Single Dropdowns, Multiple Dropdowns, Galleries and Forms. I need the hierarchy to default to the minimum allowed on all screens; and then be able to adjust as necessary.

   1. Employee (Employee Hierarchy) should only be able to see their Park data and their own personal Data.

   2. The Supervisor (Park Hierarchy) should be able to see all data associated with their Park, and all employees like the one above

       at that Park.

   3. Region (Region Hierarchy) should be able to see all Park data for Parks in 'only' their Region (there are seven regions), as well

      as data for all region, supervisor, and employees with the region designation in that Region.

   4. Nation (National Hierarchy) has no limitation on access; should be able to see ALL Regions, Park, Employees.

I've provided examples of some of the screens. Most look/function like the below. I believe variables need to be set at AppStart, and formulas in the Dropdown, Combo Boxes and Galleries to force data to be based on the 'UserName' and 'MemberHierarchy'.

[v-bofeng-msft]

Hi @Anonymous :

Could you tell me:

• Do you want to get the current user's level from the Member List' when loading this app, and then display items which in 'Training and Competencies List'  according to the current user's level?
• How to get the'UnqID' of the current user?
• How to get the ‘Park’ of the current user?
• How to get the 'Region' of the current user?

I assume that you retrieve the user's UnqID, Park, and region in the'Training and Competencies List' via User().FullName.Then I suggest you define some variables when loading the app, and use these variables to control which items are displayed.For example:

1\Set the app's OnStart Property to:

Set(UserUnqID,LookUp('Training and Competencies List',UserName=User().FullName).UnqID);
Set(ThePark,,LookUp('Training and Competencies List',UserName=User().FullName).'EC Park');
Set(TheRegion,,LookUp('Training and Competencies List',UserName=User().FullName).'Nps Region');
Set(TheLv,LookUp( 'Member List',UnqID=UserUnqID).'MemberHierarchy');

and then run OnStart.

2\Use the following formula as the data source of the gallery

Switch(
  TheLv,
  "Employee", 
   Filter('Training and Competencies List',UserName=User().FullName),
  "Park",
   Filter('Training and Competencies List','EC Park'=ThePark),
  "Region",
   Filter('Training and Competencies List','NPS Region'=Region),
   "National",
   'Training and Competencies List'
)

Best Regards,

Bof

[Anonymous]

Could you tell me:

• Do you want to get the current user's level from the Member List' when loading this app, (Yes) and then display items which in 'Training and Competencies List'  according to the current user's level?  (Yes)

• How to get the'UnqID' of the current user?  (The 'UnqID' and the UserName are found on both the Member list and the 'Training and Competencies List'. Either can be used to cross-reference the Member data in a filter.)

• How to get the ‘Park’ of the current user?  (Identical four letter ParkAlpha codes are on both lists)

• How to get the 'Region' of the current user?  (Identical four letter Region codes are on both lists)

I assume that you retrieve the user's UnqID, Park, and region in the'Training and Competencies List' (I currently retrieve all 'personal and park data from the Member list, and all training and competency data from that list. The Member list is a sort of Master list)via User().FullName. 

Does this help clarify things any?

[v-bofeng-msft]

Hi @Anonymous :

If so, then the solution I provided before should be work. But you can also try this:

1\Set the app's OnStart Property to:

Set(ThePark,,LookUp('Member List',UserName=User().FullName).'EC Park');
Set(TheRegion,,LookUp('Member List',UserName=User().FullName).'Nps Region');
Set(TheLv,LookUp( 'Member List',UserName=User().FullName).'MemberHierarchy');

Best Regards,

Bof