filter datatable results based on a Text Input field

[kriscachia]

Hi all,

I need help with something I have been trying to do for a while now. I have a text Input field and a data table. I simply want to input something in the Text Input (eg: Yogurt), then in this datatable I need to list only data which has the word yogurt in it. 

The only way i solved it temporarily is using StartsWith

Here is the code i have: 

Filter([@Products], StartsWith('Product',TextInput4.Text) &&Item_Category.Value=DataTable6_1.Selected.'Item Category')

TextInput4 is the field where I am Inputting the word I want to look for eg: Yogurt

StartWith is the method i am using to filter, but the word (Yogurt has to be at the start for the filter to work). I want something instead of StartWith which will work throughout the entire product field) 

(ignore the code after && because i am using it to filter categories)

What can i use instead of StartsWith ? 

Any help would be greatly appreciated 🙂

Many thanks 

[mmollet · Accepted solution]

What you looking for is the In function. This allows you to search for a substring in a string or an object in a table/array etc. The issue with In is that it wont be delegable so depending on how big your data source is this may cause you issues. Here is an example of how it can work:

You want to find Yogurt in any string in the list. 

UpdateContext({numEntries: CountIf(YourListName, "yogurt" In FoodName)})

The variable numEntries now stores the number of times "yogurt" was found in the FoodName column. The issue is that this only looked at the first 2000 items in the data source. If this is ok due to the data source having less than 2000 items then you are good to go, but if you have more than 2000 items we will have a little more work to do to get to your solution. 

The idea if that needs to be done will be to either wrap the non-delegable function to allow it to work or to pull down the data source to a local collection where we can use the In operator without delegation issues. 

[dsagfhjj · Accepted solution]

try this:
Filter([@Products], TextInput4.Text in Product &&Item_Category.Value=DataTable6_1.Selected.'Item Category')

[kriscachia]

thanks for code, works well, delegation should not be a problem because i will not have large data set. But eventually will need to know how to fix that too as i will have the issue in other requirements.

thanks alot for help