Filter a Data Table by if a text input contains specific text

[ngreen]

Hello,

I have a data table hooked up to a large sharepoint document library of files being constantly moved in and out of the library. The users want to be able to use a textinput to search the filter the datatable to view specific documents that meet their criteria. They are searching a sharepoint text column of manually entered keywords that could be entered in any order. Currently, I have this as part of the Items property for the datatable:

(IsBlank(TextInput1.Text) || StartsWith('Column1', TextInput1.Text))

The issue is that it only filters results if you type the words in the correct order, rather than just typing any word and it filtering by records that contain the word. So if I search "text" and the entry has "text label" in the column, it will show it as a result. But if I search "label" then it will not show it as a result. 

Is there a way to filter results if it contains anything put into the textinput?

[madlad]

Hi!

I think that here you could try using the search function, with something like:

Search(*YourTable*, TextInput1.Text, 'Column1')

You can see more about the search function here

Hope this helps!

[ngreen]

I can try it. I worry about running into 2,000 row limitations, so I'll have to see if I can force it to delegate the search to sharepoint with a filter, unless you have any ideas.

[WarrenBelz]

Hi @ngreen ,

You are correct that you will struggle with large Lists/Libraries unless you have metadata fields that allow you "pre-filter" the list to under 2,000 records as Search() is not Delegable. The structure would be as in this blog of mine

[ngreen]

Hello @WarrenBelz,

Thanks for coming in to help. I just wanted to try and sort this out a bit more, as I'm a bit confused/having trouble on the approach to this.

My original datatable Items formula is this:

Filter(

    'List1',

    (IsBlank(Column1Textbox.Text) || StartsWith('Column1', Column1Textbox.Text)) &&

    (IsBlank(Column2Input.Text) || StartsWith('Column1', Column2Input.Text)) &&

    (IsBlank(Column3Input.Text) || StartsWith('Column3', Column3Input.Text)) &&

   NOTE THIS FIELD IS NOT YET SET UP IN SHAREPOINT -> // (IsBlank(Column4ComboBox.Selected) || Column4.Value = Column4ComboBox.Selected.Value) &&

    (IsBlank(Column5ComboBox.Selected) || Column5.Value = Column5ComboBox.Selected.Value) &&

    (IsBlank(Column6ComboBox.Selected) || Column6.Value = Column6ComboBox.Selected.Value)

)

With this formula, I am currently able to filter my sharepoint list by any combination of inputs into the 6 fields and see the filtered data in the datatable. I need the Search function for the Column 2 and Column 3 text inputs so that I can get a filtered result based on any data put into the input. Column 3 can be any combination of keywords someone would manually type. 

Just substituting Search in for the StartsWith for Column 2 & 3 yields "Invalid argument type. Expecting one of the following: Boolean, Number, Decimal, Text, Untyped Object."

Since I do not know the size of the list, are you recommending that I use your blog's Collect All Records In List method? How does this work with multiple search inputs? I was recently informed that the list may never exceed 5,000 or even 2,000 records (although I'd prefer to be set up for the possibility of more than 2,000 records). Should I worry about the performance impacts of the collect all method? I think my user's would be very upset to see a drastic hit to performance.

[WarrenBelz · Accepted solution]

Hi @ngreen ,

Many things to consider here, the majority of which will be determined on the ultimate size if the data, however I will start here. Firstly set the Default of Column1Textbox to "" (empty string) - it just saves a line of code. Now your Filter

With(
   {
      _Data:
      Filter(
         'List1',
         StartsWith(
            'Column1', 
            Column1Textbox.Text
         ) &&
         (
            Len(Column4ComboBox.Selected.Value) = 0 || 
            Column4.Value = Column4ComboBox.Selected.Value
         ) &&
         (
            Len(Column5ComboBox.Selected.Value) = 0 || 
            Column5.Value = Column5ComboBox.Selected.Value
         ) &&
         (
            Len(Column6ComboBox.Selected.Value) = 0 || 
            Column6.Value = Column6ComboBox.Selected.Value
         )
      )
   },
   Filter(
      _Data,
      (
         Len(Column2Input.Text) = 0 || 
         Column2Input.Text in 'Column2'
      ) &&
      (
         Len(Column3Input.Text) = 0 || 
         Column3Input.Text in 'Column3' 
      )
   )
)

You will not have a Delegation warning on this, however it may also not be a total solution as output of the top Filter (the data can be of any size) is limited to your Data Row Limit. The bottom filter is executed "locally" and therefore not subject to Delegation.

Your second question is very relevant as well. The "collect all records" function is useful, but certainly not a "fix all", particularly in terms of performance. I do not use it a lot (preferring Delegable functions or combinations as above), however I have found anything over double (4k) records has a considerable performance penalty which gets steadily worse with more fields in the Table and (particularly) more elements in the filter, going from slow to glacial.

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[ngreen]

@WarrenBelz,

Thank you for this! It does seem to be working. I did have to make an adjustment for the bit with Column1, as the column1textbox is actually populated based on a combobox selection (they had people entering in data poorly, so they switched it to a combobox selection that populates the textbox based on selection).

Just two quick follow up questions for you to make sure I'm understanding everything properly:

1. " top Filter (the data can be of any size) is limited to your Data Row Limit." 

So anything in the Column 4, 5, or 6 comboboxes can only show results from the first 2,000 entries in the SharePoint list and entry 2,001 and beyond would not be able to be displayed in the datatable. Is that correct?

2. "The bottom filter is executed "locally" and therefore not subject to Delegation."

So anything for column 1, 2, and 3 can be displayed regardless of if it's before or after 2,000 entries? Or is it lacking delegation in that it can't see anything over 2,000.

What happens if I try to find entry 2,001 using a combination of the top and bottom filters? The top filter makes it seem like it can't be found, but the bottom filter seems like it can find it if it doesn't have delegation limits. Users might be trying to find data using any combination of just one or more or all of the fields.

[WarrenBelz]

Hi @ngreen ,

1. No - you can filter any sized data (the filter is Delegable), however as you are then using the output to address non-Delegable functions "locally" the amount of data available to that function is limited to your Data Row Limit (much like a collection).

2. I think 1. answers this. You need to "pre-filter" as much as you can using Delegable filters - I have a blog on this - and (hopefully) get the dataset under your DRL so you can then perform a "client-side" operation when (because it is not querying the data source) Delegation is not an issue (other than the overall limit of records available as above).

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.

MVP (Business Applications)   Visit my blog Practical Power Apps