While not exactly a “Group By” operator, here’s two approaches I’ve used:
A) Use your programming language to create a list of distinct IDs from a list of all the record IDs. Use the “fields” parameter to specify that you only want the API to return the ID field/column to speed things up and cache the results since, depending on your table size, this request could take 10s of seconds.
B) This requires creating a “reference” table with a “basket” field and a “distinct” field.
- Create table “reference”
- Create field “basket” that is a link back to your first table
- Create field “distinct” that is a rollup that uses the ID field and ARRAY_UNIQUE(values)
- To get the “distinct” ID’s from the field in the reference table, you’ll first need to fetch all records from your IDs table where the reciprocating linked field (called “reference”) is FALSE() and update them so they’re linked.
- then you can simply query the value of the “distinct” field of the “reference” table!
Ug… And this is why Airtable will never compete with any other data source as a backend unless the API (and probably underlying data storage engine) is overhauled OR unless you develop your own API interface and caching layer that can make some of these basic scenarios, well… basic. The Airtable interface ROCKS! And the API is great, but it’s just not built or optimized to replace a relational database backend.