Record Type Tutorial (Database)

Your enterprise data may be spread across a number of different systems. If your data lives in a relational database, you can easily bring that data into Appian by creating a record type that connects to your database.

In this tutorial, you'll learn how to create a record type that connects to employee data stored in a database table. By the end, you'll have a working record type with a configured record list, Summary view, and common record actions.

Time to complete: About 30 minutes

Experience level: Beginner

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Tutorial prerequisites

To complete this tutorial, you need:

• An Appian environment: An Appian Cloud or Appian Community Edition environment.
• A supported database: This tutorial uses codeless data modeling, which requires a MariaDB, MySQL, Oracle, SQL Server, PostgreSQL, or Aurora MySQL database. If you're using Appian Community Edition, the environment provides a MariaDB database.
• Appian Tutorial application: Create the Application Building Tutorial to store the objects created in this tutorial.
• AT Managers group: Create a managers group to assign permissions to different objects in this tutorial.

Create the Appian Tutorial application

To begin, we need to create an application to contain our design objects.

We will be creating the Appian Tutorial application for this tutorial. All of Appian's tutorials use the Appian Tutorial application as the reference application. After completing this tutorial, you can reuse this application as you work through other Appian tutorials.

To create the Appian Tutorial application:

1. Log in to Appian Designer.
2. In the Applications view, click + New Application.

3. In the Create New Application dialog, configure the following properties:

   Property	Description
   Name	Enter Appian Tutorial.
   Prefix	Keep the default prefix, AT, which Appian constructs using the initial characters of each word you entered in the Name parameter. We'll be following the recommended naming standard, and using this short, unique prefix whenever we name an object in this application.
   Description	Enter Application used to complete all tutorials from the Appian documentation at docs.appian.com.
   Generate groups and folders to secure and organize objects	Keep this checkbox selected, so that Appian will automatically generate standard groups and folders and assign default security groups for this application.

4. Click CREATE.

5. In the Review Application Security dialog, keep the default security settings. Because we selected the Generate groups and folders option in the previous step, Appian automatically uses the AT Users and AT Administrator groups it generated to set our application security appropriately.

   Note:  The security of the application object is unrelated to the security of each of the objects contained within the application. You will need to set security permissions for every object in an application in addition to the application object itself.

6. Click SAVE.

   

7. Click X to close the What do you want to build first? dialog.

Right now, the application contains the folders and groups Appian generated automatically. To see these, click Build in the navigation pane.

Each design object that you create during the course of this tutorial will appear in this list in the Build view and be associated with the tutorial application.

Because we selected the Generate groups and folders to secure and organize objects option while creating the object, Appian generates standard groups and folders for the application, as well as assigns the generated groups as the default security groups for the application.

In this tutorial, we'll be using the following generated groups and folders:

• AT Administrators group to contain all administrators of the Appian Tutorial application.
• AT Users group to contain all users of the Appian Tutorial application.
• AT Rules & Constants folder to contain all our expression rules, constants, and interfaces in this folder throughout the tutorial.
• AT Process Models folder to contain all our process models throughout the tutorial.

For each object we create in this tutorial, Appian pre-populates the AT Administrators and AT Users groups in the object role maps. Most of the time, we'll keep those pre-populated values. The rest of the time, we'll assign permissions to an application-specific managers group, which we'll create now.

Create a managers group

Managers will have a specific part to play in our new application, so let's add a new group for AT Managers.

To create a managers group:

1. In the application, go to the Build view.
2. Click NEW > Group.

3. Configure the following properties:

   Property	Description
   Name	Enter: AT Managers
   Parent Group	Enter AT Users, so that users in this group inherit Viewer permissions for the application.
   Group Members	Enter your user name.

4. Click CREATE.
5. In the Review Group Security dialog, keep the default settings. AT Administrators group should be assigned Administrator permissions for the group.
6. Click SAVE.

Create the AT Employee record type

First, we'll create a record type object to manage employee data, then configure its data source and populate it with sample records.

To create the record type object:

1. In the Build view of the Appian Tutorial application, click NEW > Record type.

2. Configure the following properties:

   Property	Description
   Name	Keep the application prefix that prepopulates this property, and add Employee, so that the name is <prefix> Employee.
   Display Name (Plural)	Keep the generated value of Employees.
   Description	(Optional) Enter Manages employee data.

3. Click CREATE.
4. In the Review Record Type Security dialog, keep the default settings.
   • AT Users group is assigned Viewer permission.
   • AT Administrators group is assigned Administrator permission.
5. Click SAVE.

Generate a database table for the record type

With the record type created, we need to configure its source. We'll generate a database table with the necessary fields so we can connect it to the record type.

