Write to Multiple Data Store Entities Smart Service

Overview

The Write to Multiple Data Store Entities Smart Service writes multiple CDT values to multiple entities within the same data store based on your input. This differs from the Write to Data Store Entity Smart Service because you can update multiple entities within a single database transaction.

The entities to be updated and the values to update each entity with are defined by the Value to Store node input of type EntityData. The transaction produces a Node Output of type EntityData containing the actual stored values of each EntityData object passed to the smart service which can be filtered to retrieve the stored values.

If any write to the data store fails, no data is written to the data store.

Also note that any CDT created through the Data Type Designer or uploaded via an XSD file has the cascade attribute set to ALL on the child CDT by default, so unless you have manually changed this setting, updating a parent CDT value with this setting also affects the child value. Learn more about CDT Relationships.

Example use case

You have an application that manages a sales cycle with a process which needs to update three different entities based on an Account Executive's work on the sale.

When the Account Executive has a call with a potential customer, he/she updates the status of the different opportunities discussed and the customer account.

Using this smart service, you can have all three data store entities updated within the same transaction. This way, if an error occurs while writing to one of them, the others entities will not update either - leaving the data in a consistent state as if they were never modified.

Permissions needed

The user running the smart service must have Viewer rights to the data store that contains the entities defined in the EntityData node input. Attempting to complete this task without the proper user rights generates an error message for the process administrator and pauses the process.

Properties

• Category: Data Services

• Icon:

• Assignment Options: Always Unattended

Configuration options

This section contains tab configuration details specific to this smart service. For more information about common configurations see the Process Node Properties page.

Data tab

Node inputs

Only the Value to Store input value is used when writing data to the data store. All other inputs are ignored.

You can write to multiple EntityData inputs through one transaction by creating an array of EntityData objects for the EntityData value.

• All entities defined in the array must be of the same data store.
• Arrays can be constructed at the EntityData level of the node input or populated in the process flow prior to executing this smart service and passed to the node input.

See also: EntityData Data Type

Example inputs 1

If you set the Value to Store input to modify data in three data store entities, using process variables for the entities and process variables populated in a prior process step for the data values, your input value may resemble this:

1
2
3
4
5
6
7
8
={
a!entityData(entity: pv!ENTITY_OPPORTUNITIES,
 data: {pv!RadiationOpp,pv!NewBusinessOpp}),
a!entityData(entity: pv!ENTITY_ACCOUNT,
 data: {pv!AcmeAccount}),
a!entityData(entity:pv!ENTITY_AE_NOTES,
 data:{pv!ListOfNotesFromAccountExec})
}

Example inputs 2

If you use the is appended to output operator on nodes in a prior process step to collect EntityData process variables and create an array of EntityData values, your input value may resemble this:

1
=pv!ArrayOfEntityDataToStore

Node outputs

If the EntityData input contains a null or empty data field value, the EntityData object still returns as part of the EntityData output array with the null or empty data field.

Example output 1

For Example 1 in the above Example inputs, you can use the known indices of the EntityData array values to store data from the outputs into process variables of the correct data type using the following expressions:

1
2
3
StoredValues[1].data → pv!StoredOpportunities
StoredValues[2].data → pv!AcmeAccount
StoredValues[3].data → pv!NewNotes

Example output 2

For Example 2 in the Example inputs, since the EntityData input value entered is an array of varying length based on runtime conditions, you can use the reduce() function to accumulate the data sent to the entity.

To do so, first create a rule similar to the following:

1
2
 getDataFromEntityDataForEntity(ri!data, ri!EntityData, ri!Entity)
 =append(ri!data, if(ri!EntityData.entity=ri!Entity, ri!EntityData.data, {})

Then use this rule with the reduce() function to define a node output similar to the following that saves into a multiple process variable of type Ticket:

1
reduce(rule!getDataFromEntityDataForEntity, {}, ac!StoredValues, cons!ENTITY_TICKET)

This node output will return a list of data where entity=ticket into your process variable of type Ticket.

Usage considerations

Data sync behavior for record types

If you use this smart service to add or update data in a database table, and that table is the source of a record type with data sync enabled, those changes will be automatically synced in Appian. This is called a smart service sync.

If a smart service sync occurs at the same time as a scheduled full sync, all changes from the smart service sync will be immediately synced and available. Then, when the full sync completes, all other changes from the source will also be available.

Depending on how your table data is structured in the CDT, the maximum number of rows that can be automatically synced will vary:

• If you're using a flat CDT, you can sync up to 50,000 rows at a time.
• If you're using a nested CDT, you can sync up to 1,000 rows at a time.

If you update more than the maximum number of rows, the smart service will successfully write to the data source, but the sync will fail.

If the smart service fails because there is an issue connecting to the database, like if VPN connection fails so you can't write your changes, Appian will automatically retry the smart service and the sync operation up to three times (with two seconds between each retry). After three attempts, the sync will fail and the record data will be unavailable.

See Troubleshooting Data Sync for more information on the different sync errors.

Bulk inserts and updates

The Write to Multiple Data Store Entities smart service inserts or updates data one row at a time. Bulk inserts and updates are not supported. If your network latency is near the upper bound of the recommended round-trip time for TCP communication with your data source, you should use the Execute Stored Procedure smart service or Query Database smart service instead to perform bulk inserts and updates.

a!writeToMultipleDataStoreEntities()

The Write to Multiple Data Store Entities smart service is available as an expression function that can be executed inside a saveInto on a Interface Component or as part of a Web API.

Syntax

a!writeToMultipleDataStoreEntities( valuesToStore, onSuccess, onError )

Parameters

Runtime behavior

All data is written to the data store in the order in which it was given.

• Casting of CDTs will occur if the data type of an EntityData data field does not match the target entity data type.
• If any failure occurs during the transaction, all changes will be rolled back and the data in the data store will not be modified.
• If none of the data fields contain values to store or if the EntityData array is an empty list, no transaction occurs and nothing is recorded in the performance log.

Review database schema best practices

Since this smart service works directly with your data store and custom data types, be sure to note the Database Schema Best Practices before implementing.

As this smart service updates multiple entities in a single database transaction, some overhead is introduced in the write process to ensure atomicity. When transactional aspects of this smart service are not required, it is preferable to use multiple instances of the Write to Data Store Entity Smart Service to ensure better performance.

Feature compatibility

Feedback