Filter/Lookup Gallery items by all items in Multiline Text Box (Delegation Error)

(0) Share

Report

Posted on by AchalDesai

Hi, 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 delegation queries.

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.

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

v-albai-msft on at

Like (0)

Report
Hi @AchalDesai
Do you want to filter list items based on the value in your search box based on your 'Brand Package Reference' column?
I assume that your 'Brand Package Reference' column is a Text column and I do a test on my side, you can refer to my formula:
Just set Items property of your Gallery to:
Filter('Lost Packages Log','Brand Package Reference' in Split(TextInput3.Text,Char(10)) .Result)
Notes:
1. You can directly use Split(TextInput3.Text,Char(10)) inside the Filter() function, I think it is no need to create one more collection to keep the results from multi TextBox.
2. For Delegation warning, it is just a warning, not an error. If the number of your list item is not too large(does not exceed 2000), you can ignore the delegation warning. If you have a large list or you do not want to see the warning, please consider save your SP list into a Collection, then use the Collection as data source within your app instead of your original SP list.
Best Regards,
Allen

Was this reply helpful? Yes No
AchalDesai 28 on at

Like (0)

Report

Hi @v-albai-msft,
Thanks for the reply.
The delegation warning is something I need to consider. The list is currently blank with only test data in it.
But once I create the app and users start using it, the list will definitely host more than 2000 records.

So I guess moving the whole list to a collection on application start would be the best way to do it.
I will put in a process to move closed records to an archive list to keep the operational list as small as possible.

Can you please help me on how to go about importing the whole list as a collection?
And then how would I update/edit the records in the collection and then write the updates/edits back to the sharepoint list?

Best Regards,
Achal

Was this reply helpful? Yes No
Verified answer

v-albai-msft on at

Like (0)

Report
Hi @AchalDesai ,
For your first question, if your filter result is less than 2000(or your archive list is less than 2000 records), you don't need to worry about the delegation issue. You can directly display the result in your gallery.
If your archive list or your filter result is more than 2000, you can use multiple Collect()/ClearCollect() function to help you avoid delegation issue.
For example, If your archive list has 5000 records, you can use below formula to save all the records into a collection:
Concurrent( ClearCollect(Col1, Filter('YourSPList', ID >= 1 && ID <= 2000)); ClearCollect(Col2, Filter('YourSPList', ID >= 2001 && ID <= 4000)); ClearCollect(Col3, Filter('YourSPList', ID >= 4001 && ID <= 6000)) ); Collect(MergedCollection; Col1; Col2; Col3)
Then you can directly use "MergedCollection" for your gallery as the datasource.
For more information about collection, you can refer to this article:
https://powerappsfornewbies.com/what-is-a-powerapps-collection/
For more about PowerApps delegation, I would suggest you refer to @WarrenBelz’s blog:
Power Apps Delegation – SharePoint – Practical Power Apps

For your second question, you should directly do actions on your list, then recreate the collection from the list.
For example,I add another “Patch” button to create a new list item into list5, then click “CreateCollection” button again to refresh the “collist5” collection, then new list item will display in the your gallery.
Best Regards,
Allen

Was this reply helpful? Yes No