Unanswered

Workaround for 'IN' function

(0) Share

Report

Posted on by mmollet

3,187

I have a consignment app that is going to be used to replace excel as I hate when companies use excel as data input and database all in one. That being said they want to be able to filter the gallery by name. This is easily done however I want it to be dynamic as they type which requires the in operator as far as I know. Is there any work around for this? Here is my code:

If(IsBlank(TextInput1) And IsBlank(TextInput1_1), Consignment, If(!IsBlank(TextInput1), Filter(Consignment, TextInput1.Text in Name), etc...

Is there anyone to replace the part in blue with something that is delegable and wont mess up in the future?

TYIA!

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Drrickryp Super User 2024 Season 1 on at

Like (2)

Report

@mmollet

Both "in" and Search() are not delegable in SharePoint. If you can use StartsWith(), then you are in business. @WarrenBelz 's blog Practical PowerApps discusses Delegation in SharePoint and has several work arounds depending on the size of the list. However, with larger lists, the work-arounds become cumbersome and require increasing time to implement them and strain the resources of the handheld devices. Otherwise, you will need to move to a premium service like Dataverse or SQL. My dad used to tell me, "You can't get a Rolls Royce for the price of a Yugo"

Was this reply helpful? Yes No
SimnaJ 205 on at

Like (0)

Report

One possible workaround is to use the StartsWith function instead of the in operator. This would allow the gallery to filter dynamically as the user types, while still being delegable and efficient.
Note that the StartsWith function is delegable. However, keep in mind that the StartsWith function is case-sensitive, so you may need to adjust your code accordingly.
Here's an example of how you could modify your code using StartsWith:
If(IsBlank(TextInput1) And IsBlank(TextInput1_1), Consignment, If(!IsBlank(TextInput1), Filter(Consignment, StartsWith(Name, TextInput1.Text)), etc...
This code will filter the Consignment data source to show only the records where the Name field starts with the text entered in TextInput1.
If you feel that my response has solved your problem, please mark it as the solution and please consider giving it a thumbs up. This will help other community members find the solution quickly if they have a similar issue in the future.

Was this reply helpful? Yes No
mmollet 3,187 on at

Like (0)

Report

@Drrickryp @SimnaJ

What I decided to do was to use a combobox to have the user select an auction name. This then filters the SP list (much bigger than 2000 items) down to just entries for that auction. That gallery is blank and invisible so its just an intermediate step to further narrow the list down. This also allows me to then filter the main gallery using my other textinputs in conjunction with the outputs of that pre-filtered gallery while at the same time making the IN function 'delegable' as its doing it locally at that point. My filtered results from the first hidden gallery filter are almost always below 500 entries.

Are there any negative side effects I am missing if I continue with development like this?

Gallery2 - hidden gallery with code -> Filter(Consignment, Auction = ComboBox1.Selected.Value)
Gallery1 - main gallery that now only has to filter the output of the above gallery which is always < 1000
2 TextInputs - both used in nested if statements to filter Gallery1 as the user needs to

TY for the help!

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@mmollet

You can safely ignore the delegation warning as long as you filter down the list below the items restriction in the Settings prior to applying a Search() or "in" operator. If you want to remove the warning icon, do the "pre-filtering "inside a With() function. It doesn't prevent delegation from affecting your formula but it removes the annoying yellow triangle. Kind of like putting a snooze button on a fire alarm.

Was this reply helpful? Yes No
DS1060 77 on at

Like (0)

Report
for text field, I would go with the function Search instead of Filter.
Filter is delegable on text field only with "=" or "Startswith()".
So I would go with a
Search(Consignment, TextInput1.Text, "Name")
~~Filter(Consignment, TextInput1.Text in Name)~~
"If 'SearchString' is found within the data of any of these columns as a partial match, the full record will be returned."

Ref: Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform | Microsoft Learn

Was this reply helpful? Yes No