Add Record Type Relationships

What are record type relationships?

Record type relationships (often referred to as just relationships) allow you to easily connect data across different record types to create a comprehensive view of your enterprise data. Unlike traditional database views, record type relationships allow you to relate data from disparate sources, like Salesforce and a SQL database, without moving or copying the data.

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

Why use relationships?

Record type relationships provide the following advantages:

• Cross-source relationships: Connect data from different sources and tables without moving or duplicating data.
• Guided configuration: Define relationships using a guided experience rather than writing complex SQL queries or involving database experts.
• Dot notation references: Reference related data directly in interfaces, expressions, and process models using standard dot notation.
• Simultaneous writes: Create and update a record and its related records simultaneously. For example, updating a Customer and their Support Case in one action.

Relationship requirements

Relationships require specific record type configurations, relationship types, and common field mappings. Review these requirements before adding relationships.

Supported record types

Relationships can be configured between any record type that uses optimized data access or direct data access, so long as relationships and related features are enabled.

When you relate a synced record type to an unsynced record type, Appian performs a federated query to retrieve data from different sources. Monitor federated queries using the Query Performance view to ensure your application remains performant.

Supported relationship types

Record type relationships support the following relationship types:

Common field requirements

Common fields are the two fields shared between the base record type and the related record type. Common fields enforce the selected relationship type by telling Appian how to match records across the two record types.

For example, in a many-to-one relationship between customers and industries, the common fields are:

• Customer record type: The industryId field.
• Industry record type: The id field (the primary key field).

Appian uses the industryId field to look up the corresponding value of the id field in the Industry record type and return the associated data for that industry.

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

Common fields must meet the following requirements:

• Single field mapping: You must select exactly one field from the base record type and one field from the related record type. Appian does not support mapping multiple fields (composite keys) as common fields. See relationships with composite primary keys for alternative configurations.
• Supported fields:
  • You can select any record fields or sync-time custom record fields as common fields.
  • If you select a sync-time custom record field as a common field, you cannot allow writes to related records.
• Data type requirements:
  • The fields must be of type Number (Integer), Text, User, or Group.
  • The fields must be the same data type.
• Unique value requirements:
  • You must select a field with unique values on the one-side of the relationship. The field can be the primary key field or a field with a uniqueness constraint.
  • In a one-to-one relationship, both common fields must be unique.

Write and delete related records requirements

You can configure one-to-many and one-to-one relationships to allow Appian to make changes to base records and related records at the same time.

When you select the Write or delete… option on a relationship, you can use a single Write Records smart service or Delete Records smart service node to modify both records.

Appian selects the Write or delete… option by default when you create a new one-to-many or one-to-one relationship, so long as the record types have the same data source.

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

Allow writes and deletes

Allow changes to related records when the base record type and related record type need to be updated together. For example, Orders and Order Line Items are almost always updated together, so the relationship on the Order record type can allow updates to Order Line Items when modifying Orders.

To allow Appian to write and delete related records, the record types and relationship configuration must meet these requirements:

• Source requirements:
  • Both record types must connect to database tables from the same data source.
  • Database views are not supported.
• Relationship configuration requirements:
  • The relationship must be one to many or one to one.
  • You must select the Write or delete… option in the relationship configuration.
• Common field requirements:
  • For one-to-many relationships: Use the primary key or a unique key from the base record type as the common field.
  • For one-to-one relationships: Use the primary key from the base record type as the common field. The common field on the related record type must be a unique key, but cannot be the primary key.
  • Unsupported fields: You cannot use custom record fields as the common fields when you want to write related records.

Do not allow writes and deletes

To prevent Appian from writing and deleting related records, select the Do not write or delete… option on the relationship. Appian automatically selects this option if the base record type and related record type do not share the same source.

When this option is selected and the base record is deleted, the behavior for the related record depends on the data source of the related record type:

Relationship considerations

To ensure your record type relationships function correctly, consider the following guidelines regarding composite primary keys and referential integrity.

Using composite primary keys in relationships

When you configure a relationship with a record type that uses a composite primary key, the supported relationship type depends on whether that record type is the base or the related record type.

Because a composite primary key requires multiple fields to uniquely identify a record, it cannot serve as the "one" side of a relationship. Instead, use the following configurations:

• Relating TO a composite key record type: You can only add one-to-many relationships. For these relationships, Allow writes and deletes is automatically enabled and cannot be disabled.
• Relating FROM a composite key record type: You can only add many-to-one relationships. Use one of the fields from the composite key as the common field.

Example: If an Order Item record type has a composite primary key of {orderId, itemId}, you can create the following relationships:

• From the Order record type, create a one-to-many relationship to the Order Item record type using orderId as the common field.
• From the Item record type, create a one-to-many relationship to the Order Item record type using itemId as the common field.
• From the Order Item record type, create many-to-one relationships to Order and Item.

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

Maintaining referential integrity

Record type relationships do not enforce referential integrity. Instead, Appian relies on the integrity rules defined in the underlying data source.

