Add Record Type Relationships

This page explains what record type relationships are and how to take advantage of them.

Overview

It’s not often that you have one record type to rule them all. Instead, you usually have several record types that work together to create a comprehensive view of your enterprise data. For example, if you work at a Customer Support Center, you may have a Support Case record type, and a separate record type for Customers who submit cases.

Since there is a business relationship between these data sets, you'll need to query data from each record type to know which cases are associated with each customer, what support level each customer pays for, and more. To make working with this data easier, data experts may create database views to relate the data so developers can build their applications more quickly and simply.

When you sync your data in Appian, you don't need a data expert to relate your data—you can build the relationships yourself. Using record type relationships, you can use a low-code experience to relate your record data so you can start building your applications faster.

About relationships

Record type relationships provide a fast and easy way to connect and reference related data. Just as you can reference a record type’s fields, filters, and actions easily throughout your applications, you can reference its related record data by defining relationships with other record types.

Relationships can be established between record types with data sync enabled. This means you can go beyond the traditional relationships established in a database—you can establish relationships between any record types that have sync enabled, regardless of the source. So if your enterprise data lives in different database tables and in Salesforce, you can unify this data in Appian without making copies of your data, or migrating your data to have the same source type.

relationship-sources

Connecting your enterprise data is easy since relationships are defined directly on the record type. Simply choose which record type to establish a relationship with, and then define the relationship name, the relationship type, and the two common fields shared between the record types that will enforce the relationship type.

For example, the Support Case record type contains detailed information about each submitted support case, but it would benefit from having more information about the customer associated with each case. From the Support Case record type, you could create a many-to-one relationship with the Customer record type based on the customerId field in the Support Case record type, and the id field in the Customer record type.

Not only does this make it easy to connect your enterprise data, it makes development more flexible. Rather than having to set up relationships early in a database view, then waiting for change requests to be processed by your data expert, you can add record type relationships at any point in development without affecting other existing relationships. This lets you work in a more contemporary, agile way.

Once you define a relationship, you are effectively creating a link from the record type to the related record type. This link is part of the record type's data model, and it allows the record type to access the record fields and relationships defined on the related record type.

This means that the Support Case record type can now reference the fields and relationships on the Customer record type. Using a simple relationship reference, the Support Case record type can display the customer name associated with each case, and even create a user filter to filter the list of support cases by customer name.

The Support Case record type can also index into the relationships on the Customer record type to leverage additional related data. So if the Customer record type has a relationship with the Region record type, you can reference the relationship on the Customer record type and select the label field from the Region record type to display the number of open support tickets in each region.

/relationship-report

By adding multiple relationships, you can use a single record type to quickly build advanced records and reports that display a holistic version of your enterprise data.

Supported relationship types

Record type relationships currently support the following relationship types:

  • One-to-one relationships: Each record in the record type relates to exactly one record in the related record type.

    For example, you need to display the phone number and address associated with each employee on the Employee record list, but that information lives in a separate record type called Employee Contact Information. Since each employee has their own contact information, you could create a one-to-one relationship on the Employee record type so you can easily reference the phoneNumber and address fields from the Employee Contact Information record type on the Employee record list.

  • Many-to-one relationships: Many records in the record type are related to one record in the related record type.

    For example, you want to create a pie chart that shows the number of customers per industry, and this information lives in two separate record types: the Customer record type, and the Industry record type. Since many customers belong to the same industry, you can add a many-to-one relationship on the Customer record type so you can reference the industryLabel field on the Industry record type to group the number of customers by industry.

When defining either type of relationship, you will need to use a unique value as the common field on the one-side of the relationship. In a one-to-one relationship, both common fields must be unique. The unique value should either be a primary key field or a field with a uniqueness constraint.

For example, in a many-to-one relationship between customers and industries, you could use the industryId field from the Customer record type and the primary key field (id) from the Industry record type as the two common fields. 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.

/customer-industry-commonfields

As Appian Records continues to grow, additional relationship types will be supported. To leverage one-to-many or many-to-many relationships at this time, you should continue using existing methods to relate that data.

Referential integrity

When data experts relate data by joining database tables into a database view, they rely on the referential integrity of foreign keys: that is, a foreign key will only refer to a valid row in another table, or it will be null. This means that when rows of data are deleted, this integrity can be maintained by also deleting the rows in other tables that refer to the deleted rows, or by failing the delete operation.

When you relate your data using record type relationships, Appian leverages your synced data. This means that the data in these relationships reflect the data and rules enforced by your source. As a result, whenever you add, update, or remove data from the source of your record type, the changes are written directly to your source and then synced in Appian, which means that record type relationships do not inherently enforce referential integrity; instead, any referential integrity enforced on your source data will be reflected in Appian when it's synced.

For example, let's say that the Customer and Order record types are related, and in the database tables that they use, the Order table has a foreign key to Customer which enforces referential integrity.

