How to prevent Flow from triggering built-in SharePoint alerts

[MikeStuart]

Hi Community,

Problem:

I use this flow to update all records in a SharePoint List with Today's Date so that a calculated field can display "Days Elapsed" from creation of the record to today's date. This gives me automatic ageing for the records without having to modify a record to force a calculated update.

However it negates the built-in alert functionality because all users with alerts would now receive hundreds of email alerts daily when the Today's Date column is updated.

Desired solution:

Prevent Flow from triggering built-in SharePoint alerts.

Work-arounds attempted

1. Custom flows for notifications on Item Modified
   1. Not being able to use the same template as the built-in alerts, these custom alerts are not as attractive or useful in the information they provide
   2. I also expect that the built-in alerts will be continually improved into the future and therefore do not wish to invest time developing and extending custom flows
   3. I am not skilled in coding and would therefore prefer to build on out of the box solutions
2. Using a second list to perform the Days Elapsed calculations in order to avoid triggering the built-in alerts on the primary list, and displaying those calculations as look-up fields in the primary list
   1. This fails because SharePoint does not allow look-up fields on calculated fields or other look-up fields

Thanks for all the great solutions on this forum that have saved time and energy along the way.

[tom_riha]

Hello @MikeStuart ,

I think updating all items in a SharePoint list daily is not a good solution, especially if you want to update just a column with today's date.

If it's only about calculation of days since an item was created, you don't need a special column with today's date, you can use today() function in the calculated column to get it. The formula below will calculate number of days since the item was created (today's date - created date) and round it to a number without decimal places.

=ROUNDDOWN((TODAY()-Created),0)

I just read a bit about today() and it doesn't seem to be ideal solution.

[tom_riha · Accepted solution]

Another idea: use JSON formatting for the 'Days Elapsed' column:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number(@now)-Number([$Created]))/(1000*60*60*24))"
}

[MikeStuart]

Hi @tom_riha ,

Thanks for the proposed solution which seems workable but I have two questions:

• Will the display values update automatically when a user opens the view?
  This is implied in the documentation by the following:
  ​"This example also takes advantage of a special value that can be used in date/time fields - @now, which resolves to the current date/time, evaluated when the user loads the list view." from https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#formatti...
• Is it possible to apply other conditional formatting on the display value? For example I currently have color coded severity warnings on the relevant field, based on a hard number calculation but I tried applying similar conditional formatting to your new JSON display value, and it returns a blank, probably because the column now no longer contains data, just a display value.

The documentation suggests you can use display values for formatting calculations here: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#displayv... but I tried nesting your code within a standard severity conditional format and just got a blank display. If it is possible to do this can you share sample code for your solution above plus a simple conditional color format (e.g. if display value is greater than 1 then color = red).

Thanks for your reply.

Mike

[tom_riha]

Hello @MikeStuart ,

• yes, the values will update automatically each time user opens the view/refreshes the page.
• yes, you can apply conditional formatting on the value, but since it's only a display value, you should use the whole calculation in each of the format conditions, the code example below will display the number of days in red if the difference is more than 1 day.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number(@now)-Number([$Created]))/(1000*60*60*24))",
  "style": {
      "color": "=if(floor((Number(@now)-Number([$Created]))/(1000*60*60*24)) > 1, '#ff0000', '')"
  }
}

[MikeStuart]

@tom_rihaworks perfectly!

For others this might also help:

I ported the conditional formatting I had previously setup on the hard data column to the new JSON formatted display value column and did a "find and replace" of the original JSON column formatting to replace the column name it was referencing:

"[$DAYS_x0020_ELAPSED]"

with the display value calculation (in my case):

"=floor((Number(@now)-Number([$Sent_x0020_to_x0020_FASO_x0020__]))/(1000*60*60*24))"

This resulted in a display value with all my original conditional formatting applied to the new JSON formatted display value.😎