Help with filtering based on user profile properties

[CarlHRVA]

Hello,

I'm trying to filter a gallery at the click of a button based on a User Profile property and a value in another list but I can't quite figure out the formula or if this is even possible. The logic would be to filter the CollectorUIGallery_1 to only show items of a certain Inn Code. We need to find that Inn Code by filtering the "Master Property List"."PS Description" is equal to the UserProfile(InfoAbout.Text).Department 

My formula currently looks like:

If(
    Filter(
        CollectorUIGallery_1.CM_InnCode, 
    'Master Property List'.'PS Description' = Office365Users.UserProfile(InfoAbout.Text)
    .Department).'Inn Code');

@wyotim any ideas?😁 Any help is greatly appreciated as always! 

[mdevaney]

@CarlHRVA 

I would suggest supplying some sample data to make it easier to understand what needs to be done here.

Please share some data contained in this table:  CollectorUIGallery_1.CM_InnCode

Include data in this column:  'Master Property List'.'PS Description'

A screenshot of the app would not work either.  Be sure to explain at least 1 scenario.  (Example: when the button is clicked the gallery should show THIS result).

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up." 

[CarlHRVA]

Absolutely, sorry for the vague post. 

I have a "loading" screen with 3 different buttons. Which button is available to you is based 

CollectorUIGallery_1:  This gallery data source is a SharePoint list called "Collection Efforts". Every item in this list has an Inn Code assigned to it. What I am ultimately trying to achieve is when users click a button on the load screen, it will transition to another screen and filter the gallery based on their assigned Inn Code. We determine the Inn Code based on two things....

Master Property List:   This is another SharePoint list containing information about all of the properties we service. Each property is a list item with columns like "Property Name", "PS Description", "Inn Code" that is unique to each property. 

O365 User Profile Data: Another data source in the app is an O365 User Profile connection. In the user profile data there is a formula that I believe is used to show information about the current user "Office365.UserProfile(InfoAbout.Text).Department" 

This ".Department" property from the User Profile Info will be the same as the "PS Description" property, which is a unique value in each item in the SharePoint list. 

What I'd like to do, if possible, is filter the Master Property List when User Profile.Department is equal to the Master Property List.PS Description and then filter CollectorUIGallery_1 when the Inn Code matches the Inn Code from the previously filtered Master Property List.

Does that make more sense? Let me know if there is anything else I can clarify better. Thank you!

[wyotim]

Hey @CarlHRVA, one thing I notice off the bat is that you have an If statement, which requires a boolean and at least one condition if that boolean resolves to true. Assuming all is well with the rest of your statement, you could remove the If statement portion. The next thing is that you are essentially getting a table but don't have an action involved. You will want to collect the list using the ClearCollect function. Try this:

ClearCollect(
    CollectorUIGallery_1.CM_InnCode,
    Filter(
        'Master Property List'
        'PS Description' = Office365Users.UserProfile(InfoAbout.Text).Department
    ).'Inn Code'
);

 I am assuming that the InfoAbout.Text contains a user ID, like an email. 

Let me know if this works or not! Hope you are doing well and that work and life in general are both hitting some kind of stride!

[wyotim]

Oops! I didn't check the recent posts, just clicked on the email link and started typing. I'll read what you and @mdevaney posted now. 😬

[wyotim]

So after doing some other work, reading the posts I missed, drinking some more coffee, and pondering what I was even thinking in my first response 😁, here is something hopefully better and more helpful. 

To filter the Master Property List, some of my old code applies:

Filter(
    'Master Property List'
    'PS Description' = Office365Users.UserProfile(InfoAbout.Text).Department
)

If this list will not change that often and has less than 500-2000 items, I would probably collect this filtered list. Using my old code somewhat:

ClearCollect(
    colMasterPropertyList,
    Filter(
        'Master Property List'
        'PS Description' = Office365Users.UserProfile(InfoAbout.Text).Department
    )
)

If the list is bigger, you could create a gallery using the same filter in the Items property.

Now the trickier part. I am assuming that there will be multiple items in the filtered Master Property List. That means we need to filter a list with another list. There are a few options here. If your list of Collection Efforts is within 500-2000 items, you could do this for the Items property in CollectorUIGallery_1:

// If using a collection
Filter(
    'Collection Efforts',
    'Inn Code' in colMasterPropertyList.'Inn Code' // or GalleryName.AllItems.'Inn Code'
) 

