Advance Editable Grid - Insert, Delete and Update data in a CDT when users add, delete and make changes to grid rows

Dear All,

Here is an advance topic for those who have been working with SAIL and Appian for longer than a newbie like me.

I have an editable grid - see the screenshot and code below.  I managed to set it up as the start form of a process (I'm new to Appian and SAIL) and I have configured it to successfully pass on the rows into a database using a CDT.

However, the trouble now is that in another form down the line in my process, I would like the editable grid to be called with the data that was entered prepopulated.  This looks like a huge task right now, and I'm not sure even where to begin.  Secondly, I am looking to find out if the user can update rows, delete, add and insert rows in the grid, and the changes will be committed to the database.  I am not sure how to achieve these two feats in SAIL, but I am wondering if there is a way I can set it up.

Summary is:

1. Can I call up data rows that were previously entered into the grid to show up in an editable grid in a later task in the process?

2a. When a user edits a row, can the data be immediately updated in the database through the CDT?

2b. When a user inserts a row, can the data be immediately inserted in the database through in the CDT?

2c. When a user deletes a row, can the data be immediately deleted from the database through the CDT?

I am not sure if this is a workable solution in SAIL so please let me know if there is a different way I should be looking at the problem.  Where possible, kindly include code snippets.

Thanks.

---

---

load(
  local!input: {
    {
      id: null,
      cashAdvnaceId: null,
      itemName: "",
      amount: 0.00,
      qty: 0,
      total: 0.0
    }
  },
  /* Use query entity to get data to local!input*/
  with(
    a!formLayout(
      label: "Submit New Cash Advance Request",
      instructions: "Fill in this form to make a new cash advance request.",
      contents: {
        a!sectionLayout(
          label: "",
          contents: {
            a!columnsLayout(
              columns: {
                a!columnLayout(
                  contents: {
                    a!textField(
                      label: "Requester Name",
                      labelPosition: "ABOVE",
                      value: ri!CashAdvanceRequestData.requester,
                      saveInto: ri!CashAdvanceRequestData.requester,
                      refreshAfter: "UNFOCUS",
                      validations: {}
                    )
                  }
                ),
                a!columnLayout(
                  contents: {
                    a!textField(
                      label: "Request Category",
                      labelPosition: "ABOVE",
                      value: ri!CashAdvanceRequestData.category,
                      saveInto: ri!CashAdvanceRequestData.category,
                      refreshAfter: "UNFOCUS",
                      validations: {}
                    )
                  }
                ),
                a!columnLayout(
                  contents: {
                    a!textField(
                      label: "Department",
                      labelPosition: "ABOVE",
                      value: ri!CashAdvanceRequestData.department,
                      saveInto: ri!CashAdvanceRequestData.department,
                      refreshAfter: "UNFOCUS",
                      validations: {}
                    )
                  }
                )
              }
            )
          }
        ),
        a!gridLayout(
          totalCount: count(
            local!input
          ),
          headerCells: {
            a!gridLayoutHeaderCell(
              label: "Item"
            ),
            a!gridLayoutHeaderCell(
              label: "Amount",
              align: "RIGHT"
            ),
            a!gridLayoutHeaderCell(
              label: "Qty",
              align: "RIGHT"
            ),
            a!gridLayoutHeaderCell(
              label: "Total",
              align: "RIGHT"
            ),
            /* For the "Remove" column */
            a!gridLayoutHeaderCell(
              label: ""
            )
          },
          /* Only needed when some columns need to be narrow */
          columnConfigs: {
            a!gridLayoutColumnConfig(
              width: "DISTRIBUTE",
              weight: 7
            ),
            a!gridLayoutColumnConfig(
              width: "DISTRIBUTE",
              weight: 2
            ),
            a!gridLayoutColumnConfig(
              width: "DISTRIBUTE",
              weight: 1
            ),
            a!gridLayoutColumnConfig(
              width: "DISTRIBUTE",
              weight: 2
            ),
            a!gridLayoutColumnConfig(
              width: "ICON"
            )
          },
          rows: a!forEach(
            items: local!input,
            expression: a!gridRowLayout(
              id: fv!index,
              contents: {
                /* For the Item Name Column*/
                a!textField(
                  /* Labels are not visible in grid cells but are necessary to meet accessibility requirements */
                  label: "item " & fv!index,
                  value: fv!item.itemName,
                  saveInto: fv!item.itemName,
                  required: true
                ),
                /* For the Amount Column*/
                a!floatingPointField(
                  label: "Amount " & fv!index,
                  labelPosition: "ADJACENT",
                  value: fv!item.amount,
                  saveInto: {
                    fv!item.amount,
                    if(
                      rule!APN_isBlank(
                        fv!item.qty
                      ),
                      "",
                      a!save(
                        fv!item.total,
                        product(
                          fv!item.amount,
                          fv!item.qty
                        )
                      )
                    )
                  },
                  refreshAfter: "UNFOCUS",
                  validations: {},
                  align: "RIGHT"
                ),
                /* For the Qty Column*/
                a!floatingPointField(
                  label: "Qty " & fv!index,
                  labelPosition: "ADJACENT",
                  value: fv!item.qty,
                  saveInto: {
                    fv!item.qty,
                    if(
                      rule!APN_isBlank(
                        fv!item.amount
                      ),
                      "",
                      a!save(
                        fv!item.total,
                        product(
                          fv!item.amount,
                          fv!item.qty
                        )
                      )
                    )
                  },
                  refreshAfter: "UNFOCUS",
                  validations: {},
                  align: "RIGHT"
                ),
                /* For the Total Column*/
                a!floatingPointField(
                  label: "Total " & fv!index,
                  labelPosition: "ADJACENT",
                 
                  value: if(
                    or(
                      rule!APN_isBlank(
                        fv!item.amount
                      ),
                      rule!APN_isBlank(
                        fv!item.qty
                      )
                    ),
                    fv!item.total,
                    product(
                      fv!item.amount,
                      fv!item.qty
                    )
                  ),
                  saveInto: fv!item.total,
                  refreshAfter: "UNFOCUS",
                  validations: {},
                  align: "RIGHT"
                ),
                /* For the Removal Column*/
                a!imageField(
                  label: "delete " & fv!index,
                  images: a!documentImage(
                    document: a!iconIndicator(
                      "REMOVE"
                    ),
                    altText: "Remove Employee",
                    caption: "Remove " & fv!item.item & " " & fv!item.lastName,
                    link: a!dynamicLink(
                      value: fv!index,
                      saveInto: {
                        a!save(
                          local!input,
                          remove(
                            local!input,
                            save!value
                          )
                        )
                      }
                    )
                  ),
                  size: "ICON"
                )
              }
            )
          ),
          addRowlink: a!dynamicLink(
            label: "Add a new line.",
            /*
           * 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(
                local!input,
                append(
                  local!input,
                  save!value
                )
              )
            }
          )
        ),
        a!sectionLayout(
          label: "",
          contents: {
            a!columnsLayout(
              columns: {
                a!columnLayout(
                  contents: {
                    a!fileUploadField(
                      label: "Supporting Documents",
                      labelPosition: "ABOVE",
                      maxselections: 1,
                      value: ri!CashAdvanceRequestData.attachment,
                      saveInto: ri!CashAdvanceRequestData.attachment,
                      validations: {}
                    )
                  }
                ),
                a!columnLayout(
                  contents: {}
                ),
                a!columnLayout(
                  contents: {
                    a!floatingPointField(
                      label: "Total Amount",
                      labelPosition: "ADJACENT",
                      value: sum(
                        a!forEach(
                          items: local!input,
                          expression: if(
                            or(
                              rule!APN_isBlank(
                                fv!item.amount
                              ),
                              rule!APN_isBlank(
                                fv!item.qty
                              )
                            ),
                            fv!item.total,
                            product(
                              fv!item.amount,
                              fv!item.qty
                            )
                          )
                        )
                      ),
                      saveInto: ri!CashAdvanceRequestData.totalApproved,
                      refreshAfter: "UNFOCUS",
                      readOnly: true,
                      validations: {},
                      align: "RIGHT"
                    )
                  }
                )
              }
            )
          }
        ),
        a!paragraphField(
          label: "Request Justification",
          labelPosition: "ABOVE",
          value: ri!CashAdvanceRequestData.justification,
          saveInto: ri!CashAdvanceRequestData.justification,
          refreshAfter: "UNFOCUS",
          height: "MEDIUM",
          validations: {}
        )
      },
      buttons: a!buttonLayout(
        primaryButtons: a!buttonWidget(
          label: "Submit",
          value: "Submit",
          saveInto: {
            a!save(
              ri!input,
              local!input
            ),
            a!forEach(
              items: ri!input,
              expression: a!save(
                fv!item.total,
                if(
                  or(
                    rule!APN_isBlank(
                      fv!item.amount
                    ),
                    rule!APN_isBlank(
                      fv!item.qty
                    )
                  ),
                  fv!item.total,
                  product(
                    fv!item.amount,
                    fv!item.qty
                  )
                )
              )
            ),
          a!save(ri!CashAdvanceRequestData.requester, loggedinuser()),
          a!save(ri!CashAdvanceRequestData.totalApproved,
            sum(
                a!forEach(
                  items: local!input,
                  expression: if(
                    or(
                      rule!APN_isBlank(
                        fv!item.amount
                      ),
                      rule!APN_isBlank(
                        fv!item.qty
                      )
                    ),
                    fv!item.total,
                    product(
                      fv!item.amount,
                      fv!item.qty
                    )
                  )
                )
              )
          ),
          a!save(ri!CashAdvanceRequestData.currentStatus, "Initiated")
          },
          submit: true
        )
      )
    )
  )
)

  Discussion posts and replies are publicly visible

  • Please find the inline answers to the requested queries below:

     

    1. Can I call up data rows that were previously entered into the grid to show up in an editable grid in a later task in the process?

    Ans)Yes you can, if the previously entered data is persisted into the Database (or) at least available in the process variable, then you can populate the previously entered data in a later task.

     

    2a. When a user edits a row, can the data be immediately updated in the database through the CDT?

    Ans) Yes, data can be immediately updated into the database using the smart service function (i.e. either a!writeToDataStoreEntity(), a!writeToMultipleDataStoreEntities(), a!startProcess() which triggers a process to perform DB operation or using Web API Call) under the saveInto of the fields, but i would recommend persisting the changes upon button/task submit via process, to avoid multiple DB call, and also it will be easy to monitor if any issue occurred. However requirement may vary and hence implementation approach can also vary.

    2b. When a user inserts a row, can the data be immediately inserted in the database through in the CDT?

    Ans) Yes, here again the approach can be same as 2a

    2c. When a user deletes a row, can the data be immediately deleted from the database through the CDT?

    Ans) Yes, here again the approach can be same as 2a with only one change, we need to use a!deleteFromDataStoreEntities() smart service function instead of a!writeToDataStoreEntity() or a!writeToMultipleDataStoreEntities()

     

    Also it will be worth checking this link to understand how an editable grid works while performing Create/Update/Delete Operation.

    Also i would recommend you to explore SAIL Recipes in order to understand the core functionality and behavior of SAIL.

     

    Hope this will help you.

  • How we can insert data into CDT from an editable grid which contains integer & textfield data and generated using forEach() method with gridRowLayout() function. Can anyone suggest here because i am new in Appian development.

    My Editable grid Code:

    ######################################################################################

    =a!localVariables(
    local!items: {
    {id:"Core",jan: 0, feb: 0, mar: 0, apr: 0, may: 0, jun:0, jul: 0, aug: 0,sep:0, oct: 0,nov: 0, dec :0},
    {id:"External",jan: 0, feb: 0, mar: 0, apr: 0, may: 0, jun:0, jul: 0, aug: 0,sep:0, oct: 0,nov: 0, dec :0},
    {id:"Internal",jan: 0, feb: 0, mar: 0, apr: 0, may: 0, jun:0, jul: 0, aug: 0,sep:0, oct: 0,nov: 0, dec :0},
    {id:"Material",jan: 0, feb: 0, mar: 0, apr: 0, may: 0, jun:0, jul: 0, aug: 0,sep:0, oct: 0,nov: 0, dec :0},
    {id:"Transmission",jan: 0, feb: 0, mar: 0, apr: 0, may: 0, jun:0, jul: 0, aug: 0,sep:0, oct: 0,nov: 0, dec :0}
    },
    a!gridLayout(
    label: "Forecast",
    headerCells: {
    a!gridLayoutHeaderCell(label: "", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "January", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "February", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "March", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "April", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "May", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "June", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "July", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "August", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "September", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "October", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "November", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "December", align: "RIGHT"),
    a!gridLayoutHeaderCell(label: "Total", align: "RIGHT")
    },
    columnConfigs: {
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW"),
    a!gridLayoutColumnConfig(width: "NARROW")
    },
    rows: {
    a!forEach(
    items: local!items,
    expression: a!gridRowLayout(
    contents: {
    a!textField(
    label: "Core" & fv!index,
    value: fv!item.id,
    saveInto: fv!item.id,
    align: "LEFT",
    readOnly: true
    ),
    a!integerField(
    label: "Jan" & fv!index,
    value: fv!item.jan,
    saveInto: fv!item.jan,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Feb" & fv!index,
    value: fv!item.feb,
    saveInto: fv!item.feb,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Mar" & fv!index,
    value: fv!item.mar,
    saveInto: fv!item.mar,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Apr" & fv!index,
    value: fv!item.apr,
    saveInto: fv!item.apr,
    align: "RIGHT"
    ),
    a!integerField(
    label: "May" & fv!index,
    value: fv!item.may,
    saveInto: fv!item.may,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Jun" & fv!index,
    value: fv!item.jun,
    saveInto: fv!item.jun,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Jul" & fv!index,
    value: fv!item.jul,
    saveInto: fv!item.jul,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Aug" & fv!index,
    value: fv!item.aug,
    saveInto: fv!item.aug,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Sep" & fv!index,
    value: fv!item.sep,
    saveInto: fv!item.sep,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Oct" & fv!index,
    value: fv!item.oct,
    saveInto: fv!item.oct,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Nov" & fv!index,
    value: fv!item.nov,
    saveInto: fv!item.nov,
    align: "RIGHT"
    ),
    a!integerField(
    label: "Dec" & fv!index,
    value: fv!item.dec,
    saveInto: fv!item.dec,
    align: "RIGHT"
    ),
    a!textField(
    label: "Total" & fv!index,
    value: dollar(sum(fv!item.jan, fv!item.feb, fv!item.mar,fv!item.apr,
    fv!item.may,fv!item.jun,fv!item.jul,fv!item.aug,
    fv!item.sep,fv!item.oct , fv!item.nov,fv!item.dec)),
    readOnly: true,
    align: "RIGHT"
    )
    },
    id:fv!index
    )
    ),
    a!gridRowLayout(
    contents: {
    a!richTextDisplayField(
    labelPosition: "COLLAPSED",
    value: a!richTextItem(
    text: "Total",
    style: "STRONG"
    )
    ),
    a!textField(
    value: "$" & sum(local!items.jan) & ".00",
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.feb)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.mar)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.apr)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.may)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.jun)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.jul)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.aug)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.sep)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.oct)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.nov)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    value: dollar(sum(local!items.dec)),
    readOnly: true,
    align: "RIGHT"
    ),
    a!textField(
    /*value: "$" & local!forecast_cost & ".00",*/
    value: dollar(sum(local!items.jan,local!items.feb,local!items.mar,local!items.apr,local!items.may,local!items.jun,
    local!items.jul,local!items.aug,local!items.sep,local!items.oct,local!items.nov,local!items.dec)),
    readOnly: true,
    validations: {if(
    sum(local!items.jan,local!items.feb,local!items.mar,local!items.apr,local!items.may,local!items.jun,
    local!items.jul,local!items.aug,local!items.sep,local!items.oct,local!items.nov,local!items.dec) < 1,
    "You must Enter atleast one Cost",
    ""
    )},
    align: "RIGHT"
    )
    }
    )
    },
    rowHeader: 1
    )
    )

    ######################################################################################################

    ==========================

    CDT FIELDS:

    ID

    JAN

    FEB

    MAR

    APR

    MAY

    JUN

    JUL

    AUG

    SEP

    OCT

    NOV

    DEC

    ==================================