Creating Data Change Logs using Pipelines 

Are you an app builder who needs to keep track of how your data is changing over time? You can do this by creating your own data change logs using Pipelines. Our Pipelines technology empowers builders of all backgrounds to automate work across integrated systems. This can be accomplished using either Audit Logs or Pipelines. But which option is right for you? 

 

Audit Logs or Pipelines? 

Account administrators can access Quickbase’s built-in Audit Logs via the admin console. (Audit Logs are available included in our Platform and Unlimited plans, and available as an add-on for Premier plans.) 

Since you can track changes to your record data using either Audit Logs or Pipelines, which solution should you choose? While they may look similar at first, each method serves a different purpose. Here are the main factors you should consider to decide which feature to use to track data changes: 

• Choose Audit Logs when the data change logs may be needed for any kind of Governance, Risk, or Compliance (GRC) program, or for e-discovery. Data change logs you create with Pipelines are records that are stored in one of your Quickbase apps. That means app builders use roles and permissions to control whether it’s possible to modify or delete data change logs created by Pipelines. This makes Pipelines unsuitable for this scenario. 

• Choose Pipelines when app builders need the data change logs to help manage their Quickbase app. For example, an app builder can review data change logs to troubleshoot issues for their app users. Only account or realm administrators can access Audit Logs, making them unsuitable for this scenario.
  

• Choose Pipelines when end users need to see the data change logs. For example, reviewing the history of a major project can help a line-of-business manager assess how well the project is going, or uncover issues. Data in Audit Logs can’t be displayed on the record they’re tracking, making them unsuitable for this scenario.
  
• Choose Pipelines if you need to track changes in more than 20 applications per realm, or 50 fields per table. Audit Logs allow changes to be tracked in up to 50 fields per table, in up to 20 applications per realm. 

Many Quickbase builders have created their own customizable audit/change history logs using Automations. This guide will help transition those use-cases from automations to pipelines. 

 Data integrity 

You will need to use roles and permissions to control access to any audit logs created by pipelines. Quickbase’s native Audit Log feature in the Admin Console is only accessible by Realm Admins, whereas the logs created by the pipeline are subject to the same record permissions as any Quickbase record. Be sure that custom audit/change logs are view only, and cannot be added to, deleted, or modified manually, and that only the appropriate users have visibility into them.  

TIP: Did you know you can restrict an app admin’s ability to add, edit, or delete records? Update your Administrator role so it cannot add/edit/delete data change records. This reduces the potential for someone to alter data change logs by mistake. 

Using Pipelines to create Data Change Logs 

Example table structure: 

Projects -> Change History Logs 

Scenario: When a Project record is created/modified, the values in the Status and Project Manager fields should be captured in the child Project History Log table, along with information about who created/modified the Project, and when the change was made. 

Example: 

This will require the use of "$prev" to call the previous value (old value before a change) of a field. Your log table must have a field for the current and previous values of each field you are interested in tracking. 

Overview of pipeline 

Step A: Trigger the pipeline "On New Event" in the Projects Table 

Step B: Create a related child record in the Change History Logs table 

 

 

Guided Instructions 

 

Step A: Trigger "On New Event" 

Step A triggers the pipeline when a new Project is created, or a project is modified. 

Setup: 

1. Create a new pipeline and give it a descriptive name. 
2. Open the Quick Base channel, then open the Records category . 
3. Drag the On New Event trigger onto the canvas. 
4. In the Account input, select your user token or enter a new one (click here to learn how).  
5. For this example, select the Projects table, which is the table you’re interested in tracking data changes for 
6. In the Specify Fields for Use in Subsequent Steps input, select the Project Manager and Status fields. This allows you to leverage these fields values later in the pipeline. 
   Note: If you’re using a custom key field, you’ll need to define that here. 
7. In Trigger on Any Field choose "No". We only want to trigger the pipeline when the fields we are interested in tracking change. 
8. Once you select "No" in Step 7, a new option will appear above, Trigger on Any of These Specific Fields. Choose Status and Project Manager. Note: choose any field you are interested in logging changes for 
9. Set On Add Record and On Modify Record to "Yes" 

 Note: You don't need to add conditions unless there are specific instance where you don't want the Pipeline to fire. Otherwise, we want the pipeline to run whenever our specified fields change. 

 

Step B: Trigger when Project is Created 

Step B creates a child record in the History Change Log table, capturing the pertinent info for the Project. 

 

Setup:  

1. Drag the Create Record action onto the canvas as Step B.  
2. In Account, select your User Token.  
3. Select the Change History Logs table.  
4. Under Select Fields to Specify Values, select the fields to be captured as part of a history log. In this example, select Project Status, Project Status Previous, Project Manager, Previous Project Manager, Modified By, Related Project.  
   Note: If you’re using a custom key field, you’ll need to define the reference field here, and map your custom key field to it in the next step. 
5. The fields defined in step 4 will appear as input fields. Populate the input field boxes with the appropriate data values from Step A by dragging the field values in. Be sure to map Record ID in the Related Project field if you’re not using a custom key field. 
   Note: For the User fields – select the email address 
6. Once you have your fields mapped, for the "Previous" value fields you'll need to "$prev" within the field input. 
   1. Previous Project Manager becomes {{a.$prev.project_manager_email}} 
   2. Previous Project Status becomes {{a.$prev.status}} 

Resource

• Updating a Child Record when the Parent Record is Modified with Pipelines
• Updating a Triggering Record with Pipelines
• Quick Base Pipelines help page
  
• Recording of the Introducing Pipelines webinar.
• Importing Pipeline YAML
• How to Use the Bucket Channel to Schedule an Archive to Box
• Build better trend reports with Pipelines

#Pipelines