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?

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
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (3,558)