I can’t believe that I took so long to discover Airtable. But now that we have one another in each others lives, the rest is history.
Now, feature request; I just created a “frequency” - Single Select - column for when subscribed payments would occur, with the options Weekly, Fortnightly, Monthly, Annually.
Next to this column is a “Payment” currency column for the input of currency. And finally next to that is an “Annual Total” Formula column, where;
Annual Total = Payment * Frequency
That’s the moment when I realised, that I’ll need nested IF loops to get the job done, as Weekly = 52, Fortnightly = 26, Monthly = 12, Annually = 1… Yet this would be so much easier & neater if I could use a switch statement - Select Case.
It sure would be nice for the devs to include this. :blush:
You’re using text to represent numeric data. At some point there has to be a conversion if you want the use the numeric data in a formula. What would the formula look like if there was a switch?
It seems to me that what’s missing is the ability to specify two values for each entry in the dropdown: the displayed value and the actual value, something like “12;Monthly” which could hide the “12” and display “Monthly” but use “12” when accessing the field in a formula.
As an alternative, you could add a number to the beginning or end of the text in the Frequency field, e.g. “01 Yearly” or “Yearly 01”, and then use the RIGHT() or LEFT() along with VALUE() to strip out the number in your formula. If, for example, the Frequency field has the format “[Text Frequency][Space][Number Frequency]”, then Annual Total = Payment * VALUE(RIGHT(Frequency, 2)).
One more option: Create another field that converts the text frequency to a number by using nested IF statements, and then hide that field and use it in your formula. Perhaps a bit easier to follow.
In any case, your current formula is not that ugly … :winking_face:
@Christoff beat me to it: I was going to make his last two suggestions. Embedding a numeric value — such as the payment frequency — in a descriptive single-select option is especially handy in cases where you anticipate having to define additional options as time goes by; that way, there’s no need to modify what would undoubtedly be a hard-to-maintain nested IF() statement from hell in order to add a new option. (I haven’t tested this, but conceivably one could put enough space or non-breaking space characters between the text and numeric portions of the select as to push the numeric portion beyond the field’s right margin, thus making it invisible to the user.)
As it’s doubtful you’ll need to support a calendar with 10-day weeks or 13 months to the year, you’re unlikely to profit from the previous benefit. In that case, the second option — stuffing the nested IF() into a hidden field referenced by the Annual Total column — would probably be easier to maintain, at least from the point of being able to follow program flow. The hidden field would simply be responsible for selecting a numeric FrequencyNbr based on the specified Frequency, while the underlying logic of Payment*Frequency = TotalAmount would become apparent.
But, you’re right: Some sort of SWITCH() function could be nice — although I’d like to know what other features would likely be delayed as a result before I picked favorites.
