editable Grid with total calculation vertically and horizontally when something in the column changes

Question

i am assigned with a task to calculate the total of records vertically(column wise) and horizontally(row-wise) when a user makes any changes in the text boxes. i have attached a screenshot below. basically, the grid can have n no of rows(to be fetched from the db) but no of columns will remain same always. but for now i'm trying to make this happen with only static values. Future task: is to make paging happen in the same Grid. 
 
 somehow, i am able to solve it for horizontal calculations. but still struggling for vertical calculations.

Chris · Accepted Answer

Here's some sample code that should accomplish what you need. Let me know if we're on track here. 
 For vertical totals, you will want to add one extra row and display the sum() for that data point across all rows. In the a!gridRowLayout this is accomplished by checking for fv!isLast, showing a sum in that case, otherwise showing only the current value for the specific row. 
 For each horizontal total, I display with sum(), then use a!save() within the Submit button so that totals are persisted even if the user does not interact with the row. If we utilize a!save within each component in the row, the total will only be calculated if the user changes one of the values. 
 Note this example does not append a column total row to your CDT, which can be accomplished if you need, but generally I would calculate these values at display time for the user. 
 Additionally, with some extra logic we can add the formatted dollar $ display in the vertical columns by checking for fv!isLast in the grid rows, and showing a rich text component instead of an integer field. 
 Another bonus would be to create a helper rule for the grid fields that chooses the component to display based on the criteria, data point, last row or not, etc, but not needed to get this sample working: 
 a!localVariables(
 local!data: {
 a!map(name: "abc", year: 2022, jan: 12, feb: 21, mar: 21, apr: 21, may: 323, jun: 43, jul: 65, aug: 78, sep: 4, oct: 6, nov: 645, dec: 43, total: null),
 a!map(name: "def", year: 2021, jan: 12, feb: 21, mar: 21, apr: 21, may: 323, jun: 43, jul: 65, aug: 78, sep: 4, oct: 6, nov: 645, dec: 43, total: null),
 a!map(name: "ghi", year: 2020, jan: 12, feb: 21, mar: 21, apr: 21, may: 323, jun: 43, jul: 65, aug: 78, sep: 4, oct: 6, nov: 645, dec: 43, total: null)
 },

 a!formLayout(
 label: "Test Grid",
 contents: {
 a!gridLayout(
 label: "Grid",
 headerCells: a!forEach(
 items: {"Name","Year","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sept","Oct","Nov","Dec","Total Budget"},
 expression: a!gridLayoutHeaderCell(label: fv!item) 
 ),
 rows: {
 a!forEach(
 items: 1+enumerate(count(local!data)+1), /* add an extra row for the column totals with +1 */
 expression: a!gridRowLayout(
 id: fv!index,
 contents: {
 a!textField(
 readOnly: true,
 value: if(fv!isLast,"",local!data[fv!index].name) /* fv!isLast checks are used for total row */
 ),
 a!textField(
 readOnly: true,
 value: if(fv!isLast,"",local!data[fv!index].year)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.jan),local!data[fv!index].jan),
 saveInto: if(fv!isLast,{},local!data[fv!index].jan)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.feb),local!data[fv!index].feb),
 saveInto: if(fv!isLast,{},local!data[fv!index].feb)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.mar),local!data[fv!index].mar),
 saveInto: if(fv!isLast,{},local!data[fv!index].mar)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.apr),local!data[fv!index].apr),
 saveInto: if(fv!isLast,{},local!data[fv!index].apr)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.may),local!data[fv!index].may),
 saveInto: if(fv!isLast,{},local!data[fv!index].may)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.jun),local!data[fv!index].jun),
 saveInto: if(fv!isLast,{},local!data[fv!index].jun)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.jul),local!data[fv!index].jul),
 saveInto: if(fv!isLast,{},local!data[fv!index].jul)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.aug),local!data[fv!index].aug),
 saveInto: if(fv!isLast,{},local!data[fv!index].aug)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.sep),local!data[fv!index].sep),
 saveInto: if(fv!isLast,{},local!data[fv!index].sep)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.oct),local!data[fv!index].oct),
 saveInto: if(fv!isLast,{},local!data[fv!index].oct)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.nov),local!data[fv!index].nov),
 saveInto: if(fv!isLast,{},local!data[fv!index].nov)
 ),
 a!integerField(
 readOnly: fv!isLast,
 value: if(fv!isLast,sum(local!data.dec),local!data[fv!index].dec),
 saveInto: if(fv!isLast,{},local!data[fv!index].dec)
 ),
 a!richTextDisplayField(
 value: {
 a!richTextItem(
 style: "STRONG",
 text: a!localVariables(
 local!index: if(fv!isLast,1+enumerate(count(local!data)),fv!index),
 dollar(
 sum(local!data[local!index].jan,local!data[local!index].feb,local!data[local!index].mar,local!data[local!index].apr,local!data[local!index].may,local!data[local!index].jun,local!data[local!index].jul,local!data[local!index].aug,local!data[local!index].sep,local!data[local!index].oct,local!data[local!index].nov,local!data[local!index].dec)
 )
 )
 )
 }
 )
 }
 )
 )
 }
 )
 },
 buttons: {
 a!buttonLayout(
 primaryButtons: {
 a!buttonWidget(
 label: "Submit",
 submit: true,
 saveInto: {
 a!forEach(
 items: 1+enumerate(count(local!data)),
 expression: {
 /* Use a!save in the submit button to calculate totals, incase the user
 does not interact with the row, hence no total saves there */
 a!save(
 local!data[fv!index].total,
 sum(local!data[fv!index].jan,local!data[fv!index].feb,local!data[fv!index].mar,local!data[fv!index].apr,local!data[fv!index].may,local!data[fv!index].jun,local!data[fv!index].jul,local!data[fv!index].aug,local!data[fv!index].sep,local!data[fv!index].oct,local!data[fv!index].nov,local!data[fv!index].dec)
 )
 }
 )
 }
 )
 }
 )
 }
 )
)

editable Grid with total calculation vertically and horizontally when something in the column changes

Top Replies