Question

3 year review check workflow

  • 12 December 2023
  • 4 replies
  • 48 views

Userlevel 1
Badge +9

Hi!

I have a sharepoint list (on prem) and need to create a workflow if a list item shows a modified date less than 2019… If an item is detected and the modified date is less than 2019, I need to notify someone by email to review. Once reviewed the modified date updates, I will  then apply 3 years to that modified date for the next notification to go out to review once again and will need this workflow to loop so user is notified every 3 years.

The list has versioning turned on, I applied a workflow to test but it seems that its sending a notification for every version that “is less than 2019”. Am I doing something wrong?

 

Thanks


4 replies

Userlevel 5
Badge +13

I would suggest creating a site workflow and then scheduling it at the recurrence that you desire (daily, weekly, monthly, etc.).

In the site workflow, use a Query List action to get all the items with a modified date before 2019. Then use a For Each action to loop through those items, get the data for each item, and add the desired data to a Send Notification action.  Finally update the item with a new modified date and any other changes.

Userlevel 1
Badge +9

@bamaeric ,

Thanks for the quick response! I’m somewhat confused...So, I setup a workflow but am lost on setting up the foreach loop...not sure what to apply in the target collection for the foreach loop and don’t see anything related to my sharepoint 2016 on-prem list.

Also, since this is a scheduled start, how do I find the columns in the list to apply to the email? 

Query a list

 

loop for each

 

Thanks!

Userlevel 1
Badge +8

In your initial list query, choose the item ID as the field to store, and create a collection variable to store the IDs in, like collItemIDs

On the for each loop, use the new collection of IDs (collItemIDs) and store the result as an Item ID variable, like idItemID.

In the second query (inside the loop) query the same list, filtering on where ID equals idItemID.  Then output each field you need as an appropriate variable (Title as single line of text variable sltTitle, Modified as Date/Time variable dtLastModified, etc.).  Since ID is unique, you can store each field value in a regular variable instead of a collection.

That should allow you to have content for each email that is specific to the item. 

A better method may be to have your loop build rows in a table, and send one email with links to every item collected in a table in the email, instead of many individual emails. 

To do this, have a multiple line of text variable like mltTable and set it prior to the loop:

<html><table border="1"><thead><th> Title </th><th> Last Modified </th></thead>

Then inside the loop, build each row (variable mltTableRow) after the inside query by Set Variable action: 

<tr><td align="center"><a href="https://mysharepointsitefarm.com/sites/mySite/Lists/myList/DispForm.aspx?ID=idItemID">sltTitle</a></td><td align="center">dtModified</td></tr>

This will be a table row with a link to each item, aong with its last modified date.  Expand to add other fields as needed.  Be sure to add the two variables (sltTitle and dtModified) using the picker, not just typing them in as I did here.

Then set your mltTable variable to: mltTablemltTableRow (being sure to use the insert reference for all these variable above, not just typing them in) using the set variable action.

Outside the for each loop, set the mltTable variable to mltTable</table> to close the table out.

Then in the singular email, you can send all the values in one email by inserting the mltTable variable into the email body like any other variable.

Lots of other tips to explore (cleaning up the dt variable by saving it as short date text variable, etc.) but that is a start!

Userlevel 1
Badge +9

@bgarvey ,

Good morning! So I am back to working on this out but am quite confused as to how I need to approach… I do appreciate your response tho!

So I have done the following but not quiet sure where to go from here and assuming I have applied everything correctly. Please see the following on what I have so far…

 

Thanks in advance!

 

1st Query list control
Loop for each
2nd Query List control

 

Reply