Status Board Timing Element (6.9.1.0+)

Status boards may have columns added to measure the time since an event happened, until an event happens, or to measure the time difference between two events.

Instructions

1. Navigate to Status Boards in the Settings.

2. Add a ‘Custom’ column.

3. Configure using Raw SQL.

   1. These functions are not available in the Condition Builder

4. SQL Timing Elements:

   1. MinutesUntil(time) takes 1 time argument, returns the minutes until that time.

      1. This will also return the minutes since a time, and display a negative number

   2. IsInFuture(time) takes 1 time argument, returns if it's in the future or not.

   3. TimeIsWithinXMinutes(time, integer) takes 1 time argument and 1 integer argument, returns if time argument is within the int argument minutes.

   4. TimeIsMoreThanXMinutesAgo(time, integer) takes 1 time argument and 1 integer argument, returns if time argument is more than the int argument minutes ago.

   5. TimeIsLessThanXMinutesAgo(time, integer) takes 1 time argument and 1 integer argument, returns if time argument is less than the int argument minutes away.

Examples:

Example #1 MinutesUntil(time)

In this example, the time since the request was created is being displayed.

Text:

'Time Since Creation ' || -MinutesUntil(DateOfService) || ' minutes ago'

**Note: adding the - in front of MinutesUntil(DateOfService) will remove the negative value and make it positive. Since the time has passed, it would have shown negative minutes.

Example #2 IsInFuture(time)

In this example, the time set for when Triage is Needed is being evaluated for being in the future vs in the past.

Text:

CASE WHEN (IsInFuture((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem))) = 1
THEN
'Time is in the future: ' || (SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed")'), ', ') FROM transferItem)
ELSE
'Time has already past'
END

Example #3 TimeIsWithinXMinutes(time, integer)

In this example, if the ‘Triage Needed’ time is within 10 minutes, then it will display yes, otherwise it will display no.

Text:

CASE WHEN (TimeIsMoreThanXMinutesAgo((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem), 5))
AND
(SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Completed" ORDER BY DateTime DESC)'), ', ') FROM transferItem) ISNULL
THEN 
'Triage Still Needed, it has been : ' || MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem))
WHEN 
(SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Completed" ORDER BY DateTime DESC)'), ', ') FROM transferItem) IS NOT NULL
THEN 
'Completed'
ELSE 
'Not Completed'
END

Text Color:

CASE WHEN (TimeIsMoreThanXMinutesAgo((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem), 5))
AND
(SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Completed" ORDER BY DateTime DESC)'), ', ') FROM transferItem) ISNULL
THEN 
RGB(0,255,64)
WHEN 
strftime('%H:%M', (SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Completed" ORDER BY DateTime DESC)'), ', ') FROM transferItem), 'localtime') != ''
THEN 
RGB(255,0,128)
ELSE 
RGB(255,128,0)
END

Example #5 TimeIsLessThanXMinutes(time, integer)

In this example, the timestamp is marked with a future time, when that time is within 5 minutes it will start to display that to the user. Notice it will tell you when the time is passed and the color of the text is different based on the amount of time that is left as well as the background color.

Text:

CASE WHEN ((IsInFuture((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem))) = 1 AND (TimeIsWithinXMinutes((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem), 5)))
THEN
'You have ' || MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) || ' minutes to go'
WHEN (IsInFuture((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem))) = 0
THEN
'Time has Passed'
END

Text Color:

CASE WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 5
THEN RGB(0,255,255)
WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 4
THEN RGB(0,0,255)
WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 3
THEN RGB(255,255,0)
WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 2
THEN RGB(255,128,0)
WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 1
THEN RGB(149,0,0)
ELSE RGB(255,255,255)
END

Background Color:

CASE WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 5
THEN RGB(255,128,128)
WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 4
THEN RGB(255,128,128)
WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 3
THEN RGB(255,128,128)
WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 2
THEN RGB(255,128,128)
WHEN MinutesUntil((SELECT group_concat(withTransferItem(ID, '(SELECT DATETIME FROM TcEntryTimes WHERE time_name = "Triage Needed" ORDER BY DateTime DESC)'), ', ') FROM transferItem)) = 1
THEN RGB(255,255,128)
ELSE RGB(192,192,192)
END