Forum Discussion
How calculate a column based on
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 |
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.