Editable Grid from SQL

Certified Associate Developer

I am very new to Appian and am trying to make an editable grid that pulls data from a SQL DB and then saves the changes back.  I have created the Data Store and configured a Data Type, Expressions and Constants for each table.

  Discussion posts and replies are publicly visible

Parents Reply Children
  • 0
    Certified Associate Developer
    in reply to Chris

    Thank you for the welcome and the info.  I must be over looking something as I attempted this all weekend and still no luck.  I know I am missing something simple just can't seem to put my finger on it.

  • Feel free to add any details and/or code snippets here, such as - are you receiving an error?  Is data not populating in your grid?  Is data not saving back to the DB, etc?

  • 0
    Certified Associate Developer
    in reply to Chris

    Thank you Chris.  I guess where I am lost is getting the data to display.  I am not understanding how to convert the link you attached from a local variable to the DB.

  • For instance, a simple setup would contain 3 nodes in a process model.  Configure the process model to contain a process variable of your CDT type (multiple).  We'll call it pv!data.

    Node 1, create a Script Task that utilizes an Output Expression to save a!queryEntity() from your data source into the pv!data variable, to be edited (best practice is to save this as a rule, then call the rule instead of a!queryEntity() directly):

    a!queryEntity(
      entity: cons!YOUR_DATA_ENTITY,
      query: a!query(
        pagingInfo: a!pagingInfo(1,-1),
        filter: a!queryFilter(
          field: "fieldToFilterOn",
          operator: "=",
          value: "filter Value"
        )
      )
    ).data

    Node 2, create an Interface and corresponding User Input Task, input will for pv!data, to send in your data to edit in the grid.  Here is the SAIL Recipe edited for a ri!data variable:

    a!formLayout(
      label: "Example: Add,Update, or Remove Employee Data",
      contents: {
        a!gridLayout(
          totalCount: count(ri!data),
          headerCells: {
            a!gridLayoutHeaderCell(label: "First Name" ),
            a!gridLayoutHeaderCell(label: "Last Name" ),
            a!gridLayoutHeaderCell(label: "Department" ),
            a!gridLayoutHeaderCell(label: "Title" ),
            a!gridLayoutHeaderCell(label: "Phone Number" ),
            a!gridLayoutHeaderCell(label: "Start Date", align: "RIGHT" ),
            /* For the "Remove" column */
            a!gridLayoutHeaderCell(label: "" )
          },
          /* Only needed when some columns need to be narrow */
          columnConfigs: {
            a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight:3 ),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight:3 ),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight:3 ),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight:3 ),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight:3 ),
            a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight:2 ),
            a!gridLayoutColumnConfig(width: "ICON")
          },
          /*
          * a!forEach() will take local!employee data and used that data to loop through an
          * expression that creates each row.
          *
          * When modifying the recipe to work with your data, you only need to change:
          * 1.) the number of fields in each row
          * 2.) the types of fields for each column (i.e. a!textField() for text data elements)
          * 3.) the fv!item elements. For example fv!item.firstName would change to fv!item.yourdata
          */
          rows: a!forEach(
            items: ri!data,
            expression: a!gridRowLayout(
              contents: {
                /* For the First Name Column*/
                a!textField(
                  /* Labels are not visible in grid cells but are necessary to meet accessibility requirements */
                  label: "first name " & fv!index,
                  value: fv!item.firstName,
                  saveInto: fv!item.firstName,
                  required: true
                ),
                /* For the Last Name Column*/
                a!textField(
                  label: "last name " & fv!index,
                  value: fv!item.lastName,
                  saveInto: fv!item.lastName,
                  required:true
                ),
                /* For the Department Column*/
                a!dropdownField(
                  label: "department " & fv!index,
                  placeholderLabel:  "-- Select -- ",
                  choiceLabels: { "Corporate", "Engineering", "Finance", "Human Resources", "Professional Services", "Sales" },
                  choiceValues: { "Corporate", "Engineering", "Finance", "Human Resources", "Professional Services", "Sales" },
                  value: fv!item.department,
                  saveInto: fv!item.department,
                  required:true
                ),
                /* For the Title Column*/
                a!textField(
                  label: "title " & fv!index,
                  value: fv!item.title,
                  saveInto: fv!item.title,
                  required:true
                ),
                /* For the Phone Number Column*/
                a!textField(
                  label: "phone number " & fv!index,
                  placeholder:"555-456-7890",
                  value: fv!item.phoneNumber,
                  saveInto: fv!item.phoneNumber
                ),
                /* For the Start Date Column*/
                a!dateField(
                  label: "start date " & fv!index,
                  value: fv!item.startDate,
                  saveInto: fv!item.startDate,
                  required:true,
                  align: "RIGHT"
                ),
                /* For the Removal Column*/
                a!richTextDisplayField(
                  value: a!richTextIcon(
                    icon: "close",
                    altText: "delete " & fv!index,
                    caption: "Remove " & fv!item.firstName & " " & fv!item.lastName,
                    link: a!dynamicLink(
                      value: fv!index,
                      saveInto: {
                        a!save(ri!data, remove(ri!data, save!value))
                      }
                    ),
                    linkStyle: "STANDALONE",
                    color: "NEGATIVE"
                  )
                )
              },
              id: fv!index
            )
          ),
          addRowlink: a!dynamicLink(
            label: "Add Employee",
            /*
             * For your use case, set the value to a blank instance of your CDT using
             * the type constructor, e.g. type!Employee(). Only specify the field
             * if you want to give it a default value e.g. startDate: today()+1.
             */
            value: {
              startDate: today() + 1
            },
            saveInto: {
              a!save(ri!data, append(ri!data, save!value))
            }
          ),
          rowHeader: 1
        )
      },
      buttons: a!buttonLayout(
        primaryButtons: a!buttonWidget(
          label: "Submit",
          submit: true
        )
      )
    )

    Editing the grid will update ri!data, which should save back into pv!data via the User Input Task configuration.

    Node 3, create a Write to Data Store node that is configured to write back to the DS in question here, with pv!data.  This will persist the prior User Input Task updates back to the DB.

  • 0
    Certified Associate Developer
    in reply to Chris

    Thank you Chris, I will give that a try

  • 0
    Certified Associate Developer
    in reply to AllenP

    Doing something wrong still.  I created an expression:

    Created an interface just trying to see if it would display data and nothing.  Not sure I am telling correctly where to pull the data from.

  • For testing, in the interface designer when you utilize the "Test" button, plug your rule call into the expression for ri!data as:  =rule!Expression_cp_part(), you should see your grid load.

    Otherwise for the actual process, In your process model on the Data / Inputs tab of the User Input task, you should have a Node Input parameter for "data".  For the "value" of data, the expression should read =rule!Expression_cp_part().  You will want to save this back into a process variable of type cp_part (multiple) to utilize in a following Write to Data Store Entity node.