Create an Audit Table

🚧

Heavy JavaScript usage

This guide uses a lot of JavaScript, which might be difficult to restructure if you have limited coding experience.

Scenario

This guide shows you how to track updates made to patient records by creating an audit log. When a user edits a row in the Patients table, an automation compares the previous and updated data, identifies which fields changed, and writes those changes to the Patient audit table.

Use this to:

• Maintain a history of edits for compliance or review
• See which fields were changed, what the old value was, and what the new value is

Steps

1. Create the tables
   In the Data area, create the following two tables:

   1. Patients

      1. First_name - type text
      2. Last_name - type text
      3. DOB - type date
      4. Phone - type text
      5. Email - type text
      6. Address - type long form text
      7. Last_visit_date - type date
      8. Audit_logs - relationship (one to many)

   2. Patient audit

      1. Column_name (field that changed) - type text

      2. Old_value - type text

      3. New_value - type text

      4. Action_type (e.g. "UPDATE") - type single select

      5. Patient - type relationship (many to one)

      6. Changed_at - type text and set the default value to {{ now }}

         

2. Generate screens

   1. Go to the Design area

   2. Click the plus to add a new screen

   3. Select Table

      

   4. Select Patients as the source for this new screen

      

   5. Then select New screen, this will generate the following;

      
      1. A table component to view all patients with a create row button
      2. A form page were you can create new patient records
      3. A form page were you can edit existing records by clicking on a row in the table

3. Set Up Automation
   Navigate to the Automation area and create a new automation:

   1. Click the Updated by trigger type for your automation

      

   2. Select Patients as the source table it will watch for changes

   3. Add a new step called JavaScript

      

   4. Copy the below code into the automation step

      JavaScript

      const oldRow = $("trigger.oldRow");
      const newRow = $("trigger.row");
      
      const fieldsToCheck = [
        "First_name", "Last_name", "DOB",
        "Phone", "Email", "Address", "Last_visit_date"
      ];
      
      const changedFields = fieldsToCheck.reduce((changes, field) => {
        const oldVal = oldRow[field];
        const newVal = newRow[field];
        const normOld = normalize(oldVal);
        const normNew = normalize(newVal);
      
        if (!(normOld == null && normNew == null) && normOld !== normNew) {
          changes.push({
            Column_name: field,
            Old_value: oldVal == null ? "" : String(oldVal),
            New_value: newVal == null ? "" : String(newVal),
            Action_type: "UPDATE",
      			_id: $("trigger.row")._id
          });
        }
        return changes;
      }, []);
      
      return changedFields;
      
      function normalize(v) {
        if (v == null) return null;
        if (v instanceof Date) return v.toISOString();
        if (typeof v === "string") {
          const d = Date.parse(v);
          return !isNaN(d) ? new Date(d).toISOString() : v.trim();
        }
        return v;
      }
      
      

      This returns an array of changed fields in the format expected by the audit table.

   5. Add a Create row step with the source set to the Patient audit table

      
      1. Add looping
      2. Use the output value from the JavaScript step for the loop
      3. Add the looped outputs against each column

         1. Columns	Binded values
            Column_name	{{ loop.currentItem.Column_name }}
            Old_value	{{ loop.currentItem.Old_value }}
            New_value	{{ loop.currentItem.New_value }}
            Action_type	{{ loop.currentItem.Old_value }}
            Patient	{{ loop.currentItem.\_id }}

Example

Automation Output

Audit logs in app

App export