Running Total for Samples

[tvinci]

Hi,

I have two spreadsheets that are related to each other. The delineatedLocations spreadsheet enumerates items and groups them together by the key value in Clipboards.

This means that locations will be grouped together, for example, by key “123” and all show up under the Clipboard 123.

I have a third table named Routes where I want to keep track of the Running Total for Samples in delineated locations. So I tried writing this expression to count how many samples in delineatedLocations correspond to each clipboard:

sum(
select(routesV7[Running Total for Samples],delineatedLocationsV7[Order]=clipboardsV6[key])
)

But this expression sums the samples in every single clipboard. Every route has at least one clipboard and I want the “Running Total for Samples” column to for every route to count all the samples in all the clipboards in all the locations for that route, not all of the samples for every single location in the app.

Does my explanation make sense?

I have three separate tables with parent-child relationships and I want the samples in the grandchild table counted properly so that it corresponds to the correct parent and grandparent tables.

[tony1]

Hi @tvinci. You might want to take a look at this sample app. It shows how to do something similar for a simple dataset: https://www.appsheet.com/samples/This-app-shows-how-to-do-SUMIFs-and-COUNTIFs-in-AppSheet?appGuidStr...

Your expression looks a little funny to me. When you write delineatedLocationsV7[Order] that evaluates to the entire list of values from your Order column. Same for clipboardsV6[key].

A common pattern for select expressions is something like this: SELECT(Other Table[Column], [_THISROW].[Column from current table] = [Column from other table]).

[tvinci]

Thanks @tony

Since there are 3 tables in question which would be the “Other Table” and which would be the “Current Table?”

[Sara_Bertomeu]

hola, creo que esta es la solucion como indica @tony1 

Un patrón común para las expresiones de selección es algo como esto: 

SELECCIONAR(Otra tabla[Columna], [_ESTA FILA].[Columna de la tabla actual] = [Columna de otra tabla]) .

La tabla actual es aquella en la que desea que se muestre el total. La otra tabla tiene los registros relacionados que desea contar.

La mayoría de las veces, la relación entre las tablas se hace explícita a través de una columna Ref.Consulte la tabla Pedido/Detalles del pedido en este ejemplo y cómo se calcula el total:  https://www.appsheet.com/samples/An-app-for-managing-customers-products-and-orders?appGuidString=245...

Gracias

[tony1]

The current table is the one where you want the total displayed. The other table has the related records that you want to count.

I don’t follow your scenario exactly, but the most common case is an Order with Line Items. You get the total for the order by summing over the related line items.

Most of the time, the relationship between the tables is made explicit via a Ref column. See the Order/OrderDetails table in this example and how the total is calculated: https://www.appsheet.com/samples/An-app-for-managing-customers-products-and-orders?appGuidString=245...