Help

Currency format in formula field

Topic Labels: Formulas
4755 8
cancel
Showing results for 
Search instead for 
Did you mean: 
J_JBA
5 - Automation Enthusiast
5 - Automation Enthusiast

I tried all kinds of ways, but I can’t get it to work.

I want to combine a lookup field (containing multiple currency values) with a placeholder symbol (e.g. “;” ) so that I can then in the next step substitute this placeholder for a line break so that each currency value goes onto a new line in the same field.

So for example, I have a lookup field {Item Amount 1} that contains the following three currency values: 100.00 150.00 100.50.

When I use the formula ARRAYJOIN({Total Item Amount 1},";") the result is: 100;150;100.5;

However, I want it to return 100.00;150.00;100.50; so that I can then use the substitute formula to substitute the “;” for “\n” which would result in:
100.00
150.00
100.50

I can’t use the Formatting tab because it’s not a currency anymore.

I’ve tried all kinds of possible solutions, including trying to concatenate the lookup field first, but I can’t get it to work. This procedure does work for a lookup field with just text strings that I want to place on a new line within the field, and I can also get it to work for a date format, but not with currency values.

Is there a way to get the currency format in my formula field? Maybe with an additional formula? Thanks so much for any help!

8 Replies 8
gwynn_kruger
6 - Interface Innovator
6 - Interface Innovator

I’m guessing you can’t because it seems that \n is handled as a special case.

When I enter this as a formula:

image

It shows this as a result (notice the \n is missing):

image

Returning to the formula editor you can see that the slash has been escaped out with \\ for all values except for \n:

image

This strongly suggests that \n is treated differently and is either removed or replaced with a space preventing the results you’re trying to achieve.

(I’d love to be wrong on this one but it’s not looking good!) :slightly_smiling_face:

Thanks for your reply Gwynn! :slightly_smiling_face:

The addition of the line break happens in a different field in the next step, in a different formula. So it’s not applicable to the problem with the ARRAYJOIN formula not showing the currency format. It could become a problem then, but I can’t tell yet since the currency format already is removed by the ARRAYJOIN formula.

I’m wondering if there maybe is a way to change/add to the ARRAYJOIN formula (or add another formula field) in order to make a pretty view that turns the numbers back into a currency format?

Or would there be a different way to create the line breaks, without the ARRAYJOIN and SUBSTITUTE formulas, keeping the currency format from the lookup field?

I can’t figure it out.

As soon as a currency is converted to a string it loses its formatting. You can verify this by converting an amount to a string using this formula:

"" & {Amount}

I’ve added another field with the above formula and you can see the formatting is stripped off when converting to a string:

image

You can manually add the formatting back using this formula:

"$" & IF(FIND(".", {AmountString}) = 0, {AmountString} & ".00", {AmountString} & REPT("0", 2 - (LEN("" & {AmountString}) - FIND(".", "" & {AmountString}))))

That results in the rightmost column:

image

You can now go to your other table and define the ARRAYJOIN function on the manually formatted string:

ARRAYJOIN({AmountStringFormatted}, ";")

And here’s the result in the rightmost column:

image

Perhaps the above gets you a little closer to what you want to do.

I did try changing the semi-colon to the \n but Airtable replaces the newline with a space:

ARRAYJOIN({AmountStringFormatted}, "\n")

image

J_JBA
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks so much for this Gwynn! This does take me in the right direction!

I’m not quite there yet though, because in my table the output is different. I get an error for amounts that end on .00 or on two numbers after the decimal separator. And for amounts that end on one number and a 0 after the decimal separator it substitutes the final 0 for .00

Knipsel

I’m quite new to Airtable and formulas, so I don’t understand the formula well enough yet to understand why in my case the output is different than in your example. Maybe it’s because the {Incl Tax} field in my case is already a formula?

J_JBA
5 - Automation Enthusiast
5 - Automation Enthusiast

I got one step further and managed to get the amounts correctly when they finish on one zero. But the other ones (ending on two zeros or no zeros) still give an error.

This is what the formula looks like now:

"$" & IF(FIND(".", {Incl Tax}) = 0, {Incl Tax} & "0", {Incl Tax} & REPT("0", 2 - (LEN("" & {Incl Tax}) - FIND(".", "" & {Incl Tax}))))

This is the result in Airtable:
Knipsel

I really hope there’s a way to get this right. It would help me out a lot! :slightly_smiling_face:

J_JBA
5 - Automation Enthusiast
5 - Automation Enthusiast

I think I have solved it!

The problem was that the {Incl Tax} field was a formula field, which caused an error in the FIND formula in the next step.

My solution was to create a field called {Incl Tax concatenated} and then concatenate the {Incl Tax} field using the following formula:

“$” &{Incl Tax}

Then I’ve created a third field called {Incl Tax string} with the original formula from Gwynn :

IF(FIND(".", {Incl Tax concatenated}) = 0, {Incl Tax concatenated} & “.00”, {Incl Tax concatenated} & REPT(“0”, 2 - (LEN("" & {Incl Tax concatenated}) - FIND(".", “” & {Incl Tax concatenated}))))

Now it works:
Knipsel

So far it seems to be working properly, but I’m going to test it a bit more to make sure it’s working like it should.

Thanks so so much Gwynn for creating that formula and pointing me in the right direction! :grinning:

PS I’m not sure how to properly quote the formulas, so my apologies for the curly quotes.

There are several ways. My preference is to surround the formula in pairs of lines containing triplets of the “grave” character: ` With that in place, this in the editor:

```
IF({Field Name}, “True”, “False”)
```

…becomes this in the published post:

IF({Field Name}, "True", "False")

Thanks Justin, good to know! :slightly_smiling_face: