4.2 Data Modification & the Write to Data Store Entity Smart Service

4.2 Data Modification & the Write to Data Store Entity Smart Service

The Write to Data Store Entity Smart Service in Process Models

In Appian enterprise architecture, modifying relational data is primarily executed through the Write to Data Store Entity smart service node within Process Models. Unlike simple script tasks that manipulate variables in memory, this smart service opens a transactional database connection through the Java Persistence API (JPA) layer, evaluates data modifications, and commits them to the physical relational database schema.

As an unattended system smart service, it is placed along workflow paths after user forms, validation rules, or system integrations. When configured with activity chaining, the database write occurs seamlessly between human tasks without interrupting the user's interface flow, ensuring immediate data consistency.

---

Configuring Node Inputs: Data Store Entity and Values to Store

The Write to Data Store Entity smart service is configured through the Data tab of the node properties window:

+-----------------------------------------------------------------------------------+
| Node Properties: Write to Data Store Entity                                       |
+-----------------------------------------------------------------------------------+
| [General]   [Data]   [Scheduling]   [Assignment]                                  |
+-----------------------------------------------------------------------------------+
| Inputs:                                                                           |
|   * Data Store Entity (DataStoreEntity) -> cons!PURCHASE_ORDER_DSE               |
|   * Values to Store   (Any Type)          -> pv!purchaseOrder                     |
+-----------------------------------------------------------------------------------+
| Outputs:                                                                          |
|   * Stored Values     (AC!StoredValues)   -> pv!purchaseOrder                     |
+-----------------------------------------------------------------------------------+

Required Inputs

1. Data Store Entity: The specific DSE pointer representing the target database table (typically assigned via a constant such as cons!PURCHASE_ORDER_DSE).
2. Values to Store: The process variable containing the data to persist. This input accepts either:
   • A Single CDT Instance: Writes or updates one specific row (e.g., pv!customer of type Customer).
   • An Array / List of CDTs: Writes or updates multiple rows in a single batch operation (e.g., pv!orderItems of type List of OrderItem).

Single vs. Batch Array Writes

When an array of CDTs is supplied to Values to Store, Appian executes a batch JDBC write within a single database transaction. This is substantially faster than looping through a process model with a single-item write node, which incurs individual transaction overhead, process engine transitions, and connection handshakes for each record.

---

Node Outputs: The Critical Role of Stored Values (AC!StoredValues)

A fundamental concept on the Associate Developer exam is the role of the node's Outputs tab. When a new entity record is inserted into a database table configured with an auto-incrementing primary key (annotated with @GeneratedValue), the primary key field in the incoming process variable is initially null or 0.

When the Write to Data Store Entity node completes the SQL INSERT, the relational database engine generates the next sequence value (e.g., 10492). Appian captures this newly generated identifier and returns the updated, complete CDT via the activity class output variable AC!StoredValues.

---

SQL Generation Mechanics: Primary Key Dictates INSERT vs. UPDATE

The Write to Data Store Entity smart service automatically deduces whether to execute an SQL INSERT or an SQL UPDATE statement by inspecting the primary key field of the CDT being passed to Values to Store:

Loading diagram...

Detailed Persistence Behavior

