How can I create a SUM() of a list of referenced i...

swcrazyfan · 06-22-2020 05:13 PM

I’m a rose breeder, and I’m creating an app to track my pollinations. I’m trying to find a way to create a sum of numbers from a list of referenced items.

Currently, I have 3 tables. Roses, Crosses, and Daily Pollinations. I have each of these reference each other. Currently, whenever I create an item/row in the Daily Pollinations table, I select a Cross and add the number of pollinations for that day. Then, that set of daily pollinations shows up in a list in the Crosses table.

I want to find a way to summarize the total number of pollinations for that cross. For example, if I did 20 pollinations the May 1st and I did 30 pollinations May 2nd, there would be two items. I want to a new column with the combined total of 50.

Hope that makes sense. I’ve been trying to find a way to do this, but I’m still learning all the formulas.

Thanks in advance!

tony1

Hi @swcrazyfan. Your Crosses table should have a virtual column of related rows from your daily pollinations table. You could add a virtual column that looks like this: SUM([Related Pollinations Column][Measurement Column]) .

swcrazyfan

Hey Tony, I did almost exactly what you wrote. The only difference is I create an actual column instead of a virtual one. Airtable is my back-end source, so I’d like to have access to the data through there if I need to. That’s why I didn’t use a virtual column.

So, the exact formula I used is: sum(

SELECT(
[Related Daily Crosses][Number of Pollinations], TRUE
)

)

I’ve included some screenshots of where I have the formula and how it’s not adding the numbers together.

Now, the strangest thing is I have precisely the same setup with a MySQL database using the same formula. I decided to rebuild this with Airtable so I didn’t need a higher AppSheet plan. However, the exact same setup isn’t working. I have no idea why.

Columns and Formula|690x427

Entrenador_Hisp

THIS WORKS.

But it is kind of counterintuitive
Why it dont work with a point between if it is referencing column of other table??

This works:
SUM([Related Pollinations Column][Measurement Column])
This dont:
SUM([Related Pollinations Column].[Measurement Column])

thanks!

tony1

@Entrenador_Hispano There’s a slight difference between them.

[Related Orders][Total] evaluates to an entire column of data. So it will be all of the values of Total for the related orders.

[Related Orders].[Total] … I’m pretty sure that won’t parse. Normally you’d do [Customer].[Email] to get the value of Email for a single customer. But the syntax doesn’t apply to list of refs (like Related Orders). Dereference Expressions | AppSheet Help Center

Entrenador_Hisp

Awesome, I got it, thanks!!

swcrazyfan

Also, when I click the “TEST” button when typing the formula, it works perfectly! However, the value simply doesn’t show up when I load that row. It doesn’t show in the app and doesn’t update in Airtable. So strange.

hru

Try creating a new virtual column with the same expression to see a live update.

swcrazyfan

This is so weird. Why will the virtual column update as I add new rows but the regular column will not? I want the real column’s data to be updated inside my Airtable. A virtual column doesn’t let me save and use the information later on.

hru

By design a virtual column will update after sync, permanent column will update if you edit a record. Try to open the record in a form and save it. A permanent column can be updated using behavior (action/workflow, etc).

swcrazyfan

Hmm, okay. Strangely, it worked perfectly when I built the app with a regular column using MySQL as the table source. With Airtable, it doesn’t do this.

Either way, how can I make it so this column is automatically updated at sync? Is there an article I could look into? I’ve tried to review actions/workflows, but I’m completely at a loss on how to do this…

PS: I tried clicking to edit and just closing the row. It updated like you said. Just want this to be automatic. I know I’ll forget this step lol.

hru

Several references:

https://www.appsheet.com/Template/AppDef?appName=EquipmentInventory-71626#Behavior.Actions

swcrazyfan

I was able to add a button to each Cross that let me update the numbers instead of editing and saving. I wish it could automatically update, but this is good enough for now.

swcrazyfan

Thanks for the links! I’ve gone through all of the multiple times, but I can’t seem to get anything to work.

I’ve tried to create a combination of actions/behaviors based on the links to sent me, but I simply can’t wrap my head around this.

It’s so simple with virtual columns. I feel there should be a simple way to just let me do this. Thanks for your help!

How can I create a SUM() of a list of referenced items