Setting up a document reminder

[DylanB02]

Hi,

I have set up a document library within SharePoint to store some certificates. SharePoint Syntex has been applied to this library to extract the company name, location and issue date. This part works fine. I then added a "Defects End Date" column which contains the issue date plus 1 year. Then I have an "EndDateBefore14Days" column which is the end date -14 which sets the date for when I want the reminder to go out via email.

I have attached the full flow below but having difficulties with this, it either runs successfully but sends nothing or works and sends me 15 emails all with blank info on.

formatDateTime(utcNow(),'dd-MM-yyyy')

formatDateTime(items('Apply_to_each')?['EndDateBefore14Days'],'dd-MM-yyyy')

formatDateTime(utcNow(),'dd-MM-yyyy')

Thanks in advance

[AndrewR1]

Hi @DylanB02 here is how I achieved this: 

Because you are trying to set the format of the date in the condition this can cause some issues I would recommend you have 3 initials variables at the top: 

Initialise Todays date to be: 

formatDateTime(utcNow(),'yyyy-MM-dd')

Then inside your for each loop set the End date variable to be: 

This will then be the same format to be compared yyyy-MM-dd

then in your email you can rest this format back to the UK Date time: 

formatDateTime(variables('ItemEndDate'),'dd-MM-yyyy')

Resulting email:

Full flow: 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[DylanB02]

Thanks for the help with this so soon, I've had a go at copying what you have done but just a few things I'm unsure of.

I received this error message 

Flow save failed with code 'InvalidVariableOperation' and message 'The inputs of workflow run action 'Send_an_email_(V2)' of type 'OpenApiConnection' are not valid. The variable 'Defects End Date' must be initialized before it can be used inside action 'Send_an_email_(V2)'.'.

I'm unsure what this variable should be set as also, should this be the SharePoint column of the end date or something else? And again below, just checking these variables are right.

[AndrewR1]

Hi @DylanB02 Yes these variables look like you have set them up correctly but where in the flow have you placed them? as in my flow it is straight after the trigger for the flow then the variables are initialised the error you are getting is that you are using the variable before it has been initialised.  It also looks like your using your old variable not the new end date variable ?

Step 1: 

Action and initialise variables: 

Set 2 get all items form SP List: 

Step 3 add a for all control and assign the select output from previous step to value from get Items:

Step 4 in the apply to each now add a set variable data operation and change the Item End date to your field in your SP list that you wish to be checked: 

(End date is your date that you wish to check = todays date)

Step 5 add a condition control inside of the apply to each: 

Todays date should be formatted to 'yyyy-MM-dd' and this is the default format for the SharePoint List Dates.

Step 6 send out your email reminder: 

Full flow (collapsed Apply to each): 

Full flow (With Apply to each): 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[DylanB02]

Hi @AndrewR1

Had another look through and flow seems to run successfully, I've attached the full flow below but not getting an email sent through for some reason. I have uploaded a test doc with and EndDateBefore14Days that matches todays date, as I want it to check if the end date - 2 weeks = today and then sends a reminder out.

Just wanted to check I'm using the correct variables for the condition, I noticed you said they need to be formatted to a certain date type but not sure where to do this.

[AndrewR1]

Hi @DylanB02 can you show me the successful run and see what the value of the variables are? did you change the todays date to be formatted like : yyyy-MM-dd ? you would do this when you initialise the todays date at the top of your flow! 

So it should be 

formatDateTime(utcNow(),'yyyy-MM-dd')

Everything else is looking ok to me your setting the variable in the Apply to each correctly like so: 

(End date is replaced with your 14 days before end date this is fine and should work the same way!) 

The output of this is yyyy-MM-dd which is why we need to set todays date as the same format for the condition to be true 

If the email isn't being sent this would suggest that the values of your variable are different can you see which side of the condition was ran the yes or the no? It would be good to view the last run and see the variables valuse and then we can clearly see what is being passed: 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[DylanB02]

No worries, I have ran it again, see below for results. I have checked the today variable says 'yyyy-MM-dd' also.

And this is the variable in the "apply to each" for the below SP record - seems to have the 10th of the 10th for some reason even though on Sharepoint its the 11th of the 10th.

[AndrewR1]

Hi @DylanB02 the reason the email isn't being sent is because you are comparing: 

Todays date: 2023-10-10 

14DaysToEndDate: 2023-10-10T23:00:00Z 

These are different values this may be because you have used a calculation to get this date in your list

So lets try adding the format date time to the second part of your condition like so: 

Code: 

formatDateTime(variables('ItemEndDate'),'yyyy-MM-dd')

This will then reformat this variable before being checked! 

It is showing as 10-10-2023 23:00 not the 11-10-2023  because you have date and time field for the data type of the calculated field not date only?

I re tweaked mine to use a calculated field and the code above works perfectly when checking the dates match (I would recommend using date only for your calculation although after testing it doesn't seem to make a difference still works with code above): 

My field: 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[DylanB02]

Thanks,

I checked the column on SP and it was set to date only, see below.

It also, still seems to be showing as 10th of the 10th for some reason.

I changed the condition to have a formatdatetime as below also.

Really confusing, appreciate your help with this so far.

[AndrewR1]

Hi @DylanB02 that all looks fine to me mate! not sure why its giving you the 10-10-2023 that's a strange one but can you create a test item that will have the 25-10-2023 as the end date and see if the automation run and send you the email? it could be how your environment is set up that the default time is always 23:00 (Mine is et to 07:00 this is why I can do -14!) so if this still pulls the 10-10-2023 then change your calculation to - 13 this should solve that issue 🙂 

And its no problem mate I'm happy to help!

Lets try adding the new item in your list and then test the automation by using this: 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[DylanB02]

Tried adding a new entry with an end date of the 25th so then the EndDateBefore14Days shows as the 12th which is today, still not receiving any emails from this.

For some reason it just isn't doing the -14 properly, it's always 1 off no matter how many days I take off the end date.

Is there anyway I can work out the 'EndDateBefore14Days' within the flow or instead of it matching try and do a less then or greater than maybe?

Thanks

[AndrewR1]

Hi @DylanB02 In your calculated column set this to : 

[EndDate] - 13

this will give you 13days and 23 hours as your default time is set to 23:00 this will then return todays date! 

This is probably the best way to achieve this with little work involved 🙂 Change this and  give it another test!

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[DylanB02]

Hi Andrew,

Already was set to this, same results as before, very odd.

Thanks

[AndrewR1]

Ok @DylanB02  in that case can you then change your end date to date time field and set the time to say 07:00 and change this back to -14? 

Lets see what that returns us 🙂 this is very strange but its to do with your environment time zone settings which we cant change just for this! 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[DylanB02]

Updated this here

It seems to have it defaulted at midnight and I'm not sure where this can be changed,

Still no email sent 

[AndrewR1]

Yes that's fine can you now change the end date column to include the time? like above then set this to be 07:00 :

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[AndrewR1 · Accepted solution]

Hi @DylanB02  not sure why I didn't think of the earlier my apologies you can just do the following instead as you end date is a calculated filed you cannot change the date and time format so what you can do instead is just add a day to the outputs of your 14 days effected date: 

formatDateTime(addDays(variables('ItemEndDate'),+1),'yyyy-MM-dd')

You will need to make this change here(in your check condition)in your flow: 

This will then pass the 11-10-2023 + 1 day which is 12-10-2023 which is todays date 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[DylanB02]

Much appreciated, it seems to be working correctly as is but if I experience any issues I'll have a look at that too.

Thanks