Re: Counting a column and then putting in a single cell
There is a way to do this – but it is a bit non-intuitive. It also requires you to link each new Contact record to a ‘ContactsByMonth’ table – a bit of manual effort for which I’ve yet to identify a work-around. (This applies to new contacts only; existing ones can be updated en masse.) The example base linked at the end of this reply offers a solution assuming a maximum of 99 contacts tracked per month but can be modified to support larger maximums.
I’ll start with the non-scary part, first: the finished product. In this demo, I started with 200 dummy contact records, each with a fake CREATED_DATE in 2016 or 2017, in the ‘Contacts’ table. In the ‘ContactsByMonths’ table, the ‘ContactsYear’ field contains a string showing per-month contacts for that year, as so:
(I wasn’t sure how you planned to display contacts on your dashboard page; you could as easily show a rolling month, this month/last month, or any other temporal grouping you wish.)
Now for the ugly details. The solution is based on the technique I described here; in effect, what it does is use successive pairs of digits in a large integer to track contacts per month:
Ja.Fe.Ma.Ap.Ma.Ju.Jl.Au.Se.Oc.No.De
00.00.00.00.00.00.00.00.00.00.00.00
(Unfortunately, this runs up against Airtable’s degree of precision, so I track January-June and July-December independently.)
If you look at the Contacts table, you’ll see a column of faked (seeing how I have no access to a time machine) CREATED_DATEs; from there, I extract the month into ‘GetMonth’. The real work is done in the next step:
IF(GetMonth<7,1*POWER(10,(GetMonth-1)*2))
(That handles January through June; for July through December, the formula is
IF(GetMonth>7,1*POWER(10,(GetMonth-7)*2)) .)
Finally, ‘YearToCount’ links to the ContactsPerMonth table.
What happens there depends on what you want to do with the data. Since I intended to display monthly counts as part of a string, I first converted the encoded monthly rollups into strings and then used LEFT() and MID() to extract individual months. Alternatively, if I needed a numeric value per month, I could have used
INT({EncodedCounts}/[MonthOffset])
where [MonthOffset] = 10,000,000,000 for the left-most encoded month, and
INT(({EncodedCounts} - [PreviousMonthCount*PreviousMonthOffset]/[CurrentMonthOffset])
for subsequent months. (Or alternatively alternatively, I could have used VALUE({MonthStr}); there are multiple paths to the same end point.)
As I said, it’s not particularly pretty – but it gets the job done.
=============
To use existing Contact records, first create the ContactsPerMonth table. Create a row for each year you wish to track. In the Contact table, create a new field with the formula
YEAR(CREATED_DATE)
Using ‘Customize field now’, change the field type to ‘Single line text’. Next, change the field type to ‘Link to another record’, and specify ‘ContactsPerMonth’ as the table to which to link. This will correctly load all the links.
