cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lefty
Impactful Individual
Impactful Individual

Filtering and lookup to verify user from a different table

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/p...

 

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 

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

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

View solution in original post

11 REPLIES 11
timl
Super User
Super User

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"
)
Lefty
Impactful Individual
Impactful Individual

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

timl
Super User
Super User

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"
)
Lefty
Impactful Individual
Impactful Individual

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

Lefty
Impactful Individual
Impactful Individual

@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-F...

Lefty
Impactful Individual
Impactful Individual

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

timl
Super User
Super User

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 
)

 

Lefty
Impactful Individual
Impactful Individual

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

Lefty
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,655)