Combine Filter and other functions

[ppl]

Hello.

I am trying to modify Filter Function which @KrishnaV adviced me in topic Filter by value  and combine it with default function which we can see in canvas app:

SortByColumns(Filter([@'Facility Desk'], StartsWith(Title, TextSearchBox1.Text)), "Created", If(SortDescending1, Descending, Ascending))

And after the I get

If(
    varMyValue,
    If(
        IsBlank(varStatusValue),
       SortByColumns(Filter([@'Facility Desk'],varPlantValue in Plant.Value And Responsible.Email=varUserEmail And StartsWith(Title, TextSearchBox1.Text)), "Created", If(SortDescending1, Descending, Ascending)),
      Filter(
            'Facility Desk',
            (varPlantValue in Plant.Value And varStatusValue in Status.Value And Responsible.Email = varUser.mail And StartsWith(
                Title,
                TextSearchBox1.Text
            ))
        )
    ),
    If(
        IsBlank(varStatusValue),
        SortByColumns(Filter([@'Facility Desk'],
            varPlantValue in Plant.Value And StartsWith(
                Title,
                TextSearchBox1.Text
            )
        ), "Created", If(SortDescending1, Descending, Ascending)),
        Filter(
            'Facility Desk',
            (varPlantValue in Plant.Value And varStatusValue in Status.Value And StartsWith(
                Title,
                TextSearchBox1.Text
            ))
        )
    )
)

But after clicking several times on buttons which changes filter values all items are disappearing.

May be you can see where is an error?

Main parameters I need now are:

1. Show only Plant 1 items.
2. Show only Plant 2 items
3. Show items where Responsible column is empty
4. Show items where Responsible is user who is using app now
5. Don't show items with Status : Finished.

And how to do that all that filters works together correctly?

[WarrenBelz]

Hi @ppl ,

Before we start working on your current structure, please have a look at the below as see if it close to what you need. Note I have free-typed it, so commas and brackets may need a tidy up

