Sync records for a record created from materialized view

Issue with Dynamic Identifiers in Materialized View Causing Sync Failures

We are consuming data from an external view that only returns records where:

• BPM_PROCESSING_STATUS IS NULL
• BPM_CASEID IS NULL

This external view is built on top of a base table that contains all records.

Current Implementation

• When a case is created in Appian, we update the base table using a Query Database Smart Service, populating:

  • BPM_CASEID
  • BPM_PROCESSING_STATUS (e.g., PENDINGFORAPPROVAL, REFERREDBACK, PROCESSED)

• Since we do not have access to define a primary key on the source table/view (due to grant restrictions), we:

  1. Created a Materialized View on top of the external view
  2. Added a synthetic identifier column:
     SQL

     1

     ROW_NUMBER() OVER (ORDER BY UNIQUE_ID) AS ID

     Show more lines
  3. Built an Appian Record Type using this materialized view (to leverage record filters, etc.)
  4. Created a stored procedure to refresh the materialized view
  5. Used:
     • Execute Stored Procedure Smart Service
     • Sync Records Smart Service

Problem

The identifier (ROW_NUMBER) is not stable.

• Initially, suppose we have 3000345 records
• When cases are created for some UINs, those records are updated in the base table
• As a result, they no longer satisfy the condition (BPM_CASEID IS NULL, BPM_PROCESSING_STATUS IS NULL)
• Hence, they disappear from the external view and the materialized view

Example:

• Record count reduces from 3000345 → 3000335
• Previously generated IDs such as:
  • 3000271, 3000313, 3000341
• After refresh:
  • Some of these IDs (e.g., 3000341) no longer exist

Impact

• The Record Type identifier changes dynamically
• When we run Sync Records, Appian attempts to sync using old identifiers
• Since those IDs no longer exist, sync fails

Attempted Workaround

• Tried using Delete Record Smart Service
• However, deletion is not allowed since the source is a materialized view

Key Concern

We need a way to:

• Refresh the record data immediately
• Ensure users do not create duplicate cases for the same UIN

Question

Is there a recommended approach in Appian to handle:

1. Dynamic datasets where records disappear after updates
2. Unstable identifiers (ROW_NUMBER-based)
3. Immediate refresh/sync of record data
4. Preventing duplicate case creation for the same UIN