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

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,936)