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
        )
      )
    )
  )
)