1

I have a calculated column in SharePoint.

It is dependent on non-calculated columns.

So for instance, my calculated column is "past deadline?" and its formula is:
=IF([run]="yes",IF(NOW()<[deadline],"past","not past"))

I know that SharePoint online calculated list columns don't refresh automatically - they have to be triggered by an update to the item or an update to the column.

The thing is, I'm updating each item in the list every 30 minutes from a power automate flow. I added a column call "dummy id" and every 30 minutes, power automate puts a new unique ID in that field, for each item in the list.

This wasn't triggering the update for the calculated column so I added that field into my calculated formula:
=IF([unique id]="","",IF([run]="yes",IF(NOW()<[deadline],"past","not past")))

However this also did nothing - the calculated column is still not re-calculating. So I added an http request to update the formula every 30 minutes, to try to force the recalculation - now the formula gets updated to:
=IF([unique id]="hhmmss","",IF([run]="yes",IF(NOW()<[deadline],"past","not past")))

where "hhmmss" is just the current hour minute and second that the power automate flow runs

I verified that the formula is actually updating by going into the list settings, and I can see the new timestamp in the formula after it updates.

To make sure there isn't a problem with my formula, I've gone in an manually updated the "unique ID" field, and it forces the calculated field to update - so I initially thought maybe there is a restriction where calculated columns can somehow tell the difference between power automate updates and manual user updates

But I have another power automate flow updating the "run" column to yes for certain items, and when that happens, the calculated field does update, so that means its not ignoring power automate.

So what is happening here? I'm at my wits end - I just need this thing to tell me if its past the deadline, and I can't get it to work unless I go in manually for some reason.

4
  • can you include your Power Automate flow ? Nov 8, 2022 at 10:48
  • Action: get items in SharePoint list, pagination set to 500 items (there are only 46 items in the list), action: apply to each returned item, action: update item (using the IDs in each apply to each item). It's a relatively simple flow Nov 9, 2022 at 12:56
  • can you check the items version history ? Nov 10, 2022 at 8:15
  • What should I be looking for? Nov 11, 2022 at 11:58

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.