cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kouliscon
Helper V
Helper V

combine filter text box with combo drop down

hello all,

 

i have a text box and i am using the below formula to filter data at a DataTable:

SortByColumns(Filter(Helpdesk,( StartsWith(Requestor.DisplayName , FilterBox.Text)) Or (StartsWith(Subject , FilterBox.Text))),"Request_x0020_Date",Descending)

 

i would like also to add at the formula a combobox, any ideas of how the formula should be modified?

 

thank you in advance

19 REPLIES 19

hello @v-qiaqi-msft  and thank you for your reply,

 

maybe i am not very clear,

 

Capture.PNG

 

from the picture above what i want is:

 

  • when i input data at 1 to then return data
  • when i select from 2 to then return data
  • if i input data at 1 which will return data and i select something from 2 to then return different data

 

tried your formula and i didnt work. i have to press "space" at 1 in order for 2 to work after i selected something.

 

hope is clear

 

BCBuizer
Super User
Super User

Just something to try, basically using nested If functions for the four different scenarios: 

1 empty, 2 empty: show the whole list

1 filled, 2 empty: Your original code

1 empty, 2 filled: Filter for status

1 filled, 2 filled: Combining both above

 

 

If(
	And(IsBlank(FilterBox.Text), Or(IsEmpty(ComboBox1.Selected), IsBlank(ComboBox1.Selected))),
	Helpdesk,
	
	If(Or(IsEmpty(ComboBox1.Selected), IsBlank(ComboBox1.Selected)),
		SortByColumns(Filter(Helpdesk,( StartsWith(Requestor.DisplayName , FilterBox.Text)) Or (StartsWith(Subject , FilterBox.Text))),"Request_x0020_Date",Descending),

		If(IsBlank(FilterBox.Text),
			SortByColumns(Filter(Helpdesk,(Status=ComboBox1.selected.value), "Request_x0020_Date",Descending),
			
			SortByColumns(Filter(Helpdesk,(Status=ComboBox1.selected.value) And ( StartsWith(Requestor.DisplayName , FilterBox.Text)) Or (StartsWith(Subject , FilterBox.Text))),"Request_x0020_Date",Descending),
		)
	)	
)

 

 

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

hello @BCBuizer 

 

that is the idea and thank you for your thought but i believe the formula needs some review as is not working.

let me know pls when you can.

thx

@BCBuizer  hello, any update on the formula above?

BCBuizer
Super User
Super User

I have tested the nested If functions and those are working for me, so I assume the issue is in the Sort / Filter part. Can you please describe what it is that is not working?



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

hello @BCBuizer 

 

i am attaching a picture of the results of when i paste the formula

 

Capture.PNG

 

as you can see the last lines have the error red line. i tried to look at the parenthesis but no luck till now.

one small comment the correct name is "FilterBox_1" so that is the only difference with your formula.

 

let me know

Hi @kouliscon ,

Firstly put "" (empty string) as the Default of FilterBox and then try this

SortByColumns(
   Filter(
      Helpdesk,
      StartsWith(
         Requestor.DisplayName,
         FilterBox.Text
      ) ||
      StartsWith(
         Subject , 
         FilterBox.Text
      ) && 
      (
         Len(ComboBox1.Selected.Value) = 0 ||
         Status.Value=ComboBox1.Selected.Value
      )
   ),
   "Request_x0020_Date",
   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.

@WarrenBelz  i tried it based on your instructions and it doesnt seem to work the filterbox. If i input any value at the filterbox it wont return anything. if i press space at filterbox and then select the combobox will return value.

 

thanks

Thanks @kouliscon ,

It should work (but obviously does not for you) - try adding this

SortByColumns(
   Filter(
      Helpdesk,
      (
         Len(FilterBox.Text) = 0 ||
         (      
            StartsWith(
               Requestor.DisplayName,
               FilterBox.Text
            ) ||
            StartsWith(
               Subject , 
               FilterBox.Text
            ) 
         )
      ) &&
      (
         Len(ComboBox1.Selected.Value) = 0 ||
         Status.Value=ComboBox1.Selected.Value
      )
   ),
   "Request_x0020_Date",
   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.

 

nailed it @WarrenBelz  thank you!!! worked as 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

Check out the New Ideas Site

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

Top Solution Authors
Top Kudoed Authors
Users online (3,630)