Currency Format in Email

[Sharon_Visser]

I have a field defined as Currency however when I add that field to the Message section of an email that I am creating from an Automation, the value does not appear as Currency. It does have the decimal point for the cents, and I can easily add “$” as part of the preceding text, but I would really like to see the commas that are part of the defined format on the field. Any ideas?

[ScottWorld]

Unfortunately, one of the limitations of automations is that the actions don’t recognize formatting from the fields.

This affects date & time fields, number fields, currency fields, rich text fields — even checkbox & rating fields.

So you would need to create a separate formula field to reformat your currency field all over again into how you would like it to look.

Yes, this is redundant. I’m hoping that Airtable addresses this limitation soon. Although I should mention that this limitation affects the API too. So, across the board, field formatting really isn’t recognized outside of the fields themselves.

In the meantime, this thread explains how to reformat your currency field with a formula:

Number and Currency 'Pretty-Print' Routines Show and tell

tl;dr version: A demonstration base offering three approaches to formatting numbers or numerals while providing ‘pretty-print’ formatting (e.g., currency indicators, thousands and decimal separators, and so on). Allows for non-US separators and decimals, as well. The third table supports parentheses for negative values, right-aligned output with a specified number of columns, and more. A while back, in response to this query, I started on what I thought would be no more than a half-hour’s task…

[AlliAlosa]

Unfortunately, there’s currently no easy way to do this. I have a ridiculously long and involved formula that I use (I will share it when I get to my computer later today).

Another option would be to use the scripting block - I have a function that will convert a number to a currency display value that I can share as well :slightly_smiling_face:

Last option - use Zapier to send the email instead of airtable. They have a formatting step that works well :slightly_smiling_face:

[ScottWorld]

Also, as @AlliAlosa mentioned, Zapier has a formatting option that you can turn to. And if you prefer Integromat, they also have a “formatNumber” function:

Integromat Support

Math Functions

average ([array of values]) average(value1; [value2], ...) Returns the average value of the numeric values in a specific array, or the average value of numerical values entered individually. ceil ...

[Sharon_Visser]

Thanks, @AlliAlosa - I would be interested to see your scripting block. As for Zapier, I’m currently using a Zap to send this notification. Now that we have Automations, I am trying to replace as many Zaps as I can with Automations. So keeping this one in Zapier is certainly an option.

[Sharon_Visser]

Thanks, @ScottWorld - I will take a look at your thread on reformatting. I appreciate your help!

[AlliAlosa · Accepted solution]

This should do the trick :slightly_smiling_face: Add a “Run a script” step to your automation, before the “Send Email” step.

In the side bar on the left, add an input variable called currency and choose your {Currency} field.

Use this code for the script:

let inputValues = input.config();
let currency = inputValues.currency;

//define function to convert numbers to currencies for report display
function toCurrency(rawValue) {
    return "$ " + (Number(rawValue)).toFixed(2).replace(/\d(?=(\d{3})+\.)/g, '$&,');
}

output.set("amount", toCurrency(currency));

This should output a formatted value for you to use in your email step :slightly_smiling_face:

Hope this helps!

[Sharon_Visser]

@AlliAlosa - I will give this a try! Thank you!

And … @AlliAlosa for the WIN! This worked perfectly (and was my first script too!). Much appreciated!!

[AlliAlosa]

Yay!! Happy to help :slightly_smiling_face:

Congrats on your first script as well - keep going! :smiling_face_with_halo: