Write Records Smart Service

The Write Records smart service allows you to insert or update data in your database tables using a record type. If your record type has optimized data access, any new or updated data is automatically synced in the record type.

The Write Records smart service is available as a process model node and as a function (a!writeRecords()). The node and function share the same core behavior for writing records, but each has unique configuration options described in its own section below.

Requirements

The Write Records smart service can write to one or more record types in a single execution.

To use Write Records, each record type must meet the following requirements:

• Database table source: The record type must use a database table as the source. Database views are not supported.
• Primary key: The record type must have at least one primary key field. You can use a single primary key or a composite primary key.
• Data access method: The record type must use Optimized Data Access or Direct Data Access with relationships and related features enabled.
• Permission: The smart service initiator must have at least Viewer permissions to all record types where data is created or updated.

Write Records node

The Write Records node has the following properties:

• Category: Data Services
• Icon:

• Assignment Options: Unattended

• Compatible with autoscale: Yes

To configure the Write Records node, use the Setup tab and the Data tab. See Process Node and Smart Service Properties for common node configurations.

Configure the Setup tab

The Setup tab is where you'll configure the records to write, as well as any corresponding record events to write. If your record type does not have record events configured, you will only specify the records to write on this tab.

To configure the Setup tab:

1. (Optional) Configure record events for any record types you want to capture events for. You can only write records and events at the same time if the record type has record events configured.
2. In your process model, open the Write Records node.

3. Go to the Setup tab.

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

4. For Records Input, specify the data to write. You can select one or more process variables of type Record Type, or use an expression to pass data.

   To write to a base record and its related records:

   • Select a single process variable of type Record Type.
   • This allows you to write to the base record and any related records configured to Write or delete related records in a single transaction.

   Note:  If a record type has a composite primary key, the variable must contain values for all fields that make up the key.

   To write to unrelated record types:

   • Select multiple process variables of type Record Type.
   • This allows you to write to each unrelated record type in a single transaction.
   • Use this option for related records that are configured to Do not write or delete related records.

5. For Record Type for Events, select the record type you want to capture events for. You can only write events for one record type per node.

   Note:  If the record type does not have record events configured or multiple record types are selected, no events will be written.

6. If the record type in Record Type for Events has record events configured, choose when to write events:

   Option	Description
   Always	Write events whenever the node executes.
   Never	Never write events whenever the node executes.
   Only when…	Specify the condition in which events are written. Click Edit Condition to write an expression defining the specific condition.

7. Select the values to write for the event. The field inputs are populated from the related Event History record type.

   You must map values for the following field inputs:

   Input	Description
   Event Type	The type of event that occurred. The dropdown list is populated from the values in the Event Type Lookup record type. If you use an expression, you must use the event type identifier to indicate the type of event. Learn more about conditionally choosing event values.
   User	Who completed the event. If the event will be completed by an automated task, set the value to Null (Set as System Event).
   Automation Type	The type of automation that completed the event. If the event isn't automated, set the value to None (User) to indicate it will be completed by a user.
   Timestamp	The date and time when the event occurred. Select Now to capture the date and time when the node executed.

8. Click OK.
9. Click Save & Publish in the process model.

Configure the Data tab

The Data tab allows you to add additional inputs and store the resulting node outputs.

Configure node inputs

To configure the Inputs for the Write Records node:

1. In your process model, open the Write Records node.
2. Go to Data > Inputs.
3. Configure the following inputs:

Configure node outputs

To configure the Outputs for the Write Records node:

1. In your process model, open the Write Records node.
2. Go to Data > Outputs.
3. Configure the following outputs:

Write Records node examples

For step-by-step examples of creating, updating, and deleting records using process models, see Writing and Deleting Records Recipes.

a!writeRecords() function

The Write Records smart service is available as a function that can be executed inside a saveInto on a Interface Component or as part of a Web API. When creating a web API, select the Write Records template to generate the object with this function automatically configured.

Parameters

a!writeRecords( records, onSuccess, onError )

a!writeRecords() examples

The following examples reference record types from another environment and will not evaluate in yours. Use the following examples as a reference only.

Example: Create a record using an expression

Let's say you want to create a form that updates your Status record type with a new status option for orders. You want to automatically write and sync any data provided in the form whenever a user clicks Submit.

To create this form:

1. Create a new interface using a form layout.
2. Create a local variable called status. This will store the new status value and pass it to the a!writeRecords() function.
3. Add components that allow users to add new values. For example, a Text field allows users to add a new status label.

