Help writing filter statement to avoid Delegation issue...

(1) Share

Report

Posted on by martinav

3,347

Friends,

Yes, I have read all of the delegation documentation. However, that does not always provide enough to know how to use all of the tricks to make something delegable. There are a lot of creative folk out there that have some great ideas.

Actually, I have had good luck eliminating most of my delegation problems. This one remains, and its a really important one. It is in our main search app that must be completely clean. Here is my current statement:

FirstN(Filter(SortByColumns(Filter(Master_Drawing_List,StartsWith(MD_PartNumber,TextInput1_28.Text)),Dropdown2_8.Selected.Value,FlipSortDirection),MD_IsActive=!Checkbox1_4.Value And TextInput1_30.Text in Title And TextInput1_29.Text in MD_Project),100)

This is a multi text-box search statement. There may be searchable text in one or all of the text boxes. It works fine with the MD_PartNumber search, because its the inner filter, and is a delegable statement. However, it is when I am not searching for MD_PartNumber, but other criteria that I get in trouble, because those are affected by delegation issues.

I might need to have multiple statements, given which text-box is used to search. I get that. But, it would be nice to have some other options that would not leave me trying to find all of the combinations of text-box usages to get every possibility to work correctly.

You help would be GREATLY appreciated!

Categories:

Building Power Apps

All responses (13)

Answers (2)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @martinav,
I'd like to see if I can help with this one. As an overview, delegation is only a problem when directly filtering a datasource (the Sharepoint list in question) and then only if >2000 items are in the source. In my own experience, Sharepoint has a built in limit of 5000 items before some administrative limit is reached. If you can start with a filter that brings in less than 2000 items, you can then make it a collection and all of your nondelegatable functions will work. Let's examine your filter below:
FirstN(Filter(SortByColumns(Filter(Master_Drawing_List,StartsWith(MD_PartNumber,TextInput1_28.Text)),Dropdown2_8.Selected.Value,FlipSortDirection),MD_IsActive=!Checkbox1_4.Value And TextInput1_30.Text in Title And TextInput1_29.Text in MD_Project),100)

The highlighted functions in red are not delegatable. But it appears that you are filtering the MasterDrawingList by a parts number (the filter in green). If the result of that filter is always <2000 you are good to go. At this point, I would create the collection using ClearCollect(mdlfiltered,Filter(Master_Drawing_List,StartsWith(MD_PartNumber,TextInput1_28.Text)). You can then Filter it by the checkbox and use your Textboxes for further filtering on the Title and Project fields. In the example above, I used the magnifying glass icon to create the collection, ClearCollect(coldatasource,list). The gallery items property was set to filter coldatsource by the checkbox and the two textboxes.

In the example, the state was filtered by SearchInput29.Text in the State Column && SearchInput30.Text in the City Column. If the TextInput boxes are left blank, the entire list is visible. In order for the Edit and View forms on their respective screens to work, the DataSource property should remain as the original list name but the Items property will need to be changed to Lookup(Gallery1.Selected.ID=ID). If this resolves your issues, please mark this discussion as Solved.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your reply. The issue is that there are more than 2,000 items in the source. The search works perfect as long as the primary search criteria used is the MD_PartNumber (TextInput1_28) field. Since that inside search is delegable, and the result will always be below 2,000 then like you say, i'm golden. The problem is... if the primary search requred is for part name (TextInput1_30) that part of the search statement is outside of the delegable portion, and truncates if TextInput1_28 is blank.

The only way I can see out of this is to make a "primary search" pulldown, or radio button that will use if-statements to make the primary criteria a simple delegable filter (and perhaps pull the results into a collection as you suggest). I wanted to have the user able to use any, some, or all of the search criteria without having the complexity of fiddling with other selections.

I also kicked around the idea of trying to make something automatic that whatever field was entered first would be "primary". But still, results may be different based on the order a user enters their search criteria, which may not instill confidence on the quality of the data.

I'm definetely open to more suggestions.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp,

Here is another thought... if possible... I could use a collection independently for each search criteria. Thus, if you search PartNumber, you get one table, and search Name, you get another table. However, I would need the intersection of the two as the result. Like an Inner Join in SQL or something like that. I have very limited use of the collections. Possible?

Edited:
I will try this:

https://powerusers.microsoft.com/t5/General-Discussion/How-can-I-join-data-set-in-2-Galleries/m-p/267
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

How many items are we talking about and are there categories of the items? If there areless than 4000 items in the dataset there is another approach that can be taken.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

In this list, there are 2,394.

The only category would be the Active checkbox. Are you thinking of dividing search to catergories, and then bringing it together?
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

I’m thinking of creating two collection, one sorted Ascending and the other sorted Descending. Then merging the two collections with a !in function. To get all of the list items in one collection. Then filtering that by your check and comboboxes. If the collections and merge is done when the app starts or when the screen becomes visible, your filtering will be fast and efficient, transparent to the users.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

So, in essense, this would create the ability to do 4,000 items? Is that your scheme here?
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

In essence yes. And with collections all searches are instantaneous
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

Very createive. The exact kind of thinking I'm needing! By the time we get to 4,000 items... i hope delegation has evolved a bit more. Do you know of a way to get a list count? The only place I have seen it is in Sharepoint "Site Contents". It would be nice to put in a time-bomb to pop up when list starts getting close to a limit.
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

I’m not certain but my SharePoint account has a hard limit at 5000 items. It may be possible to set a lower limit in SharePoint but that’s not my expertise. If so SharePoint will drop the bomb when someone tries to exceed it. Another way is to add a SharePoint column for similar items that you can use to prefilter the list. Say in the case of produce, in a food store you could use beverages, poultry, fruits, etc to categorize each item. Then in the app you could have a dropdown that prefilters the list using = since equals is delegatable.