Exclude one set of data from another set

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.

Hey there! Can you change the settings on your linked document that “anyone with link can view”


Hi @Piotr_Surmacki and welcome to the community! :smiley:

Your doc, as @Scott_Collier-Weir pointed out, is locked, you have to share it with anyone! (or a copy of it :slight_smile: )
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 :slight_smile:

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 :slight_smile:

Hi @Scott_Collier-Weir - done :grin: Sorry for this.

Sorry @Mario - it should be available right now.

The condition is true or false depending on the Classes.[Date&Time] value. So it’s not the matter of adding additional checkbox column I think.

Hi @Piotr_Surmacki , i think i’ve solved it simplifying your formula!

Wrote like that it works!

Now if i use the select list i got some teachers for the classes :slight_smile:

P.s. i’ll give you some best practices for future usage :slight_smile:

You’ve got 3 tables, Teachers, Availabilities, Classes

Each of those have a type of data in 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 :slight_smile:

Hope i’ve helped :slight_smile:

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.

hi @Piotr_Surmacki , i’m pretty sure it works now!

Are you sure it is not working?

Because in my doc this is working fine and off course it is this is a super simple task to do! :slight_smile:

Which specific case are you refeering to?

Hi @Mario, yes I’m sure it’s not working :wink:

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.

AAAAAAAAAA NOW I GOT IT!!! Sorry :sweat_smile:

'cause “absent” means that in that period he’s absent! i got it gimme some minutes to update the formula :slight_smile:

Half an hour later i got it :slight_smile:
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 :slight_smile:

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 :heart:

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 :slight_smile:

P.s. here’s the doc if you want to copy the formulas :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.