PowerApps - Gallery Filter by all values in multiline text input - Delegation Error

0
votes

I am trying to create a simple CRUD app for my teams to create, View & update records in a SharePoint List.

I am fairly new to powerapps and don't know much about bypassing delegation queries/errors.

I am stuck at creating a view/update screen. I have a Multiline Text Input that the users will input multiple unique package numbers. The idea is that the gallery should filter the records from the SharePoint List matching the package reference numbers in the multiline text box. The user can then select all and update a status by clicking a button.

enter image description here

As seen in the image above, I want the gallery to be filtered for only the Brand Package Reference that are in the Multiline Text Search Box.

I tried to create a collection of the ref nos in the search box with the below formula.

ClearCollect(FilterList,{RefNos: Split(Substitute(TextInput2.Text," ",","),",")})

Then tried a filter function with a ForAll as below. But this didn't work. It gave me a delegation error.

ForAll('Lost Packages Log', Filter('Lost Packages Log',FilterList.RefNos = 'Brand Package Reference'))

Can someone please help me create a formula that would help me achieve my goal.

1
sharepointfilterlookuppowerapps

1 Answers

0
votes

Sounds like you have two issues:

  • Delgation
  • Filter a column by multiple values in a text box.

To get around the delegation errors, you need to keep the dataset below 2k records (assuming you set max records to 2000 in File/Settings/Advanced Settings/Data row limit...).

One way to do this is to create a collection of records OnVisible of the screen, that will always be below 2k.

Example:

ClearCollect(colRecords,
    Filter(yourSharepointList, 
        StartsWith(status, "urgent")
    )
)

The StartsWith() function works on text columns and is delegable to Sharepoint. Once you get your record count under 2k at all times, you can address the multi-line filter.

This will filter the gallery by items entered in the search box:

Set the Gallery Items property to:

If(
    IsBlank(TextInput1.Text),
    colSample,
    Filter(
        colSample, 
        packingNumber exactin  Split(
                            TextInput1.Text, 
                            Char(10)
                          ).Result
    )
)

Loosely translated:

  • If the search box is blank, show all records.
  • Otherwise, filter the collection of records where the packingNumber is exactly in the results of splitting the text box by newline characters.

Results in: enter image description here

(here is a sample app though the hosting service will only serve it up for 30 days)