SortByColumns(
   Filter(
      [@'Facility Desk'],
      StartsWith(
         Title, 
         TextSearchBox1.Text
      ) &&
      If(
         IsBlank(varStatusValue),
         varStatusValue in Status.Value
      ) &&
      If(
         varMyValue,
         varPlantValue in Plant.Value &&
         Responsible.Email=varUserEmail,
         varPlantValue in Plant.Value
      )
   ),
   "Created", 
   If(
      SortDescending1, 
      Descending, 
     Ascending
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[iAm_ManCat]

Hi @ppl 

I agree with what @WarrenBelz has written, I think it's just missing the false check for if varMyValue is false:

SortByColumns(
    Filter(
        [@'Facility Desk'],
        StartsWith(Title, TextSearchBox1.Text)
        &&
        If(IsBlank(varStatusValue),
           varStatusValue in Status.Value)
        &&
        If(varMyValue,
           
           //true value
           varPlantValue in Plant.Value 
           &&
           varStatusValue in Status.Value 
           &&
           Responsible.Email = varUser.mail,
           
           //false value without Responsible
           varPlantValue in Plant.Value 
           &&
           varStatusValue in Status.Value
        )
    ),
    "Created", 
    If(
       SortDescending1, 
       Descending, 
       Ascending
    )
)

Although this only includes 3 filter conditions, whereas I see your picture showed four, so might need some further tweaking to do what you want,

Cheers,

Sancho

[ppl]

@iAm_ManCat 

@WarrenBelz 

It seems like what I am need, but when I type your code, my buttons are not working. It is not showing any items.

On plant1 onSelect property:

UpdateContext({varPlantValue:"Plant 1"});

on Plant2 onSelect:

UpdateContext({varPlantValue:"Plant 2"});

On Not assigned OnSelect:

UpdateContext({varStatusValue:"New"});

On EveryStatus OnSelect:

UpdateContext({varStatusValue:""});

 On Assigned to me OnSelect:

UpdateContext({varMyValue:true});

On All Users on Select:

UpdateContext({varMyValue:false});

[KrishnaV]

Hi @ppl,

Please check the below:

Onselect property of the radio: UpdateContext({varSelPlant:Plantradio.Selected.Value});
For Without Responsible button OnSelect:
SortByColumns(Filter([@'Facility Desk'],IsBlank(responsible) and varSelPlant = Plant.Value), "Created", If(SortDescending1, Descending, Ascending))
For Without Responsible to me button OnSelect:
SortByColumns(Filter([@'Facility Desk'],responsible.mail = User().Email and varSelPlant = Plant.Value), "Created", If(SortDescending1, Descending, Ascending))
For Without Responsible to me button OnSelect:
SortByColumns(Filter([@'Facility Desk'],responsible.mail = User().Email and varSelPlant = Plant.Value and plantStatus.Value <> "Finished"), "Created", If(SortDescending1, Descending, Ascending))

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to help.

Regards,
Krishna
If this post helps give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

[WarrenBelz · Accepted solution]

Hi @ppl ,

Firstly, please have a look at the code below and try to understand if the logic is what you are after. With something this complex, I can only help you solve it if you help me identify the bits that work and then build on them.

SortByColumns(
   Filter(
      [@'Facility Desk'],             //Look in Facility desk
      If(
         !IsBlank(TextSearchBox1),    //if something in search box
         StartsWith(
            Title,                    //filter by Title field starting with this
            TextSearchBox1.Text
         ),
         true                         //otherwise ignore
      )&&                            //AND 
      If(
         !IsBlank(varStatusValue),              //if there is a value in varStatusValue 
         varStatusValue = Status.Value  &&      //This value should equal Choices columns Status
         If(                                    //AND
            varMyValue,                         //if varMyValue is true 
            varPlantValue = Plant.Value  &&     //Plant.Value equals "Plant 1" or "Plant 2"
            Responsible.Email = varUser.mail,   //AND Responsible.Email equals User's email 
            varPlantValue in Plant.Value        //OR varMyValue false, only plant needs to equal
         ),
         true                                   //OR if varStatusValue empty, show all records
      )                                         //but filter by the text box if necessary
   ),
   "Created",            //sorted by Created date                       
   If(
      SortDescending1,   //order depends on your
      Descending, 
      Ascending
   )
)

Also, when you do the  EveryStatus OnSelect:

UpdateContext({varStatusValue:Blank()})

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[ppl]

@WarrenBelz 

but now Plant filter working only when varStatusValue is not blank and Responsible filter is not working at all

[WarrenBelz]

@ppl ,

I am a user like you and cannot read you mind on the exact results you need. I have commented the present code I supplied with exactly what it does.

Rather than keeping telling me what is not working, what I need you to tell me on each item is

• What it does now
• EXACTLY what you want it to do

You are trying to construct a quite complex filter and before you respond, have a think about what may need to change and make a suggestion on how to do this.

[ppl]

@WarrenBelz 

Ok. Let me try to start with simple one.

We will save our search and sort functions and will add three filters:

1. Show only Plant 1 items

2. Show only Plant 2 items

3. Show only items with Status New and Processing (don't show Finished and Rejected)

And of course they must work together(Plant1 and only New and Processing status or Plant 2 and only New and Processing status)

[ppl]

I made it like this:

SortByColumns(
    Filter(
        [@'Facility Desk'],
        If(
            !IsBlank(TextSearchBox1),
            StartsWith(
                Title,
                TextSearchBox1.Text
            ),
            true
        ) && If(
            !IsBlank(varPlantValue),
            varPlantValue = Plant.Value,
            true
        ) && If(
            !IsBlank(varStatusValue1),
            varStatusValue1 = Status.Value,
            true
        ) && If(
            !IsBlank(varStatusValue),
            varStatusValue = Status.Value,
            true
        )
    ),
    "Created",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

and set on my Show Only New and Processing button formula:

UpdateContext({varStatusValue:"New"});UpdateContext({varStatusValue1:"Processing"});

But it shows me nothing. 

And If I type only one:

UpdateContext({varStatusValue:"New"}

it works perfect.

🤔

Why two status conditions are not working together?