Advance Editable Grid Configurations

Question

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

Hema · Accepted Answer

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

Hema · Answer

Hi Susan, 
 
rule!APN_isBlank checks for null values. You can use isnull() function. 
 
Thanks, 
Hema

Mike Schmitt · Answer

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.

Hema · Answer

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

Vinod Naduvinamani · Answer

HI Susan, 1.create rule Input as CDT 2.For dyanamicall store the multiplication value use product() function 3.For fetching a value from DB create a expression rule with queryEntity and store it in a local variable from that you can make it a local!datasubset 4.For getting Total amount outside the grid use the Foreach function to loop and sum for adding the items 5.And also you can check null values using isnull() funtion I hope above mentioned points helps you to understand Thanks: Vinod S N

Hema · Answer

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. 
 
 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. 
 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! 
 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 . 
 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