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 MariaDB, MySQL, Oracle, or SQL Server 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 MODIFY 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 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 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 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.

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. This will be the name of the source field and the record 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

Removing a field will delete it from the record type and the database table.

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. Click SAVE CHANGES.
4. 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 will automatically add a foreign key constraint for your common fields in the generated database script. Your database will then enforce referential integrity as records are modified to ensure your data remains consistent and up-to-date.

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.

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.