When you modify data using a smart service:

1. Appian writes the change to the source system.
2. The source system enforces constraints (such as foreign keys or cascading deletes).
3. Appian syncs the changes back to the record type.

For example, a Customer record type is related to an Order record type, and the underlying database enforces a foreign key constraint between the two tables. If you delete a customer using a Delete Records smart service, the customer is deleted from the database table, and any of their orders are also deleted in the order database table.

Relationship best practices

Relationships perform best when you follow these practices:

• Use similar sync schedules: If your source enforces referential integrity, keep the base record type and related record types on similar sync schedules. Matching schedules ensure foreign key relationships remain consistent in Appian.
• Manage cross-source consistency in application logic: If you relate record types from different sources (like Salesforce and a database), referential integrity cannot be enforced by a single system. You must manage data consistency within your application logic.

Add relationships

There is no limit to the number of relationships you can add to a record type. You can relate synced record types, unsynced record types, or a combination of both.

To add a relationship:

1. In a record type, go to Data Model.
2. Click ADD RELATIONSHIP.
3. In Related Record Type, enter the name of the record type you want to relate.
4. Click NEXT.
5. In Relationship Name, enter a name. This is how you’ll reference the relationship and access the related record fields.
6. For Relationship Type, select a relationship type:
   • One-to-Many
   • Many-to-One
   • One-to-One

7. For Common Fields, select exactly one field from the base record type and one field from the related record type to enforce the relationship.

   Field uniqueness requirements:

   • For one-to-many: The field on the base record type must be unique.
   • For many-to-one: The field on the related record type must be unique.
   • For one-to-one: Fields on both sides must be unique.

   Data type requirements:

   • Fields must be type Number (Integer), Text, User, or Group.
   • Both fields must be the same data type.

8. (Optional) Select Write or delete related records to allow the Write Records and Delete Records smart services to update base and related records simultaneously.

   Note:  This option is only available for one-to-many and one-to-one relationships. If you add a one-to-many relationship to a record type with a composite primary key, this option is enabled by default and cannot be disabled.

9. In Preview, select a record to see a preview of the related data.

   

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

10. Click ADD.
11. Click SAVE.

Add many-to-many relationships

You can't implement a direct many-to-many relationship between record types. Instead, you can effectively create a many-to-many relationship by creating a third record type to connect the other two. In a database, this would be a join table.

The join table should include a primary key field, and two additional fields that references the primary key fields from the other two record types. Once you set up the source, you can then connect the three record types using one-to-many and many-to-one relationships.

Let's look at the Employee and Project example below.

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

To create a many-to-many relationship between employees and projects:

1. Create a new record type. In this example, we'll call it Project Assignments.

2. Set the data source of the record type to a join table.

   In this example, the Project Assignment record type uses a join table with the fields:

   • Id (primary key)
   • employeeId
   • projectId

   Note:  In the underlying data source of your third record type, you must have a primary key field. It's also recommended to add foreign keys to the fields that reference the primary keys from the other two record types. This will allow your source to enforce referential integrity.

3. From the Project Assignment record type:
   • Add a many-to-one relationship with the Employee record type.
   • Add a many-to-one relationship with the Project record type.
   

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

4. Click SAVE.
5. From the Employee record type, add a one-to-many relationship with the Project Assignment record type.
6. From the Project record type, add a one-to-many relationship with the Project Assignment record type.

Now, you can use the relationship on the Employee record type to index into the Project Assignment record type, then use the relationship on the Project Assignment record type to index into the Project record type.

For example, recordType!Employee.relationships.projectAssignments.relationships.projects.fields.name would return the related project names for a given employee.

Since the Project record type also has a relationship with Project Assignments, you can use the Project record type to return fields from the Employee record type. In this instance, you could use recordType!Project.relationships.projectAssignments.relationships.employees.fields.lastName to return the related employee last names for a given project.

By connecting all three record types, you can easily reference your many-to-many data in your grids, charts, queries, and more.

Add suggested relationships

By default, a relationship only exists on the record type where it was created. You can easily create a bi-directional relationship, where both record types reference each other, using suggested relationships.

Suggested relationships will appear on a record type when there is an opportunity to create a bi-directional relationship with another record type.

For example, if the Order record type has a relationship to the Customer record type, then the Customer record type would have a suggested relationship to the Order record type.

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

To add a suggested relationship:

1. In a record type, go to Data Model.
2. Under Suggested Relationships:
   • Click to add a relationship to a specific record type.
   • Click ADD ALL RELATIONSHIPS to add relationships to all the record types in the list.
3. Click SAVE.

Manage relationships

After you add relationships to a record type, you can view, edit, or delete them from the Data Model page.

View relationships

All relationships defined in the record type will appear in the RELATIONSHIPS section of the Data Model page.

To view all relationships accessible from your record type, select the Show relationships from related record types checkbox under the relationship diagram.

To enlarge the diagram and easily view complex relationships, click the Expand relationship diagram in dialog box button.

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

Edit relationships

