Using Metadata to filter a Gallery

[EmilyWiT1]

Hello,

I am trying to use an existing SharePoint list with Managed Metadata columns. Those columns are what I am needing to filter my PowerApps Gallery along with one search box where I would need to incorporate the StartsWith() formula for "Title".

Due to being Metadata, I cannot effectively use formulas like “Distinct()” with “ClearCollect()” to create collections for filtering. Here is the formula that I have effectively used for one filter:

Dropdown box – Choices(MarketingAssetsLibrary.ProductTag).Label

Gallery - Filter(MarketingAssetsLibrary,Dropdown2.Selected.Label in ProductTag.Label).

The issue is, I can not find a good way to continue to use these formulas while also gaining the functionality to filter by multiple custom metadata dropdowns as well as a search box “Title” filter. I have tried “||”, And() and “&&” with no success. And as stated early I have tried creating table collections for each dropdown with no sucess.

I am also wanting to add an "ALL" for each dropdown (2). The only information I have seen on that (plus my needs above) utilizes the ClearCollect() formulas and standard SharePoint columns/data. 

Any help you all could provide would be greatly appreciated!

Thank you!

[EmilyWiT1]

Ok, quick update. I was able to successfully use the ClearCollect() and Collect() functions because I found out that I was doing everything right, PowerApps just needed a full reboot in order to properly calculate. Frustrating to say the least.

However, even though I was able to define my filters with collections and add the "All" features to the three fields, I am not able to get the If() and Filter() functions to work in the formula below:

Gallery Items =

//This is for All PrivateFederal and All Product Tags and All Divisions Filters
If(ddPrivateFederal.Selected.Label= "-All-" And ddProductTag.Selected.Label = "-All-" And ddDivision.Selected.Label = "-All-", MarketingAssetsLibrary,

//This is for Selected Product Tag and All Product Tags and All Division Filters
ddPrivateFederal.Selected.Label = "-All-" And ddDivision.Selected.Label = "-All" And ddProductTag.Selected.Label <> "-All-",
Filter(MarketingAssetsLibrary, PrivateFederal.Label = ddProductTag.Selected.Label),

//This is for All PrivateFederal and Selected Private Federal and All Division Filters
ddProductTag.Selected.Label = "-All-" And ddDivision.Selected.Label = "-All-" And ddPrivateFederal.Selected.Label <> "-All-",
Filter(MarketingAssetsLibrary, ProductTag.Label = ddPrivateFederal.Selected.Label),

//This is for All PrivateFederal and All Priduct Tags and Selected Division Filters
ddProductTag.Selected.Label = "-All-" And ddPrivateFederal.Selected.Label = "-All-" And ddDivision.Selected.Label <> "-All-",
Filter(MarketingAssetsLibrary, Division.Label = ddDivision.Selected.Label))

I know technically I have 5 more to go for the three fields (changed the search bar to a third dropdown), but none of them were working properly so I stopped.

NOTE: I am using "Label" due to the fields being managed metadata.

[v-siky-msft]

Hi @EmilyWiT1 ,

I think the code can be simplified as follows. Please try it on to see if it works for you.

Filter(
MarketingAssetsLibrary,
If( ddProductTag.Selected.Label = "-All-", true ,ProductTag.Label = ddProductTag.Selected.Label),
If( ddDivision.Selected.Label = "-All-", true, Division.Label = ddDivision.Selected.Label),
If( ddPrivateFederal.Selected.Label= "-All-", true, PrivateFederal.Label = ddPrivateFederal.Selected.Label)
)

If you also need a Search box to search on Title column, please try this.

Filter(
Filter
(
MarketingAssetsLibrary,
If( ddProductTag.Selected.Label = "-All-", true ,ProductTag.Label = ddProductTag.Selected.Label),
If( ddDivision.Selected.Label = "-All-", true, Division.Label = ddDivision.Selected.Label),
If( ddPrivateFederal.Selected.Label= "-All-", true, PrivateFederal.Label = ddPrivateFederal.Selected.Label)
),
StartsWith(Title,SearchBox.Text)
)

