Compile overdue items into 1 email per AssignedTo

  • 21 March 2018
  • 7 replies
  • 4 views

Badge +1

My current site wf that sorts on items that are overdue, pulls field values from the SP list and sends an email to the assignee. 1 email per overdue item (this wf runs weekly).  However, I want to consolidate the number of emails. So my goal... Goal = send one email (weekly) to the assignee that contains all their overdue items. I will update the original post as I go on so everything is in one place and easy to follow for future. Special thanks to  -

I've read through mulitple posts so I've started building on the original WF. 

1. Query List 1:  

      Filters:          Internal Due Date is less than Current Date

                           AND

                           Issue Status is equal to Active.

      Field: Assigned To  (AssignedToColl)

2. Collection Operation: 

       Target Collection: AssignedToColl

            Remove Duplicates

       Store Result in: AssignedToColl

3. For Each 1:

      Target Collection: AssignedToColl

      Store Result in: varAssignedTo (people/group)

4. (I would then place the Query List 2 (from oringal wf) within the first For Each 1)

    Query List 2:

            Filters: Internal Due Date is less than Current Date

                         AND

                        Issue Status is equal to Active

                         AND

                        AssignedToColl is equal to varAssignedTo

            Fields:  Title (titleColl)

                   Assigned To (AssignedToColl)   --> This can be removed now. It was needed for my original wf.

                   Internal Due Date (dueDateColl)

                   Obs. No. (obsnoColl)

5. For Each

         Titlecoll store result in itemTitle

         Index: index (number)

6. Run parallel actions

7. Collection Operation (x3) to get my 3 fields and store each into their own variable.

8. Math operation 

      Calculation: Workflow Data --> index

            Plus Value 1

      Store result: index

9. Regular expression (this just changes the date to appear how I want it to) --> store result in varDateText

10. Build String w/ my variables --> store result in emailString

11. Exit the inner loop

12. Send Notification 

         To: varAssignedTo (individually addressed)

13. Exit outer loop --> done

--------------------------------------------------------------------------                        

I can get the email to work for a single user, but I'm having trouble getting the email to send to the specific Assignee when I test it under the above.

My guess is I need a set variable, but the one I tried didn't seem to match userID's correctly. An internal employee was cced on a failed workflow when I tried that and I was the only user I was testing it.

Any help would be greatly appreciated. I've read through a fair amount posts so if you could add some more insights...wfOneItemEmailPerUser

This is where I am with my mission.

building_AllItems

Thanks!!

Jess


7 replies

Userlevel 5
Badge +14

and what exactly you have problem with?

roughly your approach seems to be correct.

first 3 steps as you described them should give you all the assignees involved, that seems to be correct.

hard to provide any comments on you existing workflow since almost all the configurations gets wiped out if you import it in an environment that miss your specific underlying infrastructure. it would be more helpful if you posted screenshots of your configuration.

but if you say it's working for a single item it shouldn't a problem to merge/enclose it with above 3 steps. you just need to extend filter by above varAssignedTo variable (and maybe remove restriction for single item).

still i think you might have missed some good articles on the topic.

eg following two blogposts describe solutions for exactly the same problem

Summary email of outstanding tasks - only one per Assigned To 

 

Badge +1

HI Marian,

I've added images to my orignal post, thanks for letting me know that the attachment doesn't load the way I see it. If I need to elaborate on any section, please let me know.

So I should Extend the filter in the Second Query to filter Assigned To is Equal to varAssignedTo ... what do you mean remove restriction for single item?

I

Userlevel 5
Badge +14

I've added images to my orignal post,

it would be helpfull if you posted configuration of initial query list action as well

So I should Extend the filter in the Second Query to filter Assigned To is Equal to varAssignedTo

yes.

your outer loop should determine all the involved assignees.

the inner one should look for all the open items for a given current assignee (driven by outer)

notification action has to be placed within outer loop and out of inner one to get one notification per assignee

what do you mean remove restriction for single item?

I do not know how does  query list action in your existing workflow exactly look like.

it was meant so that you remove all the extra restrictions that might cause an open item for some assignee(s) is not found

Badge +1

1st1st Query 2nd 2nd Query

Badge +1

After I take the collAssignedTo (duplicates removed), would I index the first For Each where collAssignedTo stores values into varAssignedTo (people/group picker)? Or should I leave that index blank?

Userlevel 5
Badge +14

.... Or should I leave that index blank?

it's up to you.

since your 1st query list action returns just one collection you will not effectively need to know current index/iteration.

only if you wanted to place it into notification (like "Nth assignee = XYZ")

remove step 8 - Math operation .

index variable is controlled/incremented by a for each loop action, you shouldn't increment it on your own until you have good reasons to (skip some iteration). even if you needed, it still safer to make a condition instead of incrementing index variable.

step 9. - maybe you could use formatDate() inline function instead of regex.

but if regex works for you, nothing wrong with that.

Badge +1

SOLVED:

Hey everyone,

Here's how I ended up getting my wf to run. 

1. Query List: List 1

Filter for:

      Internal Due Date is less than ______.  (*I leave this blank for now)

      Issue Status is equal to Active

Sort:

      Assigned To

Fields:

   (input all the fields you want to return values for and store them as Collection Variables)

   I have 6 fields including Assigned To.

*Now I put the Editor mode into CAML editor. I search for where my filter criteria, where it says

<Value Type="DateTime">.  I add <Today /> next to it.  So it will look like <Value Type="DateTime"><Today />  

Once you click SAVE, it will reposition itself and look like the image below.  I do this so when I test the Run Now feature, it will return the results. If you use the "Current Date", it will not return results.

214079_pastedImage_7.png

2. For Each

   Target collection = AssignedToColl

   Store Results in = varAssignedTo (variable -person/group)

   Index = index (variable -number)

3. Run parallel actions

    Create as many branches as you need to query your required fields,

4.Collection Operations - insert to get each field value

      Target Collection: Ex. dueDateColl --> GET --> index --> Store results in itemDueDate

     214080_pastedImage_10.png

5. Regular Expression (only needed if you are returning a date value and want it to look normal)

   Pattern: [0-9]+:[0-9]+ [a-zA-Z]+

   Select Replace Text

   Input text: (insert reference - select your date variable that was stored from the Collection Operation)

         Ex:  {WorkflowVariable:itemDueDate}

   Store results in: (create single line of text variable)  Ex: varDateText

6. Run if --> (the rest of this I took from what Giacomo Gelosi had posted on grouped item notifs)

Just create a variable to store Previous AssignedTo  Ex: varPreviousAssignedTo (variable -person/group)

214196_pastedImage_1.png

7. Send Notification

    (you can leave this out of your workflow for now as you will be creating another Send Notification. Once that one is created, you can copy and paste it into this spot.)

8. Build String

      Leave the Text blank

      Store results in: (variable -Multiple Lines of Text) Ex: emailString

EXIT the RUN IF

9. Build String

      Text: Input the emailString variable first (I like to add a empty line after this)

         Then add your workflow item variables from the Collections Operations as you prefer.

      Store result in: emailString

Ex:

214083_pastedImage_21.png

10. Set Variable

Set: varPreviousAssignedTo

      Equals

 Workflow Data --> varAssignedTo

EXIT FOR EACH LOOP

11. Send Notification

    To: varPreviousAssignedTo

    Check send individually addressed notifs

   Create your Subject line and body of email. 

   Click Insert Reference --> and then insert your emailString variable

  Save, then copy this action and paste for Step 7 (insert right below the RUN IF as pictured below)

214084_pastedImage_27.png

Complete View:

Overview

Reply