Forum Discussion
How calculate a column based on
No. It did not return the the last Recordable Date for all Jobs. It did for only one - 6881. The FIRSTNONBLANK function is not finding the last entry in the data set.
I have an excel file with a subset of the jobs so you can see an example of the data that is in the UMC Safety Incident Log table. How can I upload the file?
Here is an example of the data:
| UMC Status | Date of Incident | Project Number |
| 10/26/14 | 6262 | |
| UMC First Aid | 01/21/15 | 6262 |
| Recordable | 01/27/15 | 6262 |
| UMC First Aid | 06/18/15 | 6262 |
| UMC First Aid | 09/03/15 | 6262 |
| 10/08/15 | 6262 | |
| Recordable | 01/14/15 | 6265 |
| Recordable | 07/19/16 | 6623 |
| Notification | 08/09/16 | 6623 |
| Notification | 10/03/16 | 6623 |
| Notification | 10/07/16 | 6623 |
| 12/01/16 | 6623 | |
| Notification | 05/15/17 | 6623 |
| Recordable | 06/16/17 | 6623 |
| UMC First Aid | 06/29/17 | 6623 |
| UMC First Aid | 03/02/17 | 6881 |
| Recordable | 04/21/17 | 6881 |
| UMC First Aid | 05/10/17 | 6881 |
| UMC First Aid | 06/08/17 | 7000 |
Any further ideas? If you could help me with the syntax for a function for determining the set of UMC Safety Incident Log rows that are related to the udJob row that is being evaluated, I think I could take it from there. If there was a "Recordable" event, I am setting a Custom Column to 1. That is working. So I could perform a sum on the set of related rows and if > 0, then use the Max Incident Date for that related data set, otherwise set the value to the StartDate in udJob.
One twist to this is that there may be a NULL set of related rows if a Recordable event has not occurred.
Here are the expected results.
1. Job 6262 has been closed. I am filtering on Open jobs only in the table udJob. So there isn't a row in udJob for it.
2. Same is true for Job 6265. It is closed so no row is found in udJob.
3. For Job 6623, there are two events in the table UMC Safety Incident Log where the column UMC Status = Recordable. The value to use for the custom column Last Recordable Date in the table udJob is the latest Date of Incident (Max) 2017-06-16
4. For job 6881, there is one event in the table UMC Safety Incident Log where the column UMC Status = Recordable. The value to use for the custom column Last Recordable Date in udJob is the Date of Incident: 2017-04-21
5. For job 7000, there isn't an event where the column UMC Status = Recordable. So the date to use for the custom column Date Last Recordable in the table udJob is the column StartDate from the table udJob. That date is 2017-01-23
If there are no entries in the table UMC Safety Incident Log at all for a Job, then the result would be the same as example 5, the date used is the column StartDate from the table udJob.
