Combining Budget Columns

#1

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

#2

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

#3

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.

#4

Hi Noel ­ thanks a lot. Worked perfectly.

#5

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

#6

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