Filter based on Parent and child relation in PowerApps

[Mo_gamal]

Hi Everyone,

Please i need you Help for this Issue.

I have 3 SharePoint List (Company , branch and BranchUsers)

Company (ID, Title , IsActive, other's columns ).
Branch  (ID , Lookup (Company) , IsActive  and Other Columns for branch details)
UsersBranch (ID , Lookup (Branch) , AssignTo (Person) , IsActive ).

Now, I want to Filter only Companies which user have privilage on it and Filter only branchies which user have privilage on it.

how's it can be?

[RusselThomas]

Hi @Mo_gamal ,

[sorry a few edits, my post got scrambled]

Probably a few ways to achieve this - not taking any potential delegation issues into account the most obvious to me is the below, but there may be more efficient ways if others care to comment 🙂

Branch Gallery filtered by selected user:

// Filter branches where the selected users branch lookup matches the Branch ID column
Filter(Branch, ID=UserGallery.Selected.BranchLookup)

Company Gallery filtered by selected user:

// Filter companies where the results of the above filtered branches company lookup match the company ID column
Filter(Company, ID=LookUp(Branch, ID=UserGallery.Selected.BranchLookup, CompanyLookup))

The above was tested on local data, so you may potentially hit delegation issues with the second company gallery filter LookUp() function.  If you do, you may want to use a variable on the UserGallery template OnSelect: function as follows;

UserGallery template (or arrow icon or something in the card) OnSelect: property;

UpdateContext({userBranchLookup: ThisItem.BranchLookup});
UpdateContext({branchCompanyLookup: LookUp(Branch, ID=userBranchLookup, CompanyLookup});

Then the above formulas can be further simplified as follows;

// Filter branches where the selected users branch lookup matches the Branch ID column 
Filter(Branch, ID=userBranchLookup)

// Filter companies where the results of the above filtered branches company lookup match the company ID column 
Filter(Company, ID=branchCompanyLookup)

Hope this helps,

RT

[Mo_gamal]

Thanks for your Interested 

But, for Company and branches it will load list of items in dropdowns So, i want to load list of companies and list of branches based on selected company in dropdown based on UserBranch on field AssignTo=User().Email

[RusselThomas · Accepted solution]

Hi @Mo_gamal ,

Perhaps I'm misunderstanding you, but to me the result, whether a Gallery or a Dropdown, is the same?

Whether it's a Gallery or Dropdown shouldn't change the filter....but again, perhaps I misunderstand.  This is how I read it;

Based on what user you've selected in UserBranch, (your primary starting focus is a particular user) 

• Filter the branch that user is assigned to (UserBranch Lookup(Branch) = Branch ID)
• Filter the Company that branch belongs to (Branch LookUp(Company) = Company ID)

Couple of assumptions I'm making is that ;

• Many users will belong to one branch.
• Many Branches will belong to one company.

Meaning for one user you will only see one branch and one company.

If your starting point is the company and you want to see all branches and users assigned to a company, then the filter is just in reverse;

Company DropDown lists all companies,

Branch DropDown (assumes a company has been selected from the CompanyDropDown)

Filter(Branch, CompanyLookup=CompanyDropDown.Selected.ID)

UserBranch DropDown (assumes a branch has been selected from the BranchDropDown)

Filter(userBranch, BranchLookup=BranchDropDown.Selected.ID)

If I'm still missing the point perhaps pop a couple of screenshots up to help clarify?

Kind regards,

RT

[TopShelf-MSFT]

Thanks for posting in the community @Mo_gamal. Do you still need help with this topic? Can you review the latest reply and update the thread if it was helpful?

Thank you,

@TopShelf-MSFT