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!
Solved! Go to 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
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.
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 =
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
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!!
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
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?
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
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.
It would help if I was in the right account. 🙂