Solved: Automatic Update from status another table - Page 2

Julio_Moyano · 10-12-2021 06:53 PM

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

Steve

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"
)

View solution in original post