Prevent double booking SharePoint calendar for the day

[Anonymous]

The goal of the flow is to prevent double cooking if the category has been assigned for the day (no need to check time) of the calendar. This is what I have so far. If the category is chosen, do not save the item, send email about the double booking.

So for example, April 10, 2019, Category = Meeting then someone trying to book again for the same day and trying to choose Category = meeting, do not save, send email.

Thanks for the help.

[v-yamao-msft]

Hi @Anonymous ,

Do you want to check if the selected category already exists or not? If it already exists (for the same day), delete the newly added one and send an email, stopping double booking.

I have set up the flow likes below. In the following scenario, I am going to check the booking for the same day and stop duplicate booking for one category.

Add trigger When an item is created or modified. Manually enter the name of the calendar list.

Add action Get items, input the code likes below in the Filter query field to filter out events that start on the same day and with the same category.

Then add a Condition with following code:

length(body('Get_items')?['value'])

is greater than

1

Under if yes branch, add action Delete item. Make sure select dynamic content ID from the trigger.

Best regards,

Mabel

[Anonymous]

Thank you for this reply. I followed your flow but somehow, the yes condition is not working. Section value is a lookup field.

[Anonymous]

The yes condition is working now but the original copy is the one getting deleted. I double checked the correct ID is used.

[Anonymous]

Looks like I got some parts to work now but I am having an issue with infinite loop of flow for the NO condition since an Update Item action is considered "MODIFIED" so it will go to the top and run the flow again and again.

[Anonymous]

I think the condition is not working because it is deleting all the entries for the day.

[v-yamao-msft]

Hi @Anonymous ,

Thanks for updating.

In the action Delete item, please make you select the dynamic content ID from the trigger, then it will only delete the newly created one when the condition match.

If you want to update item under If no branch, to get rid of an infinite loop, pleas change the trigger to “When an item is created”.

I have made another test and it does work as expected.

Best regards,

Mabel

[Anonymous]

Thank you for this reply. I got this create item trigger to work but the problem is when a user modify an item, this trigger..

[v-yamao-msft]

Hi @Anonymous ,

I guess that you are using the trigger “When an item is created or modified”. If so, the flow will be triggered whenever a new item is created or an existing item is modified.

I suggest use the trigger “When an item is created” so that the flow won’t be triggered when the existing items are updated/modified.

Hope this make sense.

Best regards,

Mabel

[Anonymous]

because create or modified triggers are required to make sure no double booking of the section or category.

[v-yamao-msft]

Hi @Anonymous ,

I got it. How about removing the action Update item?

Without using the action Update item, the flow won’t be triggered infinitely.

Best regards,

Mabel

[Anonymous]

I looked at it but it is a band-aid solution, not a permanent one. I don't understand, this problem has been going on for more than ONE year so why can't MS just create a separate trigger for the creation and for modification respectively?

[v-yamao-msft]

Hi @Anonymous ,

Yes, currently Flow doesn’t have a trigger for When an item is modified only.

There is a similar request on Flow Ideas Forum, please vote the idea at here:

https://powerusers.microsoft.com/t5/Flow-Ideas/Trigger-flow-When-Item-is-Modified-only/idi-p/237883?advanced=false&collapse_discussion=true&filter=location&location=idea-board:FlowIdeas&q=sharepoint%20when%20an%20item%20is%20modified&search_type=thread

I will help report this issue as well. PGs will take hot ideas into consideration first, hope it could be taken into consideration in future releases.

Thanks for your understanding.

Best regards,

Mabel

[Anonymous]

I just got out of the meeting with the MS Premier Support. They were able to duplicate the infinite loop but could not come up with a workaround. They could not explain either the reason for the Flow Production team nuking the modified trigger only to merge create or modified into one trigger. I told them that this bug has been going on for years now and it seems MS just ignored - they just apologized!

Anyway, the Premier Support team is in contact with the Flow Production team but no estimated time for a resolution.

[v-yamao-msft]

Hi @Anonymous ,

Thanks for updating and sharing.

I really appreciate your efforts.

Best regards,

Mabel

[Anonymous]

Actually, your first reply works only to check for duplicate after adding an item within a few minutes on the same day but if you add a new item for the same date a day later, the condition will return false even there is a duplicate category. I think it is due to the fact, it is checking the time of the event date? Is it possible just check the date without time without changing the Start date column to Date only?

[francescjp]

All the steps seems to work but in Filter Query of getitems if somebody wants to filter by date and time  i would use this expression:

Category eq @{triggerOutputs()?['body/Category/Value']} and ((EventDate lt @{triggerOutputs()?['body/EventDate']} and EndDate lt @{triggerOutputs()?['body/EndDate']} ) or (EventDate gt @{triggerOutputs()?['body/EventDate']} and EndDate gt @{triggerOutputs()?['body/EndDate']} ) or (EventDate gt @{triggerOutputs()?['body/EventDate']} and EndDate lt @{triggerOutputs()?['body/EndDate']} ) or (EventDate eq @{triggerOutputs()?['body/EventDate']} and EndDate lt @{triggerOutputs()?['body/EndDate']} )  or (EventDate eq @{triggerOutputs()?['body/EventDate']} and EndDate gt @{triggerOutputs()?['body/EndDate']} ) or  (EventDate lt @{triggerOutputs()?['body/EventDate']} and EndDate eq @{triggerOutputs()?['body/EndDate']} ) or (EventDate gt @{triggerOutputs()?['body/EventDate']} and EndDate eq @{triggerOutputs()?['body/EndDate']} ) or (EventDate eq @{triggerOutputs()?['body/EventDate']} and EndDate eq @{triggerOutputs()?['body/EndDate']}) or (EventDate lt @{triggerOutputs()?['body/EventDate']} and EndDate gt @{triggerOutputs()?['body/EndDate']}) ) 

I think it covers all scenarios.

Bye!

[francescjp]

Thank you very much for your help!