Update a Source from Your Record Type

This page describes how to update a database table at the same time that you update a record type.

Overview

Updating relational database tables usually involves specialized knowledge of SQL commands and best data practices. With codeless data modeling in Appian, however, it's not just database administrators who can configure database tables. In this streamlined process, any developer can update database tables while updating a record type.

You can use a guided experience to update a table in any MariaDB databases connected to your Appian environment. If you generated a source for your record type, you'll find this experience familiar.

To take full advantage of codeless data modeling, you'll need permissions to update the table in your selected database. If you don't have those permissions, you can still use the guided experience to generate a database script, which you'll then need to run in the database yourself.

To update a database table from a record type:

  1. Start the guided experience.
  2. Modify the data structure.
  3. Modify record type relationships.
  4. (Optional) Prevent table update.
  5. Save your changes.
  6. (Optional) Download the auto-generated database script.
  7. Click FINISH.

Start the guided experience

To start the guided experience:

  1. In the record type, go to Data Model.
  2. Click ADD SOURCE FIELDS. This option is only available if the latest full sync for the record type was successful.

    add-source-fields

Modify the data structure

By default, the Fields tab is active. In this tab, you can modify the fields in your record type.

Add a choice list field

A choice list field allows you to create reference data that represents a list of static values (for example, the status fields "Not Started", "In Progress" and "Done"). You can then allow your end users to select from this list of choices in your interfaces or reports.

When you create a choice list field, Appian creates a related record type to store the static values you define, and adds a common field to the current record type that links to the new related record type.

To add a new choice list field:

  1. Click Down NEW CHOICE LIST.
  2. Configure the following properties:

    Property Description
    Record Type Name The name of the related record type that will be generated to store your reference data. This is populated by default with your application prefix.
    List Items The static values that end users can select from a list. Enter one item per line without any comma separators. Do not wrap individual values in quotation marks unless you want the quotation marks to be included in the value.
    Preview database script A preview of the SQL script that will be generated once you save your changes. You can download this script later in this process.
  3. Click CREATE CHOICE LIST. A new field is created to establish a relationship to your new related record type.

Add a field

To add a single field to the data structure:

  1. Click Down NEW FIELD.
  2. Configure the properties for the field you added.

The system adds the new field as follows:

  • If you've retained the default fields in default order, the system inserts the new field before the createdBy field.
  • If you've changed the order or modified the default fields, the system inserts the new field as the last field of the table.

To add a new field below a specific existing field, click Down next to the existing field.

Add multiple fields

To add multiple fields:

  1. Identify the field after which you want to add multiple fields.
  2. In that row, click Add Multiple Fields.
  3. In the Add Multiple Fields dialog, enter a number between 1 and 20.
  4. Click ADD. The system adds the specified number of blank fields immediately after that field.
  5. Configure the properties for each field you added.

Configure field properties

For each field in the data structure, you can configure the following field properties:

blank row of field properties from the Create Data Model dialog

# Description
1 In the first box, enter a name for the field.
2 In the dropdown list, select a primitive data type appropriate to the field.
3 If you selected the Text data type and anticipate that the field will need to contain data longer than 255 characters, select the Long Text checkbox.

To reduce the likelihood of exceeding limits that MariaDB enforces on row data, we recommend adding no more than two Long Text fields to a record type. If you configure more than two Long Text fields, a warning displays in the field configuration dialog. You'll see a "row size too large" error if you attempt to run the generated script in the database.

Reorder fields

To reorder fields in the data structure:

  • Click Up to move a field up.
  • Click Down to move a field down.

Remove a field

To remove a field:

  1. Click in the field row. The Remove <name> field? dialog displays

    This dialog lists any objects that rely on that field. For example, the list could include:

    • A record type object if you're removing a common field that enforces a relationship to another record type.
    • An interface object if the interface references the field you're removing.
  2. Click DELETE ANYWAY to remove the field.
  3. Update the affected objects. For example, you might then remove the record type relationship or remove the field reference in the interface object. To quickly identify objects that might contain broken references, you can view the Health Dashboard.

