Alerts from comparison of ranges of dates and other details


Hi! I’m not the most well versed in code/formulas, so I would appreciate simple explanations but I’m not afraid to learn! I also had no idea how to name my ticket…

I’m making an employee schedule/holiday manager and I’ve been asked if it’s possible to get an alert when 2 or more employees with the same job, working for the same client and on the same shift (day/evening/night) are on vacation on the same date.

I have 3 tables, one that list the holidays (with a date range on a calendar view), one that list the employee (where the job (single select), shift (single select) and client (link to the 3rd table) are associated with each person), and one that list our clients. Maybe I shouldn’t have a 3rd table and just do a multiple select (employees can work for more than one client)?

So I guess I might need to first make a lookup for the 3 variables and associate it with the holiday, then maybe extract the single dates from the date range and then compare all date that are the same to find wether or not there’s a problem? Not sure how to do that second and third part.
There’s also that tricky part where if an employee works for 3 clients and another employee for 2, and one of the client is the same but not the others, when the comparison occurs we need an alert event if the “bundle” is different.

I’m not sure how the alerts should work. Would it be a field with a formula, or maybe there’s a block that could display them?
We plan on getting the Pro version, so I can use blocks. It would be better, in fact, if there was a way to display them visually in a block.

Thank you very much!