cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Help writing filter statement to avoid Delegation issue...

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!

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

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.

View solution in original post

Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

@Drrickryp,

 

Thanks for your continued dialog!

 

Yes!  Indeed it does.  Hey... please have a look.  I think this is the great method your suggested.  For others that may be insterested in this subject.

 

OnVisible:

Concurrent(
ClearCollect(MDL_List1,Filter(SortByColumns(Master_Drawing_List,"ID",Ascending),ID>0)),
ClearCollect(MDL_List2,Filter(SortByColumns(Master_Drawing_List,"ID",Descending),ID>0))
)

 

This provided "merging" the two lists, with no repeats.

ClearCollect(MDL_All,MDL_List1,Filter(MDL_List2, Not(ID in MDL_List1.ID)));Clear(MDL_List1);Clear(MDL_List2)

 

 

Props to @Drrickryp for inspiring the answer above.

View solution in original post

13 REPLIES 13
Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

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.  Capture.PNGIn 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. Capture2.PNGCapture3.PNG

 

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.

 

 

Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

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.

Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

@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/26...

 

 

Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

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.
Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

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?

Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

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.

View solution in original post

Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

So, in essense, this would create the ability to do 4,000 items?  Is that your scheme here?

Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

In essence yes. And with collections all searches are instantaneous
Super User
Super User

Re: Help writing filter statement to avoid Delegation issue...

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.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,080)