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
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?
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
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
I didnโt see that
You figured it out anyway, list substraction is one solution.
But I see A problem:
[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
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()))
User | Count |
---|---|
25 | |
25 | |
24 | |
21 | |
20 |