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.
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]) .
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.
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.
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.
Why will the virtual column update as I add new rows but the regular column will not?
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).
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.
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.
@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
