Delete Records Smart Service

Overview

The Delete Records smart service allows you to delete data in your database tables using a record type. If your record type has optimized data access, the changes are automatically synced and removed from the record type.

You can delete one or more records at a time per node. Related records can also be deleted when you delete a base record. See the specific usage considerations for deleting related records.

Requirements

• Required Object: One or more record types that meets the following conditions:
  • 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 field 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.
• Required Permission: Node executer needs at least Viewer permissions to all record types where data is created or updated.

Properties

• Category: Data Services
• Icon:

• Assignment Options: Unattended

• Compatible with autoscale: Yes

Configuration options

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

Data tab

Node inputs

See also: Record Type

Node outputs

Usage considerations

Pass record data to process models

Use this smart service in a related action to allow users to review data and confirm it should be deleted.

You can automatically generate a Delete record action, like process model below.

To learn how to manually set up a Delete record action, see the hard delete a record using a process model example.

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

Hard versus soft deletes

The Delete Records smart service permanently removes data from the source. This is a hard deletion.

If your app or database design requires a soft delete for auditing or to maintain referential integrity, use the Write Records smart service to mark a record as deleted according to your schema.

To configure a soft delete:

• Use a field in your data source, such as isDeleted or status, to mark a record as inactive.
• Use the Write Records smart service to update this field when a user deletes a record.
• Configure a sync filter so Appian only syncs records where the isDeleted value is false.

Delete related records

You can use a single Delete Records smart service to delete a record and its related records at the same time, or you can use multiple Delete Records smart services to delete records from different record types in a sequence.

Use a single node to delete records and related records

When you delete records in a base record type, related records from a one-to-many or one-to-one relationship can also be deleted.

The Delete Records node executes all of the deletes as a single transaction. This ensures that either all of the input data is deleted or none of the data is deleted. This helps avoid incomplete deletes (such as where an Order is deleted, but an Order Line Item is not).

To delete data for a base record and its related records, the following conditions must be met:

• Data Source: The records and related records must share the same data source.

  If the record types do not share a single source schema or database, the smart service deletes the base record and makes no change to the related record.

• Relationship Configuration: The relationship must be configured to allow writing and deleting related records.

  If you do not allow writing and deleting, the related record's foreign key field referencing the base record is set to null.

  Note:  If foreign keys are used in your source table, ensure the correct delete behavior is set for the foreign key constraint:

  • To allow writing and deleting, set the delete behavior to CASCADE.
  • To not allow writing and deleting, set the delete behavior to SET NULL.

• Primary Key Requirements: If you need to delete a record that uses a composite primary key, you must provide the values for all of the key fields.

Use multiple nodes to delete records and related records

If you need to sequentially delete related data or your data lives in different data sources, use a separate Delete Records node for each record type.

For example, consider a Customer record type that has a one-to-many relationship with a Payment Type record type. The data lives in two distinct relational databases.

To delete a customer and their associated payment data, your process model should follow this sequence:

• Delete Related Records: Use a Delete Records node to remove all payment types associated with the customer.
• Delete Base Record: Use a second Delete Records node to remove the customer.

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

Delete document information

When you use this smart service to delete a document identifier from a document management record type, the document itself will still exist in the generated document folder; however, the document will be inaccessible since there is no way to reference it.

Documents without an associated record will be automatically deleted based on the cleanup schedule. You can also add a Delete Document smart service to your process models to immediately delete unused documents.

Once the document is deleted from the document folder, the document information will also be removed from the Document record type.

Sync behavior for record types

If you use this smart service to delete data in a database table, and that table is the source of a synced record type, those changes will be automatically synced and removed from 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:

• Troubleshooting Data Sync for more information on the different sync errors.
• Skip failed smart service syncs to learn about the sync option you can enable to skip failed syncs and keep data available.

a!deleteRecords()

The Delete Records 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. When creating a web API, select the Delete Records template to generate the object with this function automatically configured.

Syntax

a!deleteRecords( records, onSuccess, onError )

Parameters

Automatic refresh on record views

When you use this smart service on a record view, the view will automatically refresh with the latest record data.

This means that when your record view interface has a Record Action component that triggers the Delete Records smart service, or a button component that triggers the a!deleteRecords() function, the record view data will automatically refresh after the delete occurs.

This refresh behavior only applies to data supplied by rv!record. If your record view definition uses rv!identifier or a local variable to supply the record data, the data will not automatically refresh; instead, you will need to configure additional refresh behavior.

Possible errors