// If using a gallery named GalleryName
Filter(
    'Collection Efforts',
    'Inn Code' in GalleryName.AllItems.'Inn Code'
)

The "in" part isn't delegable, so you will see the blue squiggly under it. If the list is small enough this doesn't matter.

For bigger lists, what I have resorted to are nested galleries. The high-level view of this is I create a flexible height gallery that will contain the first list that I want to filter the second list by, then add a gallery inside that first gallery, filtering that second gallery by something in the first. In this case, you could use the first filter code on the Master Property List in the Items property for the first gallery. I would then put in a label for the Inn Code and add the second gallery, which would be filtered like so:

Filter(
    'Collection Efforts',
    'Inn Code' = ThisItem.'Inn Code'
)

I would then make it so the user can click on an item in the second gallery to navigate to a screen or have a form or something on that same screen that allows the user to work with the selected data. 

A simpler take on this theme would be to show a gallery of the filtered Master Property List, have the user pick one of those, and then use that choice to filter a gallery of the Collection efforts. The Item properties would be the same for each gallery; they would just be separate galleries rather than nested ones and the second one would reference what was chosen in the first as opposed to ThisItem.'Inn Code'.

Apologies for the much more complex answer. If I am understanding this correctly, this entails a many-to-many filter, which can be a bit of a complex thing. Personally, I like the nested gallery solution as it bypasses many of the issues with many-to-many filters and can provide a nice UI/UX for the user as they can look at a categorized list of all the things and choose what they want.

I hope that is more helpful/useful than my previous (overly-hasty) response. And I look forward to what @mdevaney (the Forum King 👑 in my book) responds with as well. I enjoy seeing how others handle these types of things!

[CarlHRVA]

@wyotim Thank you for your incredibly detailed and well thought out response. You are so kind and have helped me immeasurably. I am mostly following here but the part I get confused by is:

"I am assuming that there will be multiple items in the filtered Master Property List. That means we need to filter a list with another list." 

What exactly do you mean by this? With the original filter, it should return only one item from the Master Property List. One item in the Master Property List should have the same value in the 'PS Description' field as what the user has in their

Office365Users.UserProfile(InfoAbout.Text).Department

property from O365. Does that make sense? I think I am using that connector properly at least....

In regards to the list size, the Master Property List is less than 500 items so that won't be an issue.  

Thank you so much, as always. I have been trying to find some time to spend on Lynda.com learning the Excel language and I'm hoping that will translate into me needing less help😂 

[wyotim]

@CarlHRVA so I was probably overthinking things. What I was assuming was that there could be multiple items in the Master Property List that had the same Department. If there is only one Department per item, that makes things much simpler! What I would do would be to set a global variable for the Master Property List Inn Code. This could happen when the user pushes the button (OnSelect property) and would be something like:

Set(
    glbInnCode,
    LookUp(
        'Master Property List'
        'PS Description' = Office365Users.UserProfile(InfoAbout.Text).Department,
        'Inn Code'
    )
)

A note on this: there are two types of variables in Power Apps, global and context. Global are created using the Set function and creates a variable that can be used/referenced on any screen. Context variables are created using the UpdateContext function and create a variable that "lives" within the screen it is declared in. We are using a global variable here because we want to set it up before we go to the screen that will use it. There are ways to pass a context variable between screens in the Navigate function but this is easier to keep track of (in my opinion).

You could then filter the gallery by that Inn Code like so:

// Put this in the Items property of CollectorUIGallery_1
Filter(
    'Collection Efforts',
    'Inn Code' = glbInnCode
)

If the Inn Code is a number, you may need to wrap the glbInnCode in a Value function:

Filter(
    'Collection Efforts',
    'Inn Code' = Value(glbInnCode)
)

Apologies for overcomplicating things! Hopefully, there is something to take from all that for the future. 😁 And as always, let me know if this does what you need or not!

And on another note, I am a not-so-closeted Excel junky. I don't get to work with it as much as I would like these days (besides maintaining some existing forms and the occasional analysis that pops up). If you ever want to talk Excel, feel free to private message me here or hit me up on Twitter if you are on there. My handle is in my bio. 👍 I might have to knock the cobwebs off my brain but I can chat VBA and some PowerQuery as well if you get that deep into it.