cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Superelastic
Frequent Visitor

Updating a calculated field in a SharePoint list (that old chesnut)

 Hi all

 

I'm building an action tracking app.  Actions are stored in a Sharepoint list with a calculated field which works out whether an action is overdue.

 

My initial understanding was that the calculated field in SharePoint would update on query (i.e. collectActions) however, further investigation shows that the field is only updated if the formula is edited in SharePoint or the record is edited and saved.  I've read *a lot* of forums to find an elegant solution and have tried the following:

 

Using MS flow to directly update the calculated field.  Unfortunately, calculated fields are not directly supported for flows (https://powerusers.microsoft.com/t5/General-Power-Automate/Unable-to-find-update-a-calculated-column...)

I tried a more complicated solution from here http://365hq.net/use-microsoft-flow-to-automatically-update-calculated-column-on-a-sharepoint-list/ but this failed (saves fine but I get notified that the flow has failed when it runs).  Possibly because of access violations.

I can't directly patch calculated columns as they are read only (https://powerusers.microsoft.com/t5/Building-Power-Apps/New-Record-Form-and-Calculated-Columns-Patch...)

 

Should I:

  • Remove the calculated field from SharePoint and do the formula in PowerApps?  This will remove the ability to use a default display form but I could just show it separately.
  • Open and save every record in the list (there will never be more than a few hundred records)?  If so, what's the best way to do this?
  • Persevere with MS Flows?  If so, what's the best way to force the calculated field to refresh?

This is my first Power App and I'm quite pleased with it....apart from the fact it doesn't actually calculate overdue actions over time 😕

 

Any help would be appreciated as this is becoming rather frustrating.

 

Cheers

 

1 ACCEPTED SOLUTION

Accepted Solutions
edguerre
Microsoft
Microsoft

Hi Superelastic,

 

One suggestion would be to remove the calculated column, add a simple one, and then create a flow that does the calculation.

That flow would trigger when a new item is created, and you can either apply a filter on the trigger or a condition after it, that will update that field only if it is emtpy.

 

Alternatively, you can also change the column type to non calculated, make the calculation on a Power Apps custom form on a read only field or with a Patch.

 

Would this work for you?

View solution in original post

6 REPLIES 6
edguerre
Microsoft
Microsoft

Hi Superelastic,

 

One suggestion would be to remove the calculated column, add a simple one, and then create a flow that does the calculation.

That flow would trigger when a new item is created, and you can either apply a filter on the trigger or a condition after it, that will update that field only if it is emtpy.

 

Alternatively, you can also change the column type to non calculated, make the calculation on a Power Apps custom form on a read only field or with a Patch.

 

Would this work for you?

View solution in original post

Hi edguerre

 

Thanks for the feedback.  The field needs to be updated daily so it reflects overdue status, but calculating that in Power Apps then patching to a non calculated field in the SP list could work.  I'll give it a go!

You can also make a recurring flow, that checks that field daily, or add a loop with a 1 day delay on the flow that triggers on new item creation! Let me know if the patching works for you though 🙂

Hi edguerre

 

After a bit more messing around with flows, back to patching as a potential solution.  I need to understand how to patch formulas to an SP List though and wondering if you could help.

 

I can update a field in all records with a value, e.g.

ForAll(RenameColumns(Actions, "ID", "AID"), Patch(Actions, LookUp(Actions, ID = AID), {Overdue:"Test"}))

 

However, I need to patch the following formula:

=IF(Date_completed<>"", "Complete", IF(Due_date<TODAY(), "Overdue", "Not overdue"))

 

Is there some syntax where I can replace "Test" with the above formula?

 

 

Ok, worked out the formula (also have a revised date which makes it look a bit more complicated):

 

ForAll(RenameColumns(Actions, "ID", "AID"), Patch(Actions, LookUp(Actions, ID = AID), {Overdue:If(!IsBlank(Date_completed),"Complete", If(And(IsBlank(Revised_due_date), Date_completed < Today()), "Overdue (original date)", If(And(!IsBlank(Revised_due_date), Revised_due_date < Today()), "Overdue (revised date)", "Not overdue")))}))

 

Not as elegant as updating the SP list in the background but gives people a button to press to update the alerts.

Locotoro
Advocate I
Advocate I

Hey friends,

 

I'm trying to create the calculation through flow - and get it to stop calculating once a new column was filled in

 

Any help with doing this through flow (as I can't get the calculated fields to update in sharepoint)

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (23,585)