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

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,344)