Since we're generating a database table, the record type will use optimized data access by default, giving us full access to all data fabric features.

To generate a database table for the record type:

1. Click TELL US ABOUT YOUR DATA.
2. Select New Data Model.
3. Click NEXT.
4. For Data Source, select a New Data Model.
5. Click NEXT.

6. For Data Source, select any MariaDB, MySQL, Oracle, SQL Server, PostgreSQL, or Aurora MySQL database connected to your Appian environment.

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

7. For Schema, select the schema or catalog where you want to generate the table.
8. Click NEXT.
9. In the row for the id field, click Add Multiple Fields.
10. For Enter a number between 1 and 20, enter 4.
11. Click Add.

12. For each field in the following table, enter the field name in one of the empty fields created in the previous step.

    Name	Type
    firstName	Text
    lastName	Text
    title	Text
    phoneNumber	Text

13. Click New Choice List.
14. Enter the Record Type Name as AT Department.

15. In List Items, paste the expression below.

    1
    2
    3
    4
    5
     Engineering
     Finance
     Sales
     HR
     Professional Services
    

16. Click Create Choice List. A new departmentId field is added to the data model and a new AT Department record type is created.
17. In the list of Commonly Used Fields, click startDate.
18. Remove the following fields:
    • Created By
    • Created On
    • Modified By
    • Modified On
19. Click NEXT.
20. On the Relationships page, there is a department relationship. Click NEXT.
21. On the Review page, keep the Create Table checkbox selected.
22. Click SAVE CHANGES.
23. On the success page, click CLOSE. You can discard the database script created for the record type.

Create sample data for the database table

Once the database table is created, we need to populate it with employee data.

If your environment allows you to generate sample data, go to the Data Preview page of the record type and generate sample data.

If your environment doesn't allow you to generate sample data, complete the Use the Write Records Smart Service Function on an Interface recipe to write employee data to our newly created database table.

Configure the record list

With the record type and its source configured, let's set up the record list so users can see a full list of employees. We'll also test the user filter that Appian generated from the many-to-one department relationship.

Configure record list columns

We'll start by configuring the fields displayed in the record list. We only want to display select fields since this data is meant to be viewed at a glance before users drill into each record.

When looking at a list of employees, we only want to see their name, title, and department. To configure the record list to show only these columns, we'll remove the ID, phone number, and start date columns, and combine the first and last name columns into one.

To remove unnecessary fields:

1. In the record type, go to List.
2. Click EDIT LIST.

3. In the Columns section of the Grid configuration, delete the Id column by hovering over and clicking .

   Note:  We don't need to show the Id column in the record list, because the record field is primarily used by the database to identify records and not used by users.

4. Delete the Phonenumber and Startdate columns in the same way.

Next, we will combine the first and last name columns into one Name column. We will also add sorting to allow users to easily sort data in a column, and set the display value to let the column know which record field to show.

To create the Name column:

1. In the Columns section, delete the Lastname column by hovering over and clicking .
2. In the Columns section, click Firstname.
3. In the Label field, change firstname to Name.
4. In the Sort Field field, remove firstname and select lastname.
5. Under Display Value, click Link.
6. Under Links, click List of Links.
7. Under Links, click Record Link.
8. Hover over Label, then click Edit as Expression .

9. To concatenate the first and last names to show both values in the same field, copy and paste the following expression:

   fv!row[recordType!AT Employee.fields.firstName] & " " & fv!row[recordType!AT Employee.fields.lastName]

10. In the expression, change the record type references to use the record type reference in your environment:
    • Remove the existing record field reference.
    • Enter recordType!, then select your AT Employee record type from the context menu.
    • Enter . after recordType!AT Employee, and select fields from the first context menu and lastname from the second context menu.

    Note:  Record field references are specific to each record type and environment. This means that you need to reference the record fields from your own environment whenever you copy and paste a record type reference from outside the application.

11. Click OK to close the expression editor.

12. Click OK to close the list editor.

    

    Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

13. Click SAVE.

Test generated user filter

The record list columns are configured. Let's verify the generated department user filter works as expected.

To test the generated user filter:

1. In the record type, go to List.
2. Click the link under URL. The record list opens in Tempo with the column configurations already applied, and it automatically displays the new user filter.

3. Select a value from the Department user filter.

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Configure the Summary view

Next, we'll configure the a Summary view. We'll also configure the record header background color and the record title with the employee's name.

Generate a Summary view

We can quickly generate a Summary view to display information for a single record.

To generate a Summary view:

1. Return to the AT Employee record type.
2. In the record type, go to Views.

3. Click GENERATE RECORD VIEW.

   

