• State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 18 replies
  • Answers 2 answers
  • Subscribers 12 subscribers
  • Views 16034 views
  • Users 0 members are here
Related Discussions
Home » Discussions » User Interface

Advance Editable Grid Configurations

susana197
A Score Level 1

Hello everyone,

I'm just looking for some advise on advance editable grid configurations.  See the SAIL code and screenshot below.

I am new to SAIL and have just created the structure of my editable data grid (thanks to Appian documentation).

Now, I am looking for guidance on some advance features:

1. I would like to bind the data source of my editable grid to a CDT.

2. Also looking to dynamically store the result of a multiplication of the qty and amount fields into the Total field.

3. Lastly would like to get the sum of all totals across the rows and save in the AmountTotal field (in a floatingPointField outside the grid).

Thanks in advance.

 

SAIL code

------------

=load(
  /* 
  * local!employess is provided in this recipe as a way to start with hard-coded
  * data. However, this data is identical to the data created from the entity-backed
  * tutorial. Replace the hard-coded data with a query to the employee data store
  * entity and all of the employee records from the tutorial will appear.
  *
  * To replace this data with your own, replace (ctrl+H or cmd+H) all references to
  * local!invoice with your data source, either via rule input or local variable.
  */
  local!invoice: {
      { id: 1, cashAdvnaceId: 1, itemName: "Logitec Mouse" , amount: 74.99 , qty: 3, total: 0.0 },
      { id: 2, cashAdvnaceId: 1, itemName: "Go Pro Camera" , amount: 134.50 , qty: 5 , total: 0.0 },
      { id: 3, cashAdvnaceId: 1, itemName: "Sony Bluetooth Earphones", amount: 34.99 , qty: 10 , total: 0.0, }
  },
  local!runningTotal: 0.0,
 
  a!formLayout(
    label: "Invoice Style",
    contents: {
      a!gridLayout(
        totalCount: count(local!invoice),
        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")
        },
        /*
        * 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.item would change to fv!item.yourdata
        */
        rows: a!forEach(
          items: local!invoice,
          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,
                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,
                refreshAfter: "UNFOCUS",
                validations: {},
                align: "RIGHT"
              ),
              /* For the Total Column*/
              a!floatingPointField(
                label: "Qty " & fv!index,
                labelPosition: "ADJACENT",
                value: fv!item.total,
                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!invoice, remove(local!invoice, 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!invoice, append(local!invoice, save!value))
          }
        )
      ),
      a!sectionLayout(
        label: "",
        contents: {
          a!columnsLayout(
            columns: {
              a!columnLayout(
                contents: {}
              ),
              a!columnLayout(
                contents: {}
              ),
              a!columnLayout(
                contents: {
                  a!floatingPointField(
                    label: "Total Amount",
                    labelPosition: "ADJACENT",
                    saveInto: {},
                    refreshAfter: "UNFOCUS",
                    validations: {},
                    align: "RIGHT"
                  )
                }
              )
            }
          )
        }
      )
    },
    buttons: a!buttonLayout(
      primaryButtons: a!buttonWidget(
        label: "Submit",
        submit: true
      )
    )
  )
)

 

Screenshot

-------------

  Discussion posts and replies are publicly visible

Parents
  • +2

    Hi Susan,

    Please try the below code,

    load(
  local!input: {
    {
      id: 1,
      cashAdvnaceId: 1,
      itemName: "Logitec Mouse",
      amount: 74.99,
      qty: 3,
      total: 0.0
    },
    {
      id: 2,
      cashAdvnaceId: 1,
      itemName: "Go Pro Camera",
      amount: 134.50,
      qty: 5,
      total: 0.0
    },
    {
      id: 3,
      cashAdvnaceId: 1,
      itemName: "Sony Bluetooth Earphones",
      amount: 34.99,
      qty: 10,
      total: 0.0,
      
    }
  },
  /* Use query entity to get data to local!input*/
  with(
    a!formLayout(
      label: "Invoice Style",
      contents: {
        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!columnLayout(
                  contents: {}
                ),
                a!columnLayout(
                  contents: {
                    a!floatingPointField(
                      label: "Total Amount",
                      labelPosition: "ADJACENT",
                      readOnly: true,
                      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: {},
                      refreshAfter: "UNFOCUS",
                      validations: {},
                      align: "RIGHT"
                    )
                  }
                )
              }
            )
          }
        )
      },
      buttons: a!buttonLayout(
        primaryButtons: a!buttonWidget(
          label: "Submit",
          submit: true,
          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
                  )
                )
              )
            )
          }
        )
      )
    )
  )
)

     

    Create a rule input of required CDT type. If the data is stored in DB, get that data into a local variable and display data from that variable into the editable grid. All the modifications done to the data are stored into that local variable.

    On click of Submit button, the data in local variable can be saved into ri.

    Hope this helps!!!

     

    Thanks,

    Hema

  • 0
    A Score Level 1
     in reply to Hema
    Hi Hema,

    Thanks for going through the trouble of sending SAIL code. I really appreciate it. In the code, there is reference to a rule - rule!APN_isBlank Without it, I am unable to run this elegant SAIL code. Are you able to send the definition for this code? If not please let me know what it is doing so I can try to replicate.

    Thanks in advance.
  • +1 in reply to susana197
    Hi Susan,

    rule!APN_isBlank checks for null values. You can use isnull() function.

    Thanks,
    Hema
  • +1
    Certified Lead Developer
     in reply to susana197
    APN_isBlank actually does something slightly more gracefull than isNull -- it also checks to determine whether the input was an empty array, and in that case also returns true. This is necessary to prevent false negatives (and certain cases that will break) if you use the primitive isNull() function.

    Here's the definition I have in one of my environments:
    =or(ri!singlePV="", isnull(ri!singlePV))

    ri!singlePV is a variable of type "Any Type", and can potentially hold a single value or an array.
