extension like "Summary" that allows for a subtraction?
Airtable has come a long way from when I first started where I had to pair it with Excel to get earnings and cashflow projections for my small freelance business. I now have four extensions that give me data I used to have to calculate with Excel, but I’m missing the fifth that would let me ditch Excel completely, and this extension needs the following formula: Projected Available Next Month - Fixed Number=“Left to Earn (negative)/Rollover (positive).”
I have the first part to get these numbers and then I have the method to use the answer of the equation to redistribute those numbers in AT according to whether I’m short or flush, but I cannot get that middle calculation in AT.
FWIW because my income is so variable, this data lets me even out my earnings and pay myself a regular paycheque. I started doing it manually on paper, then with Excel, then I started in AT and continually improved my base as more features came available. I really want to get to the point that I can ditch Excel and just use AT and my accounting software.
Any thoughts? Thank you!
Rae
Page 1 / 1
Hey @Rae_Crothers!
Would you mind sharing some more details on how your base is set up? What tables do you have? What fields on each table? (high-level)
I have five tables: Queue, Orders, Invoices, Clients, Rates. All tables have a numbers of views that filter using check boxes and single select drop-downs.
I enter a new client order in the Orders table, selecting the client and rate by looking up those tables.
I manage my calendar in the Queue table, where I pull up the order and then track the days, time worked on it, and other information. That data is rolled up in various views in the Order Table.
This is how the extensions get data:
Month Earned=Orders Table, “Current Month” view, sum of the “Earned” field that is a rollup calculation from the Queue table.
Projected Earnings This Month=Orders Table, “Current Month” view, sum of the “Total” field in that table that is a multiplication of the manually entered Estimated Job Length and Rate fields.
Available This Month is the same as 1 but uses a different view with a different month filter
Available Next Month is the same as 2 but uses a different view with a different month filter
I hope this is clear. I’ve been building my AT for years and don’t remember how I put everything together, especially the total rollups as I’ve never been able to get that feature to work again since I first got these calculations working.
Thank you!
Took years with zero help from Airtable and lots of being told that what I want to do isn’t possible in Airtable, but it is possible and I figured this out, and oh, was it satisfying. It really was a puzzle. It was all about starting with really granular data in a table, summarizing that in another table, then summarizing that slightly less granular data in another table, and so on, until I got to the number I needed.
Nobody’s interested in this, so I’m not going to detail it, but the short of it is that I needed to add a field to my Invoice table that would let me do a conditional view that would sum up data from previous tables.
I then needed another table that fed off that conditional view. Using Link, Lookup, Rollup, and Formula fields, I was then able to do that calculation I was missing.
Finally, I added a “Summary” extension for that Table and view that sums up the singular entry.
Not real numbers, but this is what data for a month looks like now. If Short/Over was a positive number, I would go into my Invoices table and reallocate money to June to bring this Short/Over balance as close to zero as possible. I used to get to the “Definitely Available” number in AT, then had to go to Excel to get that Short/Over number, then come back to AT to reallocate funds. So just like that, I no longer need Excel.