4. For Select Record Types, keep the Employee record type selected to display all fields in the view. Don't select Department since we don't need all of the lookup table fields in this view.
5. Click NEXT.

6. For Edit Settings, leave the default configurations:

   Property	Value
   View Name	Summary
   Application	Appian Tutorial
   Rule Input Name	employee

7. Click NEXT.

8. Review the objects that Appian will use for your new record view:

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

9. Click GENERATE. Appian generates the new interface.

10. Click CLOSE. Since we generated the Summary view, Appian automatically configured the interface as the Summary view for the record type.

    

    Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

11. Click SAVE.

Configure the record header background

With the Summary view configured, let's set up a record header background. The record header background contains the title, breadcrumbs, and related actions on every record view of your record.

You can opt for no background or you can set an image or color. We are going to configure a light blue background:

1. In the Header section, for Record Header Background, select COLOR.
2. For Color, keep the default Static Color.
3. In the hex code text editor, enter #F3F9FF to select a light blue color.

Notice in the Preview that the header now displays with a light blue background.

Configure the record title

Next, we'll configure the record title so that it shows the employee's first and last name.

To configure the record title:

1. For Record Title, select Expression.

2. In the expression editor, copy and paste the following expression:

   1
    rv!record[recordType!AT Employee.fields.firstName] & " " & rv!record[recordType!AT Employee.fields.lastName]
   

3. In the expression, change the record type references to use the record type reference in your environment.

   Note:  Record field references are specific to each record type and environment. This means that you need to reference the record fields from your own environment whenever you copy and paste a record type reference from outside the application.

4. Click SAVE.
5. To preview the Summary view, go to List in the record type and open the link under URL.

6. From the record list, select an employee to view their Summary view. Notice that the employee's full name appears in the record title.

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Generate record actions

In this last part of the tutorial, we'll generate some common record actions.

You can generate actions to create, update, and delete records. Since we only want managers to have the ability to update and delete employee information, we'll also add security on those related actions.

Generate create, update, and delete actions

To generate the create, update, and delete employee actions:

1. Return to the AT Employee record type.
2. In the record type, go to Actions.
3. Click GENERATE RECORD ACTIONS.

4. Keep the default so all actions are selected.

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

5. Click NEXT.

6. For Selected Record Types, keep the Employee record type selected.

   By default, generated record actions will also include data from any many-to-one relationships. Since the Employee record type has a many-to-one relationship with the Department record type, the create and update actions will included a dropdown component so users can select a department.

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

7. Click NEXT.

8. For Select Template, choose a template from the list. Appian will recommend a template based on the number of record types included in the action.

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

9. Click GENERATE.

Appian generates the actions, creating and configuring the record actions and the objects supporting those actions in your application.

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Add the related actions to the Summary view

With the record actions generated, let's add the Update Employee and Delete Employee actions to the Summary view.

These related actions only appear on the Related Actions view on each record by default, but we'll add related action shortcuts so managers can more quickly update or delete employee information while viewing the summary.

To add the related actions to the Summary view:

1. In the record type, go to Views.
2. Click next to Summary.
3. In the Edit View dialog, under Related Action Shortcuts, select the checkboxes for Update Employee and Delete Employee.
4. Click OK.
5. Click SAVE.

Set security on the related actions

Since we don't want everyone to be able to update or delete an employee's information, we are going to make sure that only those in the manager group are able to view and use these related actions.

To set security on the related actions:

1. In the record type, go to Views and Actions Security.
2. Go to the Actions tab.
3. Click next to the Update Employee action.
4. For Who can see this action, select Only users…
5. Expand the Found in groups option and search for the AT Managers group.
6. Click OK.
7. Click next to the Delete Employee action.
8. For Who can see this action, select Only users…
9. Expand the Found in groups option and search for the AT Managers group.
10. Click OK.
11. Click SAVE.

Now, only managers can update or delete employees.

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Test record actions

In this last step, we'll test out our record actions.

To test the record list action:

1. In the record type, go to List.
2. Open the link under URL.
3. On the record list, click + NEW EMPLOYEE.
4. Enter some information in the form.
5. Click CREATE. The new employee should appear in the record list.

To test a related action:

1. From the record list, select an employee to view their Summary view.

2. Click UPDATE EMPLOYEE.

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

3. Update some information in the Update Employee form.
4. Click SAVE. The Summary view should update to reflect your changes.

Success!

You've created a record type that connects to database and displays employee data. Your AT Employee record type now has:

• A database table with the necessary employee fields.
• A record list with configured columns and a department user filter.
• A Summary view with a styled record header and descriptive record title.
• Record actions to create, update, and delete employees, secured so that only managers can update and delete employees.

Feedback