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
Resolver III
Resolver III

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),
		)
	)	
)

 

 

 

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
Resolver III
Resolver III

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?

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.

 

View solution in original post

nailed it @WarrenBelz  thank you!!! worked as a charm

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!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,653)