Delegation warning for calculated column

[aldenniklas]

According to this article: https://docs.microsoft.com/en-us/connectors/sharepointonline/ the calculated column should count as a text column and text columns are able to delegate. Still, I get delegation warnings when trying to filter on a calculated text field.

Any suggestions?

[WarrenBelz]

Hi @aldenniklas ,

My experience with SharePoint calculated columns is that they are not delegable, however as the calculation is generally available with connected PowerApps data sources, you should be generally be able to do the same filter in PowerApps.

Also here is a post I did earlier this week on the same issue.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Pstork1]

Can you show us what your filter statement looks like?  It might not be that the column is calculated that is causing the issue.  Also, when you created the calculated column was it created to hold a value of text.  you can choose what kind of data type the calcuated column will hold.  Text is the default, but its not the only option.  The screenshot below shows the options available when creating the calculated column.

[Eelman · Accepted solution]

@aldenniklas

@WarrenBelz  is correct, calculated columns are not delegatable. I've had a similar experience trying to use them.

The docs on this are a little fuzzy. Yes, they say Calculated columns are treated as 'Text' but that is only inside the app. This is why it says this in the 'mapping' table of the docs. When you try to delegate PA functions to calculated columns they are no longer treated as text, but as calculated columns, if that makes sense?

In a database sense, you are best trying to avoid them where possible. I'll add another tip, if I may, try to avoid Yes/No columns as well. If you need a Yes/No column, use a Choice column and only use Yes/No as options.

[aldenniklas]

Then we share the same experience.

I wrote a long post with a detailed explanation of my exact problem but for some reason it was marked as spam.

Basically what I want to do is filter a SharePoint list from a date column. Since the date column is not delegable I added a calculated column formatting the date as a string like this: yyyy-mm-dd

The only work around I've seen is using Flow to add a text version of the date but that is not quick enough as I want the users to be able to view their entries directly. I will probably have to replace the date with a string instead and then re-configure all the forms and galleries in the app.

[aldenniklas]

I have tried making as simple a statement as possible to avoid errors:

Filter(MySharePointList, CalculatedColumn="2020-02-14"))

The actual filter I want to use is a bit more complicated but I get the same issue with this simple version.

I am using the "Single line of text" option of course.

[aldenniklas]

Ok, I will have to find a different solution then. Thank you for the answer.

Yes, I seldom use the "Yes/No" for this reason. They have got better though, the Person column works pretty well with delegation nowadays.

[aldenniklas]

If anyone finds this and wants to know how to work around it I see two possible options:

1. Add a Flow that automatically adds a string version of the date to a separate columns. There are multiple guides for this, however it isn't fast enough if you want users to instantly see their input.

2. Add a new string column, use Flow to copy the date column into this one and then re-configure the forms and galleries in the app to work with a string value instead of a date value.

[WarrenBelz]

Ok @aldenniklas ,

Glad to hear you have fixed it.

Another thought with dates is that a numeric column based on yymmdd either on a hidden card reset, defaulted and submitted or Patched can be set whenever a date is entered or changed in a date picker. This gets around all date delegation issues.

[Eelman · Accepted solution]

@aldenniklas , no need for Flow, @WarrenBelz 's response is all you need. 

I've started using extra date columns in my SP lists which are double ups for any date format columns I may have, using the principals that @WarrenBelz set out.

The only difference is that is use the format yyyymmdd. As per @WarrenBelz response, I set this as a number column. Numbers are delegatable which means you can use '<', '>', etc on these columns, essentially filtering dates how you want.

Only '=' is delegatable in Date columns so this makes date ranges much more accessible.

I also include these columns in any Patch, Collect, etc back to SP.  For example, this is the formula I use for one of these fields (usually hidden) when making reference to a DatePicker in Form:

Value(Text(DatePicker1.SelectedDate, "yyyymmdd"))

Note: to retrofit these columns in my SP lists I did use Flow but that was only to set them up, not use on a continual basis

I also think @WarrenBelz solution is worth a tick, you can tick more than one response as the solution 🙂