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!
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.
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!
@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
Awesome, I got it, thanks!!
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.
Try creating a new virtual column with the same expression to see a live update.
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.
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.
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!
User | Count |
---|---|
40 | |
36 | |
34 | |
23 | |
17 |