Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 16, 2021 12:49 AM
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
Dec 16, 2021 02:56 AM
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.
Anyway, I’m sure there are other variants of these or possibly even completely different options as well.
Chris
Dec 16, 2021 08:01 AM
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
Dec 17, 2021 02:41 AM
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
Dec 17, 2021 09:09 AM
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.
Dec 20, 2021 11:18 PM
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
Dec 21, 2021 08:01 AM
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).
Dec 21, 2021 11:09 AM
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
Dec 21, 2021 12:49 PM
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.