cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ppl
Post Patron
Post Patron

Combine Filter and other functions

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.

{3195FF6A-9ACC-42D8-ACF6-EE0F08DC237D}.png.jpg

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions

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.

 

View solution in original post

32 REPLIES 32
WarrenBelz
Super User
Super User

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.

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

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


ppl
Post Patron
Post Patron

@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});

 

 

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.


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

Regards,
KrishnaV
Business Applications MVP, Microsoft Certified Trainer
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

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.

 

@WarrenBelz 

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

@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.

@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
Post Patron
Post Patron

 

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?

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,901)