Modify record type relationships

Record type relationships allow you to reference related data from your record type, which makes development faster and easier.

You can modify relationships elsewhere in the data model configuration, but modifying a relationship here has many benefits. A relationship requires that your record type contains a common field that's used in both your record type and the related record type. If you modify a relationship at this point, Appian can update that common field in the source database, so the relationship is represented accurately in both the source and the record type. Also, if you're adding a relationship, Appian can automatically generate a common field for you based on the relationship type you select.

If you added a choice list field, the relationship to your new related record type is already configured.

In the Relationships tab, you can add or remove record type relationships.

Add a relationship

To add a record type relationship:

  1. Go to the Relationships tab.
  2. Click NEW RELATIONSHIP.
  3. Search for and select a record type in the Record Type Relationship field.
  4. Click NEXT. The Add Relationship to <Record Type> dialog displays. For example:

    Add Relationship to Project dialog

  5. Under Relationship Name, enter a name for the relationship. This is how you’ll reference the relationship to access the related record fields.
  6. Under Relationship Type, select One-to-Many, Many-to-One, or One-to-One. By default, Many-to-One is selected. Learn more about supported relationship types.
  7. Under Common Fields, choose the fields to enforce the relationship type. The common fields can be of type Text, Number (Integer), User, or Group, and the common fields must be the same data type.

    In this dialog, the common fields default to the following settings, based on the relationship type:

    Relationship Type Common Field in Current Record Type Common Field in Related Record Type
    One-to-Many The primary key field of the current record type. No default setting. Select a field of the same data type as the primary key field of the current record type.
    Many-to-One An auto-generated field that matches the primary key field of the related record. For example, see employeeId in the example above. The primary key field of the related record type.
    One-to-One An auto-generated field that matches the primary key field of the related record. The primary key field of the related record type.

    You can change the names of auto-generated fields and select different common fields for both the current and related record types. However, you can only select fields that meet the criteria for the relationship type. For example, the common field in the one-side record type of a one-to-many relationship must be the primary key field.

  8. Click ADD.

Remove a relationship

To remove a relationship:

  1. Go to the Relationships tab.
  2. Click in the relationship row. The Remove <name> relationship? dialog displays. This dialog lists the objects that rely on the relationship to function.
  3. Click DELETE ANYWAY to confirm the relationship removal.
  4. Manually remove the common field that enforced the relationship.

Prevent table update (Optional)

By default, the system will use the auto-generated database script to update the table in the source database. You'll just need to keep the Update Table option selected.

However, under certain conditions, you might want to prevent Appian from updating the table. For example, you might need to make modifications to the script like editing the field names to meet database administration requirements or adding foreign keys and constraints.

In that case, you can prevent the system from updating the table, while still taking advantage of the guided experience to generate the basic script.

To prevent the table update, unselect the Update Table checkbox.

Save your changes

Click SAVE CHANGES to save your changes to the record type.

If the Update Table checkbox is selected, Appian will save the data model for the record type and update the table in the database.

If the Update Table checkbox is unselected, Appian will save the data model for your record type, but makes no changes to the database itself. Because the fields you added to the record type do not yet exist in the database, the sync will fail for your record type. As a result, error indicators will display in the record type until you update the table in the database and trigger a full sync.

Download the database script (Optional)

After Appian successfully saves the changes to your record type, you have only one step left: deciding whether to download the auto-generated database script.

Record Type Updated Successfully message

You can use the database script to:

By default, the Download database script is selected, and the system will download the script when you click FINISH.

If you do not want to download the script, unselect the Download database script checkbox before clicking FINISH.

When you unselect this checkbox, a banner will appear asking if you want to save your download preferences. You can choose to skip the database script download each time you update to the source, or return to the default download setting so the script is downloaded whenever you update the source.

The auto-generated database script is not saved in Appian, so we strongly recommend downloading the script now and storing it locally.

Open in Github Built: Fri, Feb 03, 2023 (08:17:45 PM)

On This Page

FEEDBACK