Comparison for two rows of same record data

Certified Senior Developer

We have a requirement for comparison of same record type data and get the difference of the data & columns. We are aware of "CDT Diff Utilities" which does the same operation and gives the expected output. However we are looking for similar way of plugin availability or workaround to achieve the desired functionality.

Note: We are not looking for manual comparison of the record fields and the data, as we need to perform the comparison across all record types in the application and it will be become very tedious process to achieve by manual comparison.

  Discussion posts and replies are publicly visible

  • So you can write you own (in Appian). Here's some initial guidance:

    • use a!keys() on your first record instance to extract the record attributes
    • for each extracted attribute, test if recordA[fv!item] = recordB[fv!item]  - if it does, it's a match. If it doesn't then it's not (you could out put the actual values in either or both cases if you wished
    • either way, you can generate a map() with attributes that contain the result, and the values from each record's corresponding attribute 
    • because you can define an expression with rule inputs of 'Any Type' then you could pass in your record instances of any type into that expression and you'd have the generic solution you're looking for

    Out of curiosity, what is the use case here? Are you looking to provide an audit solution that compares two different versions of the data between two rows of data (read into Appian as records)? If so, there's still a missing piece of the puzzle. You will need to be able to map each record/attribute's technical name to a name a business auditor would recognize so that you can present this to an end-user.

  • 0
    Certified Lead Developer

    We are using a similar approach. Have a look at it.
    I am not sure about your use case, but I hope this helps with what you are expecting.
    Happy to discuss your use case if this is not what you are looking for.
    We created reusable expression.
    Inputs:
    1) oldRecord (Define ri! as AnyType)
    2) newRecord (Define ri! as AnyType)

    a!localVariables(
      local!allFields: union(
        a!keys(ri!oldRecord),
        a!keys(ri!newRecord)
      ),
      local!differences: reject(
        a!isNullOrEmpty,
        a!forEach(
          items: local!allFields,
          expression: a!localVariables(
            local!fieldName: fv!item,
            local!oldValue: index(ri!oldRecord, local!fieldName, null),
            local!newValue: index(ri!newRecord, local!fieldName, null),
            local!oldText: if(
              isnull(local!oldValue),
              "",
              tostring(local!oldValue)
            ),
            local!newText: if(
              isnull(local!newValue),
              "",
              tostring(local!newValue)
            ),
            local!isDifferent: if(
              and(
                isnull(local!oldValue),
                isnull(local!newValue)
              ),
              false,
              if(
                or(
                  isnull(local!oldValue),
                  isnull(local!newValue)
                ),
                true,
                not(exact(local!oldText, local!newText))
              )
            ),
            if(
              local!isDifferent,
              a!map(
                fieldName: local!fieldName,
                oldValue: if(
                  isnull(local!oldValue),
                  null,
                  local!oldValue
                ),
                newValue: if(
                  isnull(local!newValue),
                  null,
                  local!newValue
                ),
                changeType: if(
                  and(
                    isnull(local!oldValue),
                    not(isnull(local!newValue))
                  ),
                  "ADDED",
                  and(
                    not(isnull(local!oldValue)),
                    isnull(local!newValue)
                  ),
                  "REMOVED",
                  "MODIFIED"
                ),
                dataType: typeof(local!newValue)
              ),
              null
            )
          )
        )
      ),
      /* Count changes manually */
      local!addedCount: length(
        where(
          a!forEach(
            items: local!differences,
            expression: index(fv!item, "changeType", "") = "ADDED"
          )
        )
      ),
      local!modifiedCount: length(
        where(
          a!forEach(
            items: local!differences,
            expression: index(fv!item, "changeType", "") = "MODIFIED"
          )
        )
      ),
      local!removedCount: length(
        where(
          a!forEach(
            items: local!differences,
            expression: index(fv!item, "changeType", "") = "REMOVED"
          )
        )
      ),
      a!map(
        recordType: typeof(ri!oldRecord),
        hasChanges: length(local!differences) > 0,
        totalChanges: length(local!differences),
        differences: local!differences,
        totalFields: length(local!allFields),
        summary: a!map(
          fieldsAdded: local!addedCount,
          fieldsModified: local!modifiedCount,
          fieldsRemoved: local!removedCount
        )
      )
    )

  • 0
    Certified Lead Developer

    For what purpose? What do you want to achieve with the results?

  • 0
    Certified Lead Developer

    In my experience this is a fairly common use case during review cycles, where the reviewers of edits to a record want to see what changed in an easily digestible way. Whether it's implemented via formal audit tables (outside of or supplemental to Record Events) or something more ad hoc, my approach is generally the same. Here is some prototype code on my current project that we'll productionize in a couple of weeks.

    Pre-requisites:

    • TCG_findData is a type-safe and null-safe encapsulation around index/wherecontains with an index() wrapper for the select parameter
    • A rule that rejects null elements from an array
    • Reference data is a rule input that allows id-based integers to be replaced with human-readable text. Useful for lookup labels and any other foreign-key related data.
      List of a!map()
      • field: <field in fromData/toData with a value that needs to be replaced>,
      • referenceData: <list of lookup or related record data; should contain the id and human-readable text>
      • idField: Used to index into the reference data
      • labelField: Is the field in the reference data that has the human-readable text

    This could be made even better if there were an Appian function to return a record field's Display Name given just the record field rather than explicitly hardcoding it (e.g. the 25.2 feature), but so far I haven't been able to figure that out.

    a!localVariables(
      local!fromKeys: a!defaultValue(ri!fields, a!keys(ri!fromData)),
      local!toKeys: a!defaultValue(ri!fields, a!keys(ri!toData)),
      rule!JS_rejectNull({
        a!forEach(
          union(local!fromKeys, local!toKeys),
          a!localVariables(
            local!a:  index(ri!fromData, fv!item, null),
            local!b:  index(ri!toData, fv!item, null),
            local!referenceData: rule!TCG_findData(
              data_any: ri!referenceData,
              field_any: "field",
              value_any: fv!item,
              select_any: 1,
            ),
            a!match(
              value: null,
              whenTrue: and(a!isNullOrEmpty(local!a), a!isNullOrEmpty(local!b)),
              then: null,
              whenTrue: and(
                a!isNotNullOrEmpty(local!a),
                a!isNotNullOrEmpty(local!b),
                local!a = local!b,
              ),
              then: null,
              default: a!map(
                field: rule!JS_pascalToReadable(tostring(fv!item)),
                from: if(
                  a!isNullOrEmpty(local!referenceData),
                  tostring(a!defaultValue(index(ri!fromData, fv!item, null),"---")),
                  rule!TCG_findData(
                    data_any: local!referenceData.data,
                    field_any: local!referenceData.idField,
                    value_any: index(ri!fromData, fv!item, null),
                    select_any: local!referenceData.labelField,
                  )[1]
                ),
                to: if(
                  a!isNullOrEmpty(local!referenceData),
                  tostring(a!defaultValue(index(ri!toData, fv!item, null),"---")),
                  rule!TCG_findData(
                    data_any: local!referenceData.data,
                    field_any: local!referenceData.idField,
                    value_any: index(ri!toData, fv!item, null),
                    select_any: local!referenceData.labelField,
                  )[1]
                ),
              ),
            )
          )
        ),
      })
    )