Hi,
I have three table: Employees, Holiday and Medical issues.
Sometime a employee has holiday or medical issue and he are not available.
When he is not available in Holiday table there is a virtual column [STATUS] = ´NOT AVAILABLE’ , (Yes/No Type)
The formula STATUS is:
OR(([END DATE] < TODAY()), ([START DATE] > TODAY()))
where, end date is the last day holiday and start day is a first date holiday
For Medical issue is the same.
I Would like change the ‘status’ in Employee table, for instance: if the holidays is over then ‘status’ change to available.
I tried to make a virtual column in Employee table call ‘STATUS’ and the formula is:
OR(IN('NOT available ',Holidays[Status]), IN('NOT available ',Medical issues[Status]))
But when Holiday are over not changed the Status to ‘Available’ in Employee table. I have to click edit to the status has changed.
Now, I have tried to make a Automation, but I don not know if when holidays end the status change automatically
Somebody has a tips for this
Solved! Go to Solution.
You’ll need to add to the FILTER() expressions to consider the user. For instance:
IF(
OR(
ISNOTBLANK(
FILTER(
"Holiday",
AND(
([Email] = [_THISROW].[Email]),
OR(
([End Date] < TODAY()),
([Start Date] > TODAY())
)
)
)
),
ISNOTBLANK(
FILTER(
"Medical issues",
AND(
([Email] = [_THISROW].[Email]),
OR(
([End Date] < TODAY()),
([Start Date] > TODAY())
)
)
)
)
),
"NOT AVAILABLE",
"AVAILABLE"
)
The way I would do this is from the employee table, create a virtual column to count the number of Holidays and Medical issues where [END DATE]>=Today() and [START DATE]<=Today() Something like this
COUNT(Select(HolidayTable[HolidayTableKeyColumn],AND(
[END DATE]>=Today(),
[START DATE]<=Today()
)
))
+
COUNT(Select(MedicalTable[MedicalTableKeyColumn],AND(
[END DATE]>=Today(),
[START DATE]<=Today()
)
))
This will give a number. But if you want it to show AVAILABLE or NOT AVAILABLE then wrap it in an IF statement:
IF(
COUNT(Select(HolidayTable[HolidayTableKeyColumn],AND(
[END DATE]>=Today(),
[START DATE]<=Today()
)
))
+
COUNT(Select(MedicalTable[MedicalTableKeyColumn],AND(
[END DATE]>=Today(),
[START DATE]<=Today()
)
))>0,
"NOT AVAILABLE",
"AVAILABLE"
)
Hope this helps
Thanks!
Hi Simon,
I do not why, but every people who not have holiday neither medical issues (they are not on the tables) then they have count = 2 and ‘NOT AVAILABLE’
is like repeat for every employee
I have tried put past dates but I had the same issue
I have tried to figure out without success
You don’t need nor should you use Automation for this.
As @1minManager suggested, a virtual column attached to each user is a good approach. My vesion of an expression for it is:
IF(
OR(
ISNOTBLANK(
FILTER(
"Holiday",
OR(
([End Date] < TODAY()),
([Start Date] > TODAY())
)
)
),
ISNOTBLANK(
FILTER(
"Medical issues",
OR(
([End Date] < TODAY()),
([Start Date] > TODAY())
)
)
)
),
"NOT AVAILABLE",
"AVAILABLE"
)
As the Holiday and Medical issues tables grow, the virtual column approach could slow app sync times.
Thanks Steve,
I do not know why when the state changed on one case (Employee) change the state for every employee.
if one case is ‘NOT AVAILABLE’ then every employee is ‘NOT AVAILABLE’ , even if he has not data in the table “Holiday” and “Medical issues”
You’ll need to add to the FILTER() expressions to consider the user. For instance:
IF(
OR(
ISNOTBLANK(
FILTER(
"Holiday",
AND(
([Email] = [_THISROW].[Email]),
OR(
([End Date] < TODAY()),
([Start Date] > TODAY())
)
)
)
),
ISNOTBLANK(
FILTER(
"Medical issues",
AND(
([Email] = [_THISROW].[Email]),
OR(
([End Date] < TODAY()),
([Start Date] > TODAY())
)
)
)
)
),
"NOT AVAILABLE",
"AVAILABLE"
)
Thank you! Steve
regards
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |