cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aldenniklas
Level: Powered On

Delegation warning for calculated column

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Eelman
Level 10

Re: Delegation warning for calculated column

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

View solution in original post

Eelman
Level 10

Re: Delegation warning for calculated column

@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 🙂 

 

View solution in original post

10 REPLIES 10
WarrenBelz
Level 8

Re: Delegation warning for calculated column

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.

Dual Super User
Dual Super User

Re: Delegation warning for calculated column

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.

screenshot.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Eelman
Level 10

Re: Delegation warning for calculated column

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

View solution in original post

aldenniklas
Level: Powered On

Re: Delegation warning for calculated column

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
Level: Powered On

Re: Delegation warning for calculated column

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
Level: Powered On

Re: Delegation warning for calculated column

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
Level: Powered On

Re: Delegation warning for calculated column

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
Level 8

Re: Delegation warning for calculated column

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
Level 10

Re: Delegation warning for calculated column

@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 🙂 

 

View solution in original post

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps 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

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,424)