If you use a process model to delete a Customer from the database table, any Orders referencing that Customer would have an invalid foreign key. Therefore, the database will either cause the delete operation to fail and nothing will be deleted, or it will delete both the Customer and all Orders associated with that Customer. If the delete operation fails, no sync is needed because data has not changed. If it succeeds, Appian will immediately sync the deletions from both record types.

Now, let's say that the Customer record type also has a relationship with the Industry record type, and in this case it's the Customer database table that holds a foreign key to the Industry table, since Industry is just reference information.

When you delete a Customer, there's no need to affect the Industry table because the foreign key itself is being deleted and there's no risk to referential integrity. In this case, Appian will immediately sync the change to the Customer record, but no immediate sync will occur on the Industry record type.

If you enforce referential integrity on the sources of your related record types, it's recommended to keep these record types on a similar sync schedule. This way, you can be sure that your foreign key relationships are consistently reflected in the synced data, and any changes to the data as a result of referential integrity are synced at the same time.

If you create a relationship between record types with different sources, referential integrity cannot be enforced by any one system.

Add relationships

You can add one or more relationships to any record type that has data sync enabled. The relationship can be with another record type with data sync enabled, or with itself.

For example, if your Employee record type has a supervisorId and an employeeId, you could create a many-to-one relationship on the Employee record type using those two fields as the common fields. Then you can use the relationship to reference the supervisor's first and last name instead of displaying the supervisorId.

When you add a relationship, note that the relationship will only exist on the record type that defines the relationship. If you want to establish the inverse relationship on the related record type, you will need to manually create that relationship on that record type.

Before adding a relationship, ensure the record types have a supported relationship type.

To add a record type relationship:

  1. In a record type with data sync enabled, go to the Data Model page.
  2. Click ADD RELATIONSHIP.
  3. Enter the name of the record type you want to establish a relationship with.
  4. In Relationship Name, enter a name for the relationship. This is how you’ll reference the relationship to access the related record fields.
  5. Under Relationship Type, select Many-to-One or One-to-One.
  6. Under Common Fields, choose the fields to enforce the relationship type. The common fields must be the same data type.

    You must use the primary key or other unique field on the one-side of any relationship type. For example, you could use the customerId field in a Support Case record type and the Id field (the primary key) of the Customer record type as the common fields.

  7. Click OK.

All relationships defined on the record type will appear in the RELATIONSHIPS section of the Data Model page and in a record type relationship diagram.

  • To edit a relationship, click the relationship name.
  • To remove a relationship from the record type, click Remove Relationship . If you remove a relationship, any references to the relationship will break.
  • To view all relationships accessible from your record type, select the Show relationships from related record types checkbox under the relationship diagram. Learn how to reference relationships.

relationship-diagram

Once you configure a record type relationship, you can reference related record fields as easily as any other record fields. Using Design Mode or simple relationship references in Expression Mode, you can quickly build advanced record views, lists, reports, or queries using related record data.

When you reference related record fields, Appian automatically enforces the record type object security and any default filters configured on the related record type. This means you don't need to add any additional configurations to your queries to filter out sensitive or conditionally available information. Learn more about record type security.

Design mode

In Design Mode, you can easily reference related record fields when configuring your record list, or when creating a read-only grid or chart that uses a record type as the source.

When you select a field to configure the component, you'll see your relationship names appear in the list of available record fields. By hovering over the relationship, you can select a related record field, or index into any relationships defined on the related record type to select a record field from a different record type.

For example, say you have a read-only grid that displays order information and the customerId associated with each order. But, instead of the customerId field, you want to display the customer name. In the grid column, you can change the Sort Field and the Display Field by hovering over the customer relationship and selecting the name field from the Customer record type. The field will appear appended to the relationship name. In this example, the field appears as customer.name.

Now, let's say you want to create a report that shows the number of orders per customer region. If the Customer record type has a relationship with the Region record type, then the Order record type can index into the relationship on the Customer record type and select a field from the Region record type.

Expression mode

To reference a related record field in an expression, you'll use the recordType! domain followed by . dot notation and the relationships property.

Simply append the relationships property to the record type object reference to autosuggest a list of available relationships. From the relationship, you can select a related record field or index into a relationship on the related record type to select a record field from a different record type.

For example, while configuring the record list on the Order record type, you decide you want to add the name field from the Customer record type to your list. You also want users to easily view more information about each customer, so you want to add a link to the Customer record type.

From the record list, you can create a new column called Customer. In the Display Value, you can enter an expression that will display the customer name associated with each order, and link to the specific customer record on the Customer record type.

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

Like in Design Mode, you can also reference nested relationships by indexing into the relationships property on a related record type.

For example, if you wanted to display the customer industry on your record list, and the Customer record type has a relationship with the Industry record type, you can create a new column called Industry and use the following reference: fv!row[recordType!Order.relationships.customer.relationships.industry.fields.label].

Learn more about referencing records in expressions.

Open in Github Built: Mon, Nov 15, 2021 (03:03:53 PM)

On This Page

FEEDBACK