cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rob_CTL
Helper V
Helper V

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

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

3 REPLIES 3
mdevaney
Super User
Super User

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

v-xida-msft
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

 

pa-calcol.jpg

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:

 

pa-hol-list.jpg

And the function that I am running looks like this:

 

pa-filtercode.jpg

 

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.

 

pa-filterapproval.jpg

 

Any help gratefully received.


Rob

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,979)