SharePoint list FILTER function delegation problem

(0) Share

Report

Posted on by Lucian

256

Hello,

I would like to get rid of the delegation warning when using FILTER function on a SharePoint list.

Basically, I need to avoid duplicate values for some fields (like Phone Number or Email Address) when adding new records or modifying existing records.

So, I try to CountRows over a Filter, avoiding the current record

But in this case the "<>" (not equal) operation seems that cannot be delegated even I'm using a numeric data type column.

According to Microsoft documentation Power Apps delegable functions and operations for SharePoint it seems that "<>" can be delegated for a numeric column except when the SharePoint ID is involved and in this case only "=" is supported for delegation.

But using "=" is even worse - the entire formula gets highlighted:

I try to avoid to create a collection at form opening because it might take a lot of time adding/modifying a record and other users could enter data that could break the uniqueness requirement.

Is it possible to avoid the delegation warning?... or I just have to ignore it?

Kind Regards,

Lucian

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (2)

KvB1 1,596 on at

Like (0)

Report

Are you using the native ID assigned to an item by sharepoint itself or is this a column that you created?

Was this reply helpful? Yes No
Lucian 256 on at

Like (0)

Report

Hi @KvB1 ,

I'm using the native ID. I did not create that ID column.

Kind Regards,
Lucian

Was this reply helpful? Yes No
KvB1 1,596 on at

Like (0)

Report

I think the CountRows function is not delegable. This means that if you would ask PowerApps to count a number of rows that is larger than 2000, it will simply return 2000.

Would it be possible for you to do a LookUp instead? If i understand correctly your goal is to make sure there are no records with a certain value in the sharepoint list, but excluding the current record?

In that case if the LookUp returns anything, you know that there is atleast one row which holds duplicate information excluding your current item. Is that sufficient, or do you really need to know how many rows hold duplicate records?

Was this reply helpful? Yes No
Verified answer

CU-18081211-6 9,270 Moderator on at

Like (1)

Report
@Lucian ,

Check for the duplicate values on OnChange property of the control where you collect the sensible values.

Example, for email adresses - use the OnChange property to check if in the list there is already registered the current email adress with something like:

UpdateContext( {emailDuplicateFound: !IsBlank( LookUp(yourlist,emailColumn=ControlForForm.Text) ) } )

You can set the display mode of the submit button to disabled if emailDuplicateFound is true.

Hope it helps !

Was this reply helpful? Yes No
Lucian 256 on at

Like (0)

Report

Hello!

@gabibalaban thank you for your suggestion, but this approach is possible only for new records, for existing records LookUp function will hit the current item, so I will get a "false positive".

Trying to avoid the current item, will get me the same warning as before.

@KvB1 - you are right - it seems that CountRows cannot be delegated, but in this case I cannot understand why the delegation highlighted only the "ID<>" part from the Filter function.

It seems that the only thing I could do is to hope for the best and the "Planned" status of this ideea - Extend delegation to Sum and CountRows - would be soon changed to "Done" 😁.

Or just ignore the warning as the list should not reach 2000 items in the near future...

Kind Regards,
Lucian

Was this reply helpful? Yes No
KvB1 1,596 on at

Like (0)

Report

@Lucian
You are almost there, all you have to do is say !ID=ThisItem.ID instead of ID<>ThisItem.ID, and you shouldnt get any delegation error.

Was this reply helpful? Yes No
Lucian 256 on at

Like (0)

Report

Hi @KvB1 ,

Unfortunately it seems that any "negation" cannot be delegated (according to Microsoft documentation) 😞

Was this reply helpful? Yes No
KvB1 1,596 on at

Like (0)

Report

I couldn't find that in documentation. It does seem that IsBlank( definately isnt delegatable.

You could try Len(LookUp(WhatYouHave).Title)>0. This should also return true if a record already exists that matches your LookUp requirement

Was this reply helpful? Yes No
CU-18081211-6 9,270 Moderator on at

Like (0)

Report

@Lucian

Take a thought, we talk about OnChange property and this suppose that the value is changed …

Was this reply helpful? Yes No
Lucian 256 on at

Like (1)

Report

Hi @gabibalaban ,

Sorry, my mistake - I have all the "error logic" into the "ErrorMessage" label and missed your mention to the OnChange property.
Putting the uniqueness check into OnChange property and updating an variable used in the ErrorMessage label it seems to work and this way will avoid that delegation warning.

Thank you.

Lucian

Was this reply helpful? Yes No