This section explains some of the common reasons the Delete 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!deleteRecords() function.
• Attempting to write related record data when the relationship is not configured to allow updates to related records.

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 Delete 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 Delete Records node is deleting data from three different database tables: Orders, Customers, and Employees. If the node is unable to delete data in the Employees table, then the node will not delete data in the Orders or Customers table either. This helps avoid incomplete writes (like deleting a Customer, but not their Orders).

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.

Examples

The following examples show the different ways you can use the a!deleteRecords() function to delete your record data.

The following example data is from the Appian Retail application, available for free in Appian Community Edition. To follow along with this example, log in to Appian Community and register for Appian Community Edition.

Hard delete a record using a process model

Goal: Use the Delete Records smart service to permanently delete a record.

Sometimes data is created by mistake or your business could be required to delete information on customer request. Users will need the ability to remove record data from your application in those types of situations.

Example

Imagine you need to remove an Employee from the Appian Retail application. You can let users delete record data using a related action connected to a process model. Set a process variable using the record type as the data type to pass the user's selection to the Delete Records smart service.

1. Create a new form interface that allows users to review the employee's information and confirm they want to delete the Employee record.
2. Create a rule input called employee and set the type to the Employee record type. This will allow you to pass employee data to the process model.

3. Create a new process model and configure the process model properties to use the new form and pass record data:

   • Go to File > Properties.
   • Go to the Variables tab and create a new process variable called employee.
   • Set the Type to the Employee record type.
   • Select the Parameter checkbox.
   

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

   • Go to the Process Start Form tab and select your interface.
   • Map your employee process variable to the employee rule input from the interface.
4. Drag and drop a Delete Records node into your process model.
5. Go to the Data tab.
6. For the Records input, set the employee process variable as the Value.
7. Click OK.
8. (Optional) Configure a cancel flow.

9. Save and publish the process model.

   

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

10. Create a record action that launches the new process model.

The process node examines the primary key field (in this case, the id field) and deletes that record from the source.

Hard delete a record and its related records using a process model

Goal: Use the Delete Records smart service to permanently delete a record and its related records.

Sometimes data is created by mistake or your business could be required to delete information on customer request. When the data in question is modeled with a record type and related record types, users will need the ability to remove record data from your application in a seamless way.

Example

Imagine you need to delete an Order from the Appian Retail application. The Order record type has six relationships that you need to consider when building the action to delete these records.

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

When you delete a record, you want to consider whether or not any related data should also be deleted.

In this example:

• Order Event History and Order Line Item each store supporting data about orders. This means that if an order is deleted, the related order line items and events are no longer needed.
• Case stores data about support cases submitted by customers. You'll want to keep all logged cases even if an order is deleted since support engineers' performance is evaluated based on the number of cases they close.
• Address, Customer, and Employee are not reliant on order information since many orders can belong to the same address, customer, or employee.

Based on how the data is related, you'll want to update the relationship configurations for your record types so certain related data is deleted whenever an order is deleted:

You cannot write or delete data from a many-to-one relationship, so you don't need to update the relationships to the Address, Customer, or Employee record types.

Once the relationships are properly configured, you can create a related action to delete a record and any relevant related records.

To create a related action that deletes a record and its related records:

1. Create a new form interface that allows users to review the order information and confirm they want to delete the Order record. Because line items and the event history will also be deleted, the interface should indicate this fact. To do so, you could use one of these options:

   • Show the order line items in a grid component and the order event history in an event history list component on the form.
   • Warn the user that this related data will be deleted by stating that fact in the confirmation dialog.
2. Create a rule input called order and set the type to the Order record type. This will allow you to pass order data to the process model.

3. Create a new process model and configure the process model properties to use the new form and pass record data:

   • Go to File > Properties.
   • Go to the Variables tab and create a new process variable called order.
   • Set the Type to the Order record type.
   • Select the Parameter checkbox.
   

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

   • Go to the Process Start Form tab and select your interface.
   • Map your order process variable to the order rule input from the interface.
4. Drag and drop a Delete Records node into your process model.
5. Go to the Data tab.

6. For the Records input, set the order process variable as the Value.

   

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

7. Click OK.
8. (Optional) Configure a cancel flow.

9. Save and publish the process model.

   

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

10. Create a record action that launches the new process model.

The process node examines the primary key field (in this case, the id field) and deletes that Order record from the source, as well as any Line Item or Event History records associated with that Order. Any Case records with an orderId value matching the Order's id will now show null for orderId.

Soft delete a record using a process model

Goal: Use the Write Records smart service to remove a record from a record type but keep the data available in the source.

Some delete actions need to make the data generally inaccessible in your app, but administrators or other privileged users can retain access in the record type's source (like a database). This type of deleting lets you restore the data if needed.

