Sharepoint Calendar Notifications Flow

[Anonymous]

Hi there,

I have built an Event Reminder Flow whereby it sends an email reminder when an event in my Sharepoint Calendar is going to happen. I have built it with a substring formula so that the EventDate is equal to the UTCNOW however when I run the flow it sends me all of the events in the calendar i.e. it is not recognising the date in question.

My guess is that there is a problem with the substring or UTC formulas. 

I have tried making the events in the calendar all day events and a specific time frame and it´s not working. Events are also not recurring, they have all been put in individually. I originally thought it might be because the column EventDate is also taking into account time but it just returned me error when I put this in. 

Please help me out. 

Thanks!

Here is my flow:

[ArchitectMadhan]

Hi @Anonymous ,

The date format you use for utcnow is "yyyy-mm-dd", but you didnt specified the date format you get from the substring. Please make sure both are having same format. Also without getting all events from the SharePoint, you could have used the filter query option to get list items that are required to send the remainder email.

If you feel the above solution works for you, please mark this as solution.

[Anonymous]

Hi there,

Thanks for your reply. Can you draw up the formula here? I am not sure how to draw this formula up. 

Also, how would I do the filter query option? I have tried to do this but I´m no expert on this and would like to learn how to use.

Thanks!

Stuart

[ScottShearer]

@Anonymous 

See my example below.  I used Filter Query as suggested by @ArchitectMadhan .  Get items will retrieve only events for today.  Please note that this will not work with recurring events.  Also, note the single quotes in my filter query.  Finally, SharePoint stores dates in a yyyy-MM-dd format.  Finally, you may to make an adjustment for your timezone.

[Anonymous]

Okay, I see what you are doing in the filter query but can you please provide the formulas in text format so that I can copy and paste? Do I also need to edit the substring in the condition? 

I need the formula in text to copy and paste into my formula.

Thank you for your help!

[ScottShearer]

@Anonymous 

The formulas that I used are shown in the comments section of the Get Items action.

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

addDays(utcNow(),1,'yyyy-MM-dd')

If you copy and paste, you'll probably want to remove and replace the single quotes.

Also, you shouldn't need your condition - it was checking the date.  With the filter you will only receive items that meet your criteria specified in the Filter Query.  If you use my example, just send an email inside of the loop at the bottom of my Flow.

[Anonymous]

This is still giving me error... I have tried to change the the "1" in the add days to "0". Still doesn´t do anything...

[ArchitectMadhan]

Hi @Anonymous ,

The screenshot is not clearly visible for me, could copy and paste the error message alone?

[Anonymous]

Hi @ArchitectMadhan 

Here you go:

The expression "EventDate ge '2021-07-03' and It '2021-07-04'" is not valid. Creating query failed.
clientRequestId: c2812021-ff28-4569-8d44-8cca906f7484
serviceRequestId: c2812021-ff28-4569-8d44-8cca906f7484

Thanks

Stuart

[ArchitectMadhan]

Hi @Anonymous ,

Please check whether the Event Date field is of "Date only" type in SharePoint calendar. 

 like show in the screenshot

[ArchitectMadhan]

Hi @Anonymous ,

If you want to know step by step process to send reminder email using Power Automate Schedule Flow, please follow the steps given in the below link.

https://learningtechnixs.blogspot.com/2021/07/send-reminder-email-for-tasks-nearing.html?m=1 

If the above post is helpful for you to solve the problem, please mark it as solution.

[Anonymous]

Hi @ArchitectMadhan 

I think you are right, it´s taking into account time as well as the date. Can you please show me how to change the Event Date field to "Date only" type in SharePoint calendar?

I cannot find it at all. Is it possible to get rid of the "time" field?

Thank you

[ArchitectMadhan]

Hi @Anonymous ,

In SharePoint List Settings, you have all the fields and field types, there you go to Event Date field and select the option from Date time to Date only.

How to go to list settings :

https://support.microsoft.com/en-us/office/edit-list-settings-4d35793b-246e-42a3-990c-563a83795b7f

 

