Table needs to be able to run SUMs across rows and down columns

Question

I am new to Appian and am looking for sample code snippets to add values across rows and columns. I have added two more columns called salary and bonus and want to have a third column that shows the total of salary and bonus. 
 At the same time, I only want a row that can keep track of running sum of all the salary, bonus and total columns. 
 
 Attached is the sample code that shows the three new columns. 
 
 https://docs.appian.com/suite/help/17.3/recipe_add_edit_and_remove_data_in_an_inline_editable_grid.html

/cfs-file/__key/communityserver-discussions-components-files/11/Sample-SAIL-Add-Rows-Columns.txt 
 Thanks

Chris · Answer

Here's some sample code updated for totaling, based on the recipe above. Note the Salary and Bonus fields have additional a!save()'s to update a new local!employees.total value, for the sum on each row. There are additional saves to update a local!total variable with the overall total value. Some logic is also added to default values to 0 if they are cleared - you can also allow nulls but any sum() call will have to be modified to replace nulls with 0 to avoid errors. 
 Another option for the totaling is to remove the sum() calls on the salary and bonus fields, and the Total column will perform the sum() within the rich text field for display - in which case the calculations are moved to the Submit button saveInto parameter. If you do not need to store the totals (it is possible to always calculate them at display time), those could be removed (few ways to do it). 
 Note the overall total is displayed by appending an a!gridRowLayout() with blank fields except for the total column. 
 =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: "John" , lastName: "Smith" , department: "Engineering" , title: "Director" , phoneNumber: "555-123-4567" , startDate: today()-360, salary: 0, bonus: 0, total: 0 },
 { id: 2, firstName: "Michael" , lastName: "Johnson" , department: "Finance" , title: "Analyst" , phoneNumber: "555-987-6543" , startDate: today()-360, salary: 0, bonus: 0, total: 0 },
 { id: 3, firstName: "Mary", lastName: "Reed" , department: "Engineering" , title: "Software Engineer" , phoneNumber: "555-456-0123" , startDate: today()-240, salary: 0, bonus: 0, total: 0 },
 },
 local!total,
 a!formLayout(
 label: "SAIL Example: Add,Update, or Remove Employee Data",
 contents: {
 a!gridLayout(
 totalCount: count(local!employees),
 headerCells: {
 a!gridLayoutHeaderCell(label: "First Name" ),
 a!gridLayoutHeaderCell(label: "Last Name" ),
 a!gridLayoutHeaderCell(label: "Department" ),
 a!gridLayoutHeaderCell(label: "Title" ),
 a!gridLayoutHeaderCell(label: "Phone Number" ),
 a!gridLayoutHeaderCell(label: "Start Date", align: "RIGHT" ),
 a!gridLayoutHeaderCell(label: "Salary", align: "RIGHT" ),
 a!gridLayoutHeaderCell(label: "Bonus", 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:3 ),
 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: "DISTRIBUTE", weight:2 ),
 a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight:2 ),
 a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight:2 ),
 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.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!dropdownField(
 label: "department " & fv!index,
 placeholderLabel: "-- Select -- ",
 choiceLabels: { "Corporate", "Engineering", "Finance", "Human Resources", "Professional Services", "Sales" },
 choiceValues: { "Corporate", "Engineering", "Finance", "Human Resources", "Professional Services", "Sales" },
 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*/
 a!textField(
 label: "phone number " & fv!index,
 placeholder:"555-456-7890",
 value: fv!item.phoneNumber,
 saveInto: fv!item.phoneNumber
 ),
 /* For the Start Date Column*/
 a!dateField(
 label: "start date " & fv!index,
 value: fv!item.startDate,
 saveInto: fv!item.startDate,
 required:true,
 align: "RIGHT"
 ),
 a!floatingPointField(
 label: "salary " & fv!index,
 value: fv!item.salary,
 saveInto: {
 a!save(fv!item.salary,if(rule!APN_isEmpty(save!value),0,save!value)),
 a!save(fv!item.total,sum(fv!item.salary,fv!item.bonus)),
 a!save(local!total,sum(local!employees.salary,local!employees.bonus))
 },
 required:true,
 align: "RIGHT"
 ),
 a!floatingPointField(
 label: "bonus " & fv!index,
 value: fv!item.bonus,
 saveInto: {
 a!save(fv!item.bonus,if(rule!APN_isEmpty(save!value),0,save!value)),
 a!save(fv!item.total,sum(fv!item.salary,fv!item.bonus)),
 a!save(local!total,sum(local!employees.salary,local!employees.bonus))
 },
 required:true,
 align: "RIGHT"
 ),
 a!richTextDisplayField(
 align: "RIGHT",
 value: {
 a!richTextItem(
 text: "$",
 style: "STRONG"
 ),
 a!richTextItem(
 text: fv!item.total,
 style: "STRONG"
 ),
 }
 ),
 /* 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
 )
 ),
 a!gridRowLayout(
 contents: {
 a!textField(readOnly: true),
 a!textField(readOnly: true),
 a!textField(readOnly: true),
 a!textField(readOnly: true),
 a!textField(readOnly: true),
 a!textField(readOnly: true),
 a!textField(readOnly: true),
 a!textField(readOnly: true),
 a!richTextDisplayField(
 align: "RIGHT",
 value: {
 a!richTextItem(
 text: "$",
 style: "STRONG",
 color: "POSITIVE"
 ),
 a!richTextItem(
 text: local!total,
 style: "STRONG",
 color: "POSITIVE"
 ),
 }
 ),
 a!textField(readOnly: true)
 }
 )
 },
 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))
 }
 )
 )
 },
 buttons: a!buttonLayout(
 primaryButtons: a!buttonWidgetSubmit(
 label: "Submit"
 )
 )
 )
)