Trying to make a dropdown in a table with classes where for each class there are only the teachers available who’s availability in the current date is true and absence is false.
Here is my formula:
Filter(Availability,[Date from]<=thisRow.[Date & Time] AND [Date to]>=thisRow.[Date & Time] AND Type=“Available” AND Not([Date from]<=thisRow.[Date & Time] AND [Date to]>=thisRow.[Date & Time] AND Type=“Absence”)).Teacher
The formula is almost fine except one thing - absent teacher is available to pick from the dropdown.
Your doc, as @Scott_Collier-Weir pointed out, is locked, you have to share it with anyone! (or a copy of it )
The goal seems easy so it’s probably just a little tricky thing, share it and me or someone else will give it a look
p.s. a fast trick is to use another way, if in the “teacher” table you add a column with a checkbox that is true is he’s available and it’s false if not, an then you just have to use it in the select list based on that checkbox!
Most of the times when a formula doesnt work, splitting it in different columns or parts help to debug it
Teachers contains People’s profiles, so the main type of data is Peoples
Availabilities contains Teachers availability period, but you’re referring to those rows as just teachers names, it will create confusion! I would try using teachername+available date (or period name, like “carnival”), so you can see way more clear what you’re selecting in the other tables!
Classes, that contains teachers and their availability for that class.
It could me a little messy to understand which teacher’s period you’ve selected if it is named just as the teacher name
Thanks, @Mario but unfortunately this does not solve my problem. Still, the case where within teacher availability period there is an absence period is not solved.
We have two sets of data and we want to exclude from the first part everything which is common part with the other set of data.
For the classes on 2022-03-19 18:30 there are two teachers available right now and should be available only Jan as there is Piotr absence from 2022-03-16 to 2022-03-21.
'cause “absent” means that in that period he’s absent! i got it gimme some minutes to update the formula
Half an hour later i got it
Basically all the problems came from the way you’ve decided to save the data, availabilities in normal occasion is just “dates when this person is available”, so they are not of 2 types, but i can imagine you like it like that and i took it as a challenge
I’ve added 2 column to the classes table (available, not available), those are like the first formula i show’d you, one for available and the other for not available, then the select list is generated comparing those 2 column!
You could compress everything in one formula but it’s extra work that (with love eh!) i’m not gonna do for free
If you don’t care too much you can just hide those 2 column (they are just working column, nothing you’ll need to touch in future) and use it like that
P.s. here’s the doc if you want to copy the formulas
)
Sorry for this.
