Skip to main content
30/31

Reconcile an Integration Feed Against Existing Rows

Hard

A nightly sync compares what is in the database with what an external system sends.

  • ri!existing: maps with id, externalId, name, email
  • ri!incoming: maps with externalId, name, email (no id)

Rows are matched on externalId, which is unique within each list. Return one map with three lists:

  • toInsert: incoming rows whose externalId is not in the database, as received and in incoming order
  • toUpdate: for incoming rows that match an existing row and differ in name or email, a map of the existing id with the incoming externalId, name and email, in incoming order
  • toDeleteIds: the id of every existing row whose externalId was not sent, in existing order

A matching row that has not changed appears in none of the three. Compare text exactly: a name going from "raj" to "Raj" is a change.


Why it comes up: writing every row back on every sync fires record events and audit entries for rows that did not change. With the three lists worked out first, the sync only writes what changed.

ri! Rule Inputs

ri!existingList of Map
{
  a!map(id: 101, externalId: "E1", name: "Ana", email: "ana@x.com"),
  a!map(id: 102, externalId: "E2", name: "Raj", email: "raj@x.com"),
  a!map(id: 103, externalId: "E3", name: "Li", email: "li@x.com")
}
ri!incomingList of Map
{
  a!map(externalId: "E2", name: "Raj", email: "raj@new.com"),
  a!map(externalId: "E3", name: "Li", email: "li@x.com"),
  a!map(externalId: "E4", name: "Tom", email: "tom@x.com")
}

Example 1: Insert, update and delete together

Input: ri!existing
{
  a!map(id: 101, externalId: "E1", name: "Ana", email: "ana@x.com"),
  a!map(id: 102, externalId: "E2", name: "Raj", email: "raj@x.com"),
  a!map(id: 103, externalId: "E3", name: "Li", email: "li@x.com")
}
Input: ri!incoming
{
  a!map(externalId: "E2", name: "Raj", email: "raj@new.com"),
  a!map(externalId: "E3", name: "Li", email: "li@x.com"),
  a!map(externalId: "E4", name: "Tom", email: "tom@x.com")
}
Expected output:
a!map(toInsert: {a!map(externalId: "E4", name: "Tom", email: "tom@x.com")}, toUpdate: {a!map(id: 102, externalId: "E2", name: "Raj", email: "raj@new.com")}, toDeleteIds: {101})

Example 2: Nothing changed

Input: ri!existing
{
  a!map(id: 101, externalId: "E1", name: "Ana", email: "ana@x.com"),
  a!map(id: 102, externalId: "E2", name: "Raj", email: "raj@x.com"),
  a!map(id: 103, externalId: "E3", name: "Li", email: "li@x.com")
}
Input: ri!incoming
{
  a!map(externalId: "E1", name: "Ana", email: "ana@x.com"),
  a!map(externalId: "E2", name: "Raj", email: "raj@x.com"),
  a!map(externalId: "E3", name: "Li", email: "li@x.com")
}
Expected output:
a!map(toInsert: {}, toUpdate: {}, toDeleteIds: {})

Example 3: First ever sync

Input: ri!existing
{}
Input: ri!incoming
{
  a!map(externalId: "N1", name: "Uma", email: "uma@x.com"),
  a!map(externalId: "N2", name: "Vik", email: "vik@x.com")
}
Expected output:
a!map(toInsert: {a!map(externalId: "N1", name: "Uma", email: "uma@x.com"), a!map(externalId: "N2", name: "Vik", email: "vik@x.com")}, toUpdate: {}, toDeleteIds: {})

Example 4: Empty feed removes everything

Input: ri!existing
{
  a!map(id: 5, externalId: "A", name: "Ann", email: "ann@x.com"),
  a!map(id: 6, externalId: "B", name: "Ben", email: "ben@x.com")
}
Input: ri!incoming
{}
Expected output:
a!map(toInsert: {}, toUpdate: {}, toDeleteIds: {5, 6})

Example 5: Renamed row and two new ones, out of order

Input: ri!existing
{
  a!map(id: 1, externalId: "K1", name: "Old Name", email: "a@x.com"),
  a!map(id: 2, externalId: "K2", name: "Bo", email: "b@x.com")
}
Input: ri!incoming
{
  a!map(externalId: "K2", name: "Bo", email: "b@x.com"),
  a!map(externalId: "K1", name: "New Name", email: "a@x.com"),
  a!map(externalId: "K9", name: "Zed", email: "z@x.com"),
  a!map(externalId: "K8", name: "Yan", email: "y@x.com")
}
Expected output:
a!map(toInsert: {a!map(externalId: "K9", name: "Zed", email: "z@x.com"), a!map(externalId: "K8", name: "Yan", email: "y@x.com")}, toUpdate: {a!map(id: 1, externalId: "K1", name: "New Name", email: "a@x.com")}, toDeleteIds: {})

Example 6: Only the capitals changed

Input: ri!existing
{
  a!map(id: 101, externalId: "E1", name: "Ana", email: "ana@x.com"),
  a!map(id: 102, externalId: "E2", name: "Raj", email: "raj@x.com"),
  a!map(id: 103, externalId: "E3", name: "Li", email: "li@x.com")
}
Input: ri!incoming
{
  a!map(externalId: "E1", name: "ana", email: "ana@x.com"),
  a!map(externalId: "E2", name: "Raj", email: "Raj@x.com"),
  a!map(externalId: "E3", name: "Li", email: "li@x.com")
}
Expected output:
a!map(toInsert: {}, toUpdate: {a!map(id: 101, externalId: "E1", name: "ana", email: "ana@x.com"), a!map(id: 102, externalId: "E2", name: "Raj", email: "Raj@x.com")}, toDeleteIds: {})

Appian Expression Tips

  • • Use ri!variableName for rule inputs
  • • Use a!localVariables(local!x: …, body) for locals
  • • Use a!forEach(items: …, expression: fv!item) to iterate
  • • Use where() / reject(fn!isnull, list) to filter