How to sum values from Referenced Rows in AppSheet

Introduction

Google AppSheet offers powerful tools for non-coders to build custom applications that leverage such capabilities.

One common requirement is to sum values from rows that are related or referenced by another table or column.

This blog explores how to use AppSheet expressions to accomplish this.

Example

Consider a scenario where you manage a warehouse and use an AppSheet application to track goods receipts. Each receipt in your `Goods Receipt` table has multiple associated entries in the `GRN Items` table, detailing items received, their quantities, and other specifics.

To see the total quantity received per receipt directly within your receipt view, you need to sum the `Quantity Received` from the `GRN Items` table based on a matching `Good Receipt Id`.

Expression

To achieve this, AppSheet provides a straightforward yet powerful expression:

SUM(SELECT(GRN Items[Quantity Received], [Good Receipt Id] = [_THISROW].[Good Receipt Id]))

Here’s a breakdown:
– `SUM()`: This function sums up all the values returned by the `SELECT()` function.
– `SELECT()`: This function filters and returns a list of values from a specified column. In our case, it fetches `Quantity Received` from rows in `GRN Items` where the `Good Receipt Id` matches the `Good Receipt Id` of the current row being evaluated (`[_THISROW].[Good Receipt Id]`).

Other Use Cases

The approach of summing values from referenced rows is versatile and can be applied across various scenarios in AppSheet. Here are a few more use cases:
Calculating Total Sales: For businesses tracking sales through invoices and line items, sum the `Amount` column in a `Sales Line Items` table where the `Invoice Id` matches.
Project Management: Summing hours logged against tasks in a project to provide real-time updates on labor costs.
Inventory Management: Aggregate total stock from multiple warehouse entries to maintain an accurate count of available inventory.