Grantmaking foundation migrating from FileMaker to Airtable. I have 10 columns in a table that I want to merge into one column. Each of these columns consists of a different grant budget. I want to merge these columns into one column, while differentiating the data. I need to know which budget the grant belongs to, but it doesn’t make sense to have 10 columns when I can use a single select feature and select a the appropriate budget. But I am stuck on how to accomplish this. Any guidance is much appreciated!
So there’s a few workarounds here… one is to write a lovely long and complicated formula that would effectively nest a bunch of IF statements to bring in the right category. Either way, you’ll need two separate columns- one for value and one for type.
For the value, just make a column with one formula: Budget1+Budget2+Budget3…etc. This would bring in a sum value of the values. Then convert the field to currency and it should keep all the values. (If you’re afraid of the math, just use a Concatenate formula and it will bring in the number as text)
For the Budget category… you can go for a nested IF to bring in the right budget name, or, put in a small bit of elbow grease. Just sort by each budget column just paste the budget name in a new ‘budget type’ column. When you are all done, then covert the budget name column to a single select and it will convert all the values for you. (Alternatively you can convert it to a linked table> Create new table, but not sure the value in that.)
I’ve implemented Noel’s suggestions in a demo base you can view at Budget Demo. (I stopped after 5 budget columns.) I’m assuming in each row only one budget column will contain a value.
After creating the two formula fields (‘Which Budget’ and ‘All Budgets’), I copied them and then converted the first to a multi-select and the second to currency. The copy was simply to show them pre- and post-conversion; you can easily convert them in-place. (If you’re as paranoid as I am while I learn Airtable, converting a copy of a column offers a safety belt.) That auto-creation of a multi-select from a collection of values is one of Airtable’s nicest features – especially when migrating from another database.