4. In the primary button of the form, add a saveInto parameter to save the local variable to the appropriate field.

   In this example, we would save the local!status to the label field in the Status record type.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
    buttons: a!buttonLayout(
      primaryButtons: {
        a!buttonWidget(
          label: "Submit",
          saveInto: {
            a!writeRecords(
              records: {
                recordType!Status(
                  recordType!Status.fields.label: local!status
                )
              }
            )
          },
          submit: true,
          style: "SOLID"
          )
        },
      )

Now, whenever a user clicks Submit, a new record will be created in the Status record type.

Example: Create a record with a composite primary key using an expression

Let's say you have an order form where customers can add products to their cart. When the form is submitted, you want to use a!writeRecords() to create an Order Item record for each product. Because the Order Item record type uses a composite primary key (orderId and itemId), you must provide both values to create each record.

The a!writeRecords() expression would look something like this:

1
2
3
4
5
6
7
8
9
10
11
12
a!writeRecords(
  records: {
    recordType!Order Item(
      /* You must provide all fields  */
      /* that make up the composite primary key */
      recordType!Order Item.fields.orderId: 101,
      recordType!Order Item.fields.itemId: 5,
      recordType!Order Item.fields.quantity: 2,
      recordType!Order Item.fields.price: 19.99
    )
  }
)

Using the smart service

The following considerations and behavior apply to the Write Records smart service whether you use the Write Records node in a process model or the a!writeRecords() function in an expression.

Maximum writes for Write Records

The Write Records smart service can write up to 50,000 records, related records, and events combined per execution.

Events can only be written at the same time as records when using the Write Records node; the a!writeRecords() function does not support writing records and events at the same time.

Pass data using a record data type

To pass data into the Write Records node or function, you must use a record data type. This means that when you build a start form, use record types as your rule inputs or local variable, and any process variables should have a Type of Record Type.

If you're not passing data from a variable, you can use a record type constructor to pass in the record data type and the data to write.

Write records

The Write Records smart service can create new records and update existing records. The following sections describe how Write Records determines whether to create or update a record.

Create new records

A new record is created when one of the following is true:

• Auto-generated keys: The source table uses AUTO_INCREMENT or an Appian sequence to generate values, and you omit the primary key field or set it to null.
• Manual keys: The source does not use auto-generation, and you provide a primary key value that does not already exist.
• Composite keys: You provide values for all fields that make up the key, and the combination of those values does not already exist.

Create records with composite primary keys

To create a record with a composite primary key, you must provide values for all fields that make up the key. Composite key fields do not support AUTO_INCREMENT or database sequences, so you must supply every value explicitly.

When writing a related record with a composite key alongside its parent record, the related record automatically inherits the parent's primary key value for the common field. You must explicitly provide values for all other component fields in the composite key.

For example, a Part record type has a single primary key (partId) and a one-to-many relationship with a Part Supplier record type that has a composite primary key (partId, supplierId). When you write a new Part and its Part Suppliers together, Appian generates the partId for the Part and automatically passes it to the related Part Supplier records. Because the supplierId is captured in the interface, the composite key is complete and the write succeeds.

Update existing records

To update existing data, the record must include the primary key value. If the record type has a composite primary key, you must include a value for every primary key component field.

To update an existing record, you only need to pass in the data you want to update, not all record fields and their values. The smart service will only update the data you provide. It will not write null values for any fields that are not provided.

Single primary key example:

If you only need to update an employee's last name, pass the record's identifier and the new lastName to the Write Records node. The employee's first name and other data remains unchanged.

1
2
3
4
5
recordType!Employee(
  recordType!Employee.fields.id: 233,
  /* Only the field being changed needs to be provided */
  recordType!Employee.fields.lastName: "Kwan"
)

Composite primary key example:

If you want to update the quantity of an item in an order where the record is identified by both the orderId and the itemID, you must provide both field values:

1
2
3
4
5
6
7
recordType!OrderItem(
  /* Both key fields are required to identify the record */
  recordType!OrderItem.fields.orderId: 101,
  recordType!OrderItem.fields.itemId: 5,
  /* Only the field being changed needs to be provided */
  recordType!OrderItem.fields.quantity: 10
)

Write records and related records

The Write Records smart service can write records and related records from any one-to-many or one-to-one relationships in a single execution.

The Write Records smart service executes all of the writes as a single transaction. This ensures that either all of the data is written or none of the data is written. This helps avoid incomplete writes (such as where an Order is written, but an Order Line Item is not).

Within that transaction, Appian automatically handles the write order for dependent data. The base record is written first and its generated primary key is captured, then Appian supplies that key as the foreign key on the related records before writing them. You do not need separate nodes to ensure the parent record exists before its related records reference it.

Requirements for writing related records

