Use the Write to Data Store Entity Smart Service Function on an Interface

Interface patterns give you an opportunity to explore different interface designs. Be sure to check out How to Adapt a Pattern for Your Application.

Goal

Allow the user to publish several rows of data to a table through the a!writeToDataStoreEntity() smart service function.

SAIL_Recipe_Use_Smart_Service_Function.png

This recipe is frequently used as the basis for another recipe that features an entity query. If you aren't able to follow the steps in this recipe to create that entity, see the section, Substitute with Manual Data.

Follow this recipe to create the supporting objects so you can easily copy-and-paste related recipes. Note that variations in your data and the data displayed in the recipes (like differing start dates, or different employee names) is expected.

This scenario demonstrates:

  • How to use the a!writeToDataStoreEntity() smart service function to persist data directly from an interface
  • How to use the 'showWhen' parameter of an interface component to choose one component or another, based off a user's interactions.

Setup

Try to follow these setup steps verbatim. However, if you do need to adjust any of these values, you'll need to ensure that you making the necessary modifications to interface recipes that depend on this data.

This example uses a custom data type. In an appropriate application, create an employee custom data type (leave namespace set to default) with the following fields:

  • id (Number (Integer))
    • Select the Key icon and set this field to Primary Key
    • click the Auto-generate checkbox to let the database handle value assignment
  • firstName (Text)
  • lastName (Text)
  • department (Text)
  • title (Text)
  • phoneNumber (Text)
  • startDate (Date)

Next, let's create a data store within the same application where the CDT was created:

  1. Create a new data store. Name the data store with a unique relevant name, like Employee DS.
  2. Configure the data source to a data source you can access
  3. In the data store, create a data store entity called employee
    • Set the data type to employee
  4. Save & Publish the data store
    • Optionally, check the data base table to see that an employee table has been created

Finally, let's create constant within that same application so we can use the newly created data store in an expression:

  • Create a new constant. Name the constant EMPLOYEE_ENTITY.
  • Set the Data Type to Data Store Entity
  • Set the Data Store dropdown value to the Data Store created above
  • Set the Data Store Entity to Employee

Now that we've created the dependent objects and custom data type, let's move on to the main expression.

In the following expression, the type constructor (line 3) needs to match the data type you created. If you have issues getting this to work, replace 'type!{urn:com:appian:types}employee?list' with your data type's namespace and name. You can get that information from the data type, in the PROPERTIES section. The format is: type!{<Namespace>}<Name>?list. For this example data type, Namespace is urn:com:appian:types, and the Name is employee.

Expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
a!localVariables(
  /* The data to load into the data store entity. */
  local!dataToLoad: cast(
    'type!{urn:com:appian:types}employee?list',
    {
      { firstName: "John" , lastName: "Smith" , department: "Engineering" , title: "Director" , phoneNumber: "555-123-4567" , startDate: today()-360 },
      { firstName: "Michael" , lastName: "Johnson" , department: "Finance" , title: "Analyst" , phoneNumber: "555-987-6543" , startDate: today()-360 },
      { firstName: "Mary", lastName: "Reed" , department: "Engineering" , title: "Software Engineer" , phoneNumber: "555-456-0123" , startDate: today()-240 },
      { firstName: "Angela" , lastName: "Cooper" , department: "Sales" , title: "Manager" , phoneNumber: "555-123-4567" , startDate: today()-240 },
      { firstName: "Elizabeth" , lastName: "Ward" , department: "Sales" , title: "Sales Associate" , phoneNumber: "555-987-6543" , startDate: today()-240 },
      { firstName: "Daniel", lastName: "Lewis" , department: "HR" , title: "Manager" , phoneNumber: "555-876-5432" , startDate: today()-180 },
      { firstName: "Paul" , lastName: "Martin" , department: "Finance" , title: "Analyst" , phoneNumber: "555-609-3691" , startDate: today()-150 },
      { firstName: "Jessica" , lastName: "Peterson" , department: "Finance" , title: "Analyst" , phoneNumber: "555-987-6543" , startDate: today()-150 },
      { firstName: "Mark" , lastName: "Hall" , department: "Professional Services" , title: "Director" , phoneNumber: "555-012-3456" , startDate: today()-150 },
      { firstName: "Rebecca" , lastName: "Wood" , department: "Engineering" , title: "Manager" , phoneNumber: "555-210-3456" , startDate: today()-150 },
      { firstName: "Pamela" , lastName: "Sanders" , department: "Engineering" , title: "Software Engineer" , phoneNumber: "555-123-4567" , startDate:today()-120 },
      { firstName: "Christopher" , lastName: "Morris" , department: "Professional Services" , title: "Consultant" , phoneNumber: "555-456-7890" , startDate: today()-120 },
      { firstName: "Kevin" , lastName: "Stewart" , department: "Professional Services" , title: "Manager" , phoneNumber: "555-345-6789" , startDate: today()-120 },
      { firstName: "Stephen" , lastName: "Edwards" , department: "Sales" , title: "Sales Associate" , phoneNumber: "555-765-4321" , startDate: today()-120 },
      { firstName: "Janet", lastName:"Coleman" , department: "Finance" , title: "Director" , phoneNumber: "555-654-3210" , startDate: today()-90 },
      { firstName: "Scott" , lastName: "Bailey" , department: "Engineering" , title: "Software Engineer" , phoneNumber: "555-678-1235" , startDate: today()-30 },
      { firstName: "Andrew" , lastName: "Nelson" , department: "Professional Services" , title: "Consultant" , phoneNumber: "555-789-4560" , startDate: today()-30 },
      { firstName: "Michelle" , lastName: "Foster" , department: "HR" , title: "Director" , phoneNumber: "555-345-6789" , startDate: today()-30 },
      { firstName: "Laura" , lastName:"Bryant" , department: "Sales" , title: "Sales Associate" , phoneNumber: "555-987-6543" , startDate: today()-14 },
      { firstName: "William" , lastName: "Ross" , department: "Engineering" , title: "Software Engineer" , phoneNumber: "555-123-4567" , startDate: today()-10 },
      { firstName: "Arya" , lastName:"Colson" , department: "Sales" , title: "Sales Associate" , phoneNumber: "555-927-3343" , startDate: today()-5 }

    }
  ),
  /* This value gets updated when the user clicks on the PUBLISH DATA button in order to disable it
     so the user can't click it multiple times. */
  local!submitted: false,
  /* The refresh variable watches local!submitted and reruns the query when it changes. */
  local!datasubset: a!refreshVariable(
    value: a!queryEntity(
      entity: cons!EMPLOYEE_ENTITY,
      query: a!query(
        pagingInfo: a!pagingInfo(
          startIndex: 1,
          batchSize: -1,
          sort: a!sortInfo(field: "id", ascending: true)
        )
      ),
      fetchTotalCount: true
    ),
    refreshOnVarChange: local!submitted
  ),
  local!hasData: not(local!datasubset.totalCount=0),
  a!sectionLayout(
    contents: {
      a!buttonLayout(
        secondaryButtons:{
          a!buttonWidget(
            label: if(local!hasData, "Data Already Published", "Publish Data"),
            saveInto: {
              a!writeToDataStoreEntity(
                dataStoreEntity: cons!EMPLOYEE_ENTITY,
                valueToStore: local!dataToLoad,
                /* Since the writeToDataStoreEntity function doesn't change values in the
                 interface, we add an additional save to update a local variable so the
                 the query in local!datasubset runs again. */
                onSuccess: a!save(local!submitted, true)
              )
            },
            submit: true,
            style: "PRIMARY",
            disabled: or(local!hasData, local!submitted)
          )
        }
      ),
      a!gridField(
        label: "DATA TO WRITE",
        labelPosition: "ABOVE",
        data: local!dataToLoad,
        columns: {
          a!gridColumn(
            label: "ID",
            value: if(isnull(fv!row.id), "N/A", fv!row.id)
          ),
          a!gridColumn(
            label: "First Name",
            value: fv!row.firstName
          ),
          a!gridColumn(
            label: "Last Name",
            value: fv!row.lastName
          ),
          a!gridColumn(
            label: "Department",
            value: fv!row.department
          ),
          a!gridColumn(
            label: "Title",
            value: fv!row.title
          ),
          a!gridColumn(
            label: "Phone Number",
            value: fv!row.phoneNumber
          ),
          a!gridColumn(
            label: "Start Date",
            value: fv!row.startDate
          )
        },
        validations: {},
        showWhen: local!datasubset.totalCount = 0
      ),
      a!gridField(
        label: "DATA WRITTEN",
        labelPosition: "ABOVE",
        data: a!queryEntity(
          entity: cons!EMPLOYEE_ENTITY,
          query: a!query(pagingInfo: fv!pagingInfo),
          fetchTotalCount: true
        ),
        columns: {
          a!gridColumn(
            label: "ID",
            value: fv!row.id
          ),
          a!gridColumn(
            label: "First Name",
            value: fv!row.firstName
          ),
          a!gridColumn(
            label: "Last Name",
            value: fv!row.lastName
          ),
          a!gridColumn(
            label: "Department",
            value: fv!row.department
          ),
          a!gridColumn(
            label: "Title",
            value: fv!row.title
          ),
          a!gridColumn(
            label: "Phone Number",
            value: fv!row.phoneNumber
          ),
          a!gridColumn(
            label: "Start Date",
            value: fv!row.startDate
          )
        },
        initialSorts: a!sortInfo("id", true),
        showWhen: not(local!datasubset.totalCount = 0)
      )
    }
  )
)

