cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GraemeNZ
Advocate III
Advocate III

Syntax for adding combo filter to existing If/SortByColumns/Filter

Hi. I have a gallery which filters the data based on the customer logged on, unless the current user is an approver in which case they can see everything. This was fine when there were only a few customers, but now there are more I want to add a filter to the approver's view.

 

Current Gallery items formula is:

//If current user is approver show all, else filter the data based on the customer logged in

If(gvCurrentUser = "FIW Approver",

    SortByColumns(Filter('Master Ingredients List', (HalalStatus.Value = "Expired" || HalalStatus.Value = "Expiring") && Halal.Value="Yes"),"Title"),

    SortByColumns(Filter('Master Ingredients List', (HalalStatus.Value = "Expired" || HalalStatus.Value = "Expiring") && Halal.Value="Yes" && Customer.Value=gvCustomer),"Title")

)

 

How do I add a combo box filter to the approver's view? Something like this, which I have in another gallery:

 

If(gvCurrentUser = "FIW Approver",If(IsBlank(cbCustFilter_Ing.Selected.Value),true,Customer.Value=cbCustFilter_Ing.Selected.Value
),Customer.Value = gvCustomer)

 

Screenshot of gallery, customer names replaced with 1 and 2 for confidentiality.

GraemeNZ_0-1660600839256.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @GraemeNZ ,

Try the below - free-typed and I cannot test, but I think it will work

SortByColumns(
   Filter(
      'Master Ingredients List', 
      (
         HalalStatus.Value = "Expired" || 
         HalalStatus.Value = "Expiring"
      ) && 
      Halal.Value = "Yes" &&
      ( 
         (
            gvCurrentUser = "FIW Approver" &&
            (
               Len(cbCustFilter_Ing.Selected.Value) = 0 ||
               Customer.Value = cbCustFilter_Ing.Selected.Value
            )
         ) ||
         Customer.Value = gvCustomer
      )
   ),
   "Title"
)

 

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.

Visit my blog Practical Power Apps

View solution in original post

2 REPLIES 2
WarrenBelz
Super User
Super User

Hi @GraemeNZ ,

Try the below - free-typed and I cannot test, but I think it will work

SortByColumns(
   Filter(
      'Master Ingredients List', 
      (
         HalalStatus.Value = "Expired" || 
         HalalStatus.Value = "Expiring"
      ) && 
      Halal.Value = "Yes" &&
      ( 
         (
            gvCurrentUser = "FIW Approver" &&
            (
               Len(cbCustFilter_Ing.Selected.Value) = 0 ||
               Customer.Value = cbCustFilter_Ing.Selected.Value
            )
         ) ||
         Customer.Value = gvCustomer
      )
   ),
   "Title"
)

 

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.

Visit my blog Practical Power Apps

Thanks @WarrenBelz - works like a charm 😃

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

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