To write data for a record and its related records in a Write Records smart service:

• Data source: The records and related records must share the same data source. You can write to different schemas and tables within a data source so long as the record types share the same connection information (like a data source connected system).

  For example, if the source tables are in separate databases, you can't update them in a single Write Records smart service. Instead, use a separate Write Records smart service to write to each source.

• Relationship configuration: The relationship must be configured to allow writing related records.

• Primary key requirements:

  • When writing related records that have a composite primary key, you must provide values for all fields that make up the key.
  • Because the relationship is configured to Write or delete related records, the related record automatically inherits the base record's primary key value for the common field. However, you must explicitly provide values for all other component fields in the composite key.

  For example, if a Part Supplier record type has a composite key of {partId, supplierId} and is related to a Part record type, the partId can be inherited from the parent Part, but supplierId must be explicitly provided.

Write related document information

When you use the Write Records smart service to write a document identifier to a document management record type, that information is also automatically written and synced to the Document record type.

See Create and delete documents for examples uploading documents and writing the document identifier.

Automatic behavior for Write Records

The following behavior automatically occurs when the Write Records smart service executes.

Automatically sync writes

Whenever you use this smart service to update your source data, those changes will be automatically synced in the record type. 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.

See also:

• Errors syncing data changes if you run into any issues.
• Considerations for using smart services that automatically sync data.
• Skip failed smart service syncs to learn about the sync option you can enable to skip failed syncs and keep data available.

Write Records and the synced row limit

If your capability tier has a synced row limit, the Write Records smart service succeeds even if the write exceeds that limit. However, the record type only syncs successfully if:

• Keep data available at high volumes is enabled.
• The total row count remains within 10% of the synced row limit.

If these conditions are not met, the write succeeds but the sync fails and record data becomes unavailable.

Automatic refresh on record views

When the Write Records smart service executes from a record view interface, either through a Record Action component or a button that calls a!writeRecords(), the record view automatically refreshes with the latest data.

The automatic record view refresh only applies to data supplied by rv!record. If your record view uses rv!identifier or a local variable to supply the record data in the record view, you need to configure additional refresh behavior.

Ignore custom record fields

If you try to update custom record fields in the Records Input, those changes will be ignored. Custom record fields are defined by and exist only in Appian, so attempting to write values for them will have no effect.

Using the node

The following information applies only to using the Write Records node in a process model. For general information that applies to both the node and function, see Using the smart service.

Write records and record events

The Write Records node can write records and record events at the same time. You cannot write events using the a!writeRecords() function.

Write events for the base record type

To write records and events at the same time:

1. Open the Write Records node and go to the Setup tab.
2. For Record Type for Events, select a record type with record events configured. By default, this field is populated with the record type selected for the Records Input.
3. Configure the Write Events section to capture the appropriate event information.

When the node executes, the record type specified in the Records Input is updated, as well as the related Event History record type.

If you are updating multiple records in one node, then each new record will have a corresponding event created. For example, if you create order 1, order 2, and order 3, each order will have its own unique event created.

The Write Records node can only write events for a single record type, and that record type must be specified in Record Type for Events. Event data is not automatically create for any related record data, but it can be created using a separate Write Records node.

Write events for related record types

You can write events for a related record type by adding the event data to the process variable specified in the Records Input. Events will be written for the record and the related record when the node executes.

To write events for a related record type in the same Write Records node, you can use one of the following options:

• In the start form, configure the submit button to save the event information for the related record type in the same rule input as the other form data.
• In the process model, use a script task to specify the event information and pass that information into the Write Records node.

For a step-by-step example, see Write events for multiple record types in one node.

Capture automation types

When you're capturing automation types in your event data, you can configure your Write Records nodes to write the type of automation that completed each event. Remember, even though a process might involve multiple users or automation types, you want your event data to show the user or automation type that actually completed the task.

In general, use the following guidelines to configure the User and Automation Type properties when writing events:

• If the event was completed by a human, set the User property to the appropriate field from the Event History record type. Then, set the Automation Type property to None (User).

• If the event was completed by automation, set the User property to Null (Set as System Event). Then, select the appropriate automation type for the Automation Type property.

Learn more about capturing automation types.

Conditionally choose event values

You can conditionally set the value for any event input, like Event Type, User, Automation Type, or Timestamp. This approach is useful when you use a single Write Records smart service to add and update data, or when you want to write different event values depending on a value in the record.

For example, when you generate Create and Update record actions at the same time, Appian will create a single process model to perform both actions. If you selected the Include Common Event Types option when you generated your event record types, the generated process model is automatically configured to write events correctly. As a result, "Created record" events are only written when a new record is created, and "Updated record" events are only written when a record is updated.

