Automatic Update from status another table

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

Hi @Julio_Moyano

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

Simon@1minManager.com

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!

1 Like