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

   

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 field

To add a single field to the data structure:

1. Identify the row after which you want to add the field.
2. In that row, click . The system adds a blank row immediately after the current row.
3. Configure the properties for the field you added.

Add multiple fields

To add multiple fields:

1. Identify the field after which you want to add multiple fields.
2. In that row, click .
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:

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

Reorder fields

To reorder fields in the data structure:

• Click to move a field up.
• Click 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.

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:

   

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.

You can use the database script to:

• Update the new table directly in the database if you prevented the table update earlier.
• Deploy any application that uses the record type to another environment. To do this, you can include the script in a deployment package or run the script in the target environment prior to a manual deployment.

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.