Solved: 3 variable expression - Page 2

CorpIT · 07-30-2019 07:52 AM

I have a table of Times (see attached) and a table of Employees (just a column of employee names) for a time tracking app. I’m trying to create a virtual column of “Current Status” to determine if an Employee is clocked in or clocked out. I need the Clock Type of the latest Timestamp for each Employee. I’m not really sure if I need the virtual column on the Employees table or the Times table? I also need help with the formula. This is as close as I’ve gotten, but it only works for one employee at a time:
SELECT(Times[Clock Type],
AND([Timestamp]=MAX(Times[Timestamp]), [Employee]=[_THISROW])
)

LeventK

@CorpIT
You already have a REF_ROWS column for each of your employee. I just noticed that when I open the post from my laptop. So I believe below AppFormula will be more appropriate:

ANY(
	SELECT(
		[Related Times][Clock Type],[Timestamp]=MAX(SELECT(Times[Timestamp],[Employee]=[_THISROW].[Employee])),
	)
)

View solution in original post