Unanswered

Advice needed: Dealing with calculated columns in SharePoint and avoiding delegation issues

(0) Share

Report

Posted on by Rob_CTL

Hi,

I am just after some advice or pointers to the best to deal with calculated columns in SharePoint and avoiding delegation warnings. One of the lists in question is using content approval and the PowerApp is filtering a gallery based on the approval status. I've looked at using Flow to update a seperate text column but this is overly complicated and doesn't work partularly well.

Thanks

Rob

Categories:

General Questions

All responses (3)

Answers (0)

Rob_CTL on at

Like (0)

Report

Re: Advice needed: Dealing with calculated columns in SharePoint and avoiding delegation issues

Hi @v-xida-msft

Thanks for the reply. To explain a bit more what I am trying to do (and if I am going the wrong way about it). I have a list that is used to track employee holiday bookings, this has a "start date" and "end date" date columns, these columns are populated by the user everytime an item is created.

Within the PowerApp I am trying to present this list in a more user friendly manner, and additional function that you can't do with standard List web part. One of the functions is to filter the date column by a given year. To do this I've got a dropdown box with "2019,2020,2021 etc" when a use selects a year the value of the dropdown is inserted into a filter. The calculated column is a used to get the Year from the "Start date" column as follows:

I am doing it this way because I couldn't find a solution that would do the same within PowerApps, at least not without triggering the same delegation warning.

So the PowerApps form looks like this:

And the function that I am running looks like this:

As mentioned in my original post I am seeing the exact same behaviour if I try to filter on the "Content Approval Status" column within the same list.

Any help gratefully received.

Rob
v-xida-msft on at

Like (0)

Report

Re: Advice needed: Dealing with calculated columns in SharePoint and avoiding delegation issues

Hi @Rob_CTL ,

Could you please share more details about your issue?

Do you add a Calculated field within your SP List, and you want to filter your data source records based on the Calculated field?

Currently, the result from Calculated field of a SP list would be acted as a Text type field within an app. Please check the Delegable function supported for Text field as below:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#powerapps-data-type-mappings

In addition, the Calculated field of a SP List would be acted as a Read-Only field in a PowerApps app, you could not assign a value to this column. The Calculate field value would be generated by SP System automatically.

Actually, it is not necessary to add a Calculated field in your SP list, instead, you could consider add a normal type field in your SP list, then do the calculation within your app, then save the calculation result back to the Calculated field in your SP List.

Best regards,
mdevaney 29,987 Super User 2025 Season 1 on at

Like (1)

Report
Re: Advice needed: Dealing with calculated columns in SharePoint and avoiding delegation issues

@Rob_CTL
My preferred way to deal with calculated columns is not to have them at all. To give an example: let's say you have 2 columns Sales and Costs. You want to create a 3rd column called Profit. One way to do this would be to created a calculated column that uses this forumla in the Sharepoint column settings.

[Sales] - [Costs]

I like to have Profit as a number type column instead. How would this work? Let's say a user creates a new entry using an edit form. I include the Profit DataCard but I set the Visible property to False to hide it from view. Then, I put this code in the Default property of the TextInput for Profit. When the form is submitted the Profit is pre-calculated and therefore does not require a complex column.

DataCardValue_Sales - DataCardValue_Costs

Other actions within the app may affect the Sales or the Costs of the transaction. Every time Sales or Costs change the Profit must be updated as well. We can use a PATCH statement to accomplish this.

Patch( your_datasource_name, LookUp(your_datasource_name,ID=ID_label.value), {Profit: Sales - Costs + (lbl_salesChangeAmount - lbl_costs_ChangeAmount)} )

Hopefully you find this example to be helpful.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."