Hope this helps.

Sik

[EmilyWiT1]

I was able to get the "-All-" function to work as stated in my reply, but the formula you gave actually no longer shows any data when filtered for "-All-" or any other selections (I also restarted the app to be sure).

FYI, I took most of my instruction from Shane in the linked video: https://www.youtube.com/watch?v=kLGglidmPxg

I tried the following formula just to be sure I covered my bases. It has some logical inaccuracies...but it was worth a shot:

Gallery Items = 

If(ddPrivateFederal.Selected.Label= "-All-" And ddProductTag.Selected.Label = "-All-" And ddDivision.Selected.Label = "-All-", MarketingAssetsLibrary,
Filter(MarketingAssetsLibrary,If( ddProductTag.Selected.Label = "-All-", true ,ProductTag.Label = ddProductTag.Selected.Label),
If( ddDivision.Selected.Label = "-All-", true, Division.Label = ddDivision.Selected.Label),
If( ddPrivateFederal.Selected.Label= "-All-", true, PrivateFederal.Label = ddPrivateFederal.Selected.Label)
))

Still no luck.

I greatly appreciate your reply!!

[v-siky-msft]

Hi @EmilyWiT1 ,

That's weird as it works perfectly on my side, below is my demo and the code.

Filter(
    'SP list',
    If(
        ComboBox2.Selected.Label = "All",
        true,
        Department.Label = ComboBox2.Selected.Label
    ),
    If(
        ComboBox3.Selected.Label = "All",
        true,
        JobTitle.Label = ComboBox3.Selected.Label
    )
)

Hope this can help you.

Sik

[EmilyWinonaIT]

Thank you for your response on this! I'm not sure what to say. I understand the formula and I see that it is working, but for some reason it does not work with my app. It seems like the metadata fields are just not matching the dropdown Labels. I have moved on from that error and just recreated the metadata fields. NOT ideal but I had to just move on. However, now I am having trials trying to default the dropdowns to "All".

Again, I added the "All" option using the Collect() function {Value: "All} (Value not Label due to no longer using Metadata). Then I change the default value of the field to "All". However when I navigate to a different screen (Like the "View Details" on the Gallery ) and go back to the first screen below, it changes the dropdowns to first value in the dropbox.

Thoughts?

[v-siky-msft · Accepted solution]

Hi @EmilyWinonaIT ,

I just try to reproduce your issue. But I can't.

Please consider replacing the Dropdown control by Combo box to see if the issue is fixed?

Sik

[EmilyWinonaIT · Accepted solution]

Great! The Combobox switch worked. I'm really annoyed that the dropdowns didn't for no apparent reason but at least it is fixed. I also tried your formulas again and just keep getting zilch. Microsoft Support believes it is due to the Delegation warning. Here is the formula that works though ("Active?" filter was added to reduce amount of data to a SharePoint view - not essential in resolution.):

If(cbPrivateFederal.Selected.Value = "All" And cbDivision.Selected.Value = "All", Filter('Marketing Catalog', 'Active?'.Value = ddActive.Selected.Value),

cbDivision.Selected.Value = "All" And cbPrivateFederal.Selected.Value <> "All",
Filter('Marketing Catalog', 'Private/Federal'.Value = cbPrivateFederal.Selected.Value And 'Active?'.Value = ddActive.Selected.Value),

cbPrivateFederal.Selected.Value = "All" And cbDivision.Selected.Value <> "All",
Filter('Marketing Catalog', Division.Value = cbDivision.Selected.Value And 'Active?'.Value = ddActive.Selected.Value),

cbPrivateFederal.Selected.Value <> "All" And cbDivision.Selected.Value <> "All",
Filter('Marketing Catalog', Division.Value = cbDivision.Selected.Value And 'Private/Federal'.Value = cbPrivateFederal.Selected.Value And 'Active?'.Value = ddActive.Selected.Value))

I'm marking this and your combobox answer correct.

Thank you so much for your help! Lesson Learned: Stop using Term Store Metadata.

[EmilyWiT1]

It would help if I was in the right account. 🙂