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
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.
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
Hi Noel thanks a lot. Worked perfectly.
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.
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
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
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