If you feel this post is useful for you solve the issue, please accept this as solution.

[Anonymous]

Hi,

This will not let me edit the column "End Time" so what I have done is create another column called "EndDate" with the parameters shown in your screenshot and it´s giving me this error still...

The expression "EndDate gt ´2021-07-05T00:00:00.0000000´ and EndDate lt ´2021-07-05´" is not valid. Creating query failed.
clientRequestId: c2a2b9b5-b834-4082-adb0-3eba94ec1603
serviceRequestId: c2a2b9b5-b834-4082-adb0-3eba94ec1603

I really do not understand why this is happening... It seems that it is still taking time into account...

Here is the column settings that I created.

[ArchitectMadhan]

Hi @Anonymous ,

From the error: The expression "EndDate gt ´2021-07-05T00:00:00.0000000´ and EndDate lt ´2021-07-05´" is not valid. Creating query failed.

You need to change only the above highlighted part in red, the first formula of formatDateTime() function. Can you check whether that formula is as shown below ?

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

If you feel this post is useful for you solve the issue, please accept this as solution.

[Anonymous]

Hi,

Yes, the formulas I have in this part :

Are the following:

EndDate gt ´formatDateTime(addDays(utcNow(),0,'yyyy-MM-dd')) ´ and EndDate lt ´formatDateTime(utcNow(),'yyyy-MM-dd')´ 

Here is an example of a calendar event with the new column EventDate added:

I have tried changing the formula date structure to dd-MM-yyyy instead of yyyy-MM-dd but this didn´t work either... 

[ArchitectMadhan]

Hi @Anonymous ,

From the EndDate in your calendar item I can see the date is in "dd/MM/yyyy" format. But whatever you are using is in "yyyy-MM-dd" format.

Also your formula is totally opposite to what it has to be.

EndDate gt ´formatDateTime(utcNow(),'dd/MM/yyyy') ´ and EndDate lt  ´formatDateTime(addDays(utcNow(),0,'dd/MM/yyyy')) ´

If you feel this post is useful for you solve the issue, please accept this as solution.

[Anonymous]

This is still not working... I have tried putting dates in with "/" and also "-" and it does not work.

Here is the error message:

The expression "EndDate gt ´2021/07/05´ and EndDate lt ´2021-07-05T00:00:00.0000000´" is not valid. Creating query failed.
clientRequestId: fbcc5c54-5318-42e3-94ef-99309154e5db
serviceRequestId: fbcc5c54-5318-42e3-94ef-99309154e5db

Because of this error, I changed the "/" to "-" in one of the formulas and the following error is given:

The expression "EndDate gt ´2021-07-05´ and EndDate lt ´2021-07-05T00:00:00.0000000´" is not valid. Creating query failed.
clientRequestId: c5641e43-6625-47e6-acd0-70a4fa948482
serviceRequestId: c5641e43-6625-47e6-acd0-70a4fa948482

Is there any way that I can workaround this? Obviously the filter query is not working. I have seen other forums and they use Compose actions in their formulas? 

[ArchitectMadhan]

Hi @Anonymous ,

Here is what you need to do, its the exact scenario. First you change your End Date format in Field settings from dd/MM/yyyy to "yyyy-MM-dd". After that you try the below example.

If you feel this post is useful for you solve the issue, please accept this as solution.

[Anonymous]

Hi @ArchitectMadhan 

I have changed the format to "yyyy-MM-dd" and it still doesn´t work...

Here is the error message:

The expression "EndDate gt ´2021-07-05T00:00:00.0000000´ and EndDate lt ´2021-07-05´" is not valid. Creating query failed.
clientRequestId: 240aceef-2a8d-4694-bd3d-e11f9bf756f1
serviceRequestId: 240aceef-2a8d-4694-bd3d-e11f9bf756f1

It seems to still be recognising time... 

I have also tried to create a completely new flow and the same error message appears...

Can you please advise? Is there any workaround available?