Carry forward a total?

RGCreative · ‎Jun 21, 2021

My base records trading days and transactions for a small visitor attraction that is open a few days each week.

There are two tables -
Trading days… which records the days open and the visitor numbers.
Income/Expenditure… which records any cash transactions linked to the trading days.

Each transaction is a positive or negative cashflow amount. I roll up the cashflow in a column in the Trading Days table, and can thus report on the monthly cashflow through a grouped view. If income is £10 more than outgoings, the total of the rollup is £10… that’s the “cash on hand”.

What’s missing is the ability to show the previous month’s closing “cash on hand” anywhere - which is the starting point for this month of course. It’s effectively the total of the cashflow column in I/E view grouped by month and limited to last month’s entries.

So I want to rollup the PREVIOUS month’s cashflow column into a column called “CARRIED FORWARD”. I know, this will show the same figure in every record created this month - because last month’s total is fixed of course, as there won’t be any new entries. The column header can show the AVERAGE so it’s just the fixed amount.

We can then have a view filtered by month, and show a monthly cashflow report whose “cash on hand” makes sense because it accounts for the non-zero starting point.

My brain is fried, I’ve gone down several blind alleys, and can’t work it out although it seems logically simple enough. Any ideas?

Justin_Barrett · ‎Jun 27, 2021

Create a [Months] table, with each record name indicating the month and year for clarity; e.g. May 2021, June 2021, etc.

Link each record in [Trading Days] to the appropriate month record from [Months]. (Hint: to make this easier, make a view in [Trading Days] that groups by this new linked record field. Adding a record to the group automatically builds the link.)

Create a rollup field in [Months] that rolls up all of the daily totals, which will give you a monthly total.

Also in the [Months] table, add a {Previous Month} link field to link to records in the same table. When you create the next months’ record, manually link this to the previous month’s record. Add a lookup field that lets you look up the previous month’s total using this link.

If you want to see this previous month total in [Trading Days], add a lookup field there to pull it in. Because each day’s record is tied to a month, it has access to that month’s details, including the total from the previous month.

RGCreative · ‎Jun 27, 2021

Thank you for preventing the meltdown of my brain!

I was SO close.

This: " add a {Previous Month} link field to link to records in the same table." was the thing I didn’t come up with. Everything else was there, but I didn’t explore linking to the same table. I even read some examples of doing that but they were for different purposes and I still didn’t twig it. Duh.

Have a great day! :beers:

Justin_Barrett · ‎Jun 27, 2021

@RGCreative Glad to know that you got the answer you were seeking! If you would, please mark my comment (the one above, not this one) as the solution to your question. This helps others who may be searching with similar questions. Thanks!

RGCreative · ‎Jun 27, 2021

I’m within a whisker of the full thing, but I realise not quite there because…

The aim “show the previous month’s closing “cash on hand” anywhere - which is the starting point for this month of course…” is now half met. I can show the previous month’s closing - but it isn’t the starting point for the current month yet. The remaining half is that it needs to add into the current month total, so that becomes the new carry forward. Eek, I think this is another roadblock but I’m not working on it until tomorrow.

Justin_Barrett · ‎Jun 28, 2021

That should just be a matter of adding a formula field to the [Months] table to add the current month’s total to that from the previous month. If there’s no previous linked month, it should just add zero.

RGCreative · ‎Jun 28, 2021

Thanks for coming back on this.

I have the extra column (see below, ), but it isn’t quite right.

Line 3 {Last Month CF} value needs to be Line 2 {Total Cash} plus Line 3 {Cashflow In/Out} so the CF is cumulative. I know, Airtable won’t look at a previous line to get a figure.

Maybe I can use a checkbox in the Month table to trigger an automation that creates a new record in the Trading Days table on the first of the following month, containing the CF value from the month and a description of “Carried Forward”? Does that make sense?

Here’s the Trading Days table BTW

Justin_Barrett · ‎Jun 28, 2021

Sorry. I don’t know why it took this long to click that you’re looking for a running total (I’ve got a lot going on, and in my haste to answer I somehow thought this might be going a different direction). Unfortunately running totals can’t be done via links, lookups, and formulas because they end up causing circular references. This could be done through other means, though: a script in a Scripting app could be designed to do the calculations, and you could run this script as often as desired to update the totals. The downside to this solution is that your ability to use it will depend on your workspace plan. Normally the Scripting app is only available on Pro-plan or higher workspaces, but users on Free and Plus plans can use the Scripting app until September 2021.

Unfortunately I’m not in a position to take on any more clients or I’d offer to help with this, but there are lots of others here who can code this for you. I recommend posting in the #developers:work-offered category to find someone who can assist.

RGCreative · ‎Jun 29, 2021

I think I have it, via an automation.
A tickbox in the Months table triggers creation of a new transaction record in the Trading Days table, with “Cash Movement” = Last Month CF, also Linked Month = Month, and Description “Last Month’s Balance Carried Forward”.

The Cash Movement column is now renamed “BALANCE Change”.

An additional column “Monthly Profit/Loss” calculates:
IF(Description=“C/F from last month”,BLANK(),{BALANCE Change}) which gives only the transactions for the actual month, ignoring the added Carried Forward record.

So I now have from each month:

Profit/Loss on trading (i.e. total income/expenditure for the day)
Balance at end of month (running total!)

Trading Days view (Transaction Details):

Month View (Summary):

No coding or scripting required! A bit of tidying up required, but it seems to satisfy what I was asked to provide, on the free level. It’s for a charity, so they can’t commit to a monthly/annual fee.