Re: Currency format in formula field

J_JBA · ‎Aug 10, 2020

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!

gwynn_kruger · ‎Aug 10, 2020

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

When I enter this as a formula:

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

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

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:

J_JBA · ‎Aug 10, 2020

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.

gwynn_kruger · ‎Aug 11, 2020

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:

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:

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:

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")

J_JBA · ‎Aug 11, 2020

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

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 · ‎Aug 11, 2020

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:

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

J_JBA · ‎Aug 12, 2020

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:

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.

Justin_Barrett · ‎Aug 15, 2020

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")

J_JBA · ‎Aug 17, 2020

Thanks Justin, good to know! :slightly_smiling_face: