SAIL help - Invoice Style Editable Data Grid

Question

Hi Everyone, 
 I am trying to create an invoice style editable grid (see image below). I can find SAIL examples (in Appian documentation) on how to create editable grids but I'm still very new to SAIL and struggling with aspects such as saving grid data to my CDT, inserting new rows into my CDT, getting the sum of the total column to dynamically update when other grid values are changed etc. 
 My customer datatype has - id (autoNumber), item (decimal), qty (integer), amount (decimal), total (decimal) 
 Does someone have an idea and can send a code snippet I can use? 
 Thanks.

swarajd0001 · Accepted Answer

load(
 /* 
* local!data 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 data store
* entity and all of the data records from the tutorial will appear.
*
* To replace this data with your own, replace (ctrl+H or cmd+H) all references to
* Local!data with your data source, either via rule input or local variable.
*/

 Local!data: {
 {
 id: 1,
 Item: "Dell laptop",
 Quantity: 2,
 Amount: 865.00,
 Total: _
 },
 {
 id: 2,
 Item: "Air Mouse",
 Quantity: 3,
 Amount: 43.00,
 Total: _
 },
 {
 id: 3,
 Item: "HDMI Converter",
 Quantity: 1,
 Amount: 75.00,
 Total: _
 },
 
 },
 a!formLayout(
 label: " Invoice Style Editable Data Grid",
 contents: {
 a!gridLayout(
 totalCount: count(
 Local!data
 ),
 headerCells: {
 a!gridLayoutHeaderCell(
 label: ""
 ),
 a!gridLayoutHeaderCell(
 label: ""
 ),
 a!gridLayoutHeaderCell(
 label: ""
 ),
 a!gridLayoutHeaderCell(
 label: ""
 ),
 /* 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: "ICON"
 )
 },
 /*
* a!forEach() will take local!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!gridRowLayout(
 contents: { 
 a!richTextDisplayField(
 value: a!richTextItem(
 text: "Item",
 style: "STRONG",
 color:"#10c3f0"
 )
 ),
 a!richTextDisplayField(
 value: a!richTextItem(
 text: "Quantity",
 style: "STRONG",
 color:"#10c3f0"
 )
 ), a!richTextDisplayField(
 value: a!richTextItem(
 text: "Amount",
 style: "STRONG",
 color:"#10c3f0"
 )
 ), a!richTextDisplayField(
 value: a!richTextItem(
 text: "Total",
 style: "STRONG",
 color:"#10c3f0"
 )
 ), 
 a!richTextDisplayField(
 value: a!richTextItem(
 text: "Remove",
 style: "STRONG",
 color:"#10c3f0"
 )
 )
 
 
 }
 ),
 a!forEach(
 items: Local!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: "Item " & fv!index,
 value: fv!item.Item,
 saveInto: fv!item.Item,
 required: true
 ),
 /* For the Last Name Column*/
 a!textField(
 label: "Quantity " & fv!index,
 value: fv!item.Quantity,
 saveInto: fv!item.Quantity,
 required: true
 ),
 /* For the Amount Column*/
 a!textField(
 label: "Amount " & fv!index,
 value: fv!item.Amount,
 saveInto: fv!item.Amount,
 required: true
 ),
 /* For the Total Column*/
 a!textField(
 label: "Total " & fv!index,
 readOnly: true(),
 value: if(
 or(
 isnull(
 fv!item.Quantity
 ),
 isnull(
 fv!item.Amount
 )
 ),
 fv!item.Total,
 product(
 fv!item.Amount,
 fv!item.Quantity
 )
 ),
 saveInto: fv!item.Total,
 required: true
 ),
 /* For the Phone Number Column*/
 /* For the Removal Column*/
 a!imageField(
 label: "delete " & fv!index,
 images: a!documentImage(
 document: a!iconIndicator(
 "REMOVE"
 ),
 altText: "Remove Item",
 caption: "Remove " & fv!item.Item & " " & fv!item.Quantity,
 link: a!dynamicLink(
 value: fv!index,
 saveInto: {
 a!save(
 Local!data,
 remove(
 Local!data,
 save!value
 )
 )
 }
 )
 ),
 size: "ICON"
 )
 },
 id: fv!index
 )}
 )},
 addRowlink: a!dynamicLink(
 label: "Add Item",
 /*
* For your use case, set the value to a blank instance of your CDT using
* the type constructor, e.g. type!data(). 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!data,
 append(
 Local!data,
 save!value
 )
 )
 }
 )
 ),
 a!textField(
 readOnly: true
 ),
 
 
 a!gridField(
 label: "",
 totalCount: count(
 Local!data
 ),
 columns: {
 a!gridTextColumn(
 label: "Total Count",
 data: "",
 alignment: "LEFT"
 ),
 a!gridTextColumn(
 label: sum(
 a!forEach(
 items: Local!data,
 expression: if(
 or(
 rule!APN_isBlank(
 fv!item.Quantity
 ),
 rule!APN_isBlank(
 fv!item.Amount
 )
 ),
 fv!item.Total,
 product(
 fv!item.Quantity,
 fv!item.Amount
 )
 )
 )
 ),
 
 alignment: "LEFT"
 ),
 
 },
 value: a!pagingInfo(
 startIndex: 1,
 batchSize: 5,
 sort: a!sortInfo(
 field: "name",
 ascending: true
 )
 )
 )
 },
 buttons: a!buttonLayout(
 primaryButtons: a!buttonWidgetSubmit(
 label: "Submit"
 )
 )
 )
 
) Hi susana.. 
 please find the the below code ... 
 
 load(
 /* 
* local!data 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 data store
* entity and all of the data records from the tutorial will appear.
*
* To replace this data with your own, replace (ctrl+H or cmd+H) all references to
* Local!data with your data source, either via rule input or local variable.
*/

 Local!data: {
 {
 id: 1,
 Item: "Dell laptop",
 Quantity: 2,
 Amount: 865.00,
 Total: _
 },
 {
 id: 2,
 Item: "Air Mouse",
 Quantity: 3,
 Amount: 43.00,
 Total: _
 },
 {
 id: 3,
 Item: "HDMI Converter",
 Quantity: 1,
 Amount: 75.00,
 Total: _
 },
 
 },
 a!formLayout(
 label: " Invoice Style Editable Data Grid",
 contents: {
 a!gridLayout(
 totalCount: count(
 Local!data
 ),
 headerCells: {
 a!gridLayoutHeaderCell(
 label: ""
 ),
 a!gridLayoutHeaderCell(
 label: ""
 ),
 a!gridLayoutHeaderCell(
 label: ""
 ),
 a!gridLayoutHeaderCell(
 label: ""
 ),
 /* 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: "ICON"
 )
 },
 /*
* a!forEach() will take local!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!gridRowLayout(
 contents: { 
 a!richTextDisplayField(
 value: a!richTextItem(
 text: "First name",
 style: "STRONG",
 color:"#10c3f0"
 )
 ),
 a!richTextDisplayField(
 value: a!richTextItem(
 text: "Item",
 style: "STRONG",
 color:"#10c3f0"
 )
 ), a!richTextDisplayField(
 value: a!richTextItem(
 text: "Quantity",
 style: "STRONG",
 color:"#10c3f0"
 )
 ), a!richTextDisplayField(
 value: a!richTextItem(
 text: "Amount",
 style: "STRONG",
 color:"#10c3f0"
 )
 ), a!richTextDisplayField(
 value: a!richTextItem(
 text: "Total",
 style: "STRONG",
 color:"#10c3f0"
 )
 )
 
 
 }
 ),
 a!forEach(
 items: Local!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: "Item " & fv!index,
 value: fv!item.Item,
 saveInto: fv!item.Item,
 required: true
 ),
 /* For the Last Name Column*/
 a!textField(
 label: "Quantity " & fv!index,
 value: fv!item.Quantity,
 saveInto: fv!item.Quantity,
 required: true
 ),
 /* For the Amount Column*/
 a!textField(
 label: "Amount " & fv!index,
 value: fv!item.Amount,
 saveInto: fv!item.Amount,
 required: true
 ),
 /* For the Total Column*/
 a!textField(
 label: "Total " & fv!index,
 readOnly: true(),
 value: if(
 or(
 isnull(
 fv!item.Quantity
 ),
 isnull(
 fv!item.Amount
 )
 ),
 fv!item.Total,
 product(
 fv!item.Amount,
 fv!item.Quantity
 )
 ),
 saveInto: fv!item.Total,
 required: true
 ),
 /* For the Phone Number Column*/
 /* For the Removal Column*/
 a!imageField(
 label: "delete " & fv!index,
 images: a!documentImage(
 document: a!iconIndicator(
 "REMOVE"
 ),
 altText: "Remove Item",
 caption: "Remove " & fv!item.Item & " " & fv!item.Quantity,
 link: a!dynamicLink(
 value: fv!index,
 saveInto: {
 a!save(
 Local!data,
 remove(
 Local!data,
 save!value
 )
 )
 }
 )
 ),
 size: "ICON"
 )
 },
 id: fv!index
 )}
 )},
 addRowlink: a!dynamicLink(
 label: "Add Item",
 /*
* For your use case, set the value to a blank instance of your CDT using
* the type constructor, e.g. type!data(). 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!data,
 append(
 Local!data,
 save!value
 )
 )
 }
 )
 ),
 a!textField(
 readOnly: true
 ),
 a!gridField(
 label: "",
 totalCount: count(
 Local!data
 ),
 columns: {
 a!gridTextColumn(
 /*label: "Total Count",*/
 data: "Total Count",
 alignment: "LEFT"
 ),
 a!gridTextColumn(
 /*label: "Total",*/
 data: {
 sum(
 a!forEach(
 items: Local!data,
 expression: if(
 or(
 rule!APN_isBlank(
 fv!item.Quantity
 ),
 rule!APN_isBlank(
 fv!item.Amount
 )
 ),
 fv!item.Total,
 product(
 fv!item.Quantity,
 fv!item.Amount
 )
 )
 )
 )
 },
 alignment: "LEFT"
 ),
 
 },
 value: a!pagingInfo(
 startIndex: 1,
 batchSize: 5,
 sort: a!sortInfo(
 field: "name",
 ascending: true
 )
 )
 )
 },
 buttons: a!buttonLayout(
 primaryButtons: a!buttonWidgetSubmit(
 label: "Submit"
 )
 )
 )
 
)

rajesh kilaru · Answer

Use the following code Which will Do exactly same as your mock up If the data is coming from the DB table please query accordingly. 
 
Hope it helps. 
 
Thank you! 
Rajesh Kilaru

=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!employees with your data source, either via rule input or local variable. 
 */ 
 local!employees: { 
 { id: 1, firstName: "Dell laptop" , lastName: 2 , department: 865.00, title: 1730 , phoneNumber: "100000" , startDate: today()-360 }, 
 { id: 2, firstName: "Air Mouse" , lastName: 3 , department: 43.00 , title: 129, phoneNumber: "65000" , startDate: today()-360 }, 
 { id: 3, firstName: "HDMI Converter", lastName: 1 , department: 75.00 , title: 75, phoneNumber: "85000" , startDate: today()-240 }, 
 }, 
 a!formLayout( 
 label: "Example: Add,Update, or Remove Employee Data", 
 contents: { 
 a!gridLayout( 
 totalCount: count(local!employees), 
 headerCells: { 
 a!gridLayoutHeaderCell(label: "Item" ), 
 a!gridLayoutHeaderCell(label: "Quantity" ), 
 a!gridLayoutHeaderCell(label: "Amount" ), 
 a!gridLayoutHeaderCell(label: "Total" ), 
 
 /* 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: "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: local!employees, 
 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!textField( 
 label: "last name " & fv!index, 
 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*/ 
 
 /* For the Removal Column*/ 
 a!imageField( 
 label: "delete " & fv!index, 
 images: a!documentImage( 
 document: a!iconIndicator("REMOVE"), 
 altText: "Remove Employee", 
 caption: "Remove " & fv!item.firstName & " " & fv!item.lastName, 
 link: a!dynamicLink( 
 value: fv!index, 
 saveInto: { 
 a!save(local!employees, remove(local!employees, save!value)) 
 } 
 ) 
 ), 
 size: "ICON" 
 ) 
 }, 
 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(local!employees, append(local!employees, save!value)) 
 } 
 ) 
 ),

a!textField( 
 
 readOnly: true 
 ), 
 a!gridField( 
 label: "Employees", 
 totalCount: 20, 
 columns: { 
 a!gridTextColumn( 
 /*label: "Total Count",*/ 
 data: "Total Count", 
 alignment: "LEFT" 
 ), 
 a!gridTextColumn( 
 /*label: "Title",*/ 
 data: {sum(local!employees.title)}, 
 alignment: "LEFT" 
 ), 
 
 }, 
 value: a!pagingInfo( 
 startIndex: 1, 
 batchSize: 5, 
 sort: a!sortInfo( 
 field: "name", 
 ascending: true 
 ) 
 ) 
 ) 
 
 }, 
 buttons: a!buttonLayout( 
 primaryButtons: a!buttonWidgetSubmit( 
 label: "Submit" 
 ) 
 ) 
 ) 
)

Deepa_M · Answer

Go over this link: https://docs.appian.com/suite/help/18.2/recipe_add_edit_and_remove_data_in_an_inline_editable_grid.html 
 It will give you clear idea on how to work with editable grids.

susana197 · Answer

Thanks Deepa, 
 
This has taken me far enough is getting started. I will make another post with the specific advance features I need more clarity on.