cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JmccoyPeerless
Helper II
Helper II

Adding to a Gallery Items Filter

I have a gallery for a SQL table, I am currently filtering the items by two drop downs that contain status and amount range. See formula below.

 

If(
//This is for All and ALL
AmountDropdown.Selected.Result = "All" And StatusDropdown.Selected.Result = "-",
'[dbo].[Power_HotTagCollect]',

 

// This is for all amounts and selected status
AmountDropdown.Selected.Result = "All" And StatusDropdown.Selected.Result <> "-",
Filter('[dbo].[Power_HotTagCollect]',Status = StatusDropdown.Selected.Result),

 

// This is for selected amounts and All Status
AmountDropdown.Selected.Result <> "All" And StatusDropdown.Selected.Result = "-",
Filter('[dbo].[Power_HotTagCollect]',Amount = AmountDropdown.Selected.Result),

 

//This is for selected amount and selected status
AmountDropdown.Selected.Result <> "All" And StatusDropdown.Selected.Result <> "-",
Filter('[dbo].[Power_HotTagCollect]',Amount = AmountDropdown.Selected.Result And Status = StatusDropdown.Selected.Result),
Filter('[dbo].[Power_HotTagCollect]',Hide=false))


I want to add a text box on this so the user can type in a ordernum and find the incidents with that ordernum. I would also like to use a toggle that was added to change the hide column from false to true, which would hide all of the values that are true: Ex.  Filter('[dbo].[Power_HotTagCollect]',Hide=false))

I can't seem to figure out how to add these other two filter features within this large filter formula. 

11 REPLIES 11

Filter(
'[dbo].[Power_HotTagCollect]',
Amount = AmountDropdown.Selected.Result || AmountDropdown.Selected.Result = "All",
Status = StatusDropdown.Selected.Result || StatusDropdown.Selected.Result = "-",
OrdNum = Value(OrdNumSearch.Text) || IsBlank(OrdNumSearch.Text),
Hide = false)

So this is what I am using currently and seems to mostly be working how I want it to. I have a toggle in the gallery that is only visible on incidents that have been marked complete. The onchange property of the toggle patches the DB to change the hide value from false to true, in turn hiding all the projects the user wants to. I used this as a way to remove the completed items from the gallery without deleting them from the DB, So the list doesn't grow extremely large. IT would be nice to add a toggle outside of the gallery that would pull in all the projects regardless if they are true/false. I may just end up putting a table on a different screen for view if the user wants to. 

@JmccoyPeerless Okay, that makes more sense with the error you were getting. The toggle that the filter uses will need to be outside of the gallery. Otherwise, it is using a control in the gallery to filter the data feeding the gallery (which is probably where the circular reference is coming in) and will have multiple values anyway, meaning no single true or false being used but rather a set of true/false values, which wouldn't work. Using that same code that I described (but with the corrected || and a toggle outside of the gallery) should do the trick.

 

For the record, that is a great trick for limiting the data. I often put a boolean field called 'Active' in data tables to get the same effect. 👍

 

If you do give that outer toggle a try, feel free to comment back with how it goes! And if you have any issues in the future, feel free to tag me in your post. 

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 (2,461)