You may also want to write different event types depending on the value of a record. For example, let's say an order is updated to have a status of "Reviewed" so you want write an event called "Reviewed Order" instead of "Updated Order".

To conditionally set the event type for this example:

1. In your Write Records smart service, go to the Setup tab.
2. Next to Event Type, click Edit as Expression.

3. Write an expression using an if() statement and the order status value. Use the event type identifier to specify the event type. For example:

   1
   2
   3
   4
   5
   6
   7
   if(
    pv!order[recordType!Order.relationships.status.fields.name] = "Reviewed",
    /* The ID for the "Reviewed Order" event type */
    3,
    /* The ID for the "Updated Order" event type */
    2
   )
   

   Tip:  For easier maintenance, use a constant to specify the event type identifier instead of entering a static numerical value.

Write events without writing records

There are some scenarios where you may want to write an event without making any changes to the base record type.

For example, let's say you sent a feedback form to a customer after an order is shipped. If the customer doesn't respond to the form in five days, the process model will automatically send an email reminder.

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

To capture an event for the automatic email reminder, you can write directly to the Event History record type. To do this:

• Add a Script Task node and configure it with the data to pass to the Write Records node. Use a record type constructor to specify the event information to write to the Event History record type. For example:

  1
  2
  3
  4
  5
  6
   recordType!Customer Events(
       recordType!Customer Events.fields.recordId: pv!customer[recordType!Customer.fields.recordId],
       recordType!Customer Events.fields.timestamp: now(),
       recordType!Customer Events.fields.user: null, 
       recordType!Customer Events.fields.activityId: cons!CUSTOMER_EVENT_EMAIL_REMINDER_ID
   )
  

  Note:  A null user field indicates that this is an automated activity, it was not started by a specific user.

• Add a Write Records node and set the Records Input to a process variable or expression that evaluates to the Event History record type.

The updated process would look something like this:

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Write to unrelated record types

You can write to multiple unrelated record types in a single transaction by specifying multiple process variables of type Record Type in the Records Input.

For a step-by-step example, see Write to unrelated record types.

Use the Records Updated output to work with changed data

When your process needs to use or act on the data written by a Write Records node, you can use the data provided in the Records Updated output.

This output includes everything from the input plus the primary keys of any newly created records. If the node is configured to write record events, the output also contains any new events. Returning this combination of data improves performance of the Write Records node.

You can use the Records Updated output to:

• Store the updated data from a single record type.
• Store the updated data from multiple record types in custom variables.
• Recalculate custom record fields using the latest values updated in the node.
• Examine the results in Process History to see exactly what was written to the source.

Store updated data from a record type

If you provided a single process variable in the Records Input, you can store the data updated in that record type, as well as any events updated for the record type.

For example, let's say you have a process model that creates or updates a single customer. That customer information is stored in the process variable pv!customer, which is used in the Records Input to update data in the Customer record type. The record type also has record events configured and the node was configured to also write events.

To store the customer information and the events written by the Write Records node:

1. Create a process variable with the following properties:

   Property	Value
   Name	Enter customerUpdated
   Type	Select the Customer record type.

2. Open the Write Records node.
3. Click the Data tab and select the Outputs subtab.
4. Select Records Updated and set the Target to customerUpdated to store the updated customer and event data.
5. Add the remaining nodes needed for your process model and use the variables customerUpdated to work with the newly written data from the record type.

Store updated data from multiple record types in custom variables

If you provided multiple process variables in the Records Input, you can store the changed values for each record type in separate custom outputs. This way, you can use the new or updated data from these record types in the rest of your process.

For example, let's say you have a process model that creates a new order and a new district. You provided the process variables pv!order and pv!district in the Records Input to update data in the Order record type and the District record type.

To store the order and district information written by the Write Records node:

1. Create process variables to store the output values for each record type.

   Note:  Since there are multiple process variables in the Records Input, you must allow each process variable to store multiple output values.

   1. Create a process variable to store order information:

      Property	Value
      Name	Enter ordersUpdated
      Type	Select the Order record type.
      Multiple	Select the Variable can store multiple values checkbox.

   2. Create a process variable to store district information:

      Property	Value
      Name	Enter districtsUpdated
      Type	Select the District record type.
      Multiple	Select the Variable can store multiple values checkbox.

2. Open the Write Records node.
3. Click the Data tab and select the Outputs subtab.
4. Select Records Updated and set the Target to ordersUpdated to only store data updated in the Order record type.

