Record Type Tutorial (Snowflake)

If your enterprise data lives in Snowflake, you can query it directly from Appian without moving or copying it. Simply connect a record type to your Snowflake table to display, filter, and interact with your data in real time.

In this tutorial, you'll learn how to create a record type that connects to order data stored in Snowflake. By the end, you'll have a working record type with a configured record list, user filter, and Summary view.

Time to complete: About 30 minutes

Experience level: Intermediate

Tutorial prerequisites

To complete this tutorial, you need:

• An Appian environment: An Appian Cloud or Appian Community Edition environment.
• Appian Tutorial application: Create the Appian Tutorial application to store the objects created in this tutorial.
• A Snowflake account: Use your own Snowflake account or create a Snowflake trial account, which provides a 30-day free trial.

• The Snowflake sample database: The SNOWFLAKE_SAMPLE_DATA database, which is provided as a sample data set in your Snowflake account. If you do not see the database, you can create it yourself.

  Note:  The Snowflake sample data set is read-only, so this tutorial focuses on displaying Snowflake data instead of writing it. To write to Snowflake, you must use a writable Snowflake database.

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 and assigns the generated groups as the default security groups.

In this tutorial, we'll use 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 expression rules, constants, and interfaces.

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.

Create a Snowflake connected system

First, we'll connect Appian to the Snowflake database by creating a connected system. The connected system stores the connection details so your record type can query data from Snowflake.

To create a Snowflake connected system:

1. In your application, go to the Build view.
2. Click NEW > Connected System. The Create Connected System dialog appears.
3. Click Snowflake Data Source.

4. Configure the following connected system properties:

   Property	Action
   Name	Enter AT Snowflake.
   Description	Enter Snowflake connection for the Appian Tutorial application.
   System Logo	Leave blank. By default, the Snowflake logo appears.

5. In the Snowflake Data Source Configuration section, configure the following properties:

   Property	Action
   Account URL	Enter your Snowflake connection URL using the following format: youraccount.snowflakecomputing.com
   Warehouse	Leave blank.
   Database	Enter SNOWFLAKE_SAMPLE_DATA.
   Role	Enter ACCOUNTADMIN.
   Authentication	Keep the default Programmatic Access Token selected.
   Username	Enter the username for your Snowflake trial account.
   Password	Enter the password for your Snowflake trial account.

6. Click TEST CONNECTION to verify your configuration.
7. Click CREATE.
8. In the Review Group Security dialog, keep the default settings:
   • AT Users group is assigned Viewer permission.
   • AT Administrators group is assigned Administrator permission.
9. Click SAVE.

The connected system is created and ready to use as a data source for a record type.

Create the AT Order record type

Now that we have a connected system, we'll create the record type object that will query and display the Snowflake data.

To create the record type object:

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

3. Configure the following record type properties:

   Property	Action
   Name	Keep the application prefix that prepopulates this property, and add Order, so the name is AT Order.
   Display Name (Plural)	Keep the generated value of Orders.
   Description	Enter Displays Snowflake order data.

4. Click CREATE.
5. 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.
6. Click SAVE.

The AT Order record type opens in a new tab.

Configure Snowflake as the record type source

Now that we have our record type, we'll connect it to a Snowflake table using the connected system we created. Since the sample Snowflake database is optimized for performance, we'll use direct data access to query the data. Then, we'll rename the fields so they use camel case formatting.

To configure a Snowflake table as the source of the record type:

1. In the Data Model page, click Tell Us About Your Data.

2. Select Snowflake.

   

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

3. Click NEXT.
4. For Choose how to access data, select Direct Data Access. Relationships and all related features are enabled by default.
5. Click NEXT.
6. For the schema, select TPCH_SF1.
7. For the table, select ORDERS.
8. Click NEXT.
9. For Primary Key Field, select O_ORDERKEY.
10. Leave Unique Fields blank.

11. Edit the Record Field Name for each field:

    Original Field Name	New Field Name
    oOrderkey	orderId
    oCustkey	customerId
    oOrderstatus	orderStatus
    oTotalprice	totalPrice
    oOrderdate	orderDate
    oOrderpriority	orderPriority
    oClerk	clerk
    oShippriority	shipPriority
    oComment	comment

1. Edit the Display Name for each field to match the new Record Field Name.
2. Click FINISH.
3. Click SAVE.

The data model page displays the configured fields from the Snowflake ORDERS table.

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

Configure the record list

