Hi @RP1,
Because Airtable tables do not have an inherit row order and it’s possible to have multiple tasks occur on the same date, you’ll need to either
- create a end date field and use datetime_diff() or workday_diff() between these two dates
- create a linked record field pointed at the same table, populate it with dependencies, use a roll-up or lookup + formula field on the dependent task to get the difference
Hope that helps
Stephen
Hi @RP1,
Because Airtable tables do not have an inherit row order and it’s possible to have multiple tasks occur on the same date, you’ll need to either
- create a end date field and use datetime_diff() or workday_diff() between these two dates
- create a linked record field pointed at the same table, populate it with dependencies, use a roll-up or lookup + formula field on the dependent task to get the difference
Hope that helps
Stephen
Thank you for the input.
The end date would be the date of the next status log.
I figured that the solution would involve lookup/rollup but I don't know how to put it together.
I've done a fair amout of lookups before, but rollups are new to me.
If anybody has an idea it would be much appreciated.
In order to use a lookup or roll up, you will need a linked record field pointed at the same table. You can name this linked record field "Predecessor Status Log" and create an automation that inserts the last available record (based on date) into this field. Then you can create a lookup on this field to get the "Predecessor Status Log Date" as field on the same record to use in a datetime_diff() formula field.
In order to use a lookup or roll up, you will need a linked record field pointed at the same table. You can name this linked record field "Predecessor Status Log" and create an automation that inserts the last available record (based on date) into this field. Then you can create a lookup on this field to get the "Predecessor Status Log Date" as field on the same record to use in a datetime_diff() formula field.
I accepted as a Solution by accident.
I get this part:
"In order to use a lookup or roll up, you will need a linked record field pointed at the same table. You can name this linked record field "Predecessor Status Log" (...)
(...)Then you can create a lookup on this field to get the "Predecessor Status Log Date" as field on the same record to use in a datetime_diff() formula field.
The automation is the problem:
(...)and create an automation that inserts the last available record (based on date) into this field. (...)
I thought about doing something like:
When a record is created in Log
Update a record
Table: Log
Record ID: Airtable Record ID
Fields: Predecessor Status Log Date
It didn't work. I'm sure missing something.
I think I might have missed that Find Records has a limit of 100 records and that Airtable does not allow conditions in repeating group statements. In that case, either this will require a script to get the max date from all records or every log status needs to be linked to the same record in a parent table where you can use a roll up to get the max date and then use this in the automation. Apologies for the change.