Reply
  • +1
    Certified Lead Developer
     in reply to susana197
    APN_isBlank actually does something slightly more gracefull than isNull -- it also checks to determine whether the input was an empty array, and in that case also returns true. This is necessary to prevent false negatives (and certain cases that will break) if you use the primitive isNull() function.

    Here's the definition I have in one of my environments:
    =or(ri!singlePV="", isnull(ri!singlePV))

    ri!singlePV is a variable of type "Any Type", and can potentially hold a single value or an array.
Children
  • 0
    A Score Level 1
     in reply to Mike Schmitt
    Thanks Mike, this was very helpful!
  • 0
    A Score Level 1
     in reply to Mike Schmitt
    Hi Mike,

    Here is a follow up question on this. I have succeeded in getting the form to work as intended. However, saving the data into the DB is another story. There is a foreign key in the CDT and it's making it a bit tricky to save the array of CDTs I am getting from my editable grid.

    I am using two "Write to Data Store Entity" smart services. One to save the CDT having the foreign key so I have the ID. The second Write to Data Store Entity then saves the values from my Editable Grid.

    When I configure each element of the node individually, only the first row from the data grid is saved.

    However, when I configure each child to be saved with the parent, then I can select the "Multiple" checkbox, but I have no way of specifying the input for the foreign key individually.

    Do you have any tips?

    Thanks in advance.
  • 0 in reply to susana197

    Hi Susan,

    As far as I understand, there are 2 tables, consider table A and table B. One column from A should be mapped to a column in B and the data in table B is the data from the grid.

    1. If the column you are trying to write to table B have the same(single) value, repeat() function can be used. Insert a script task in between the two Write to DB nodes. In the output tab of the script task, write the code as repeat(count(ri!B),ri!A.columnName). This should be saved to ri!B.columnName.

    2. If the column you are trying to write to table B have different values, based on the relation between them, logic can be configured in the script task and can be saved to column in table B. If this is the case, can you share the tables structure and relation so that it would help us figure out the logic!

    3. A stored procedure can also be written and can be executed through "Execute Stored Procedure" smart service. Please find the link to the same in app market here.

    4. Writing a trigger on table B will also help to achieve this. The data from the grid can be saved through Write to DB Smart Service and an Insert trigger. Note: If triggers are used, the values set by your triggers do not populate in the 'StoredValue' output of the Write to Data Store node.

    If you are trying to write both the tables at the same time using Write to multiple DB smart services, that won't be possible.

     

    Thanks,

    Hema

  • 0
    A Score Level 1
     in reply to Hema
    Hi Hema,

    Thank you very much again for responding. Here below are the technical details of the situation.

    In my form, I have two inputs (different CDTs) as explained below:

    CDT 1) (Single item)
    Cash Advance
    -----------------------------
    id,
    requester,
    date,
    category,
    subCategory,
    department,
    justification,
    priority,
    type,
    onBehalfOf,
    currentStatus,
    totalRequested,
    totalApproved,
    attachment

    CDT 2) - (Multiple items)
    Request Line Items
    ---------------------------
    id,
    cashAdvnaceId,
    itemName,
    amount,
    qty,
    total

    The first CDT captures information about the entire case.

    The second CDT collects a number of rows (using the editable grid - multiple items).

    I first save the fields into the CDT and obtain the autogenerated id (using the output) because I need the id to insert into CDT 2. I am using a "Write to Data Store Entity" Smart Service to do this, and this part is working ok.

    I will then need to pass the id field from CDT 1 to each of the cashAdvnaceId fields in CDT 2. CDT 2 has many rows (depending on how many rows the user created in the editable grid). I have a second "Write to Data Store Entity" I am trying to configure for this and having issues with the fact that there are multiple rows that I need to pass the id value from the first CDT into.

    Thanks,
  • +1 in reply to susana197

    Hi Susan,

    In this case, you can add a script task in between the two Write to Data Store Entity Smart Services. In the output tab of the script task, add a new output and configure the logic as repeat(count(pv!cdt2),pv!cdt1.id) and save this to pv!cdt2.cashAdvanceId.

    This logic writes the value of id from cdt1 to each of the cashAdvanceId field in cdt2. Then, for the second Write to Data Store Entity Smart Service, cdt2 can be passed.

    Hope this helps!

    Thanks,

    Hema

  • 0
    A Score Level 1
     in reply to Hema
    Wow,

    Yes, I think this will work.

    Are you able to send me the syntax of this script?
  • 0 in reply to susana197

    Hi Susan,

    Please find the below images,

    The above image shows the high level diagram of the process model.

    The above attached image has the script task configuration. pv!cdt1 is the name of the process variable of type first CDT and pv!cdt2 is the name of the process variable of type second CDT. The target should be pv!cdt2.cashAdvanceId.

    Hope it helps!

     

    Thanks,

    Hema

  • 0
    A Score Level 1
     in reply to Hema

    Thanks again for looking into this for me.

    I just did as in the screenshot below but the id value from CDT1 is not getting saved in the cashAdvnaceId fields of CD2.  In the process monitor, I see that all cashAdvnaceId fields have null values.

     

  • 0 in reply to susana197
    Could you verify if pv!CashAdvanceRequestData.id has a value?
  • 0
    A Score Level 1
     in reply to Hema

    Yes it has a value - 6.   I also noticed that the id fields of CDT2 also have values - 1, 2, 3 etc.  See screenshots below:

     

© 2024 Appian. All rights reserved.