Now that we have our data model configured, let's configure the record list so users can see a list of orders at a glance.

By default, the grid shows all record fields, but we only want to display the most useful columns for browsing orders. We'll also clean up some of the sample data so it's easier to read in the grid.

To configure the record list columns:

1. In the record type, go to List.
2. Click EDIT LIST.
3. In the Grid configuration pane, go to the Data tab.
4. In the Columns section, delete the following columns by hovering over and clicking :
   • Customer ID
   • Ship Priority
   • Comment
5. In the Columns section, reorder the following columns by hovering over and clicking . The columns should be in the following order:
   • Order ID
   • Order Date
   • Order Status
   • Order Priority
   • Total Price
   • Clerk
6. Click the Order Status column and go to the Data tab.
7. Hover over Display Value, then click Edit as Expression .

8. Copy and paste the following in the expression editor:

   1
   2
   3
   4
   5
    if(
      fv!row[recordType!AT Order.fields.orderStatus] = "F",
      "Closed",
      "Open"
     )
   

9. Replace the AT Order.fields.orderStatus reference with the field reference in your environment.

   Note:  Record type references are specific to each environment. You must reference the record fields from your own environment whenever you copy and paste a record type reference from outside the application.

10. Click OK to close the expression editor.
11. Click Grid to return to the grid configuration.
12. Go to the Data tab.
13. Under Initial Sorts, click ADD SORT.
14. Click Sort Info and configure the following sort information:
    • For Field, select orderStatus.
    • For Order, keep Descending selected so all open orders appear first by default.
15. Click OK to close the list editor.
16. Click SAVE.

Add a user filter

User filters let users narrow down the record list. We'll add a filter on the order status field so users can find orders that are open or closed.

To add a user filter:

1. In the record type, go to Filters.
2. In the User Filters section, click New User Filter.
3. Keep Guided Configuration selected.

4. In the Filter Configurations section, configure the following properties:

   Property	Action
   Name	Enter Order Status.
   Label	Enter "Status".
   Field	Select orderStatus.
   Type	Keep List selected.
   Visibility	Keep Always selected.

5. In the List Configurations section, click New Option.

6. In the Create New Filter Option dialog, configure the following properties:

   Property	Action
   Option Label	Enter "Open".
   Operator	Keep as =.
   Value	Enter "O".

7. Click SAVE FILTER OPTION.
8. Click New Option again.

9. In the Create New Filter Option dialog, configure the following properties:

   Property	Action
   Option Label	Enter "Closed".
   Operator	Keep as =.
   Value	Enter "F".

10. Click OK.
11. Click SAVE.
12. To test the record list and its user filter, go to List in the record type and open the link under URL.

The record list opens in Tempo with the column configurations applied and displays the new user filter.

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

Configure the Summary view

Next, we'll generate a Summary view for the AT Order record type. The Summary view displays detailed information for a single order record, including a styled record header and descriptive record title.

Generate the Summary view

To generate the Summary view:

1. Return to the AT Order record type.
2. In the record type, go to Views.
3. Click GENERATE RECORD VIEW.
4. For Select Record Types, keep the Order record type selected. The view will display all fields from the AT Order record type.
5. Click NEXT.

6. For Edit Settings, leave the default configurations:

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

7. Click NEXT.

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

   Note:  Since an existing Rules and Constants folder is being used, Groups are hidden since they have already been configured for the folder.

9. Click GENERATE. Appian generates the new interface.
10. Click CLOSE. Your new Summary view displays in the Record Views table.
11. Click SAVE.

Configure the record header

The record header displays the record title, breadcrumbs, and related actions on every record view. We'll set a background color to give the header a clean look.

To configure the record header 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.

Configure the record title

The record title helps users identify which record they are viewing. We'll configure the record title to display the order ID.

To configure the record title:

1. For Record Title, select Expression.

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

   1
   "Order #" & rv!record[recordType!AT Order.fields.orderId]
   

3. In the expression, change the record type reference to use the record type reference in your environment.
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 order to view its Summary view.

The record title displays "Order #" followed by the order ID, and the record header background appears in light blue.

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

Success!

You've created a record type that connects to Snowflake and displays order data. Your AT Order record type now has:

• A Snowflake connected system that authenticates and connects to your Snowflake database.
• A data model with renamed fields from the ORDERS table in the SNOWFLAKE_SAMPLE_DATA database.
• A record list with configured columns and a status user filter.
• A Summary view with a styled record header and a descriptive record title.

Feedback