Test it out

  1. Click the Publish Data button. The button will stay disabled and remain disabled as long as there is employee data present in the relational database table.
    • If data already exists in the database table, you should see a button that says, 'Data Already Published'
  2. Notice the ID values went from all "N/A" to a sequential value when shown in the new grid.

Notable implementation details

  • Two grids are configured in this example, but only one will ever show at a time. Instead of writing the logic in each of the grid text columns, we are showing a grid with hard-coded data when nothing is in the database table and the other grid when there is database data available.

Substitute with Manual Data

This section describes how you can use the following manual data in place of an entity query featured in an interface recipe.

Manual Data

The following data is what you'll use in place of the entity query in the subsequent examples:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
{
  {id: 1, firstName: "John", lastName: "Smith", department: "Engineering", title: "Director", phoneNumber: "800-123-4567", startDate: fn!date(2017, 7, 6)},
  {id: 2, firstName: "Michael", lastName: "Johnson", department: "Finance", title: "Analyst", phoneNumber: "866-987-6543", startDate: fn!date(2016, 6, 12)},
  {id: 3, firstName: "Mary", lastName: "Reed", department: "Engineering", title: "Software Engineer", phoneNumber: "789-456-0123", startDate: fn!date(2016, 10, 10)},
  {id: 4, firstName: "Angela", lastName: "Cooper", department: "Sales", title: "Manager", phoneNumber: "404-123-4567", startDate: fn!date(2016, 10, 10)},
  {id: 5, firstName: "Elizabeth", lastName: "Ward", department: "Sales", title: "Sales Associate", phoneNumber: "570-987-6543", startDate: fn!date(2016, 10, 10)},
  {id: 6, firstName: "Daniel", lastName: "Lewis", department: "HR", title: "Manager", phoneNumber: "866-876-5432", startDate: fn!date(2016, 12, 9)},
  {id: 7, firstName: "Paul", lastName: "Martin", department: "Finance", title: "Analyst", phoneNumber: "703-609-3691", startDate: fn!date(2017, 1, 8)},
  {id: 8, firstName: "Jessica", lastName: "Peterson", department: "Finance", title: "Analyst", phoneNumber: "404-987-6543", startDate: fn!date(2017, 1, 8)},
  {id: 9, firstName: "Mark", lastName: "Hall", department: "Professional Services", title: "Director", phoneNumber: "789-012-3456", startDate: fn!date(2017, 1, 8)},
  {id: 10, firstName: "Rebecca", lastName: "Wood", department: "Engineering", title: "Manager", phoneNumber: "570-210-3456", startDate: fn!date(2017, 1, 8)},
  {id: 11, firstName: "Pamela", lastName: "Sanders", department: "Engineering", title: "Software Engineer", phoneNumber: "570-123-4567", startDate: fn!date(2017, 2, 7)},
  {id: 12, firstName: "Christopher", lastName: "Morris", department: "Professional Services", title: "Consultant", phoneNumber: "321-456-7890", startDate: fn!date(2017, 2, 7)},
  {id: 13, firstName: "Kevin", lastName: "Stewart", department: "Professional Services", title: "Manager", phoneNumber: "800-345-6789", startDate: fn!date(2017, 2, 7)},
  {id: 14, firstName: "Stephen", lastName: "Edwards", department: "Sales", title: "Sales Associate", phoneNumber: "866-765-4321", startDate: fn!date(2017, 2, 7)},
  {id: 15, firstName: "Janet", lastName: "Coleman", department: "Finance", title: "Director", phoneNumber: "789-654-3210", startDate: fn!date(2017, 3, 9)},
  {id: 16, firstName: "Scott", lastName: "Bailey", department: "Engineering", title: "Software Engineer", phoneNumber: "404-678-1235", startDate: fn!date(2017, 5, 8)},
  {id: 17, firstName: "Andrew", lastName: "Nelson", department: "Professional Services", title: "Consultant", phoneNumber: "321-789-4560", startDate: fn!date(2017, 5, 8)},
  {id: 18, firstName: "Michelle", lastName: "Foster", department: "HR", title: "Director", phoneNumber: "404-345-6789", startDate: fn!date(2017, 5, 8)},
  {id: 19, firstName: "Laura", lastName: "Bryant", department: "Sales", title: "Sales Associate", phoneNumber: "800-987-6543", startDate: fn!date(2017, 6, 7)},
  {id: 20, firstName: "William", lastName: "Ross", department: "Engineering", title: "Software Engineer", phoneNumber: "866-123-4567", startDate: fn!date(2017, 6, 7)}
}

