Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 02, 2017 11:03 AM
Hello,
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!
Chad
Jun 02, 2017 04:03 PM
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.)
-N
Jun 03, 2017 05:58 AM
Chad -
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.
Jun 05, 2017 11:14 AM
Hi Noel thanks a lot. Worked perfectly.
Jun 05, 2017 11:16 AM
Thanks a lot. You were correct in assuming that there is only one value per
row in the various columns. I used your example base to get the code for the
"which budgets² column. Thanks again.
Chad
Jun 07, 2017 06:06 AM
Chad -
Glad it helped. Sorry to make it more difficult that it needed to be, though; I hadn’t realized the base preview method I was using didn’t allow formulas to be viewed. In the future, I’ll also enter the formula into the field description to simplify things.
Vann