Count number of values in a single select field

[Binyamin_Green]

Hi,

I’m using Airtable for the first time for a project release.

I have one master table with a record for each release versions, and separate tables for each individual release. I have a bunch of records in each release table, and each record has a single select field with either To Do, Doing or Done.

For each record on the master table, I want to have a field which calculates the percentage of how many records are marked as Done. Is this possible?

Thanks

[W_Vann_Hall]

The easiest way would be to create a formula field in each release table with the formula IF({Status}='Done',1). Then from your [Master] table record, you could either roll up the formula field using an aggregation function of SUM(values), for a raw count, or use a combination of toll up and count fields to calculate a percentage.

[Binyamin_Green]

Thanks. How do I access the formula field from the master table? I tried linking, but I couldn’t figure out how to do it.

[W_Vann_Hall]

Oh, I was assuming each record in the [Master] table was linked to all of the records in the release table for that release—

—actually, on re-reading, I see you say you have separate tables for each release. The way I’d expect to see such a base structured would be as such:

(The red lines represent linked records.)

This way, you’re managing only two tables; each of your release records is of an identical type; and you can make use of the links between your master table record and the associated release records to do such things as, for instance, roll up number and percent or records marked ‘Done.’

Maintaining individual tables per release starts out messy and ends even messier. Fortunately, assuming each table has the same data layout, you can build a combined table through copy-and-paste. (Just remember to add a {Release} field to each table first and fill it with the appropriate release number; that way, once you create the combined release table, you know how to link the records.)

---

Bonus points:

1. Define a {Release} field in each release table.
2. Populate it with the appropriate release number but make sure the phrasing is the same as you used for the primary field in your master table. That is, if the first field of the first record in your master table contains the value ‘Release 1.0’, make sure the newly added {Release} field in your [Release 1.0] table — for every record in the table — is also set to ‘Release 1.0’.
3. Repeat for each of your release tables.
4. Once each release table has had the proper {Release} field added and populated, create your combined [Releases] table. Easiest way:
   1. Duplicate your [Release 1.0] table.
   2. Go to the last record in the base and add a blank record.
   3. Open your [Release 1.1] table (in another tab if you wish).
   4. Mark and copy all cells in the table. (Select the cell in the first row, first column; scroll to the last row, last column and holding down the ‘shift’ key, select that cell; press Ctrl-C.)
   5. Return to the blank, bottom-most row in [Release 1.0].
   6. Press Ctrl-V; authorize adding new records to the table.
   7. Repeat steps 2 through 6 for each standalone release table.
5. Once you’ve completed the combined base, right-click on the header for the {Release} field and select ‘Customize field type’.
6. Change the field type from single-line text to linked-record; when asked, specify a link to the [Master] table.

This will automatically link every release record to the appropriate master record (and vice versa) without requiring any manual links be made.