Sometimes a blog post takes a village. 🙂 An idea comes from the community, where someone is trying to achieve something new, something for a project or a specific requirement within their organisation. Thanks to Todd Mercer for the original question! This blog is written based on the need to send an email to let a customer know about an upcoming appointment and ask them to confirm via a Forms Pro survey if they will still attend. The related appointment then needed to be updated and marked accordingly if confirmed, and also updated if they wished to reschedule and also create a task that someone needed to complete. I had an idea of how this could be achieved, but couldn’t get just one step right…. so thanks for the various conversations Keith Whatling, Elaiza Benitez and Rob Dawson – and for showing yet again how awesome our community is. This post shows how to update records in CDS based on using piped variable parameters from a survey response. Right, on to the blog post!
So the first thing, we must first create a piping variable on the survey. For this, the Appointment ID that the customer is confirming or rescheduling must be passed in to the survey link they are sent so that when they respond, we can get back to that Appointment ID. Click on the ellipsis from the Survey in Forms Pro and select Personalize. Add a new variable, very simple!
Next we need to create a Microsoft Flow to use to send out an email to the customer. In this instance an email will go out 30 days prior to the scheduled date on the appointment. So we will use the recurrence trigger. Then our next step will use a CDS action to list records using an ODATA filter query to find all appointments with a specific status reason (Scheduled in this example) where the start date is in the next 30 days.
If you have ever used a data field from CDand sent it in an email via FLOW, you will know it looks a bit crap. 🙂 so let’s use the Convert time zone step from the Date Time connector and set the Format string for the Appointment Start Time to be one of your own liking.
Next we will have a Get record step to get the Contact using the Regarding value on the Appointment. Then we will use the Forms Pro connector and the Create and invitation step. Using values from the previous step we can pull in the Email, First Name and Last Name from the Contact. We can set the invitation against the contact, and set them as the recipient. Then we will use the converted date step to show the date of the appointment, then the subject and description of the appointment. Finally, pull in the appointment id value.
Then we need to send an email using the invitation link we just generated. Use the send an email action and create the email body however you wish. You can use the a href tag to use text to create a hyperlink for the invite rather than just showing the long link that is generated. Finally, because we don’t want the survey invite to be generated again tomorrow, we will have a step to update the Appointment to indicate that the reminder has been sent (using a value on the Status Reason field).
Right, now we need a second Flow. This is the one that will start when the survey response is created. Then get the survey that the response is linked to. We can then do a condition check to make sure the name of the survey is the one you are interested in.
If Yes, we then need to use the Get response details action from the Forms connector (not Forms Pro). Use the following as your expression.
For the next part, we need to get information from a field on a survey response, called Context Data. To do this, fill out your survey once, then create an advanced find in CDS, and show the Context Data field. You can then export the record which you can then open in Excel. You can see here that all of the parameters we have on the survey are present. Copy this entire string.
Next add a Parse JSON step to your flow. Then click on the link at the bottom of this step (where it says Use sample payload to generate schema) and paste the string you copied from Excel. This will then get all of the piped data variables which we can use in a later step in the flow.
Now we have another condition, and this time it’s checking the response to our question about the customer confirming or requesting it be rescheduled.
If it’s yes we are going to update the appointment and change the Status Reason to Confirmed. To find the right appointment, we will select the AppointmentID option from the Parse JSON step.
If no, then it must need to be rescheduled. So for this we have a few steps. We are going to use the Parse JSON step again and use the AppointmentID value to change the Status Reason on the Appointment to Reschedule, then we will get the Contact from the regarding field on that Appointment. We will then reformat the date provided from the survey, and then create a task giving us all of the information needed.
If we trigger the first Flow, you can see all of the piped variables displayed which makes for a great user experience for the customer!
Here we can see an example where the task was created.
Want to just watch how to do this? Check out the video below: