cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hiru
Regular Visitor

Combine SortByColumns, Search, Filter and If Statement

Hi All, 

I have been trying to figure this out for hours but cannot do it. 

I am storing information in a Sharepoint list called Feedback App(test) and using that to connect to my App. 

In my Gallery, I have added the data in a tabular format and in the Screen, added two drop down lists and a search box that filter data in the Gallery. I have also added sorting buttons. This works completely fine. Please see my formula below (which I added into the Items property in my Gallery:

SortByColumns(Search(

If('drop down type'.Selected.Result = "Type" And 'drop down status'.Selected.Result = "Status",

 'Feedback App (test)',

'drop down type'.Selected.Result = "Type" And 'drop down status'.Selected.Result <> "Status",

Filter('Feedback App (test)', Status.Value = 'drop down status'.Selected.Result),

'drop down type'.Selected.Result <> "Type" And 'drop down status'.Selected.Result = "Status",

Filter('Feedback App (test)', 'Request Type (App)' = 'drop down type'.Selected.Result),

'drop down type'.Selected.Result <> "Type" And 'drop down status'.Selected.Result <> "Status",

Filter('Feedback App (test)', 'Request Type (App)' = 'drop down type'.Selected.Result And Status.Value = 'drop down status'.Selected.Result)),

'suggestion search'.Text, "Title"), SortPriority, If(SortDescending1, Ascending, Descending))



However, the issue arises when I want to add an additional Filter into that formula.  In my sharepoint list, I have a column called "Validity" with three status (Approved, Rejected and Pending). I only want the items which have an "Approved" status  in the Validity column in the Sharepoint list to be added into the app.  If I use this formula alone, it works:

Filter('Feedback App (test)', Validity.Value="Approved"


The issue is I do not know how to combine both together. 

Can anyone help?

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

 Hi @Hiru ,'

You might try this

SortByColumns(
   Search(
      Filter(
        'Feedback App (test)',
        If(
           'drop down type'.Selected.Result = "Type",
           true,
           Request Type.Value = 'drop down status'.Selected.Result 
         ) &&
         If(
            'drop down status'.Selected.Result = "Status",
            true,
            Status.Value = 'drop down status'.Selected.Result
         ) && Validity.Value="Approved"
      ),
      'suggestion search'.Text, 
      "Title"
   ), 
   SortPriority, 
   If(
      SortDescending1, 
      Ascending, 
      Descending
   )
)

 

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

6 REPLIES 6
mdevaney
Super User III
Super User III

@Hiru 

Like this...

SortByColumns(
    Search(
        If(
            'drop down type'.Selected.Result = "Type"
            And 'drop down status'.Selected.Result = "Status",
            'Feedback App (test)',

            'drop down type'.Selected.Result = "Type"
            And 'drop down status'.Selected.Result <> "Status",
            Filter('Feedback App (test)', Validity.Value="Approved", Status.Value = 'drop down status'.Selected.Result),

            'drop down type'.Selected.Result <> "Type"
            And 'drop down status'.Selected.Result = "Status",
            Filter('Feedback App (test)', Validity.Value="Approved", 'Request Type (App)' = 'drop down type'.Selected.Result),

            'drop down type'.Selected.Result <> "Type"
            And 'drop down status'.Selected.Result <> "Status",
            Filter('Feedback App (test)', Validity.Value="Approved", 'Request Type (App)' = 'drop down type'.Selected.Result And Status.Value = 'drop down status'.Selected.Result)
        ),

        'suggestion search'.Text, "Title"
        ),
        
        SortPriority, If(SortDescending1, Ascending, Descending
    )
)

 

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

WarrenBelz
Super User III
Super User III

 Hi @Hiru ,'

You might try this

SortByColumns(
   Search(
      Filter(
        'Feedback App (test)',
        If(
           'drop down type'.Selected.Result = "Type",
           true,
           Request Type.Value = 'drop down status'.Selected.Result 
         ) &&
         If(
            'drop down status'.Selected.Result = "Status",
            true,
            Status.Value = 'drop down status'.Selected.Result
         ) && Validity.Value="Approved"
      ),
      'suggestion search'.Text, 
      "Title"
   ), 
   SortPriority, 
   If(
      SortDescending1, 
      Ascending, 
      Descending
   )
)

 

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

Hi @WarrenBelz

Thanks for your quick reply and your help!!

At first, it didnt seem to work but then I realised there was an error in your formula:

If(
           'drop down type'.Selected.Result = "Type",
           true,
           Status.Value = 'drop down status'.Selected.Result 


Should have been 

If(
           'drop down type'.Selected.Result = "Type",
           true,
           Request Type.Value = 'drop down status'.Selected.Result 


After I fixed that, it worked perfectly.

Thank you so much!!:) 

Hiru
Regular Visitor

Hi @mdevaney 

Unfortunately, it doesnt seem to be doing anything at all:( I tried renaming it to whatever I wanted and the results still did not change. It just acts like the Validity.Value = Approved does not exist. Seems like something is overwriting it? 

mdevaney
Super User III
Super User III

@Hiru 

I will let @WarrenBelz continue with solving the question.


Thanks @Hiru ,

Was getting late and I was free-typing. I have fixed it in the post for the benefit of other viewing later.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (46,040)