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.
Solved! Go to Solution.
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.
User | Count |
---|---|
124 | |
87 | |
86 | |
75 | |
69 |
User | Count |
---|---|
214 | |
181 | |
140 | |
96 | |
83 |