There are scenarios where you may need to edit the name of a relationship, change the relationship type, or update a common field.

To edit a record type relationship:

1. In the record type, go to Data Model.
2. Under RELATIONSHIPS, click the name of the relationship. The Edit Relationship dialog appears.
3. Change the Relationship Name, Relationship Type, or Common Fields as necessary.
4. Click OK.
5. Click SAVE.

View relationship dependents

To see which objects depend on the record type relationship:

1. In the record type, go to Data Model.
2. Under RELATIONSHIPS, click View objects that reference this relationship next to the relationship name.

A list of relationship dependents will appear.

Delete relationships

To delete a record type relationship:

1. In the record type, go to Data Model.

2. Under RELATIONSHIPS, click Remove Relationship next the relationship name.

   Caution:  If you remove a relationship, any references to the relationship will break.

3. Click SAVE.

Use related data

Once you configure a record type relationship, you can easily reference related data to build advanced interfaces, reports, queries, and process models.

When you reference related data, Appian automatically enforces the record type object security and any record-level security configured on the related record type. You do not need to add extra filters to your queries to protect sensitive data.

Reference a related record field

Use the relationships property to reference fields from a direct relationship or a nested relationships (a relationship defined on a related record type).

For example, if Customer relates to District, and District relates to Warehouse, you can access Warehouse data through the District relationship.

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

Reference related fields in design mode

In an interface, you can use design mode to easily reference related record fields in any records-powered components, like a read-only grid or chart.

To select a related record field in design mode:

1. In the component configuration, use the dropdown to select a field.
2. In the field selection list, hover over a relationship name.

3. Select a field from the related record type.

   

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

4. To access a nested relationship, continue hovering over relationship names in the subsequent lists until you reach the desired field.

   

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

Reference related fields in expression mode

In expressions, reference a related record field using the recordType! domain, followed by . dot notation and the relationship property. You can reference a field from a direct relationship or fields from a nested relationship.

The following example uses recordType!Customer.relationships.district.fields.name to display the Name field from the related District record type.

1
2
3
4
5
6
7
8
9
a!linkField(
  links: {
    a!recordLink(
      label: fv!row[recordType!Customer.relationships.district.fields.name],
      recordType: recordType!Customer,
      identifier: fv!row[recordType!Customer.relationships.district.fields.id]
    )
  }
)

Filter, sort, and limit related records

When referencing one-to-many relationships, use a!relatedRecordData() to filter, sort, or limit the returned data.

The a!relatedRecordData() function is useful when you only need a specific subset of data, such as the most recent comment on a support case or all the critical cases assigned to an employee.

Configure a!relatedRecordData in design mode

You can use a!relatedRecordData() to filter, limit, and sort related records in a read-only grid.

To configure a!relatedRecordData() for a grid in design mode:

1. Add a read-only grid to an interface and use a record type as the source.
2. In the Component Configuration pane, go to the Data tab.
3. Click FILTER RELATED RECORDS.

4. Configure your filters, sort order, or limits. For example, to show only the latest comment, set the limit to 1 and sort by the createdOn field.

   Note:  When applying a sort or filter in a!relatedRecordData(), you must start your record type reference directly from the related record type (recordType!<Related record type name>).

   

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

Configure a!relatedRecordData in expression mode

You can use a!relatedRecordData() within the relatedRecordData parameter of a!recordData() or a!queryRecordType().

When applying a sort or filter in a!relatedRecordData(), you must start your record type reference directly from the related record type (recordType!<Related record type name>).

For example, the following expression returns the latest comment left on each case in a read-only grid:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
a!gridField(
  label: "Support Cases",
  labelPosition: "ABOVE",
  data: a!recordData(
    recordType: recordType!Case,
    relatedRecordData: a!relatedRecordData(
      relationship: recordType!Case.relationships.comment,
      limit: 1,
      /* Notice the sort starts at the Comment record type */
      sort: a!sortInfo(
        field: recordType!Comment.fields.createOn,
        ascending: false
      ),
    )
  ),
  columns: ...
)

Both configurations in design mode and expression mode would return something like this:

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

Pass related record data to a process model

If a relationship is configured to write records and related records simultaneously, you can use a single rule input in an interface to pass all data to a process model.

For example, you want to create a new customer (in the Customer record type) and also capture the customer's address (in the Address record type). In your start form, create a rule input (ri!customer) that points to the Customer record type and index into the related record fields you need.

The full expression for a component would look like this:

1
2
3
4
5
6
7
8
9
a!pickerFieldRecords(
  label: "State",
  labelPosition: "ABOVE",
  placeholder: "Select a State",
  maxSelections: 1,
  recordType: recordType!Customer,
!  value: ri!customer[recordType!Customer.relationships.address.fields.stateId],
!  saveInto: ri!customer[recordType!Customer.relationships.address.fields.stateId]
)

When the form is submitted, the customer rule input is passed to the process model as pv!customer. When the process completes, pv!RecordsUpdated shows the primary key values of the new Customer and Address.

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

Feedback