I've searched through the forum and couldn't find any solutions to my exact issue. I'm hoping someone can advise:
I'm looking to have a summary of the incomplete/open items assigned to an individual sent to them via e-mail on a daily basis - capturing the fluctuation in Open items and excluding "completed" items
I understand scheduling the site workflow and have got the workflow to send the notification, unfortunately, I have not been able to figure out how to get it all in ONE email. Some individuals have up to 20 items assigned to them and getting 20 e-mails would annoy anyone, which I'd like to avoid.
Please advise (and I'm super visual so any templates would REALLY help, though all help is welcome).
Thanks in advance!
Solved! Go to Solution.
What we do for such needs :
- scheduled workflow
- query the list to get the needed item (user = *** and status != completed)
- loop on the result collection to build a table or a multiline variable
- put the table in a email and send
The recipient get an email like :
"You have 20 items to complete for today :
- Item 1
- Item 2
If you need to format the email, you can use the Build String action to create HTML containing references to the items in your collection and store them in a multi line variable. Then this will allow you to append your HTML each time and build the HTML into something like a table before putting the that final HTML into your email.
This just builds on the correct approach Thomas has suggested.
So we do something similar all the time. Basically the trick is:
1) Query your list of tasks to get the results (I think you have all of that thus far), however make sure you order by the user that you are wanting to send the summary to
2a) Create 4 variable - one for an top table, one for the end table and one for an inner table and one for the emailTXT, all must be multiple lines of text
2b) Create a variable for PreviousUser as string
3a) Set the top table variable to something like <table><thead><tr><th>User</th><th>Task</th><th>Due Date</th>.....</thead></tr><tbody> - filling in any headers you want in a <th></th> tag as per examples above
3b) Set the end table variable to something like </tbody></table>
3c) Create a variable for the count lets call it iCount
4) Create a for each loop and loop through the results from step 1 stored in a collection
5) In the loop check if the PreviousUser is equal to the current User we are looping though
5a) If PreviousUser is not equal to User and PreviousUser is not blank then update the emailTXT to html using a build string equal to <h1>User has iCount tasks open</h1><h2>Summary</h2>+top table + inner table + end table and save output to EmailTXT
5b) add EmailTxt as body of email and send summary to user or users manager or whomever you want it to go to
5c) Reset Inner table to "" and iCount to 0
5c) if the PreviousUser is equal to User or PreviousUser is blank then
Buildstring for Inner Table <tr><td>User (variable)</td><td>TaskNmae(variable)</td>....</tr> - note you need to match all columns to all header columns
Do a maths calculation of iCount+1
6) Final step - after you have looped through all items the last item would NOT have sent a mail so copy steps 5a - 5b outside of the loop to send the last email.
Give me a shout if you need some more information.