Skip to main content

Do you know how manufacturing facilities have a "days since last accident/injury/whatever" sign? I need to create something like that on a dashboard. I was hoping to be able to use the simple "summary" extension but I can't seem to figure out how to do it.

I have a table that tracks events and their corresponding dates. Events happen almost every day. Every once in a while, there is a day that has 0 events. Each event also has a time duration (minutes). On days with no events at all, a record is still created (via automation) for charting purposes, and the field that shows the duration of the event will show 0. 

I'd like to be able to display the number of days since the date when there were no events. 

Example:

DATEEVENT DURATION (MINUTES)
5 days ago1
5 days ago4
4 days ago0
3 days ago3
3 days ago8
3 days ago10
2 days ago3
Yesterday1
Yesterday6
Today6
Today4

So if you view this summary extension block "today", it would show "4" because we've gone 4 days without seeing a 0.

How can I do this?

You could link all of your records to 1 record in another table. Then use a rollup field in that new table that looks at your date field and uses MAX(values), which gives you the most recent date. Use the "only include linked records from the table x that meet certain conditions" and set "event duration = 0". In that same new table with the 1 record, create a formula field with DATETIME_DIFF(TODAY(), {your rollup field in the same table}, "days") . 

That should work. 

You could link all of your records to 1 record in another table. Then use a rollup field in that new table that looks at your date field and uses MAX(values), which gives you the most recent date. Use the "only include linked records from the table x that meet certain conditions" and set "event duration = 0". In that same new table with the 1 record, create a formula field with DATETIME_DIFF(TODAY(), {your rollup field in the same table}, "days") . 

That should work. 


That'll do it, thanks. I also created an automation that links that record on the new Table to "yesterday's" record(s) at midnight each night - the same time another automation creates the new record for "today".

Reply


Terms & Conditions