cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Asdetevo
Microsoft
Microsoft

Filtering in a Search Function

Hi All, 

 

I have a search function that works off of an IF statement and a dropdown, where IF a sales person's name is selected from a dropdown, it filters our database on their specific deals, and if no sales person's name is selected, it shows all deals present. I need to add another filter to allow sales people to only see the "open" deals they're working, using a separate dropdown, and I'm having trouble combining the formulas. The search function is as follows: 

 

Search(If(DealConsultantDropdown.SelectedText.Value="Select an option",

               SortByColumns('[DWR].[vwCases_Innovation_App_Triage]',"Date",Descending),

               SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value),"Date",Descending)

         )

       ,SearchBox.Text,"CustomerName","CaseId"

       )

 

and my filter function on just the dropdown is this: 

 

If(Dropdown_1.Selected.Value = "Open Cases", Filter('[DWR].[vwCases_Innovation_App_Triage]','Case Ask Status' = "Open")

How do I combine these two to retain full functionality? Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

HI @Asdetevo

Do you want to filter the data source in this rule?

Case1: DealConsultantDropdown.SelectedText.Value="Select an option" && Dropdown_1.Selected.Value = "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]','Case Ask Status' = "Open"),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case2: DealConsultantDropdown.SelectedText.Value<>"Select an option"&&Dropdown_1.Selected.Value = "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value && 'Case Ask Status' = "Open"),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case3: DealConsultantDropdown.SelectedText.Value="Select an option"&&Dropdown_1.Selected.Value <>"Open Cases"

 

Search(SortByColumns('[DWR].[vwCases_Innovation_App_Triage]',"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case4: DealConsultantDropdown.SelectedText.Value<>"Select an option"&&Dropdown_1.Selected.Value <> "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

If so, I suggest you try this code(Nesting of If functions)

 

Search(
If(
  Dropdown_1.Selected.Value = "Open Cases",
  Filter(       /*If Dropdown_1.Selected.Value = "Open Cases", filter out the records that meet the 'Case Ask Status' = "Open") condition and records*/
   SortByColumns(
    If(
     DealConsultantDropdown.SelectedText.Value="Select an option",  /* Nesting of If functions */
     '[DWR].[vwCases_Innovation_App_Triage]',
     Filter(
      '[DWR].[vwCases_Innovation_App_Triage]',
      DCName=DealConsultantDropdown.SelectedText.Value)
      )
     ,"Date",Descending
    ),
   'Case Ask Status' = "Open")
  ,
  SortByColumns(
   If(                                                        /* Nesting of If functions */
    DealConsultantDropdown.SelectedText.Value="Select an option",
    '[DWR].[vwCases_Innovation_App_Triage]',
    Filter(
     '[DWR].[vwCases_Innovation_App_Triage]',
     DCName=DealConsultantDropdown.SelectedText.Value)
     )
    ,
   "Date",Descending)
  )
,SearchBox.Text,"CustomerName","CaseId"
)

 

Best Regards,

Bof

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

HI @Asdetevo

Do you want to filter the data source in this rule?

Case1: DealConsultantDropdown.SelectedText.Value="Select an option" && Dropdown_1.Selected.Value = "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]','Case Ask Status' = "Open"),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case2: DealConsultantDropdown.SelectedText.Value<>"Select an option"&&Dropdown_1.Selected.Value = "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value && 'Case Ask Status' = "Open"),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case3: DealConsultantDropdown.SelectedText.Value="Select an option"&&Dropdown_1.Selected.Value <>"Open Cases"

 

Search(SortByColumns('[DWR].[vwCases_Innovation_App_Triage]',"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case4: DealConsultantDropdown.SelectedText.Value<>"Select an option"&&Dropdown_1.Selected.Value <> "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

If so, I suggest you try this code(Nesting of If functions)

 

Search(
If(
  Dropdown_1.Selected.Value = "Open Cases",
  Filter(       /*If Dropdown_1.Selected.Value = "Open Cases", filter out the records that meet the 'Case Ask Status' = "Open") condition and records*/
   SortByColumns(
    If(
     DealConsultantDropdown.SelectedText.Value="Select an option",  /* Nesting of If functions */
     '[DWR].[vwCases_Innovation_App_Triage]',
     Filter(
      '[DWR].[vwCases_Innovation_App_Triage]',
      DCName=DealConsultantDropdown.SelectedText.Value)
      )
     ,"Date",Descending
    ),
   'Case Ask Status' = "Open")
  ,
  SortByColumns(
   If(                                                        /* Nesting of If functions */
    DealConsultantDropdown.SelectedText.Value="Select an option",
    '[DWR].[vwCases_Innovation_App_Triage]',
    Filter(
     '[DWR].[vwCases_Innovation_App_Triage]',
     DCName=DealConsultantDropdown.SelectedText.Value)
     )
    ,
   "Date",Descending)
  )
,SearchBox.Text,"CustomerName","CaseId"
)

 

Best Regards,

Bof

Thank you so much, I had this most of the way but I was struggling nesting my functions. This seems to be working great!

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (2,020)