Tags

, , ,


Today I will create a workflow which sends an email to two different mailboxes based on two sets of results.   

This is based on the requirements below from reader Suzie:

“Hi,

I have been asked to create a workflow that runs based on 2 sets of results within a list.

1.If a user answers YES to any 4 out of 6 questions in the list, an email is sent to mailbox A
2.If a user answers YES to 2 or less of the 6 questions, an email is sent to mailbox B

The question columns are all Yes/No check boxes. Is this possible and if so, how would I do this.

Thanks,

Suzie”


Step #1 – Create a Custom SharePoint list 

  • I am going to assume the user knows how to create a custom SharePoint list
  • I am creating this example using SP 2013 and SPD 2010 Workflow
  1. Create a list  – for this example I used “Example Custom List”
  2. Create 6 Questions, each as a Yes/No column type
  3. Leave the title question 

Step #2 – Create a SharePoint Designer Workflow 

  • I am using SPD 2013 but have selected to create a SPD 2010 workflow
  1. Navigate to the site in SPD
  2. Select Workflows
  3. Create a new List Workflow – and select the “Example Custom List”
  4. Give it a name – for this example I used “Email A and Email B”
  5. Click the name of the workflow and in Start Options check the box to Start workflow when an item is created
  6. Click Edit workflow

 

Step #3 – Create the logic for the Workflow

  1. Rename the first Step to “Calculate Columns”
  2. Select Local Variables at top ribbon
  3. Then create 6 different Local Variables
  4. Name them “Calculate 1”, “Calculate 2”, “Calculate 3”, “Calculate 4”, “Calculate 5”, “Calculate 6”
  5. Make sure each one is a Number type of variable
  6. Create the logic:Select this entire group and copy
    1. Select Condition at top ribbon
    2. Select Any value equals current value
    3. Select value Question 1 equals value Variable Calculate 1
    4. Set Action at top ribbon
    5. Select Set workflow variable
    6. Select variable Calculate 1 to 1
    7. Select Else-If branch
    8. Select Set workflow variable 
    9. Select variable Calculate 1 to 0 (zero)
  7. Then paste down the page 5 times
  8. Change each value to the next Question # and Calculate # (Question 2, Calculate 2….)
  9. Insert a new Step and rename it “Calculate Total”
  10. Select Action at top ribbon
  11. Select Do Calculation
  12. Select the first “value” in the statement and click the fx next to the blank space
  13. Change the Data Source in the dialog window to Workflow variables and parameters
  14. Change the Field from source to “Calculate 1”
  15. Select the second “value” in the statement and click the fx next to the blank space
  16. Change the Data Source in the dialog window to Workflow variables and parameters
  17. Change the Field from source to “Calculate 2
  18. Rename the variable outcome to “Q1+Q2”
  19. Repeat #11-19 two more times but add variables “Calculate 3” + “Calculate 4”; and “Calculate 5 + Calculate 6”
  20. Select Do Calculation again – but now add the variable outcomes for the above like this:
    • “Q1 +Q2″ plus Q3 + Q4” (Output to variable renamed to “Q1+Q2+Q3+Q4”)
  21. Select Do Calculation again – but now add this
    • “Q1+Q2+Q3+Q4″ plus”Q5+Q6” (Output to variable renamed to “Total Yes”)

So the above seems a bit convoluted, but in essence what I have done is create local variables that say for example, IF Question 1 = Yes, the saved variable “Calculate 1” = 1; IF Question 1 = No, the saved variable “Calculate 1” = 0 (zero).  This was repeated for all six questions using Else if branches.  Then I created a second step to add all the variables.  Unfortunately, I didn’t see a way to calculate more than two values at a time, so I had to add the variables for Q1 and Q2 (which are “Calculate 1” + “Calculate 2”), etc. Then I had to add those sums up for a total.   Now when the user answers the questions, the workflow will run and put a 1 value for each Yes answer.  The variables will be added up for a total.  The totla number of Yes answers will be used to determine which mailbox should be emailed.

Your logic should look just like this screen shot:

 

Step #4 – Create the Emails

  1.  Add a new Step to the Workflow and rename it “Send Email A or Email B”
    1. Create the logic for Email A:
    2. Select Condition at top ribbon
    3. Select If any value equals value
    4. Select the first value link
    5. Select the fx next to the blank space
    6. Change the Data Source in the dialog window to Workflow variables and parameters
    7. Change the Field from source to the variable “Total Yes”
    8. Change the operator to “is greater than or equal to”
    9. Change the second value to 4
    10. Select Action at top ribbon
    11. Select Send an Email 
    12. Click these users link
    13. Select who to send the email to, create a subject line and a body
  2. Create the logic for Email B:
    1. Select Else if branch at top ribbon
    2. Select If any value equals value
    3. Select the first value link
    4. Select the fx next to the blank space
    5. Change the Data Source in the dialog window to Workflow variables and parameters
    6. Change the Field from source to the variable “Total Yes”
    7. Change the operator to “is less than or equal to”
    8. Change the second value to 2
    9. Select Action at top ribbon
    10. Select Send an Email 
    11. Repeat Steps to format the email to line, subject line and body
  3. End the workflow:
    1. Select Else if branch at top ribbon
    2. Select Action at top ribbon
    3. Select Stop workflow 
    4. In the log message link add a message that explains why the workflow didn’t send an email (IE: user selected 3 Yes questions)

 

Step #5 – Test the workflow

  1. Go the custom list named Example Custom List
  2. Add a new item, answer 5 questions as Yes
  3. Add a new item, answer 2 questions as Yes
  4. Add a new item, answer 3 questions as Yes

Results should be:

  1. Email is sent to Email A for the 5 Yes answers
  2. Email is sent to Email B for the 5 Yes answers
  3. Email is not sent to any mailbox for the 3 Yes answers

See all screen shots here:

Create local variables Define a lookup Create emails logic Create Calculate Columns logic Create add total logic Create a List Workflow Copy and paste one section of logic Add a workflow Variables dialog boxExample Custom List

Advertisements