Reconcile an Integration Feed Against Existing Rows
A nightly sync compares what is in the database with what an external system sends.
ri!existing: maps withid,externalId,name,emailri!incoming: maps withexternalId,name,email(noid)
Rows are matched on externalId, which is unique within each list. Return one map with three lists:
toInsert: incoming rows whoseexternalIdis not in the database, as received and in incoming ordertoUpdate: for incoming rows that match an existing row and differ innameoremail, a map of the existingidwith the incomingexternalId,nameandemail, in incoming ordertoDeleteIds: theidof every existing row whoseexternalIdwas 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
{
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")
}{
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")
}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
{
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")
}{
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")
}a!map(toInsert: {}, toUpdate: {}, toDeleteIds: {})Example 3: First ever sync
{}{
a!map(externalId: "N1", name: "Uma", email: "uma@x.com"),
a!map(externalId: "N2", name: "Vik", email: "vik@x.com")
}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
{
a!map(id: 5, externalId: "A", name: "Ann", email: "ann@x.com"),
a!map(id: 6, externalId: "B", name: "Ben", email: "ben@x.com")
}{}a!map(toInsert: {}, toUpdate: {}, toDeleteIds: {5, 6})Example 5: Renamed row and two new ones, out of order
{
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")
}{
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")
}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
{
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")
}{
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")
}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!variableNamefor 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