I'm trying to validate entries in a classic sharepoint calendar, let's name it Cal1.
Explanation of the superseding problem: People enter dates in the calendar. Calendar entries resemble planned access times to building parts. For this purpose additional fields in Cal1 are created: Person, Group, Rooms
In another list (let's call it Acc1) I hold assignments to user groups. List item has the fields: Person, Group, Start-Date, End-Date. This means: If a date is within the designated period, the person will be assigned to a certain group. For keeping history there will be more than one possible item for each person.
Normal column validation won't work, because of the multiple entries in Acc1. I looked at Help with date list validation and blank fields but it doesn't address the different column definitions and is limited to comparisons within one item.
Now, when a new item is added to the Cal1 or an existing is altered, I want to trigger a flow which does the following: Takes the beginning and end of Date plus Person entered in the new item, checks for every item of Acc1, if Person matches Person there and if the complete Date is within Start-Date and and End-Date.
Of course, the date of the calendar will have Time, too. Acc1 should only have a dates without time.
For the comparison in Flow I assume the following might happen.
If the date has no time (as selected in the column definition) the hours, minutes and seconds will be set to 0. I.e. End of a date with same day as Acc1's End-Date e.g. Sep. 19th 15:15:00 will be considered greater than a Sep. 19th without time. Am I thinking right, that if I want the End-Date to mark the last valid day of an assignment to a group, I have to add 24 hours to it for the comparison?
And for the comparison of the start days is it the other way round?
Or are there other pitfalls when comparing dates with different column settings, i.e. do I have to convert the dates retrieved by Flow?
Should I even change the column definition of Acc1 to Date+Time?