cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Microsoft
Microsoft

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

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

5 REPLIES 5
Highlighted
Microsoft
Microsoft

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

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

Highlighted
Frequent Visitor

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

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!

Highlighted
Microsoft
Microsoft

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

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 🙂

Highlighted
Frequent Visitor

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

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?

 

 

Highlighted
Frequent Visitor

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

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.

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,245)