Monthly cashflow - columns (fields) for each month

[Andrew_Davies]

Hi

I am able to do this manually - by creating a field with a formula, but I am working on a 5 year project so making 60 columns and typing in the formula each time is a little tedious! So I wondered if anyone had any clever ideas?

Essentially, I have a table of transactions - with a field showing which year / month (yyyy-mm) the invoice will be paid. I need to show our Finance Director a cash flow report (very easy in Excel!) with a column for each month over the next 5 years.

I can create a field / column, with a Lookup field, only showing transactions for the month (the field name would be Jan-22 for example) - but that would take a while to set up - as mentioned above - 60 columns / formulas to manually enter - even with duplicating the field it would take a while.

I’d also need to show cumulative total (so the total of all spends up to that specific month)

Any ideas?

Thanks,
Andrew

[Williams_Innova]

Hi @Andrew_Davies,

Is the format you mentioned, a column for every month, mandated by the Finance Director? If so, are they open to a different format?

I get the impression that your finance department lives and breathes Excel. It doesn’t surprise me at all, as many orgs use the program extensively. I also imagine that their preferred format has derived strictly from use of Excel (or a spreadsheet to be more general). If this is the case, that finance wants a spreadsheet format (accepting nothing else), then it may be worth considering a few options. Since Airtable is a database at its foundation, I don’t recommend duplicating fields (columns) as you mentioned. My method would be do have one field for date, and possibly another to get month-year in the format you want. Then, come the options.

1. Use a pivot table. This could be the easiest and fastest way to generate a report similar to the format in the spreadsheet, although it won’t be a spreadsheet and could upset finance. We don’t want to upset them so probably best to see if they would accept this. :winking_face:
2. Use a sync between Airtable and Google sheets, then export the Google sheets to Excel. As for the format, I would still recommend one field for date in Airtable and then, if you absolutely need the many columns for finance, use a macro in Excel to transform the data. Many columns in Airtable would just be a pain, like you mentioned.
3. Last option, and it goes against my column preference, BUT if you did set up all the columns in Airtable (Months 1-60), where they auto adjusted every month, you could create a shared view that was always up to date. Then, share it with finance and let them download it to .csv every month if they need that.

Anyway, I’m sure there are other variants of these or possibly even completely different options as well.

Chris

[Andrew_Davies]

Thanks again Chris

Again - really helpful. I am sure the FD would be up for a different format - as long as they can see monthly spend.

I’ve just played with the Pivot table app and get close (but no cigar!) - I can make the report as needed, then copy and past it into Excel and then send it to them (also add the cumulative total as well).

Would be nice to do it solely in Airtable - but I think that will do. No manual making of 60 + columns!

Thanks again,
Andrew

[Andrew_Davies19]

Just thought I’d update this in case it is useful for others.

The Pívot Table app is the way to do this. Love how you can “bucket” columns by month / quarter etc.

Exactly what I needed.

The only problem I have is getting a print out / PDF of the report.

There’s no way of exporting it (Airtable suggest taking a screenshot - but it’s too big for the screen and that’s rubbish anyway)

So I copy and paste it into my old friend Excel. For some reason, when columns are “bucketed” - the column title shows as “blank” With that method.

Would love for the App to be able to print / export.

Hope this helps
Andrew

[Justin_Barrett]

Another option (though not necessarily an easy one to print) is to build a custom interface and create a bar chart, though I’m not sure if the maximum element width (or the chart options) would allow you to fit 60 bars.

[Andrew_Davies19]

Thanks Justin

I need to show figures though - not charts.

I think I’ve cracked it though. I export the raw data to Excel and create a pivot table that way. Would be wonderful if airtable add the ability to print from the pivot table app.

Thanks all
Andrew

[Justin_Barrett]

Sorry for misinterpreting your goal, Andrew. One other way to get a simple graph of figures would be to use a script that outputs a table. Scripting output can be printed (you’ll see a “Print” button appear in the scripting app header bar after the script finishes).

[Andrew_Davies19]

Oh that sounds interesting.

I find scripting a bit challenging (used to love it 30 years ago!) so may give that a try.

No worries if not - but any pointers to get started?

Thanks again
Andrew

[Justin_Barrett]

Airtable’s Scripting app contains a really useful reference space at the bottom of its UI. I recommend using that for general reference on how to access tables, view, records, record data, etc.

As far as output goes, you’ll want to build an array of objects. The structure of each object should be the same in terms of the names of the properties that you add. For example, here’s a simple array of objects that stores the first and last names of people:

const example = [
    {
        first: "Justin",
        last: "Barrett"
    },
    {
        first: "Andrew",
        last: "Davies"
    }
]

To display that as a table, use the output.table() method:

output.table(example)

…which gives me this:

As you can see, property names become field headers, and each object becomes a row. Also note that rows are displayed in the order that the objects appear in the array.

I recommend figuring out the object structure first, then work on building a script to parse your data and create an appropriate array of those objects. While working on the script, I also recommend running lots of iterative tests along the way. Don’t try to build the whole script all at once. Build it in pieces, breaking down the logic into small segments, and testing each segment once it’s written to ensure that it does what you want it to do before moving on to the next segment. Feel free to ask questions here if you get stuck on something.