• Insert Operation: If the primary key field (annotated with @Id) is null for a database-generated key, the persistence engine generates an INSERT INTO table_name (...) VALUES (...)` statement.
• Update Operation: If the primary key field contains an existing identifier (e.g., orderId = 502), the engine generates an UPDATE table_name SET ... WHERE order_id = 502.

The Partial Update Hazard (Overwriting with Nulls)

A common trap arises when developers attempt to update only one or two fields on an existing record. In Appian's ORM model, the smart service updates the entire row matching the primary key. If a developer initializes a brand-new CDT in a script task, sets orderId = 502 and status = "APPROVED", but leaves customer name, order total, and order date empty, the smart service will overwrite those existing database columns with NULL!

Best Practice for Updates: Always query the existing record from the database first using a!queryEntity(), update only the specific fields requiring modification in memory (using update() or dot notation), and then pass the complete, modified CDT to the Write to Data Store Entity node.

---

Deleting Data: Delete from Data Store Entities vs. Soft-Delete Patterns

When business records are no longer needed, Appian provides two distinct technical strategies: hard deletion and soft deletion.

1. Hard Deletes: The Delete from Data Store Entities Smart Service

Appian includes a dedicated smart service node: Delete from Data Store Entities. Developers configure:

• The target Data Store Entity.
• EntityDataIdentifiers values constructed with the entity and an array of primary-key identifiers. Do not pass CDT instances as identifiers.

The database executes physical DELETE FROM table WHERE id IN (...) statements.

• Pros: Completely frees storage space in the database.
• Risks: If other tables contain foreign keys referencing these records without cascading delete rules, the database will throw a foreign key constraint violation, pausing the process model with an alert. Furthermore, physical deletion destroys historical audit trails.

2. Soft Deletes and Retention Requirements

Choose retention and deletion according to the actual requirements. One option is a soft-delete pattern using a status indicator field on the CDT (e.g., isActive of type Boolean, or recordStatus of type Text):

• To "delete" a record, the application updates the record using Write to Data Store Entity, setting isActive = false or recordStatus = "DELETED" alongside a timestamp and user ID.
• All interface queries and reports filter by isActive = true.
• Advantages: Preserves complete historical auditability, supports the application's historical reporting requirements when used with an appropriate retention policy, and prevents broken foreign key relationships across reporting warehouses.

---

Interface-Level Persistence: a!writeToDataStoreEntity() in SAIL

In addition to process models, Appian provides an expression function for persisting data directly from SAIL interfaces: a!writeToDataStoreEntity(). This function is designed for execution within interface event handlers, such as the saveInto parameter of an a!buttonWidget():

a!buttonWidget(
  label: "Save Draft",
  value: true,
  saveInto: {
    a!writeToDataStoreEntity(
      dataStoreEntity: cons!DRAFT_APPLICATION_DSE,
      valueToStore: local!applicationData,
      onSuccess: {
        a!save(local!applicationData, fv!storedValues),
        a!save(local!saveMessage, "Draft saved successfully at " & now())
      },
      onError: {
        a!save(local!errorMessage, "The draft could not be saved. Please retry after the issue is resolved.")
      }
    )
  }
)

Architectural Trade-offs: SAIL Writes vs. Process Models

While a!writeToDataStoreEntity() provides instantaneous interface feedback without starting a process instance, developers must carefully evaluate architectural boundaries:

---

Real-World Scenario: Global Supply Chain Purchase Order Fulfillment

A global retail enterprise operates a warehouse fulfillment portal. When a shipping container arrives, a logistics manager verifies 40 distinct items. The intake process model must:

1. Insert a new Shipment parent record.
2. Receive the database-generated shipmentId.
3. Assign that shipmentId to all 40 child ShipmentItem records.
4. Persist the array of 40 child items to the database.

During initial deployment, the junior developer failed to map AC!StoredValues on the first Write to Data Store Entity node. When the process ran, pv!shipment.shipmentId remained null. The second write node attempted to insert 40 child items with foreign key shipmentId = null. The database immediately threw a NOT NULL constraint exception, failing the shipment intake and alerting the on-call administrator.

The fix was simple: on the first node's Data tab, the developer mapped AC!StoredValues back into pv!shipment. With pv!shipment.shipmentId populated, a subsequent script task mapped the generated ID across the item array using a!forEach(), allowing the child rows to reference the generated parent identifier. Verify the saved rows rather than assuming a fixed execution time.

---

Exam Watchouts & Common Traps

• Missing Stored Values Mapping: An important development scenario involves an auto-generated primary key failing to pass to child records. Remember: you must map AC!StoredValues back to the process variable to capture database-generated IDs.
• Passing Untyped Data: Values to Store expects a CDT or an array of CDTs matching the Data Store Entity definition. Passing an untyped dictionary or an incompatible CDT will result in a runtime casting error.
• Data Store Security Permissions: The user initiating the process (or the custom process model execution credentials) must possess at least Viewer permissions on the underlying Data Store design object. Lacking Viewer rights triggers an immediate security authorization failure at runtime.
• Full Row Overwrites: Passing a partially populated CDT with an existing primary key overwrites all omitted columns with null. To update partial data, always query the full record first before applying changes.

Source checked 2026-10-18: Appian write to data store.