Example: Full Query Replace

Whether the query is in a local variable, or in the data parameter of a grid, you can replace it with the manual data. First, identify the entity query (a!queryEntity) in the expression.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
a!gridField(
  label: "Read-only Grid",
  labelPosition: "ABOVE",
  data: `a!queryEntity(`
    `entity: cons!EMPLOYEE_ENTITY,`
    `query: a!query(`
      `selection: a!querySelection(`
        `columns: {`
          `a!queryColumn(field: "firstName"),`
          `a!queryColumn(field: "lastName"),`
          `a!queryColumn(field: "department"),`
          `a!queryColumn(field: "title"),`
          `a!queryColumn(field: "startDate")`
        `}`
      `),`
      `pagingInfo: fv!pagingInfo`
    `),`
    `fetchTotalCount: true`
  `)`,
  columns: {
...

Next, replace it with the manual data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
a!gridField(
  label: "Read-only Grid",
  labelPosition: "ABOVE",
!  data: {
    {id: 1, firstName: "John", lastName: "Smith", department: "Engineering", title: "Director", phoneNumber: "800-123-4567", startDate: fn!date(2017, 7, 6)},
    {id: 2, firstName: "Michael", lastName: "Johnson", department: "Finance", title: "Analyst", phoneNumber: "866-987-6543", startDate: fn!date(2016, 6, 12)},
    {id: 3, firstName: "Mary", lastName: "Reed", department: "Engineering", title: "Software Engineer", phoneNumber: "789-456-0123", startDate: fn!date(2016, 10, 10)},
    {id: 4, firstName: "Angela", lastName: "Cooper", department: "Sales", title: "Manager", phoneNumber: "404-123-4567", startDate: fn!date(2016, 10, 10)},
    {id: 5, firstName: "Elizabeth", lastName: "Ward", department: "Sales", title: "Sales Associate", phoneNumber: "570-987-6543", startDate: fn!date(2016, 10, 10)},
    {id: 6, firstName: "Daniel", lastName: "Lewis", department: "HR", title: "Manager", phoneNumber: "866-876-5432", startDate: fn!date(2016, 12, 9)},
    {id: 7, firstName: "Paul", lastName: "Martin", department: "Finance", title: "Analyst", phoneNumber: "703-609-3691", startDate: fn!date(2017, 1, 8)},
    {id: 8, firstName: "Jessica", lastName: "Peterson", department: "Finance", title: "Analyst", phoneNumber: "404-987-6543", startDate: fn!date(2017, 1, 8)},
    {id: 9, firstName: "Mark", lastName: "Hall", department: "Professional Services", title: "Director", phoneNumber: "789-012-3456", startDate: fn!date(2017, 1, 8)},
    {id: 10, firstName: "Rebecca", lastName: "Wood", department: "Engineering", title: "Manager", phoneNumber: "570-210-3456", startDate: fn!date(2017, 1, 8)},
    {id: 11, firstName: "Pamela", lastName: "Sanders", department: "Engineering", title: "Software Engineer", phoneNumber: "570-123-4567", startDate: fn!date(2017, 2, 7)},
    {id: 12, firstName: "Christopher", lastName: "Morris", department: "Professional Services", title: "Consultant", phoneNumber: "321-456-7890", startDate: fn!date(2017, 2, 7)},
    {id: 13, firstName: "Kevin", lastName: "Stewart", department: "Professional Services", title: "Manager", phoneNumber: "800-345-6789", startDate: fn!date(2017, 2, 7)},
    {id: 14, firstName: "Stephen", lastName: "Edwards", department: "Sales", title: "Sales Associate", phoneNumber: "866-765-4321", startDate: fn!date(2017, 2, 7)},
    {id: 15, firstName: "Janet", lastName: "Coleman", department: "Finance", title: "Director", phoneNumber: "789-654-3210", startDate: fn!date(2017, 3, 9)},
    {id: 16, firstName: "Scott", lastName: "Bailey", department: "Engineering", title: "Software Engineer", phoneNumber: "404-678-1235", startDate: fn!date(2017, 5, 8)},
    {id: 17, firstName: "Andrew", lastName: "Nelson", department: "Professional Services", title: "Consultant", phoneNumber: "321-789-4560", startDate: fn!date(2017, 5, 8)},
    {id: 18, firstName: "Michelle", lastName: "Foster", department: "HR", title: "Director", phoneNumber: "404-345-6789", startDate: fn!date(2017, 5, 8)},
    {id: 19, firstName: "Laura", lastName: "Bryant", department: "Sales", title: "Sales Associate", phoneNumber: "800-987-6543", startDate: fn!date(2017, 6, 7)},
    {id: 20, firstName: "William", lastName: "Ross", department: "Engineering", title: "Software Engineer", phoneNumber: "866-123-4567", startDate: fn!date(2017, 6, 7)}
!  },
  columns: {

Example: Just the Data

When just the data is being used from a query, it's usually defined in a local variable. You can tell because the query has a .data suffix.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
a!localVariables(
  /* This variable stores the grid's selection. */
  local!selection,
  /* This variable stores the row information for the grid's selection. */
  local!selectedRows,
  local!removedIds,
  local!employeeData: `a!queryEntity(`
    entity: cons!EMPLOYEE_ENTITY,
    query: a!query(
      selection: a!querySelection(
        columns: {
          a!queryColumn(field: "id"),
          a!queryColumn(field: "firstName"),
          a!queryColumn(field: "lastName"),
          a!queryColumn(field: "department"),
          a!queryColumn(field: "title"),
        }
      ),
      pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 10)
    ),
    fetchTotalCount: true
  )`.data`,

Replace the entire query, including .data, with your manual data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
a!localVariables(
  /* This variable stores the grid's selection. */
  local!selection,
  /* This variable stores the row information for the grid's selection. */
  local!selectedRows,
  local!removedIds,
  local!employeeData: `{`
    {id: 1, firstName: "John", lastName: "Smith", department: "Engineering", title: "Director", phoneNumber: "800-123-4567", startDate: fn!date(2017, 7, 6)},
    {id: 2, firstName: "Michael", lastName: "Johnson", department: "Finance", title: "Analyst", phoneNumber: "866-987-6543", startDate: fn!date(2016, 6, 12)},
    {id: 3, firstName: "Mary", lastName: "Reed", department: "Engineering", title: "Software Engineer", phoneNumber: "789-456-0123", startDate: fn!date(2016, 10, 10)},
    {id: 4, firstName: "Angela", lastName: "Cooper", department: "Sales", title: "Manager", phoneNumber: "404-123-4567", startDate: fn!date(2016, 10, 10)},
    {id: 5, firstName: "Elizabeth", lastName: "Ward", department: "Sales", title: "Sales Associate", phoneNumber: "570-987-6543", startDate: fn!date(2016, 10, 10)},
    {id: 6, firstName: "Daniel", lastName: "Lewis", department: "HR", title: "Manager", phoneNumber: "866-876-5432", startDate: fn!date(2016, 12, 9)},
    {id: 7, firstName: "Paul", lastName: "Martin", department: "Finance", title: "Analyst", phoneNumber: "703-609-3691", startDate: fn!date(2017, 1, 8)},
    {id: 8, firstName: "Jessica", lastName: "Peterson", department: "Finance", title: "Analyst", phoneNumber: "404-987-6543", startDate: fn!date(2017, 1, 8)},
    {id: 9, firstName: "Mark", lastName: "Hall", department: "Professional Services", title: "Director", phoneNumber: "789-012-3456", startDate: fn!date(2017, 1, 8)},
    {id: 10, firstName: "Rebecca", lastName: "Wood", department: "Engineering", title: "Manager", phoneNumber: "570-210-3456", startDate: fn!date(2017, 1, 8)},
    {id: 11, firstName: "Pamela", lastName: "Sanders", department: "Engineering", title: "Software Engineer", phoneNumber: "570-123-4567", startDate: fn!date(2017, 2, 7)},
    {id: 12, firstName: "Christopher", lastName: "Morris", department: "Professional Services", title: "Consultant", phoneNumber: "321-456-7890", startDate: fn!date(2017, 2, 7)},
    {id: 13, firstName: "Kevin", lastName: "Stewart", department: "Professional Services", title: "Manager", phoneNumber: "800-345-6789", startDate: fn!date(2017, 2, 7)},
    {id: 14, firstName: "Stephen", lastName: "Edwards", department: "Sales", title: "Sales Associate", phoneNumber: "866-765-4321", startDate: fn!date(2017, 2, 7)},
    {id: 15, firstName: "Janet", lastName: "Coleman", department: "Finance", title: "Director", phoneNumber: "789-654-3210", startDate: fn!date(2017, 3, 9)},
    {id: 16, firstName: "Scott", lastName: "Bailey", department: "Engineering", title: "Software Engineer", phoneNumber: "404-678-1235", startDate: fn!date(2017, 5, 8)},
    {id: 17, firstName: "Andrew", lastName: "Nelson", department: "Professional Services", title: "Consultant", phoneNumber: "321-789-4560", startDate: fn!date(2017, 5, 8)},
    {id: 18, firstName: "Michelle", lastName: "Foster", department: "HR", title: "Director", phoneNumber: "404-345-6789", startDate: fn!date(2017, 5, 8)},
    {id: 19, firstName: "Laura", lastName: "Bryant", department: "Sales", title: "Sales Associate", phoneNumber: "800-987-6543", startDate: fn!date(2017, 6, 7)},
    {id: 20, firstName: "William", lastName: "Ross", department: "Engineering", title: "Software Engineer", phoneNumber: "866-123-4567", startDate: fn!date(2017, 6, 7)}
  `}`,
Open in Github Built: Fri, Mar 11, 2022 (04:59:07 PM)
FEEDBACK