As we’ve been testing AirTable for our business’s project management needs, one thing we’ve had trouble setting up is something like an expense report, purchase order, or mileage report.
Our typical reports would be a single printable document with several standard pieces of data (job name/number, purchase date, vendor info, billing method, etc), and a table containing purchase info (item, qty, price) with totals at the bottom.
In Airtable, you can currently accomplish something like this with two tables (let’s call them “Expense Reports” and “Expense Items”) with the latter connected to the former with a “Link” field. But it’s a bit clumsy, because you’d have one table with each “report” and another table that contains lots and lots and lots of expense items that likely have nothing to do with each other. More importantly, it’s not really possible to export the data from each Report while including the Expense Item data.
A more elegant solution would be a field type called “Nested Table.” This would create (within a record) a small table/spreadsheet with customizable columns, simple math functions, and the ability to add rows on-the-fly.
Thoughts?
Page 1 / 2
+1 I think f many use-cases that would need a nested table
For further explanation, this is how Kintone’s “Nested Table” works. This is a screenshot of one segment of a single Record with an embedded. The database’s creator defines the structure/data for the columns and the user can control the rows (add/subtract rows, input data).
This is really a matter of having a form designer which can include sub forms rather than a new field type. We have the necessary data structure in Airtable (apart from the requirement to delete orphaned child records).
This is really a matter of having a form designer which can include sub forms rather than a new field type. We have the necessary data structure in Airtable (apart from the requirement to delete orphaned child records).
We might be talking about the same thing (nested table vs sub-form). I’ll just add that my team almost never uses the “form” view in Airtable; we primarily create new records in the grid view, etc. (though, to be fair, regardless of view, one would probably have to “expand” the record to access a feature like this).
Either way, I’d definitely need something like the example above to be included with the printed/emailed version of this record, not just a collapsed summary or total. I also wouldn’t consider it a “win” if the fields in the table example above were just translated into fields within the current table; even in this simple example, that would add AT LEAST 13 fields to a table (much more, if you want the user to be able to include more than 3 expense items).
I would also really like the ability to make fields of type Table. I want this so that every record has a field that is a table of the same schema.
Here’s how I imagine the Table field type working, and why I want it:
How It Works
Add New Field
1-1. Add new field a name it, e.g. “show-element”
1-2. When choosing the field type, select “Table”
Customize Field
2-1. I can add fields to the schema, just like I add options to Single or Multiple select field types.
2-2. For each field in the schema I can provide a name and set the type.
2-3. I can reorder the fields just like how I can reorder options in the Single and Multiple select field types.
Select Field
3-1. Every record will start with an empty table adhering to this schema for the default value of this field.
3-2. Selecting this field for a record will prompt me with the expand icon to open this table for editing. I can double-click the field or click on the expand icon to open and edit the table for this record.
Edit Field
4-1. The field table is displayed in an pop-up view similar to the Long text box.
4-2. The full suite of table tools is available to me in this pop-up view.
4-3. With these tools I can do everything with this field table that I can do with a base table, including renaming fields, reordering fields, adding fields, and removing fields.
4-4. Making changes to the schema for a field table for one record does not change the schema for field tables of other records.
Why I Want This
I want to have each record in my “assets” table point to its own collection of “resource” data. I want to have each of resource collection use the same schema, but I do not want to have a “master” resources table that contains all of the resource records, and have each asset record contain links to records in the resources table. The reason I don’t want the master table is because I plan to have a number of fields that are a Table type, and I do want want these “master” tables to clutter up the list of tables in the base.
I would also really like the ability to make fields of type Table. I want this so that every record has a field that is a table of the same schema.
Here’s how I imagine the Table field type working, and why I want it:
How It Works
Add New Field
1-1. Add new field a name it, e.g. “show-element”
1-2. When choosing the field type, select “Table”
Customize Field
2-1. I can add fields to the schema, just like I add options to Single or Multiple select field types.
2-2. For each field in the schema I can provide a name and set the type.
2-3. I can reorder the fields just like how I can reorder options in the Single and Multiple select field types.
Select Field
3-1. Every record will start with an empty table adhering to this schema for the default value of this field.
3-2. Selecting this field for a record will prompt me with the expand icon to open this table for editing. I can double-click the field or click on the expand icon to open and edit the table for this record.
Edit Field
4-1. The field table is displayed in an pop-up view similar to the Long text box.
4-2. The full suite of table tools is available to me in this pop-up view.
4-3. With these tools I can do everything with this field table that I can do with a base table, including renaming fields, reordering fields, adding fields, and removing fields.
4-4. Making changes to the schema for a field table for one record does not change the schema for field tables of other records.
Why I Want This
I want to have each record in my “assets” table point to its own collection of “resource” data. I want to have each of resource collection use the same schema, but I do not want to have a “master” resources table that contains all of the resource records, and have each asset record contain links to records in the resources table. The reason I don’t want the master table is because I plan to have a number of fields that are a Table type, and I do want want these “master” tables to clutter up the list of tables in the base.
Joshua, I love the design you’ve come up with. The only thing I’d add is (probably in the “edit field” window), the ability to add any number of formula/calculations to fields to the , such as a sum of a field’s values, etc.
A sub-form designer would be great. I have a need where I have one picture with multiple items and each item needs its own line item information. I think the only way I can do now is to attach the same picture to multiple entries where each entry would be one of the items in the picture.
Anthony, I think the feature I’m envisioning would be perfect for that purpose. You could set up a nested table containing an attachment field plus any number of text fields for various kinds of metadata related to the image.
It would also be great for checklists and logs related to a record, or a list of multiple phone numbers for a contact record, etc.
@airtable_team : Just curious if you have any thoughts on this, or if it is already part of your roadmap, etc?
I am having the same issue. I love the functionality of Airtable for what I am working on and would love to convince my organization to purchase it, but nested table problem is HUGE and makes the entire database I’m working on not functional. I’ve tried everything I can come up with and haven’t been able to figure out a work around that isn’t overly time-consuming.
I worked for over 20 years with a product called Alpha4, which was a desktop application based on the .dbf file structure. That was a “relational” database in that I could create reports that could “nest” an unlimited number of sub-items, sorted on any major item I chose, and print out not just the result for one record at a time, but for the whole list of records. When this went to Alpha5, which is the on-line version, it got less and less user friendly, which is what got me interested in finding an online alternative that was easier to work with. So far, Airtable has a long way to go. I hope the team works on this nesting and gets it right soon.
Conrad, you’re the second person I’ve heard sing the praises of that older product. Please let me know if you find anything comparable!
I’m also seeking this functionality, and it’s what will make or break Airtable’s usefulness for my current needs.
You might want to check out Obvibase, it is limited in functionality, but does have a pretty good implementation of this feature.
This feature will be really game changer for the Airtable. For sales pipeline we may create nested tables of products and their qty as a commercial proposal or create list of components for something etc.
Yep, have needed this for quite some time. I need it to manage sub tasks of a primary task of a project plan.
This would be a super helpful feature for multiple of my projects. +1 from me.
I have run into the need for this exact functionality on multiple projects, so far the only workaround I’ve found is having many many fields to hold all the sub-data I need for each record, it’s quite clunky.
My projects where I’ve wished for this functionality are:
Example 1:
I work at a theater production company, and I have created a base that keeps track of every person we have ever hired or interacted with or submitted a show to for production. This includes actors, producers, directors, musicians, etc etc. Some people have participated in multiple shows, playing a different role in each show. A person might play drums in show “A”, and might play the part of Mary in show “B”. I want all this information stored in the record for that person. My workaround is to have a new field for every production of every show, which lists what role a given person played in that show. A run of a show may have 6 shows/week for 4 weeks. So, that run alone, of that single show, would add 24 fields to my People table.
Example 2:
I also work for a musician, and I’ve created a base that keeps tracks of every song he co-writes, to keep track of publishing splits. A song may have 3 co-writers, and each of those co-writers has a publisher, some of the writers might have 2 publishers, and then some of them might have admins as well. So, a single song may have, say, 9 entities who each need to be paid when a song is used. I can’t just list all the writers in one field and all the publishers in one field, because I need to know the relationship of which publisher goes with which writer. And I can’t just list this relationship in a separate Writers tab, because the writer may change publishers from year to year, and I need to be able to see which publisher they were with for that particular song. So, to keep track of it all, in the songs tab I have a field for writer 1, 2, etc, a field for publisher 1, 2, etc corresponding to writer 1, 2, a field for admin 1, 2, and then a separate field for each entity’s split. That’s a whole lot of fields! It almost necessitates a separate tab for each song, but with 400+ songs, that is completely non-feasible. Hence, the need for subtables.
Using the many fields workaround creates another problem, in that, when I list “writer 1” “writer 2” etc, and link that to the writers table, in the writers table there are now multiple fields which list songs written by that writer, because I had to have separate fields for each writer on a particular song, when really all the songs written by a single writer should be listed in one single field. I can make some kind of summing field to collect all this data in one place, but the original set of multiple fields still has to exist, and again, it’s pretty clunky.
So yes, a field type called table would be helpful, and also, if anyone has any suggestions for me on how to make either of these bases more efficient I would love to hear them!
Desperately trying to find a solution such as this. I love Airtable but, I need a way to create an invoice worksheet for our operations department to build a worksheet and don’t want to use another software for it. I’d rather it all be in Airtable. Really hoping this functionality is brought to airtable.
I have run into the need for this exact functionality on multiple projects, so far the only workaround I’ve found is having many many fields to hold all the sub-data I need for each record, it’s quite clunky.
My projects where I’ve wished for this functionality are:
Example 1:
I work at a theater production company, and I have created a base that keeps track of every person we have ever hired or interacted with or submitted a show to for production. This includes actors, producers, directors, musicians, etc etc. Some people have participated in multiple shows, playing a different role in each show. A person might play drums in show “A”, and might play the part of Mary in show “B”. I want all this information stored in the record for that person. My workaround is to have a new field for every production of every show, which lists what role a given person played in that show. A run of a show may have 6 shows/week for 4 weeks. So, that run alone, of that single show, would add 24 fields to my People table.
Example 2:
I also work for a musician, and I’ve created a base that keeps tracks of every song he co-writes, to keep track of publishing splits. A song may have 3 co-writers, and each of those co-writers has a publisher, some of the writers might have 2 publishers, and then some of them might have admins as well. So, a single song may have, say, 9 entities who each need to be paid when a song is used. I can’t just list all the writers in one field and all the publishers in one field, because I need to know the relationship of which publisher goes with which writer. And I can’t just list this relationship in a separate Writers tab, because the writer may change publishers from year to year, and I need to be able to see which publisher they were with for that particular song. So, to keep track of it all, in the songs tab I have a field for writer 1, 2, etc, a field for publisher 1, 2, etc corresponding to writer 1, 2, a field for admin 1, 2, and then a separate field for each entity’s split. That’s a whole lot of fields! It almost necessitates a separate tab for each song, but with 400+ songs, that is completely non-feasible. Hence, the need for subtables.
Using the many fields workaround creates another problem, in that, when I list “writer 1” “writer 2” etc, and link that to the writers table, in the writers table there are now multiple fields which list songs written by that writer, because I had to have separate fields for each writer on a particular song, when really all the songs written by a single writer should be listed in one single field. I can make some kind of summing field to collect all this data in one place, but the original set of multiple fields still has to exist, and again, it’s pretty clunky.
So yes, a field type called table would be helpful, and also, if anyone has any suggestions for me on how to make either of these bases more efficient I would love to hear them!
This is wrong. You need a junction table for Roles (or similar), to store Person, Show, and details like Character, Payment, and so on. You can create Views, Filers, Groups… to see the information you want.
The same for example 2, you just need extra tables for people, and of course for the Roles. I would have:
Songs
Writings
Publishing
Admin
People
Then build relations with Linked Record fields. Example:
Writing: Song, Person (from People), and details
Desperately trying to find a solution such as this. I love Airtable but, I need a way to create an invoice worksheet for our operations department to build a worksheet and don’t want to use another software for it. I’d rather it all be in Airtable. Really hoping this functionality is brought to airtable.
A related Table through Linked Record field is already a nested table (kind of). I have an Events base and I generate invoices in it. I have an Events table, and a Services linked to it, and in the Page Designer block I just add the Services field, and of course the Customer and Event data.
A related Table through Linked Record field is already a nested table (kind of). I have an Events base and I generate invoices in it. I have an Events table, and a Services linked to it, and in the Page Designer block I just add the Services field, and of course the Customer and Event data.
Elias, there are probably many examples where a linked records field could serve the purpose. And I’ve certainly experimented with that. But I think my initial example (an expense report with several line items) is one where linking to another table would be cumbersome, clunky, and prone to errors.
Another good example is a checklist, or a log (a list of notes sorted by date).
In all those examples, what you need is a SINGLE record with multiple lines of data that is distinct and segregated from other records. Linking to another table would be a nightmare for this kind of info.
It’s further complicated by the fact that Airtable’s interface makes it a PITA to view data in linked records.
Elias, there are probably many examples where a linked records field could serve the purpose. And I’ve certainly experimented with that. But I think my initial example (an expense report with several line items) is one where linking to another table would be cumbersome, clunky, and prone to errors.
Another good example is a checklist, or a log (a list of notes sorted by date).
In all those examples, what you need is a SINGLE record with multiple lines of data that is distinct and segregated from other records. Linking to another table would be a nightmare for this kind of info.
It’s further complicated by the fact that Airtable’s interface makes it a PITA to view data in linked records.
And obviously to @Helen_Vaskevitch, as I’ve cited her.
Anyway, I have both ‘log’ and ‘line item’ types tables, and with the use of Shift + Space and the Expanded Record feature, I can handle them very well.
Also, I have a Page Designer for invoices, that mainly is the same than this:
You can Group them by report, or just ignore the whole table. I rarely open the Line Items tables.
What do you mean? I’d use the Page Designer and you can include as many fields from the Line Items as you want.
Me too
Need this to be able to use on my project
Any new development on this ? Hope Airtable team don’t ignore this request.
