Add Record Type Relationships

What are record type relationships?

Record type 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 record type relationships?

Record type relationships eliminate the need for complex database views and provide more advantages.

Record type relationships provide several key benefits:

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

Usage considerations for relationships

Before adding record type relationships, review the supported relationship types, common field requirements, and write and delete options.

Supported record types for relationships

You can configure relationships 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. To ensure your application remains performant, you should monitor federated queries using the Query Performance view.

Supported relationship types

You can configure the following types of relationships:

Common field requirements

Common fields are the two fields shared between the base record type and the related record type. When configuring a relationship, you'll select common fields to enforce the selected relationship type.

For example, in a many-to-one relationship between customers and industries, you could use the following fields as the common fields:

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

This will tell Appian to use 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

When configuring your common fields, note 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 doesn't support mapping multiple fields (composite keys) as common fields. Learn how to add relationships to record types with composite primary keys.
• 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. This 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.

Relationships with composite primary keys

Since you can only select a single field as a common field, the supported relationship types for record types with a composite key depend on whether it's the base or the related record type:

• 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.

You cannot use a composite primary key as the "one" side of a relationship because it requires multiple fields to uniquely identify a record.

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

Write and delete related records

When configuring one-to-many and one-to-one relationships, you can allow Appian to make changes to your 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 perform these actions.

By default, Appian has the Write or delete… option selected when you create a new one-to-many and one-to-one relationships, 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

You should 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 should almost always be updated together, so the relationship on the Order record type should 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.
  • Composite key limitation: You cannot allow writes or deletes if the relationship relies on a composite primary key, as you can only select one field as the common field.

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. This option is automatically selected if your base record type and related record type do not share the same source.

When this option is selected, the base record is deleted; what happens to the related record depends on the data source of the related record type:

Referential integrity

Record type relationships do not inherently enforce referential integrity. Instead, Appian relies on the integrity rules defined in your 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, let's say a Customer record type is related to an Order record type. The underlying database enforces a foreign key constraint between the two tables.

If you attempt to 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.

Best practices for referential integrity

If your source enforces referential integrity, keep the base record type and related record types on similar sync schedules. This ensures foreign key relationships remain consistent in Appian.

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.

   Common fields must meet these requirements:

   • Use the data type Number (Integer), Text, User, or Group.
   • Both fields must be the same data type.
   • The field on the one-side of the relationship must contain unique values. This can be a primary key field or a field with a unique constraint.
   • For one-to-one relationships, fields on both sides must contain unique values.

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

Once you add relationships to the record type, you can:

• View all relationships.
• Edit relationships.
• View relationship dependents.
• Delete relationships.

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.

Reference related data

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

This section explains how to:

• Reference a related record field in an interface or expression.
• Filter, sort, and limit related records.
• Pass related record fields into a process model.

Reference a related record field

Once you configure a relationship on your record type, you can use the relationships property to reference fields from that relationship or any nested relationships. A nested relationship is a relationship defined on a related record type.

For example, if the Customers record type has a relationship to the District record type, then you can access data from the District record type and any of the record types related to the District record type, like Warehouses.

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

Depending on the object, you can reference a related record field in design mode or expression mode. Let’s look at examples referencing related record fields in design mode and expression mode within an interface.

In design mode

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

Whenever you select a field to display in the component, a list of record fields and relationships defined on the record type will appear. To select a related record field, hover over the relationship in the list.

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

To select a related record field from a nested relationship, hover over a relationship in the list. In the list of fields and relationships configured on the related record type, hover over a relationship.

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

In expression mode

In expression mode, you can reference a related record field using the recordType! domain followed by . dot notation and the relationships property. A list of relationships configured on the record type will appear, where you can select a relationship. Then, you can use . dot notation and the fields property to select a related record field.

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

For example, the following expression uses an a!linkField() component to link to the district related to each customer. The label of the link uses recordType!Customer.relationships.district.fields.name to display the Name field from the 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]
    )
  }
)

Learn more about the relationship property.

Filter, sort, and limit related record fields

When you select the related record fields to display in your interfaces or queries, you may decide that you only need a limited amount or a filtered subset of your related data—especially when you reference data from a one-to-many relationship.

For example, if the Support Case record type has a one-to-many relationship with the Comments record type, you may not want to list every comment left on a case in your read-only grid. Instead, you just want to display the most recent comment left on each case.

To return the exact data you need, you can use a!relatedRecordData() to filter, sort, and limit the related record set returned from a one-to-many relationship. You can use this function in a!queryRecordType(), a!recordData(), or in a read-only grid.

Let's look at an example configuring a!relatedRecordData() in a read-only grid in design mode and expression mode.

In design mode

In design mode, you can easily filter, limit, and sort your related record data in a read-only grid in the Component Configuration pane by going to the Data tab and clicking FILTER RELATED RECORDS.

For example, to only display the latest comment for each case, you can limit the number of comments returned to 1 and sort by the latest value in the createdOn field from the Comment record type.

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

In expression mode

In expression mode, you can filter, sort, and limit a related record set using a!relatedRecordData() in the relatedRecordData parameter of a!recordData() or a!queryRecordType().

To return the latest comment left on each case in a read-only grid, the full expression would look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
a!gridField(
  label: "Support Cases",
  labelPosition: "ABOVE",
  data: a!recordData(
    recordType: recordType!Case,
    relatedRecordData: a!relatedRecordData(
      relationship: recordType!Case.relationships.comment,
      limit: 1,
      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 fields into a process model

Record actions to create or update record data often include information stored in a one-to-many or one-to-one related record type. When you set the relationship to allow Appian to write the record and related record data simultaneously, your interface can use a single rule input to pass the data to a process model.

For example, let's say you have a Customer record type and an Address record type, and there is a one-to-many relationship between Customer and Address. You want to add a "Create" action to the Customer record type that also captures the customer's address.

To create a form that can pass data from the Customer record type and the Address record type, you can configure your form components with a single rule input (for example, ri!customer) that references the Customer record type.

Since the Customer record type has a relationship with the Address record type, and that relationship is configured to allow writes to related records, you can use relationship references to store and pass related record fields in your form.

In design mode

To build the form in this example in design mode:

1. In an interface, select an input component.
2. In the Component Configuration pane, click Data.
3. In the Display Value field, click the arrow and hover over ri!customer, then hover over address. This will display the available fields on the Address record type.
4. Select the city field from the Address record type. The related record field will appear appended to the relationship name. In this example, the field appears as address.city.
5. For Save Input To, repeat the previous two steps to select the same field.

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

In expression mode

To build the form in this example in expression mode, you can display and save the related record field value using the value and saveInto parameters of an input component.

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