Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 10, 2017 11:54 AM
I’m a bit lost when it comes to formulas. I was hoping someone would be able to help me out! I set up my personal budget with Airtable as you can see with the linked photos.
I have one table for my budget. This has a category column to show what/where money is being spent, as well as, my ideal budget and a column for how much I actually spent.
The second table is for what I spent money on.
Is it possible to total what I spent, grouped by category, then get the sum but only with the same category and send that sum to my “budget” table column “spent”? Then I will be able to track my ideal budget next to what I actually spent. I will be tracking the spends with a form view to add receipts, total’s and category whenever I spend money.
Jan 11, 2017 02:47 AM
The way to do this is to use a Rollup field in the Budget table - something like this:
Looking at your question, I wondered whether your Budget is actually a monthly budget and so I thought I would add how you could also show a total for the current month.
This requires a calculated field in the Spends table (and a date field):
You can then use another Rollup field in the Budget table to show you a total for the current month - and your tables would look like this:
Spends
Budget
I hope this helps.
Julian
Jan 11, 2017 09:22 AM
Julian, thank you for helping! The Rollup field was something I didn’t know about and know I’m so thankful you explained it! Thank you for that. The only thing I couldn’t figure out was the Formula section. Now that I think about what I need, this would be very helpful.
With the current Rollup it will just keep totaling all the receipts and I will need to manually archive them after each month. The way you suggested would allow me to send the “Spend This Month” to keep track of every receipt purchase and only send that month’s spends to my Budget table. This will allow “Budget” to remain as a monthly only view. Does that make sense?
Do I need to add details into the Formula you supplied?
Jan 12, 2017 01:32 AM
Mikey, you should be good to go with this formula. It would also be possible (with a slightly more complex formula) to do a calculation for any month - for example you could have a last month column.
Jan 12, 2017 06:13 AM
How did you learn how to write the formulas? Any videos you recommend or learning tools?
Jan 12, 2017 06:20 AM
This is a good place to start:
Actually, the documentation is pretty good.
Jan 12, 2017 06:32 AM
Thank you! I’m going to try out the formula you sent again. I’m sure I wasn’t connecting all the dots. Can I send you a free coffee?
Mar 03, 2017 10:45 AM
Hey!
Apologies for not seeing this earlier! We recently made a video with an introduction for formulas on Airtable if you’re interested: https://vimeo.com/200915169 :coffee: ️
Hope it’s helpful!
Cheers,
Jane