Filtering a dropdown

[bradley_davis1]

Pulling my hair out day, so any help would be appriecated

I have 2 tables, the first is a table of people the second is a table of holidays that people have booked which have a start date and end date.

When I want to book works for them I want a dropdown (based on names in table 1) only to show those that are available and not on holiday for todays date.

Thanks

[Rafael_ANEIC-PY]

Hello @bradley_davis1, do you have a list of all posible dates that can be booked? that way we could filter those that are available.

I assume there’s a reference between the table of people and the table of holidays?

[bradley_davis1]

Hi, the first table is a list of users

Then the second table is a list of holidays they have booked.

I then want a drop down to lookup the list of users, taking out those who are on holiday today.

Thanks

[SkrOYC]

This on suggested values and buttons or stack config:

FILTER(
  "NAMEOFTHEUSERSTABLE",
  AND(
    [START HOLIDAY FROM]<=TODAY(),
    [LAST DAY OF HOLIDAY]>=TODAY()
  )
)

You could use the same on Valid_If but if the user is no longer on holiday you won’t be able to edit that record

Different Valid_If for each case: Add new record to a table and Edit an old record Feature Requests

Sometimes you have a good validation rule that makes sense if the record is being made for the first time, like making sure a date is inside a certain range or, basically, any data that is date-dependant. The problem is that if you are going to edit that record sometime in the future you will not be able to save it if the validation rule is checking the data against your date-dependant rule. I made an example here if you are instered: As I have shown in that topic, there are workarounds. But…

Edit: This won’t work, check my next reply

[SkrOYC]

bradley_davis1:

taking out those who are on holiday today

I didn’t see that
You figured it out anyway, list substraction is one solution.

But I see A problem:

• You are not using the foreign key on the “holidays” table. You should point to the users table and save it’s key, not the name. The column [Emp Name] should be configured as Ref or Enum basetype Ref and point to the users table.

You want to make sure that when you enter a record on the Holidays table the users available are just the ones that are not in holidays? After changing the things mentioned before, change your expression just a bit:

USERSTABLE[KEYCOLUMN]
-
SELECT(
  HOLIDAYRECORD[EMB NAME],
  AND(
    [START HOLIDAY FROM]<=TODAY(),
    [LAST DAY OF HOLIDAY]>=TODAY()
  )
)

As you can see, the change is just on the first list expression, pointing to the key instead of the name.
Hope this helps

[bradley_davis1]

Ok just managed to solve it, thanks to those who replied

users[name]-select(holiday record[emp name], and([start holiday from] <= today(),[last day of holiday] >= today()))