LinkedIn
Twitter
“Grouping” function of Appsheet will display the sum, average etc by the designated group / set of rows by default, But it could be common requirement for Appsheet creators to add row which display sum/average of the particular columns inside the table/deck or other type of views. In this demo, I will use the “table” view and explain how to add one rows which will display the sum of all the rows in a table. This tricks can be extended to display sub total per designated group/ or set of rows, as well as showing average, mean whatsoever you may require.
Sample shot
Step 1 - Add physical row
Either ID / key column or any other column you can use and feel confortable such as Category, or Group whatever, please add physical row. In sample app with screenshot (gif), I made a row with value of “Total” in the column named “Category”.
Step 2 - Add actions
Create new actions. Simply “Set the value of some column in this row” type action, to set the colmn value to total with expression. In this sample app, I prepared col, col2 fields both contain the numbers. In this sample, I attemped to show the rows total across a table, so the expression for action should be
Sum(
Select(TableName[Col], [Category]<>Total))
)
As you can see the expression itself, it will run a sum of col except for the “total” category row.
Step 3 - Add actions cont.
Add another action, which will Execute action to set of rows type. The reference table is the self/ own table, and referenced rows with expression like
select(TableName[ID],[Category]=“Total”)
Referenced action is one set by Step 2
Step 4 - Add actions cont.
Repeat Step 2 and 3 for col 2.
Now we have 4 actions
Step 5 - Add actions cont.
Create new action, Execute sequence of actions. Select 2 x “execute action to set of row” types you cread for col, col2 through the steps upto now. This single action will invoke 2 x actions.
Step 6 - Fire action on save
Fire action which you created in step 5, on save of form of this row.
Step 7 - Fire action thorugh the workflow
Create new workflow on this table, and fire “ANY/ALL THE TIME” when row is created, updated. And fire action which was generated in step 5.
Step 8 - Protect column
For Col, Col2, protect the column for edit by user. We dont want user to change the value. Add editif constrain with expression , [Category]<>Total .
Once the total, sub total row are created, apply the format rule to those rows such as text with bold, slightly larger font size so that the rows stands out.
–
Once the row is added, or updated, the actions will fired and update the row with category value of total all the time.
Action will change the value (sum) based on the login users, which is usually fine. But when the app is shared, it is possible other may add new rows without knowing by the current users. In that case, the action may result in the sum values which are not taking into accoun the rows which is added behind the scene by other users.
In the above step, on Step 6, I set the action “on save” but you can omit this step, but fire action on workflow only. This means, to see the sum value, it may take a bit of time as sync always take a bit, but it should not be a issue as far as we are able to pull the right / correct sum number. Workflow is always working on the backend, readling the bakend raw data, and return the value to the client. Firing the actions through the workflow should be best solutions to stay precise in terms of calculation.
Once row in a table is added, updated or deleted, whatever interaction by user happens, workflow fire acion and update “value” for total, sub total all the time. That s the core of this trick.
–
Extension of use of the tricks.
This trick can be easily extended to add “sub total” or even global average, average by group, sub of the set of group, or across the table whatsoever.
For instance, if you want to add “sub total” to this sample app, then you add another physical row with “Group N Sub total” to category fields. And basically fire the action to append the value to this row with the twisted sum expression such as sum by a ceratain group.
If you need grand / sub average of the set of the rows, simply change the expression to “average” instead of “sum”.
If we dont know what sort of category, grouping will be there before the app is in operation, it is impossible to prepare the physical rows with predefined values. In that case, change the approach a bit. Rather than distinguish normal row or rows for total/sub total in the normal field such as category field in this case, then employ the ID fields. For instance, Grand total row ID value is set to 1GrandTotal", Sub total fields is combined strings, whatsoever. Then prepare the action, not change the value of this row, but add row using value from this row type. When we generate new row through the action, we pass the value of ID, such as “GrandTotal” value, for total value, likewise value for certain group as ID. If no pre existing row is there, then new total/sub total row will be added. It is possible the actions fired the user will create new row with the same ID, but it is designated. For enstance, I add row with category A, then i will create the 2 rows, one for grand total, second for sub total.
THen i will do the same, create new rows, one for grandtotal, other for group total. But each new rows will have same ID value. In case, we apply the add new row to another table, but pass the ID value, Appsheet is wise enough, it will overwrite the existing rows, meaning, it looks like the Apps is “updating” existing rows.
This another trick should be demonstrating “Almost anything possible with Appsheet”
methodology…
