Automatic Update from status another table

[Julio_Moyano]

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

[1minManager]

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

[Julio_Moyano]

Thanks!

[Julio_Moyano]

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

[Steve]

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.

[Julio_Moyano]

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”

[Steve · Accepted 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"
)

[Julio_Moyano]

Thank you! Steve

regards