A related action is a simple way to let users select a record to remove and confirm they want to delete it by clicking a button. This action starts a process model that sets an isDeleted field to true. You can then create a sync filter so only records that have an isDeleted field set to false or null are synced in Appian.

Example

Let's say you need a record action that allows users to delete orders from a retail application. You want the Order data to persist in the data source, but it should not be available in Appian.

The application contains an Order record type, which stores all order information. This record type has record events configured with the following event types:

• Created Order
• Updated Order
• Shipped Order
• Delivered Order
• Deleted Order

To ensure this action can be traced back to a specific user, we'll want to capture a Deleted Order event.

Here's one way you could create this action:

1. Create an interface that lets users confirm that they want to delete the order.

2. For the form's submit button, update the value and saveInto properties so the isDeleted field is set to true:

   1
   2
   3
   4
   5
   6
   7
   8
   9
   a!buttonWidget(
      label: "Delete",
      icon: "trash",
      submit: true,
      style: "GHOST",
      color: "NEGATIVE",
      value: true,
      saveInto: ri!order[recordType!Order.fields.isDeleted]
   )
   

3. Create a new process model and configure the process model properties:
   • Go to File > Properties.
   • Go to the Variables tab and create a new process variable called order.
   • Set the Type to the Order record type.
   • Go to the Process Start Form tab and select your interface.
   • Map your order process variable to the input from the interface.
4. Drag and drop a Write Records node into your process model.
5. Go to the Setup tab.
6. Add a Write Records node to take the data from the form (as pv!order) and write the soft delete to the Order record type.
7. For Records Input, select the order process variable. By default, Record Type for Events populates with the Order record type. Since the Order record type has record events configured, the Write Events section can be set up.
8. For Choose when to write events for the Order record type, leave the default Always. The smart service will always write an event whenever an order is soft deleted.
9. Select the values to write for the event:
   • For Event Type, select Soft Deleted Order.
   • For User, keep the default Process Initiator.
   • For Automation Type, select None (User).
   • For Timestamp, keep the default Now.

10. Save and publish the process model.

    

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

11. Create a related action that launches the new process model. Use the Order record type in the related action context as rv!record.

12. In the Order record type, add a sync filter so only records with an isDeleted value of null or false are synced. This filter must be created using expression mode because you need to use the OR operator for the two filter conditions.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    a!queryLogicalExpression(
      operator: "OR",
      filters: {
        a!queryFilter(
          field: "IS_DELETED", /* The filter matches the source field's name, not the record field */
          operator: "is null"
        ),
        a!queryFilter(
          field: "IS_DELETED",
          operator: "=",
          value: false()
        )
      },
      ignoreFiltersWithEmptyValues: true
    )
    

Soft delete a related record using a process model

Goal: Use the Delete Records smart service to remove a related record but keep the data available in the source.

Some related records, like line items on an order, are managed in the context of a base record. In those situations, your interface may need to allow users to select and delete these related records.

Appian has many interface components for selecting data, including checkboxes and radio buttons. In this example, we'll use a dynamic link to create clickable text that saves a value to a rule input property.

Example

Let's say you need a record action that allows users to update a support case, including any of the comments on the case. Comments are in a separate record type and referenced in the Case through a record type relationship.

In this scenario, we'll mark the comment as deleted and use a sync filter to exclude deleted comments from the record type. Since we are soft deleting the comments, they are still available in the source for auditing purposes.

A user should be able to delete comments if they meet either of the following conditions:

• They are the comment's author.
• They are a manager of the support department.

In the following example, if Alice K. views the case, she will only be able to delete her comment.

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

Since a case can have one or more comments, a reusable interface will let you add multiple instances of these components without unnecessarily repeating code. You can see an example of how to do this in our video explaining the use of a!forEach() in interfaces.

Before you build the record action:

1. Create the interface that will display each comment.
2. Name the interface commentBox.

To create this action, you could:

1. Create an interface that lets users view the case and select the comments the want to delete.
2. In the interface, create a rule input called case and set the type to the Case record type.
3. In the interface, add a section layout where you want the case comments to appear.

