Power Pages - Filter List Based on Hierarchical Account Structure

[gburner]

Hello,

I have a requirement to show records in a table (Permits), based on a hierarchical Account structure.

The logged in Contact has a lookup to the Account table, which will be used as the filter condition in the list.

The Account table uses the Parent Account field to establish hierarchy.

The Permit table has a lookup to the Account table.

Typically I would do this with the webAPI / fetchXML / javascript, but we have a requirement to do this with as much base functionality as possible. Want to make sure I'm not overlooking an OOB way achieve this - and recommendations are appreciated. 

Thanks!

-----------------

Example:

Account Table:

accountname, parentaccount

Corp Store, null

Store 1, Corp Store

Store 2, Corp Store

Other Store, null

Permit Table:

name, associatedaccount

PER1, Corp Store

PER2, Store 1

PER3, Store 2

PER4, Other Store

Contact Table: 

name, account

USER1, Corp Store

USER 2, Store 1

If USER1 is signed in the list (Permit Table) should return:

PER1, PER2, PER3

If USER2 is signed in the list should return:

PER2

[Fubar · Accepted solution]

Do via table permissions, however just be aware that you can't create them generically for extra layers/hierachy of Account (each layer of hierarchy needs another set of distinct Table Permissions).

We previously set up a structure where Parent Accounts would have Child Accounts,  both the Account and Child Accounts would have their own users, but a set of Users of the Parent Account could access and perform the same actions as the users in the Child Account (as well as in their own Account).

Map out the relationships between the tables (including both the Child and Parent Account), and then for each relationship make sure you have a Table permission - 1 set of table permissions for the Child Account users, 1 set of Table permissions for the Parent Account users.

e.g.

Parent Account User

• Parent Account User to Parent Account (Account permission),
• Parent Account to Parent Account's Permits (Parent or Account permission)
• Parent Account to Child Account (Parent permission)
• Child Account to Child Permits (Parent permission)

Child Account user

• Child Account User to Child Account (Account permission),
• Child Account to Child Account's Permits (Parent or Account permission)

Unfortunately you can't have a generic set of permissions for parent of parent of parent without creating another set of Table Permissions for each level in the hierarchy.