Skip to main content

I have been building a income and expense tracking base for myself that has evolved from a Google Sheet. Thus far I am loving the Airtable features and am anxiously awaiting a plotting solution. I have a question about implementing main categories and sub categories. For each expense I have defined main categories and sub categories. I have currently implemented this by creating separate tables within my base. One for main categories and one for sub categories and then linking to them within my main table.


My problem is that my design requires that each sub category belongs to only a single main category however I have to manage that manually as I do not know how to explicitly restrict that relationship. Thus far it works as I am the only one that uses the base and know which sub categories belong to which main category. I would however like to share the input responsibilities with others in my family and also make the base available as a more general solution. Additionally it would be good to have an explicit link that could be manipulated more easily in the future as the base evolves.


I have read through all of the Airtable guides that I can find. It seems like the solution might reside in the junction tables implementation, but I have as yet not been able to design a solution that would work. Any direction would be helpful or simply to know whether this is even possible to cleanly implement within the current Airtable structure. Years ago, before I even used a Google Sheet, I implemented a similar solution in Filemaker Pro but no longer remember exactly how it worked then and if such an implementation would even be possible in Airtable.

Hi Paul!


You can structure this by doing the following:




  1. Create a new field called ‘Main Category’ on your subcategories table that links to the main categories table. When doing this, specify that each subcategory can only link to one main category record, but that each main category can link to multiple subcategories. So when you click ‘Customize field type’ in the subcategories table, uncheck the box that says ‘Allow linking to multiple records’, but leave it checked in the main categories table.




  2. In your main expense table, replace the main category field with a Lookup field that looks into the subcategory table and shows the main category field.




That way, when you choose a subcategory, it will automatically select the main category for you.

Thanks Pete. I think this will do the trick. I might have to change philosophical approach a bit (i.e. perhaps no more need to actually enter a main category). I need to figure out, if I remove the manual entry of a main category and in essence let that be sorted by the automatic association with a sub category if I can still a roll up function to sum main category expenses. Either way, thanks for the idea.

Thanks Pete. I think this will do the trick. I might have to change philosophical approach a bit (i.e. perhaps no more need to actually enter a main category). I need to figure out, if I remove the manual entry of a main category and in essence let that be sorted by the automatic association with a sub category if I can still a roll up function to sum main category expenses. Either way, thanks for the idea.


Yes. You can do a rollup at the subcategory level, then another rollup again at the main category level.

My question is similar to Paul’s but I’m using imported expenses from a CSV chart downloaded mint.com.


When I upload the CSV file to Airtable I get a master table that includes a column for Category.


I’d like to be able to aggregate some of those categories into a Main Category and run equations on the aggregated data.


For instance, my mint.com Category column has fields for Restaurants, Coffee Shops, Fast Food & Groceries.


Can I instrut Airtable to automatically apply a tag of Food to these subcategories so that I can then just see how much I spent on all of Food in a given month?


thank you!

Hi Paul!


You can structure this by doing the following:




  1. Create a new field called ‘Main Category’ on your subcategories table that links to the main categories table. When doing this, specify that each subcategory can only link to one main category record, but that each main category can link to multiple subcategories. So when you click ‘Customize field type’ in the subcategories table, uncheck the box that says ‘Allow linking to multiple records’, but leave it checked in the main categories table.




  2. In your main expense table, replace the main category field with a Lookup field that looks into the subcategory table and shows the main category field.




That way, when you choose a subcategory, it will automatically select the main category for you.


Hi Pete and Paul! This article is helpful, thanks. In your example, it sounds like there are 3 tables - subcategories table, main categories table, and main expense table. I am new to Airtable and trying to figure out how to newly set up a similar goal with categories & subcategories. So should there be these 3 tables? And which of the 3 tables are best to enter all of my other fields/info? Anything else that would be helpful to share with a newbie? Thanks so much. It’s time sensitive, so hoping Airtable may notify you as a prior poster on this subject. Thanks so much.

Hi Paul!


You can structure this by doing the following:




  1. Create a new field called ‘Main Category’ on your subcategories table that links to the main categories table. When doing this, specify that each subcategory can only link to one main category record, but that each main category can link to multiple subcategories. So when you click ‘Customize field type’ in the subcategories table, uncheck the box that says ‘Allow linking to multiple records’, but leave it checked in the main categories table.




  2. In your main expense table, replace the main category field with a Lookup field that looks into the subcategory table and shows the main category field.




That way, when you choose a subcategory, it will automatically select the main category for you.


Pete, this is a little helpful. However, it would be really great is single select fields could be set up as families and sub families where the family selected dictates the sub family options in the next field. Is this something that is possible or being worked on?


For example if I build a database for inputting car information and I chose a car Manufacturer in the first field, I’d like to see only car models made by that company in the second field.

I am creating a budget balance sheet for the global payouts and because some budget lines are TBC I need to show certain fields within this set field so the formulas show accurate savings.

For example, I want to show Row 52-56 under TBC. How can I can work around this?

Reply


Terms & Conditions