Audit log active changes
Jamie is a project manager who has been trying to understand why tasks are slipping. She wants to keep a record of the original start and end dates. She wants to be able to see how many times they change and what the new dates are. She is not sure how to do this and she is asking for your help.
You tell her that you know how to do it in Quick Base and will build it for her. First you will create a table to use for an audit log and then create the automation to add the data to the audit log. You will also create a relationship between the Tasks table and the Audit Log table so that you can see the number of times the dates have changed. Let’s look at the steps that you go through to build this for her.
Step 1: Create the table and fields
Start by creating the table to hold the audit log data.
- Click New Table
- Select From scratch
- Enter Audit Log for the table name
- Enter Log for the record name
- Choose an icon
- Enter the description: This table tracks the date changes from the tasks table.
On the new table, create the fields you wish to track. Create a field for both the changed and original values. The field types should be the same type as the original ones on the source table. Create the fields.
- Enter the field labels
- Select the field types
- Click Add
|Old Start Date
|New Start Date
|Old End Date
|New End Date
The automation will copy both the original data and the changes to that data into your new Audit Log table. We want to create a new audit log record every time the trigger happens to keep track of all changes.
Create the automation to trigger when data changes.
- Click When data changes to set the trigger
- Enter a name for your automation: Audit Log
- Enter a comment for your automation: Every time the start or end date change create a new record in the audit log table
Configure the automation trigger to audit for when a record is modified and the start date or end date changes.
- Deselect adds a record because we only want it to trigger when data is modified
- Select modifies a record because we want it to trigger every time data is modified
- Select the Tasks table because it is the table that we want to audit
- Click Additional Changes
- Select Any of the following fields change
- Add the fields Start Date and End Date as these are the fields we want to audit
Create the action to add a record to the Audit Log table.
- Click Add an action
- Click Add a record
- Click Next – will say Finish if you don’t have any relationships
- Choose the Audit Log
- If you did not create the relationship, but the trigger table is a parent, then you will have to navigate to the Audit Log table.
- Click Finish
If you have a relationship, you will see the grey faded text, letting you know that Quick Base will automatically add a record to the Audit Log table. If you don’t have a relationship, Quick Base will prompt you to choose an app and a table.
Configure the action to add data into fields in a new record. The important thing is to choose between the old and new values accordingly.
- In the field dropdown, select Old Start Date. This is where you want the data to go.
- In the Source dropdown, select Copy value from another record. For audit logs you will normally choose this.
- In the Value dropdown, click the arrow next to Tasks records from trigger (new values) to close the new values
- Click the arrow next to Tasks records from trigger (old values) to see the old values fields
- Select Start Date
- Click the + icon to add new rows to add other fields
- Repeat steps to add all your fields. Make sure to assign the new and old values to the correct fields
- Click Save & close
Great job! You have created the Audit Log for Jamie.
Looking to learn more about how you can use Automations?
Task Templating with Automations (Job Aid)
About Quick Base Automations (Help)
Working with Automations (University Lesson)
Complex Workflow and Automations (University Lesson)