Free cookie consent management tool by TermsFeed

Create a Record Type for a Database

On This Page

If your data lives in a relational database, you can create a record type to connect to that database using optimized data access or direct data access. A record type that connects to a database is known as a database-backed record type.

Note:  If you want to connect to a Snowflake database, use Snowflake as the data source instead since you must configure a Snowflake connected system.

To create a database-backed record type:

  1. Create a record type object.
  2. Choose a data access method.
  3. Connect to your database table or view using your preferred data access method.

Create a record type object

Tip:  If you need to create record types for multiple tables and views, you can generate multiple record types instead.

To create a record type:

  1. In your application, go to the Build view.
  2. Click NEW > Record Type.

  3. In Create Record Type, configure the following properties:

    Property Description
    Name The name of the record type that is displayed in the Appian Designer. For example, in the Appian Retail application, you could have AR Order, AR Customer, or AR Support Ticket. Follow the recommended naming standard when creating this name.
    Display Name (Plural) The name of the record type in plural form. For example, Orders, Customers, or Support Tickets. This is the name that business users see in Process HQ, Tempo, and as the search placeholder text in the record list and any records-powered grids.
    Description The description of the record type shown in Process HQ, Tempo, and in Appian Designer.

    Click Generate AI Description to allow Appian AI Copilot to generate a record type description for you.
  4. Click Create.
  5. In the Review Application Security dialog, review the record type security settings.
  6. Click SAVE.

Choose a data access method

After you create the record type object, choose a data access method to connect to your data source. The data access method determines the type of record type you create and the features available to you.

Data access method Record type created Supported databases Data fabric features
Optimized data access Synced record type All supported databases All features
Direct data access (features enabled) Unsynced record type MariaDB, MySQL, PostgreSQL Most features
Direct data access (features disabled) Legacy record type All supported databases Basic filtering and security only

Appian recommends using optimized data access whenever possible. If you cannot sync your data, use direct data access instead.

For a detailed comparison of each method, see Data Access for Record Types.

Connect to a database

After choosing a data access method, connect the record type to your database. Follow the steps for your chosen method.

Use optimized data access

Connecting to a database with optimized data access creates a synced record type. Appian syncs your source data in the record type, which enables faster queries and access to all data fabric features.

You can use optimized data access to connect your record type to a database table or view from any supported database.

To connect a record type to a database using optimized data access:

  1. In your new record type, go to Data Model.
  2. Click Tell Us About Your Data.

  3. Select Database.

    images/db-selection.png

    Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to closeimages/db-selection.png

  4. Click NEXT.

  5. For Choose how to access data, keep the default selection Optimized Data Access.

    images/data-access-methods.png

    Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to closeimages/data-access-methods.png

  6. Click NEXT.
  7. For Choose Database Table or View, keep Browse selected to see a list of available databases.

  8. Select a database.

    If your environment contains a single relational database, that database is selected and cannot be unselected.

  9. Select a schema or data catalog from the database.

    Note:  If a schema or catalog is missing, check the permissions for the account used in your data source connected system. System and internal schemas aren't supported and won't appear in the list.

  10. (Optional) If you want to limit the list to the default schema defined in the data source, then select the Show only the default schema checkbox.

    Tip:  Selecting a table or view from the default schema prevents connection issues when deploying the record type to an environment where the schema name is different. If the schema names differ between environments, a schema mismatch error occurs.

  11. Select a database table or view. For Oracle databases, you can select a table synonym.

    A preview of the data appears.

  12. Click NEXT.
  13. For Configure Sync Filters, configure the following sync options and filters:
  14. Click NEXT.
  15. (Database view only) For Select primary key field and unique fields, configure the following:
    • Primary Key Field: From the dropdown, select a field of any data type to use as the primary key. A primary key field is required.
    • Unique Fields: (Optional) Select any fields that contain all unique values. Unique fields can be used as a common field on the one-side of a record type relationship configuration.

    Note:  For database tables, Appian automatically designates the primary key and unique fields based on constraints defined in the source table. You won't see this configuration step.

  16. In the grid of fields, clear the checkbox next to any source fields you don't want to include in the record type. By default, all source fields are selected. A record type can have up to 100 fields.

    Note:  If your table includes a composite primary key, you must include all fields that make up the key. Some database column types are not supported and do not appear in the list of available fields. For details, see Unsupported Database Column Types.

  17. Configure the following field properties:

    Property

    Action
    Record Field Name Enter the name for the record field. This is the name that will be used to reference the field in Appian Designer.
    Record Field Type Use the dropdown to select the data types for the record fields. Appian automatically suggests data types based on the data type in the source.

    Appian recommends you make the following changes:
    • For fields containing usernames, change the type from Text to User.
    • For fields containing group identifiers, change the type from Number (Integer) to Group.
    • For fields containing more than 4,000 characters, change the type from Text to Extra Long Text. Extra Long Text fields can store up to 64,000 characters. You can only have a maximum of 3 Extra Long Text fields on a record type for optimal performance. If your field exceeds 64,000 characters, use it in an unsynced record type instead.
    Display Name (Optional) Enter a user-friendly field name to display to business users in Process HQ. Use title case formatting and spaces in the display name so it's easy for users to read. For example, First Name.

    When this field is blank, the Record Field Name is displayed in Process HQ using title case formatting.
    Description (Optional) Enter a description of the field to display to business users in Process HQ.
  18. Drag and drop the rows to change the order in which the record fields are displayed.
  19. Click FINISH.
  20. Click SAVE to sync your data.
  21. (Optional) If your record type has a single primary key, then you can update the primary key field to use a sequence to generate primary key values.

