Automation to notify status changes based on previous status

[airballer86]

I want to know when an approved campaign goes back in to revisions so that I can review it again. I have multiple other reasons to want to know when a campaign status changes and what the previous status was; e.g., trigger subtasks for team members, notify leadership etc.

I use the following statuses: planning, assigned, in progress, completed, approved, scheduled and launched.
and two nonsequential values (revisions, missing inputs) that can be used at any point in the process.

What I currently set up:
The status field is a single-select
An automation that captures every change in campaign status and populates another table
That status tracker table is linked to the campaigns table
The status tracker table has a 'status field' last modified date field

What I want is a way to compare the previous status field to the current status field so I can trigger actions based off of the new status. What I cannot figure out is how to connect the most previous status to the current status. 

Any help would be appreciated!

[TheTimeSavingCo]

Hmm, I'm assuming your status logging table is set up something like this:

And this is how you'd like for it to work:

If so, try setting up your automation like this:

The "Find Record" action attempts to look for a status log linked to the triggering task that has an empty "End Status" field (i.e. a record we need to put an "End Status" value in)

The conditionals are:
1. If a record is found linked to this task that has an empty "End Status", and the current status value is "Done", update the "End Status" field
2. If a record is found linked to this task that has an empty "End Status", and the current status value is not "Done", update the "End Status" field and create a new record in "Status Logging" with the current status value as the "Start Status"
3. If no record is found, create a new record in "Status Logging" with the current status value as the "Start Status"

Link to base