Filtering and lookup to verify user from a different table

(0) Share

Report

Posted on by Lefty

1,625

Hi,

All tables and views are from SQL

I have a ComboBox with its items property set to:

Sort(JobTitle, JobTD, Ascending )

I then have Gallery1 with its item property set to the following, which allows me to filter the Profiles linked to the Job:

Filter(JobPR_Mapping, JobD = CbxTitleSelection.Selected.JobTD)

And then I have a Gallery2 (not nested) which displays all available Profiles, not already displayed in Gallery1:

GroupBy(
 Search(
 Filter(
 colProfiles, //collecting at App OnStart from Profile Table
 Not(ProfileX exactin GalDeleteProfile.AllItems.ProfileX)
 ),
 TextInpSearchProfileToTitle.Text,
 "ProfileName"
 ),
 "ProfileName",
 "ProfileX",
 "ProfileGroupedDATA"
)

I have a requirement where I should only display Profiles in both galleries which are specific to the logged in users region (I will need to also allow some admins to be able to see all users, similar to what I was helped with in this question: - https://powerusers.microsoft.com/t5/Building-Power-Apps/Help-with-filtering-a-gallery/td-p/1869726/page/2

Set(varAllowOverride, 
 User().Email in Filter(Uview, DCode = "GT345").UserEmail
);

In JobPR_Mapping Table I have a column called Region, which will either be empty in which case all profiles with a NULL value should be displayed for all users regardless of region, or if it has region such as UK, it should only be displayed for users from the same region. This info is stored in a view called Uview, which contains a column called UserEmail a column called DCode (GT345 is the value determining if user is an admin) and another column called UGroup which contains the region the is in.

@timl

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (1)

Sort by

timl 37,287 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Lefty

If we work on the basis of your previous post where you save the region of the current user in varUgroup:

Set(varUgroup, LookUp(Uview, UserEmail=User().Email).Ugroup );

then for Gallery 1, you can modify the items property like so:

Filter(JobPR_Mapping, JobD = CbxTitleSelection.Selected.JobTD && ( (varAllowOverride || Region = varUgroup) || IsBlank(Region) ) )

For Gallery 2, I'm not sure what the fields are in colProfiles but assuming that it contains a Region column, you can adapt your existing formula by adding an additional child call to filter, and in that call, you would filter colProfiles using the same logic as shown above. Eg:

GroupBy( Search( Filter( Filter(colProfiles, ((varAllowOverride || Region = varUgroup) || IsBlank(Region))), Not(ProfileX exactin GalDeleteProfile.AllItems.ProfileX) ), TextInpSearchProfileToTitle.Text, "ProfileName" ), "ProfileName", "ProfileX", "ProfileGroupedDATA" )

Was this reply helpful? Yes No
Lefty 1,625 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks @timl
For coming back to me so quickly.
I've tried the code on Gallery1, but I get a delegation warning I've tried to put the notes in // but unsure how I make it green so more readable here:

Filter(JobPR_Mapping, JobD = CbxTitleSelection.Selected.JobTD && //(Delegation warning on &&) ( //If i remove this open bracket and the corresponding close bracket, the delegation on the && no longer appears (varAllowOverride || Region = varUgroup) || // delegation on the || IsBlank(Region) //deleation warning here too ) )// deleagation removed on && if I remoeve this close bracket

I haven't yet tired the code on Gallery2, but in colProfiles, it is creating a collection from the Profiles table, which does not contain a Region column, but the ProfileName column contains the regional values in brackets in the same column. Where the default one is just Profile Admin without any additional values in () that should be visible for all regions, is that workable for my scenario or should I ideally have an additional column in this Profile table too for region:

Profile Admin
Profile Admin (UK)
Profile Data (US)
Profile Data (EU)
Profile Service (EU)
Profile Service (UK)

Thanks

--EDIT
I will get Region added to the Profile table

Was this reply helpful? Yes No
Verified answer

timl 37,287 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Lefty

For the first issue, I think it's the call to IsBlank that causes the delegation warning. If you modify the formula like so, does it go away?

Filter(JobPR_Mapping, JobD = CbxTitleSelection.Selected.JobTD && //(Delegation warning on &&) ( //If i remove this open bracket and the corresponding close bracket, the delegation on the && no longer appears (varAllowOverride || Region = varUgroup) || // delegation on the || Region = "" //deleation warning here too ) )// deleagation removed on && if I remoeve this close bracket

For gallery 2, hopefully, this gives you the result without a delegation warning. If so, let me know.

GroupBy( Search( Filter( colProfiles, //collecting at App OnStart from Profile Table Not(ProfileX exactin GalDeleteProfile.AllItems.ProfileX) && ( ProfileName = "Profile Admin" || varUgroup in ProfileName ) ), TextInpSearchProfileToTitle.Text, "ProfileName" ), "ProfileName", "ProfileX", "ProfileGroupedDATA" )

When Region is added to profile table, you should be able to do this instead:

GroupBy( Search( Filter( colProfiles, //collecting at App OnStart from Profile Table Not(ProfileX exactin GalDeleteProfile.AllItems.ProfileX) && ( ProfileName = "Profile Admin" || Region = varUgroup ) ), TextInpSearchProfileToTitle.Text, "ProfileName" ), "ProfileName", "ProfileX", "ProfileGroupedDATA" )

Was this reply helpful? Yes No
Lefty 1,625 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @timl
Yes removing the IsBlank(Region) and setting it to Region = "" seems to remove the delegation warning, but some very strange issues, I thought I would test if the delegation issue was present but just not displaying in the code so i set my data row limit to 2 and now all i get is 2 records in other parts of my app, which suggest to me I have delegation issue present just not being highlighted in the code? (I'll log this as another question to see if anyone can help guide me why this happens), a simple sort function on a different CombBox with its items property set to the following, only displays 2 items: Sort(JobT, JobTD, Ascending)

Anyways back to the original questions

I had, had a jab at Gallery2 and its functioning based on what you gave me in the first response, with a minor tweak as I got the Region column added by the SQL guy and even with the data row limit set to 2, this displays more than 2 records, so safe to say no delegation issues. I didn't
Thanks all your suggestions are brilliant and have helped me heaps.... I need to test this a little more, but it seems to be functioning

Was this reply helpful? Yes No
Lefty 1,625 on at

Like
a
(0)

Report
Copy link

Link copied!

@timl

I've logged another question, as although your suggestions work, I now have delegation issues, and I thought it was best to create a new question, would be great if you have any suggestions:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Collections-Delegation-amp-workarounds-for-Filter-SQL/m-p/1904137#M478705

Was this reply helpful? Yes No
Lefty 1,625 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @timl

Are you able to advise how I can add a distinct to only populate unique values in my Combobox, tried something like this, but states invalid argument:

Distinct(Filter(Mapping_View, ThisItem.ProfileR = CbxProfileR.Selected.ProfileR && (varAllowOverride || Location = varUgroup), UnitCo ) )

This works on the Combo Box, but gives me duplicate values:

Filter(Mapping_View, ThisItem.ProfileR= CbxProfileR.Selected.ProfileR&& (varAllowOverride|| Location = varUgroup) )

Thanks

Was this reply helpful? Yes No
timl 37,287 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Lefty

If UnitCo is the distinct value you want to retrieve, I suspect the reason for the error is because UnitCo is specified in the wrong place in the formula.

Distinct(Filter(Mapping_View, ThisItem.ProfileR = CbxProfileR.Selected.ProfileR && (varAllowOverride || Location = varUgroup) ), UnitCo )

Was this reply helpful? Yes No
Lefty 1,625 on at

Like
a
(0)

Report
Copy link

Link copied!

EDIT
I stand to be corrected, it works and displays distinct values.... i've tagged you on another question if you are able to help that would be amazing!

Hi @timl

Thanks for coming back to me on this, I have just expanded my question in a new post, I will tag you if you are able to help.
But I tried to place UnitCo as you've shown, no errors, but oddly no data in my combo box, just a scroll bar, indicating there is data there, but nothing being displayed

Was this reply helpful? Yes No
Lefty 1,625 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @timl

I have something strange with this, hoping you can help.
If I'm logged in with a normal user, where varAllowOverride is false, then all I see are values specific to whatever is in CbxProfileR.Selected.ProfileR (Which is of course what I want) but when logged in where varAllowOverride is true, then the values shown are all values, but it seems like only the second part of the filtering is functioning?
i.e. if a varAllowOverride = true, then all values are shown, else varUgroup only shows the value specific to the users locations. I ideally want it to look at CbxProfileR.Selected.ProfileR to determine which values are displayed for that item in the gallery, but also then look at the varAllowOverride and varUgroup to determine to determine the logged in user

Was this reply helpful? Yes No
timl 37,287 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Lefty

>> But I tried to place UnitCo as you've shown, no errors, but oddly no data in my combo box, just a scroll bar, indicating there is data there, but nothing being displayed

The call to Distinct produces an output where the column name is 'Result'.
Therefore, you should hopefully be able to resolve this by changing the DisplayFields property of the combo box to ["Result"]

>> I ideally want it to look at CbxProfileR.Selected.ProfileR to determine which values are displayed for that item in the gallery, but also then look at the varAllowOverride and varUgroup to determine to determine the logged in user

I'm afraid I'm struggling to understand this. I take it that this behaviour is applicable to the formula below (rather than the 'Distinct' variation of this formua)?

Filter(Mapping_View, ThisItem.ProfileR= CbxProfileR.Selected.ProfileR && (varAllowOverride|| Location = varUgroup) )

Was this reply helpful? Yes No