Hi Paul,

I've tried to recreate your scenario, although the setup might be a little different as I built a minimal solution for quick testing.

I created a SharePoint list with:

• Instructor (Person and Group)
• Start time (Date & Time)
• Reserved By (Person and Group)

 
To give some context:
I've set it to send an email 5 minutes before the appointment (for the sake of quick testing).When a new SP list item is created (you fill out the above fields), the workflow kicks off.

1) The first action is 'Query List' for Start Time and the Instructor values.

2) Use the action 'Find Interval Between Dates' between the dates 'Use date when action is executed' (since it runs straight after submission) and the appointment time. Put the result in a new number variable.

3) 'Do calc' to take away a number of minutes from the new number variable created in the last step. In my case I'm taking away 5 for getting an email 5 minutes before the appointment (in your use case this would of course be 1,440 minutes).

4) 'Add Time to Date' - I use this and choose date as 'Use date when action is executed', then in minutes I add my num variable from before. I then output this into the 'Day Before Appointment' DateTime variable.

5) I then 'Pause until Date' using the 'Day Before Appointment' variable (this is of course configured once chosen to Pause until both Date & Time are met)

6) I then use 'Build String'. Outputting Instructor (as it's a person variable) directly can have weird results in formatting etc, so here I go to advanced lookup, add the Instructor variable  then with the X configure it to use 'Display Name'. I store the result in a text variable.

7) I then send the email referencing the text variable for instructor name and 'Day Before Appointment' for time. It came through like this:

You have a message appointment tomorrow with Samuel Short at 5/9/2018 11:43:00 AM. 

(Mistakenly wrote 'message' instead of 'massage' ;D)

Hope this helps,

Sam

Hi Sam,

Thanks for the response. I'm fairly new to building workflows, and not familiar with some of the actions such as build a string. Could you possibly either send screen shots of the actions as configured, or attach the file so I can see how it works? Would appreciate it. Thanks,

Paul

Hi Paul,

I've cleaned it up a bit (removed Build string as I found a different/easier way to get the same result). Let me know if you are having issues or have any questions?

The premise is the same as above, I have a basic SharePoint list with 2 People/Group type columns (Instructor, Reserved By) and 1 DateTime column (double checked the column configure lets it store both Date and Time) called Start Time.

1) Making variables:

Set up your workflow variables as show above. Make sure for the Date/Time types that initiation is 'Yes' and make sure the initiation settings (should be visible when you click to edit the initiation value) are set to the below:

2) Query List:

Add the 'Query List' action (as you had it before) and configure it as follows:

The ID rule above makes sure to only select the current list item

2) Find Interval Between Dates:

Use the 'Find Interval Between Dates' action to get the time in minutes between now and the Start Time:

3) Do Calculation:

The 'Do Calculation' action is used here to take away time. Essentially the difference in minutes we worked out in the previous step MINUS one day in minutes gives us the amount of time from now until the one-day-before reminder should be sent out.
NOTE: My second operand is set to 5 - this is because I wanted to test quickly. In your scenario to get a notification one day before would require a value of 1440.

4) Add Time to Date:

I used the 'Add Time to Date' action next so I could take the current time (the time action was called) and add the number of minutes until the one-day-before reminder. I saved this variable into the DateTime variable we made at the start - 'Day Before Appointment'.

5) Pause Until Date:

As we triggered this workflow as soon as a new massage item was added to the list (on that note, make that the workflow is set to run when a new item is created), we need to pause until it's time to send out the notification. Thankfully, we just spent the last 4 steps getting the DateTime we needed for this.

6) Send an Email:

Finally we get to sending our email, which is formatted just like your example:

This arrived in my Outlook inbox looking like this:

I know I missed mentioning how to get the 'Reserved By' column so you can send the email to that that person, you seemed to have that part sorted however so I just left it out for the sake of a quick example.

Hope this helps, let me know if you have any questions.

Regards,

Sam