One thing that is currently missing in Airtable is the concept of a Current Row. This would be the current row of each table that is currently in the base. Current Row of a table could be used in filters to filter in only the children of a parent record (e.g. Order Header and Order Items). This would make the creation of applications much easier. There are many other use cases.
Are you familiar with FileMaker perhaps? This seems like a FileMaker concept – current or selected record. Another FileMaker concept: the “found set”. Other RDBMSes have similar concepts.
But these aren’t really needed in Airtable. Airtable is a bit more like your email app (think Gmail, say, or Outlook). The only time the concept of “current row” matters in Airtable is when you’re viewing details for a single record, and in that case, it’s perfectly obvious which record (or which email) you’re looking at. Other than that, if you’re looking at a list of emails or a listing of records in an Airtable grid view (say), then the notion of the “current row” is not needed.
Why not? Well, let’s go back to FileMaker and similar systems. In FileMaker you have only two choices about which records are going to be affected by some action (say, deleting, or exporting). An action can be performed either on the selected record (what you call “current row”) or on all the records in the found set. The “found set” in FileMaker is roughly like the record set that you get when you filter records in Airtable.
Except that in a filtered record set in Airtable, no record is automatically selected. In FileMaker there is always a selected record. But in Airtable, there isn’t. And conversely, you can select multiple records in Airtable without selecting everything that is being displayed, just as in your email program you can select message 2, 3, 7, and 14 and delete them all at once, without deleting the other messages you might be looking at.
Airtable’s approach to record selection is, in my opinion, more flexible than FileMaker’s. There are a lot of things I’d like to see improved in Airtable but this isn’t one of them.
Thank you for your reply William. I understand your point. I have not worked with FileMaker for many many years. But I have worked with lots of larger databases like Oracle, DB2 and SQL Server. I also have a good deal of application development experience. I am definitely not expecting Airtable to be a full-fledged database with triggers, stored procedures and stuff. However, the Current Row concept that I’m referring to has to do with Parent/Child (1 to many) relationships. In fact, the concept of parent/child record sets are used in most application development platforms (not sure if Airtable would be in that classification)and even can be easily implemented in Excel and Google Sheets. As I mentioned in my previous example, let’s assume two tables (or tabs) Order Header and Order Details. Let’s say you’re on the Order Header tab and you want to click on the Order Items tab and view only the Order Items for the Order you were looking at on the previous tab. Currently in Airtable the only way you could do that would be by selecting a check field in the Order Header and then filtering all Order Items linked to that Order Header record that have the check field checked(via a lookup). While this would work, you’d constantly have to check and uncheck a field on the Order Header to manually signify the current ROW and that would not make for good design. However, if the current Order Header record (Row) could be used as a a filter (e.g. filter Order Header = {Order Header}.(Current Row}), then all Order Items relating to Orders that are not being looked at would be non-filtered out. In other words a true Parent/Child record set. Not sure if this would be too difficult for Airtable folk to create. It may be!
At least as I use the terms, “selected record” doesn’t have anything to do with relationships. Author record for William Shakespeare is related to Title records Hamlet, Macbeth, etc., whether Shakespeare’s author rec is “selected” or not.
But I think I understand better what you’re talking about. Alas, this isn’t easy to do in Airtable.
In FileMaker (and 4D, and Caspio and many other systems that I’m familiar with), if you’re looking at a parent record and you want to see its children, there are at least two ways to do it.
In FileMaker you could use a “portal” on the author record in form view. A portal is a scrollable listing of linked or related child records. In this case, you are actually “in” the author record, but you can see all the related titles in this portal or window. This is remotely similar to a rollup field in Airtable, but only remotely. The rollup field is much less functional than a portal.
Also in FileMaker and many other systems, there’s a “go to related record” command. In an SQL database I guess this would involve a SELECT command. In FileMaker, go-to-related-record jumps from the Author record to a list of related title records over in Titles. Unfortunately this is simply not doable in Airtable at least not without scripting. In Airtable, the record set that you see in any view is the result of the filters for that view. There is no way, while you’re looking at a parent record in the parent table, to jump to a view for the child table and filter records to show only those that are linked to the parent you were just looking at.
In Airtable, filters are a key part of what defines views; and views in Airtable have to do the work that in other systems can be done by scripts or macros, or finds/queries, and so on.
For me, the biggest problem with filters is that they’re NOT LOCAL to the current user. They are a global property of the view for all users. So you can’t easily use filters to do finds or queries. If user John is looking at the Customers grid view and filters to find customers last name = “Smith”, user Susan, if she’s looking at the same view, is suddenly going to see users named Smith! If you have a lot of users looking at a complex table with lots of records, and the users need to do a large variety of queries, you’ll need in Airtable to create a view for each query and name it clearly. (The alternative is to have users creating lots of personal views, which they can edit on their own without affecting other users.)
Airtable definitely has its own way of working that takes some getting used to if you’re familiar with more conventional RDBMS systems!
Airtable does have its uses. At one point it needs to (maybe not) decide whether it wants to take the database or the spreadsheet route. Currently, from a technical perspective, it’s neither. Example, you cannot create a record with start and end date fields where the start date is a lookup to another record’s end date. You should be able to construct that by a self-referencing lookup. However, what you get in this case is a circular reference. Something like this is easily done in Excel. As my previous example goes, you cannot have a child recordset based on the current row of another table. This is easily done in even MS Access. So Airtable is neither a full-fledged spreadsheet nor a database. But it’s got its uses (btw, I have the pro version). I know that it has an easy to use API. But I’m not sure if that would be the best use of time for me. There are other products out there with these capabilities already perfected. But I have to say, Airtable is nice to look at and for simple stuff it does well. However, with its current set of capabilities I doubt if it can attract a major developer community. Maybe that’s not the business model Airtable is following. :slightly_smiling_face:
