Adding to a Gallery Items Filter

[JmccoyPeerless]

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. 

[wyotim · Accepted solution]

Hey @JmccoyPeerless, I think I can help you out with this issue and simplify your formula quite a bit as well. I particularly enjoy these multistage filter issues as I use them a lot. To start, let me propose a different filter statement:

Filter(
    '[dbo].[Power_HotTagCollect]', 
    Amount = AmountDropdown.Selected.Result || AmountDropdown.Selected.Result = "All", 
    Status = StatusDropdown.Selected.Result || StatusDropdown.Selected.Result = "-"
)

This should be functionally the same as the code you have. Allow me to explain what is happening a bit. There are two filter statements that do essentially the same thing but I will focus on the first one. If there is a value other than "All", the filter will use that value on the Amount field. If the Amount selected is "All", the statement will return true which effectively bypasses that portion of the filter. 

To add a filter for Hide field, there are a couple of things you could do. If you want it to be a pure true/false selection, you could do this:

Filter(
    '[dbo].[Power_HotTagCollect]', 
    Amount = AmountDropdown.Selected.Result || AmountDropdown.Selected.Result = "All", 
    Status = StatusDropdown.Selected.Result || StatusDropdown.Selected.Result = "-",
    Hide <> HideToggleName.Value
)

where HideToggleName is the name of the toggle control.

If, on the other hand, you want it to show all values and only hide the Hide = true ones, you could do something like this:

Filter(
    '[dbo].[Power_HotTagCollect]', 
    Amount = AmountDropdown.Selected.Result || AmountDropdown.Selected.Result = "All", 
    Status = StatusDropdown.Selected.Result || StatusDropdown.Selected.Result = "-",
    Hide = false && Hide <> HideToggleName.Value
)

This is basically just providing a way to filter by Hide = false and Hide = true at the same time but allow the user to change it to filter by Hide = false and Hide = false by using the toggle. This is a fun little trick and, while it seems a bit redundant to filter by the same value twice, is pretty simple to implement. I may not be understanding exactly what you are doing, so you may need to reverse the values in that statement to get your desired result.

Lastly, to get the text search you could use the Search function wrapped around the whole statement. That could look like this:

Search(
    Filter(
        '[dbo].[Power_HotTagCollect]', 
        Amount = AmountDropdown.Selected.Result || AmountDropdown.Selected.Result = "All", 
        Status = StatusDropdown.Selected.Result || StatusDropdown.Selected.Result = "-",
        Hide = false && Hide <> HideToggleName.Value
    ),
    TextToSearch.Text,
    "ordernum"
)

where TextToSearch is the name of the text input control and ordernum is the field you are searching in. The double quotes are necessary for the search function. You could add more fields to search by adding a comma and field name (with double quotes) for each field you would like to search for text in.

And just to be totally exhaustive (I'm sure this is an exhausting amount of text already!), you could wrap this all in a Sort or SortByColumns if you want the results to be ordered in a particular way.

I hope this wasn't too long but did explain things well. Please let me know if I need to explain differently, if something isn't working properly, or if I can try to help out further! I'm happy to follow up!

[Drrickryp]

Hi @JmccoyPeerless 

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

That's because you wouldn't put it inside the If() function.  I think you could wrap the whole if statement with 

Filter(yourentireifstatement),OrderNumber=Value(TextInput1.Text) && Hide = var) 

assuming that the order number is actually a number. If it is text, there is no need for the Value().  

For the toggle, I would use a context variable to set this property so in the toggle, OnCheck property UpdateContext({var=true}) and in the OnUncheck property  UpdateContext({var=false})I  You would also want to set use the UpdateContext({var:true}) to in the OnVisible property of the screen so that you could set the default for the gallery with respect to Hide.  

[JmccoyPeerless]

This was very helpful I really appreciate the time and effort you put into helping me on this. A complete life saver!! 

The only part that wasn't working correctly was the "hide" Part, I am getting an error saying "there is a circular reference between properties, which is not allowed. A property cannot reference itself or other properties affected by its values." 

[wyotim]

Happy to be able to help!

What is the name of the field you are trying to filter by using that Hide toggle? And what is the name of the toggle itself? I probably put the same names in there or something like that.

[JmccoyPeerless]

Also the search function says wrong column type because the its text. I tried to wrap it in value(orderNumSearch.text) and still didn't work.

[JmccoyPeerless]

The column is called Hide and the toggle itself is called HideToggle.

Properties for toggle: 

Onchange: Patch('[dbo].[Power_HotTagCollect]',ThisItem,{Hide:HideToggle.Value})

Default: ThisItem.Hide

[wyotim]

@JmccoyPeerless, re: the ordernum, that's definitely my bad. The name is pretty clear that it's a number! The search function indeed won't work as it takes text. Try this instead:

Filter(
    '[dbo].[Power_HotTagCollect]', 
    Amount = AmountDropdown.Selected.Result || AmountDropdown.Selected.Result = "All", 
    Status = StatusDropdown.Selected.Result || StatusDropdown.Selected.Result = "-",
    ordernum = Value(orderNumSearch.Text) || IsBlank(orderNumSearch.Text),
    Hide = false && Hide <> HideToggleName.Value // <- this needs fixed still!
)

You could also set the Format property of that text input control to Number to make it so regular text can't be entered. 

*Edit: Just to be extra clear, orderNumSearch.Text will need to be an exact match for ordernum in this case. There might be a way to search partial numerical matches but I've not tried that before.*

[wyotim · Accepted solution]

@JmccoyPeerless So I noticed another error I made that probably won't change the outcome of the circular reference error but still will need to be fixed for the filter to work properly. I put an And (&&) between the two Hide statements. This is some bad logic on my part (causing the filter to look for values that are both false and true, which isn't possible and will return an empty set, or false and false, which is possible) and it should have been an Or operator (||). So like this:

Filter(
    '[dbo].[Power_HotTagCollect]', 
    Amount = AmountDropdown.Selected.Result || AmountDropdown.Selected.Result = "All", 
    Status = StatusDropdown.Selected.Result || StatusDropdown.Selected.Result = "-",
    ordernum = Value(orderNumSearch.Text) || IsBlank(orderNumSearch.Text),
    Hide = false || Hide <> HideToggle.Value
)

Where is the toggle located? Is it in a gallery? I'm not sure what I'm missing on that!

[JmccoyPeerless · Accepted solution]

Yes, the toggle is located within the gallery. Haha i do appreciate the help, I am going to mess around with it a bit. Getting closer than I was, plus its a lot cleaner which is always nice.