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

[Superelastic]

 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

[edguerre · Accepted solution]

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?

[Superelastic]

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!

[edguerre]

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 🙂

[Superelastic]

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?

[Superelastic]

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]

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)