5. Click + New Custom Output and enter the following properties:

   Property	Value
   Expression	Enter ac!RecordsUpdated to call the output.
   Target	Select districtsUpdated to only store data updated in the District record type.

6. Add the remaining nodes needed for your process model and use the variables ordersUpdated and districtsUpdated to work with the newly written data from each record type.

Recalculate custom record fields using the values updated in the node

If your process is more complex and needs data that is not immediately available in the node output, you can query the record type after a Write Records node.

For example, if a later node uses a custom record field to determine the path of the process and that node is dependent on the data updated in the Write Records node, you must run another query to retrieve its latest value. The value in the Records Updated output will not be refreshed.

If you need to get the latest record data after writing, you can quickly build that query using a Script Task.

To recalculate a custom record field using the values updated in the Write Records node:

1. Create a new process variable with the following properties:

   Property	Value
   Name	recordsUpdated
   Type	Select the same record type used in the Record Input.

2. Drag a Script Task node from the palette and drop it on the connector exiting the Write Record node.
3. Open the Script Task.
4. Click the Data tab and select the Outputs subtab.

5. Click + New Custom Output and enter the following properties:

   Property	Value
   Expression	Use a!queryRecordType() or a!queryRecordByIdentifier to query for the data you need.
   Target	Select recordsUpdated.

6. Add the remaining nodes needed for your process and use the recordsUpdated as needed to work with your newly written data.

Attempting to write unsupported related records

If you try to write a record and a related record from a many-to-one relationship, the icon appears next to the related record field in the process history. While the icon indicates that a change was attempted, the data is not written because many-to-one relationships are not supported for writes.

If you see this icon on a field you expected to be written, add a separate Write Records node to write the related data independently.

Possible errors for Write Records

This section explains some of the common reasons the Write Records smart service might fail.

Configuration errors

Configuration errors can occur when the node setup or inputs are misconfigured and attempts to write to the source cannot succeed.

Some examples of misconfiguration include:

• Using an unsupported record type (for example, a record type with a non-database source).
• An error in the related Event History record type or Event Type Lookup record type (for example, a field is deleted or the record type is deleted).
• An error in the expression calling the process model or the a!writeRecords() function.

• Attempting to write related record data when the relationship is not configured to allow updates to related records.

• Using one node to write data to record types with different sources. This could be different databases or schemas, or different data sources (like a database and Salesforce).

Errors writing too much data

If you have a field of type Extra Long Text and you try to write more than 64,000 characters to this field, the write will fail.

Ensure you configure a character limit on your paragraph or styled text editor components so users don't accidentally write more than the 64,000 character limit.

Errors with the data source

Appian is unable to change data in the source in some circumstances.

These situations include:

• Schema mismatches, such as conflicting data types.
• Database connection problems, like timeouts or invalid credentials.
• Database triggers that cause the requested data change to fail. Appian is not aware of any configured triggers, so they may need to be changed or removed for the smart service to work correctly.

If Appian is unable to write to the database, the record data will still remain available even if the smart service fails.

Errors writing to a related record type

The Write Records node executes all of the writes as a single transaction. This means that if you're updating data in multiple database tables, then data is successfully written to all tables or none of the tables.

For example, let's say a Write Records node is updating data in three different database tables: Orders, Customers, and Employees. If the node is unable to write to the Employees table, then the node will not write to the Orders or Customers table either. This helps avoid incomplete writes (like if an Order is written, but the Customer is not).

Errors syncing data changes

When Appian attempts to sync changes to the source data, the sync will fail when one of the following occurs:

• A record field contains an invalid value.
• The record type's sync filter is invalid (for example, if the source field is no longer available).
• The record type exceeds the synced row limit for your capability tier.

If Appian is unable to sync changes from the source, the record data will be unavailable. For more information on the different types of sync errors and steps to resolve these errors, see Troubleshooting Syncs.

Feature compatibility

Versions

A smart service consists of a function and a node. There are some instances when the smart service function is evolved separately from the smart service node. When the function or node is evolved, a new version of the function or node is available.

The following sections detail the different versions of the Write Records function and Write Records node.

Function versions

You can identify the version of a smart service function by looking at the function name to see if there is a version suffix.

If you are using a previous version, be sure to refer to the corresponding documentation from the list below. To use the latest version of the function, replace the old version of the function with the latest version.

Node versions

You can identify the version of a smart service node by looking at node type and version.

To review a node's type and version:

1. In the Process Modeler, click Tools > Generate Documentation.
2. In the generated documentation, review the Node Type and Version for the specific node.

If you are using a previous version, be sure to refer to the corresponding documentation from the list below. To use the latest version of the node, replace the old version of the node with a new node.

Feedback