Spent more time considering this. You may not need actions or workflows at all. The below makes assumptions about table and column names, adjust as appropriate:
- In the Users table, add a virtual column named Latest to contain a Ref to the user’s latest time sheet entry, with an app formula of:
([User] = [_THISROW].[User])
- Make the Active column of the Users table a virtual column with an app formula of:
([Latest].[Out When] > NOW()])
How the Latest column app formula works:
MAXROW("Time Sheets", "_ROWNUMBER", ...) gets the row from the Time Sheets table with the highest number in the _ROWNUMBER column, but only from those rows matching the given criteria (
...; see below). Usually, the higher the value of _ROWNUMBER, the newer the entry. See also MAXROW().
AND(..., ...) limits the rows considered by MAXROW() to only those that match both of the given criteria. See also AND().
ISNOTBLANK([User]) selects only rows where the User column (of the Time Sheets table) is not blank, just in case a time sheet entry was incorrectly made. See also ISNOTBLANK().
([User] = [_THISROW].[User]) further limits the selection to only those rows that are for this row of the Users table.
[User] refers to the value from the Time Sheets table,
[_THISROW].[User] to that from the Users table.
How the Active column app formula works:
IFS(..., "Active") returns the text, Active, if the given criteria (
...; see below) is met. If not met, a blank value is returned. See also IFS()
OR(..., ...) either of the given criteria (below) must be met. See also OR().
ISBLANK([Latest].[Out When]) looks at the Out When date-time column of the user’s most-recent time sheet entry. If Out When is blank, the user hasn’t clocked-out yet, so must be active. This criteria presumes the mere existence of the time sheet record indicates the user clocked-in and that the corresponding In When column is not blank. See also ISBLANK().
([Latest].[Out When] > NOW()]): if Out When isn’t blank, the user has clocked-out, but may have clocked-out in the future. If so, the user is still active until that time. See also NOW().