Add a CURRENCY() function for Formula Fields


#1

Right now, to format values pulled from a Currency field in a Formula field, users have to use something akin to the following formula to get “$65,756” to display as such. Otherwise, AirTable displays it as “65756”, no commas, no currency symbol:

IF(
  LEN(CONCATENATE({Trip Budget}))<4,
  {Trip Budget},
  IF(
AND(LEN(CONCATENATE({Trip Budget}))>3,LEN(CONCATENATE({Trip Budget}))<7),
LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
IF(
  AND(LEN(CONCATENATE({Trip Budget}))>6,LEN(CONCATENATE({Trip Budget}))<10),
  LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-6)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),6),3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
  IF(
    AND(LEN(CONCATENATE({Trip Budget}))>9,LEN(CONCATENATE({Trip Budget}))<13),
    LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-9)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),9),3)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),6),3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
  )
)
  )
)

This is obviously inconvenient, and it doesn’t even take into account decimals or amounts greater than $999,999,999.

I propose a new formula function that operates something like this:
CURRENCY([number or field reference], [currency symbol]) => CURRENCY(12345.6,"$") = $12,345.60


New Formula Field Functions
#2

If the output of your formula is going to be a number, you can use the formatting tab in the field customization menu to do this—just switch the Format from Integer to Currency.

Screen_Shot_2018-12-20_at_1_54_49_PM

If the output of your formula is going to be text rather than a number, then you will not be able to use the formatting tab in this way (this might be the case, if, for example, you’re concatenating the value from a currency field with a value from a text field). However, if you’re just performing mathematical operations on a value from the currency field, this shouldn’t be an issue.


#3

That only works if there the formula presents the number by itself. It doesnt help for formulas that concatenate plain text along with whatevery currency field is being referenced.

A CURRENCY() function is still requested.


#4

@Katherine_Duh if you’d like a use case, here’s a shot from my table that sorely needs such a function:

17%20PM

To keep my table a little cleaner/legible, I combined 5 Currency fields into one column using a formula in the {Budget Summary} field. However, this means the formula needs to include the 774 character snippet (which in reality would be longer to handle decimals) featured above for each number.

The CURRENCY() function could operate just like DATETIME_FORMAT() does and would only make formulas less cluttered


#5

What is your Base structure? It seems to me like you could handle that just with Grouping and summary bars.


#6

The base already has groups as summary bars implemented and they don’t do whatthat formula is doing. What the mistitled {Budget Summary} field is doing is concatenating several lookups so they just appear in one field on top of each other, simply because I like that display.
All fields represented in {Budget Summary}, which are lookups, are hidden so I can look at one field to compare a cost breakdown as opposed to 5, where some of those five are often blank.