Use direct data access (features enabled)

Connecting to a MariaDB, MySQL, or PostgreSQL database with direct data access creates an unsynced record type. Appian queries the source data directly while still giving you access to data fabric features like relationships and record-level security.

If you need direct access to a database other than MariaDB, MySQL, or PostgreSQL, you must disable relationships and related features. Connecting to a database using direct data access with features disabled creates a legacy record type instead.

To connect a record type to a database using direct data access with features enabled:

  1. In your new record type, go to Data Model.
  2. Click Tell Us About Your Data.

  3. Select Database.

    images/db-selection.png

    Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to closeimages/db-selection.png

  4. Click NEXT.
  5. For Choose how to access data, select Direct Data Access.

  6. Keep the Disable relationships and all related features checkbox unselected.

    images/data-access-methods-features-enabled.png

    Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to closeimages/data-access-methods-features-enabled.png

  7. Click NEXT.
  8. For Choose Database Table or View, keep Browse selected to see a list of available databases.

  9. Select a MariaDB, MySQL, or PostgreSQL database.

    If your environment contains a single relational database, that database is selected and cannot be unselected.

  10. Select a schema or data catalog from the database.


    Note:  If a schema or catalog is missing, check the permissions for the account used in your data source connected system. System and internal schemas aren't supported.

  11. (Optional) If you want to limit the list to the default schema defined in the data source, then select the Show only the default schema checkbox.

    Tip:  Selecting a table or view from the default schema prevents connection issues when deploying the record type to an environment where the schema name is different. If the schema names differ between environments, a schema mismatch error occurs.

  12. Select a database table or view.

    A preview of the data appears.

  13. Click NEXT.
  14. (Database view only) For Select primary key field and unique fields, configure the following:
    • Primary Key Field: From the dropdown, select a field of any data type to use as the primary key. A primary key field is required.
    • Unique Fields: (Optional) Select any fields that contain all unique values. Unique fields can be used as a common field on the one-side of a record type relationship configuration.

    Note:  For database tables, Appian automatically designates the primary key and unique fields based on constraints defined in the source table. You won't see this configuration step.

  15. In the grid of fields, clear the checkbox next to any source fields you don't want to include in the record type. By default, all source fields are selected. A record type can have up to 100 fields.

    Note:  If your table includes a composite primary key, you must include all fields that make up the key. Some database column types are not supported and do not appear in the list of available fields. For details, see Unsupported Database Column Types.

  16. Configure the following field properties:

    Property

    Action
    Record Field Name Enter the name for the record field. This is the name that will be used to reference the field in Appian Designer.
    Record Field Type Use the dropdown to select the data types for the record fields. Appian automatically suggests data types based on the data type in the source.

    Appian recommends you make the following changes:
    • For fields containing usernames, change the type from Text to User.
    • For fields containing group identifiers, change the type from Number (Integer) to Group.
    • For fields containing more than 4,000 characters, change the type from Text to Extra Long Text. Extra Long Text fields can store up to 64,000 characters. You can only have a maximum of 3 Extra Long Text fields on a record type for optimal performance. If your field exceeds 64,000 characters, use it in an unsynced record type instead.
    Display Name (Optional) Enter a user-friendly field name to display to business users in Process HQ. Use title case formatting and spaces in the display name so it's easy for users to read. For example, First Name.

    When this field is blank, the Record Field Name is displayed in Process HQ using title case formatting.
    Description (Optional) Enter a description of the field to display to business users in Process HQ.
  17. Drag and drop the rows to change the order in which the record fields are displayed.
  18. Click FINISH.
  19. Click SAVE.

Use direct data access (features disabled)

Connecting to a database other than MariaDB, MySQL, or PostgreSQL with direct data access creates a legacy record type. Appian queries the source data directly, but relationships and related features are not available. Legacy record types require a custom data type (CDT) and a data store entity (DSE) to define the connection.

You can connect a legacy record type to any database table or view from any supported database.

To connect a record type to a database using direct data access with features disabled:

  1. Create a custom data type (CDT) that reflects the fields in your database table or views. The CDT must contain a primary key.
  2. Create a data store entity (DSE) to specify the database and entity to connect to.
  3. In your new record type, go to Data Model.
  4. Click Tell Us About Your Data.

  5. Select Database.

    images/db-selection.png

    Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to closeimages/db-selection.png

  6. Click NEXT.
  7. For Choose how to access data, select Direct Data Access.

  8. Select the Disable relationships and all related features checkbox.

    images/data-access-methods-features-disabled.png

    Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to closeimages/data-access-methods-features-disabled.png

  9. Click NEXT.
  10. For Choose Data Store and Entity, select a data store.

  11. Select an entity from the selected data store.

    A preview of the data appears.

  12. Click FINISH.
  13. Click SAVE.

Feedback