cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MikeStuart
Frequent Visitor

How to prevent Flow from triggering built-in SharePoint alerts

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.

1 ACCEPTED SOLUTION

Accepted Solutions
tom_riha
Super User II
Super User II

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))"
}

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

5 REPLIES 5
tom_riha
Super User II
Super User II

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.

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]
tom_riha
Super User II
Super User II

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))"
}

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

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

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', '')"
  }
}

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]
MikeStuart
Frequent Visitor

@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.😎

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (71,740)