Updating Multiple Survey Feedback Using SP Designer Workflow

Multiple list items

 

Problem Statement – Create a SharePoint survey for Training feedback, and collect all the feedback from a particular training with the date and save as one item to another SharePoint list (loop through training), and send an email notification to the trainer with an average rating and individual rating with suggestion(s).

 

Survey (Training Feedback) will get a response as below.

 

Suppose we will get 5 feedback responses for the training ECU Integrator Workshop and the trainer is Pramod,  and the training date is 8/11/2019, with 5 different ratings and suggestions.

 

The workflow will collect all the feedback of a particular training name based on the unique value and store it in another list in HTML table format and trigger an email to the trainer with all feedback including the aggregate rating.

 

This information will be created in the loop through training as below.

 

 

 

And the feedback will be sent to the trainer on a triggered email date after 30 days from the training, i.e., 9/10/2019.

 

 

 

Following are the steps to follow.

 

Create SharePoint Survey named “Training Feedback” with questions

• Select Training (Dropdown)
• Select Trainer (Dropdown)
• Select the Date of Training (Date and Time should not be greater than today's date)
• The rating scale for training effectiveness evaluation is defined as follows. (1) Not very much, (2) Somewhat Effective, (3) Moderately Effective, (4) Very Effective, (5) Extremely Effective Please provide comments if the response is less than 5.
• Observations and suggestions for improvements for future events (Multiline Textbox)

 

 

Create SharePoint List named “Loop Through Feedback”,

Sr no	Column Name	Column Type	Description
1	Title	Single line of text	For Training Name(As plain text)
2	Trainers	Single line of text	For Trainer Name
3	Feedback Count	Number	To count the number of feedback
4	Training Date	Date and Time	For Training Date (we can compare with a new item created in the survey by using SharePoint workflow)
5	Email Body	Multiple lines of text	To Save all feedback in HTML(Table) format
6	Trigger Email on	Calculated Column	= [Training Date]+30 (To trigger an email when the current date is matched with this date)
7	Rating	Number	To Save sum of rating given in Survey list
8	Training Name	Single line of text	For Training Name (As string) we can compare with a new item created in the survey by using SharePoint workflow
9	Uniqueid	Single line of text Bottom of Form	When a new item is created in SharePoint survey then unique id will generate using workflow and it will copy in this column and for the next feedback entry - if this value is matched then data will update in the same SharePoint list (Loop through feedback) item

 

 

Now we will create a SharePoint designer workflow named “Evaluation” which will do the following operation

Create the variable as shown in the table.

 

Sr no	Variable Name	Variable Type	Description
1	uniqueid	string	To create a unique id for Training
2	EmailBody	string	to save feedback in HTML table Format
3	Countfeedback	number	to count the number of feedback for particular training
4	rating	number	to save the sum of all rating for particular training
5	looprating	number	to save rating number from the loop through training so we can add the rating newly added depending uniqueid matching training

As soon as the user enters feedback into training feedback, the “Evaluation” workflow triggers and creates the unique id by concatenating with training name and date e.g) Advanced java8/11/2019

 

Step 1

 

 

 

A new item will be created in the “Loop through training” list depending on the training name and date

 

If training name or training date is not present in Loop through training AND uniqueid (variable) is not present in any of the list items in Loop Through Training then:

 

 

 

First if Condition - If training name is not equal to any of  thetraining name list items in Loop Through training:

 

 

 

Or Second if condition - If training date is not equal to any of the training date columns in Loop Through training:

 

 

 

Third AND condition - If generated uniqueid variable is not equal to any of uniqueid columns in Loop Through training:

 

 

 

Then create item in loop through training:

 

 

• Title field in Loop Through training is Select training (as plain text) value in recently added item in Training feedback survey
• Training name field in Loop Through training is Select training (as string) value in recently added item in Training feedback survey
• Training date – as date time
• Trainers – as string
• Unique id – as string

The following screenshot shows the create title column in Loop Through training, create for Training Name, Training Date, Trainers and uniqueid in the same way.

 

 

 

The first step will look like the following,

 

 

 

Adding second step

 

Step 2

 

We also need to copy rating value but we have rating as string so we need to cut the string and copy only the number (rating needs to be cut from the 32nd location to just one).

 

Use Action “Extract Substring of String from index with length” starting at 32 for 1 character and save in variable substring as shown in the second and third screenshot.

 

 

 

 

 

 

 

Adding Step 3

 

Step 3

 

Step 3 is for,

1. Adding rating and email body to recently created item in Loop through feedback
2. Updating Email body and updating rating column by summation of rating for the same training by matching uniqueid.

Again we need to set uniqueid variable by concatenating Training Name and Training date

 

 

 

Then set EmailBody variable to the column(Email Body) in Loop Through training

 

 

 

Then set countfeedback variable to the column (Feedback Count) in Loop Through training

 

 

 

Then set rating variable to the variable Substring created in the second step:

 

 

 

Now we need to set Email body if it is empty (in case training item is not present in loop through training)

 

 

 

Then we will set the values to the above table.

 

 

 

Then increment variable countfeedback by 1 and save in variable calc2 as shown below

 

 

 

Then, set the looprating variable which is present in rating column of Loop Through training list, so we can add a new variable rating value created by using send step with the previous rating value; i.e., looprating and store in variable Calc 3. Both operations are shown in the following screenshots.

 

 

 

 

And the last operation is to update all these values in the item present in the Loop Through Training List, depending on uniqueid value.

 

 

 

Item will be updated in the Loop Through Training List.

 

The full workflow will look like below.