4. In the section's contents parameter, add a a!forEach() loop that uses the commentBox interface to display each comment on the case. Each comment box is followed by a dynamic link that sets the isDeleted field to true when clicked.

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   36
   37
   38
   39
   40
   41
   42
   43
   44
   45
   46
   a!forEach(
    /* 
    The items evaluated for each loop are the Comments related to each case.
    To loop through them, you must specify the record type relationship here.
    */
    ri!case[recordType!Case.relationships.Comment],
     {
       if(
         fv!isFirst,
         a!horizontalLine(color: "#ffffff"),
         a!horizontalLine(
           showWhen: and(
           fv!item[recordType!Comment.fields.isDeleted] <> true,
           count(fv!item) > 1
         ))
       ),
       if(
         fv!item[recordType!Comment.fields.isDeleted],
         a!richTextDisplayField(),
         rule!commentBox(fv!item),
       ),
       a!linkField(
         labelPosition: "COLLAPSED",
         links: {
           a!dynamicLink(
             label: "Delete comment",
             value: true(),
             saveInto: ri!case[recordType!Case.relationships.Comment][fv!index][recordType!Comment.fields.isDeleted],
             showWhen: {
               and(
                 fv!item[recordType!Comment.fields.isDeleted] <> true,
                 if(
                   or(
                     loggedInUser() = fv!item[recordType!Comment.fields.createdBy],
                     a!isUserMemberOfGroup(loggedInUser(), "AT Support Managers")
                   ),
                   true(),
                   false()
                 )
               )
             }
           )
         }
       )
      }
   )
   

5. Create a new process model and configure the process model properties:
   • Go to File > Properties.
   • Go to the Variables tab and create a new process variable called case.
   • Set the Type to the Case record type.
   • Go to the Process Start Form tab and select your interface.
   • Map your case process variable to the case rule input from the interface.
6. Drag and drop a Write Records node into your process model.
7. Go to the Setup tab.
8. Add a Write Records node to take the data from the form (as pv!case) and write any soft deletes to the Comment record type.
9. For Records Input, select the case process variable. By default, Record Type for Events populates with the Case record type. Since the Case record type has record events configured, the Write Events section can be set up.
10. For Choose when to write events for the Case record type, leave the default Always. The smart service will always write an event whenever an case is changed.
11. Select the values to write for the event:
    • For Event Type, select Updated Case.
    • For User, keep the default Process Initiator.
    • For Automation Type, select None (User).
    • For Timestamp, keep the default Now.
12. Save and publish the process model.
13. In the Case record type, create a related action that launches the new process model. Use the Case record type in the related action context as rv!record.

14. In the Comments record type, add a sync filter so only records with an isDeleted value of null or false are synced. This filter must be created using expression mode because you need to use the OR operator for the two filter conditions.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    a!queryLogicalExpression(
      operator: "OR",
      filters: {
        a!queryFilter(
          field: "IS_DELETED", /* The filter matches the source field's name, not the record field */
          operator: "is null"
        ),
        a!queryFilter(
          field: "IS_DELETED",
          operator: "=",
          value: false()
        )
      },
      ignoreFiltersWithEmptyValues: true
    )
    

Hard delete a record using an expression

Goal: Use the a!deleteRecords() function to delete a record.

Instead of building an entire process model to delete data, you can use the a!deleteRecords() function to let users delete data directly from an interface.

Example

For example, in a retail application, you could create an editable grid that lets users add, update, and delete inventory data.

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

In one of the columns, we'll display a rich text icon and set its link parameter so the icon is clickable. When the user clicks the icon, the saveInto deletes the current row's record data using a!deleteRecords(). To remove it from the grid, you should also use the onSuccess parameter to remove the now-deleted record from the local variable storing the data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
a!richTextDisplayField(
  value: a!richTextIcon(
    icon: "trash",
    altText: "delete " & fv!index,
    caption: "Delete " & fv!item[recordType!Item.fields.name],
    link: a!dynamicLink(
      value: fv!index,
      saveInto: {
        a!deleteRecords(
          records: fv!item,
          onSuccess: {
            a!save(local!items, remove(local!items, save!value)),
            }
        )
      }
    ),
    linkStyle: "STANDALONE",
    color: "NEGATIVE"
  )
),

Hard delete a record with a composite primary key

Goal: Use the a!deleteRecords() function to delete a record that has a composite primary key.

When a record type uses a composite primary key, you must pass a record map that includes all component fields to the records parameter.

Example

In this example, an OrderItem record is uniquely identified by both orderId and itemId. To delete a specific item from an order, both fields must be provided.

1
2
3
4
5
6
7
8
9
10
a!deleteRecords(
  records: recordType!OrderItem(
    /* Both fields that make up the key must be provided to identify the record */
    recordType!OrderItem.fields.orderId: 101,
    recordType!OrderItem.fields.itemId: 5
  ),
  onSuccess: {
    /* Add logic to refresh the interface */
  }
)

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 Delete Records function and Delete 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