Storing just modified data(Column name, column value) into another audit table

Queries and Resources
, ,
1

Hi Community,

I have a table/View from which I am pulling some data. I want Retool user to modify some missing information or modify existing records.

Once records are updated. I want to update the following Audit table where I just want to update who updated , what (which column and what value) .

[ID],[FieldName] [FieldValue] ,[CreationDateTime] ,[UpdateDateTime,[UpdateProcess] ,[AuthorName]

I am able to get some info from JS transformer as below.

currentDateTime = moment(new Date()).utc().format("YYYY-MM-DD HH:mm:ss")
let selectedRecords = {{AllSecurity.recordUpdates}}

selectedRecords = selectedRecords.map(row => _.set(row, ["UpdateDateTime"],currentDateTime) )
selectedRecords = selectedRecords.map(row => _.set(row, ["UpdateProcess"], 'ReTool' ) )
selectedRecords = selectedRecords.map(row => _.set(row, ["Retool_user"], {{current_user.firstName}} + ' ' + {{current_user.lastName}} ) )

The problem I have is to use between changeSet vs recordUpdates. if I use recordUpdates it has all columns and no information which column is updated. (User can update all columns except ID). and if I use changeSet , I think I am not able to see ID column as I need to store that in AUDIT table.

return {{Table.changeSet}}
return {{Table.recordUpdates}}

Appreciate any help on above scenario. Thank you!!

2

Hey @deepPrelude, welcome to the community :hugs:

I needed a solution like this in the past and solved it by comparing the Table.data with Table.recordUpdates to determine what changed.

Here's the logic I used (may be overkill, don't judge):

    // we're in a loop
    var replacement = pairingUpdates[i]
    var originalPairing = pairings.find(x => x.id == replacement.id)
    var pairingId = replacement.id
    var replacementUserId = _.map(_.difference(_.map(replacement, JSON.stringify), _.map(originalPairing, JSON.stringify)), JSON.parse)[0]
    
    const empty = {}

    const isObject = x => Object (x) === x

    const diff1 = (left = {}, right = {}, rel = "left") =>
      Object.entries (left)
        .map
          ( ([ k, v ]) =>
              isObject (v) && isObject (right[k])
                ? [ k, diff1 (v, right[k], rel) ]
                : right[k] !== v
                  ? [ k, { [rel]: v } ]
                  : [ k, empty ]
          )
        .reduce
          ( (acc, [ k, v ]) =>
              v === empty
                ? acc
                : { ...acc, [k]: v }
          , empty
          )

    const merge = (left = {}, right = {}) =>
      Object.entries (right)
        .reduce
          ( (acc, [ k, v ]) =>
              isObject (v) && isObject (left [k])
                ? { ...acc, [k]: merge (left [k], v) }
                : { ...acc, [k]: v }
          , left
          )

    const diff = (x = {}, y = {}) =>
      merge
        ( diff1 (x, y, "original")
        , diff1 (y, x, "changed")
        )
    
    // get update information
    var difference = diff(originalPairing, replacement)
    var role = Object.keys(difference)[0].replace(/_user_id/g, "");

Does that work for you?