I, too, would be interested in a capability analogous to an SQL Union operation.
My recollection of SQL Union operations from distant-past use of MS Access is that you essentially concatenate records from two or more source recordsets (put another way, sequentially append records from each subsequent recordset to those from the first recordset in the Union), aligning identical or analogous fields selected from among all fields in each of the source tables. (I understand that an Append Query in MS Access accomplishes something conceptually similar, but for a single receiving recordset at the source table level, not the query level.) By contrast, I understand the Lookup field to simply expand upon the set of fields for each record of the referring table. From a Grid view perspective, I see the Union operation as augmenting rows, and the Lookup field adding a column.
I’ve just begun using Airtable, and I’ve already used Lookup tables often to combine data in certain ways. For instance, I often populate a Formula field with the (text-string-level) Concatenate function to pair a Lookup value representing a higher level of a hierarchy with a value from the referring table that represents a corresponding lower level within that same hierarchy. However, I see the use cases for Union-like functionality as different.
The use case I envision would be for pages on our website. I tend to evaluate those pages in three separate categories: Articles, Topics, and all other pages. I believe that distinctions between these categories tend to favor a separation of information I track about them into three different source tables. However, it would be helpful in other contexts to be able to link to a single recordset that includes all pages. If I were to natively create and maintain a table with records comprising a comprehensive list of our site’s pages, I would not need Union functionality. However, I’d have a lot of fields relevant to only a subset of my page categories, and I don’t know how I’d be able to limit (i.e., filter) the contents of linked record field dropdown lists in other tables to only one of my three page categories.
It seems that only Union-like functionality would allow me to accomplish all of these goals simultaneously. My inchoate perception of the Airtable team’s design objectives suggest that it would prefer to hide the intimidating details of underlying table relationships and query structures from users of the basic Airtable interface. I’m not a practicing database programmer, and I greatly appreciate Airtable’s combination of an elegantly simple UI and surprising power and versatility. However, if Union-like functionality can be achieved while adhering to such design objectives, I hope Airtable will implement that functionality.
