You can add a new linked record field to the table of status changes. The new linked record field would link to the next stage (when there is one). Then use a lookup field to of the next stage’s start date/time as the current record’s end date/time. Finally, use DATETIME_DIFF in a formula field to calculate the duration of the stage.
To manage the linked records with the automation, you need to keep track of the record for the most recent stage. So the project table needs a new linked record field that will be managed by the automation.
Actions for the automation:
- Create a new status record, linking it to the project and setting the status to the project’s status.
- Get the previous linked status record from the project and set it’s “next status” linked record field to the newly created status record.
- Update the project record’s “last status record” to the newly created record, so that it will be ready for the next status change.
I usually build automations like this with scripting, so I can do different things if there are missing inputs.
However, this is also possible to create without scripting. Without scripting, you would need to have two different automations. One automation would perform the steps outlined above. The other automation would leave out the second set for the first stage, as there would be no previous stage. Use the “when record meets conditions” trigger to differentiate between the two.
