Help

Re: Currency Format in Email

Solved
Jump to Solution
3454 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Sharon_Visser
7 - App Architect
7 - App Architect

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?
Currency in Table Currency in Email

1 Solution

Accepted Solutions

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:

Scripting_Ex

Hope this helps!

See Solution in Thread

13 Replies 13

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:

AlliAlosa
10 - Mercury
10 - Mercury

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:

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:

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.

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

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:

Scripting_Ex

Hope this helps!

@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!!

Yay!! Happy to help :slightly_smiling_face:

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

Lu
6 - Interface Innovator
6 - Interface Innovator

It's bananas that this is still a problem when Airtable could just add a token output modifier to choose between formatted and raw number, but this issue can be worked around with formulae.

If you use a formula field, you don't have to run any extra automation steps, it can be reused in multiple places/automations and you can see the result as soon as you edit the original field. Add the formula next to your numeric one, and set its contents to the following:

IF({Your field} < 0, "-", "") &
"$" &
REGEX_REPLACE(
	REGEX_REPLACE(
		REGEX_REPLACE(
			REGEX_REPLACE(
				RIGHT(
					REPT("0", 2) & ABS(ROUND({Your field} * POWER(10, 2))),
					MAX(2 + 1, LEN("" & ABS(ROUND({Your field} * POWER(10, 2)))))
				),
				"\\d{" & 2 & "}$",
				"." & "$&"
			),
			"^(\\d+)(\\d{" & 3 & "})",
			"$1" & "," & "$2"
		),
		"^(\\d+)(\\d{" & 3 & "})",
		"$1" & "," & "$2"
	),
	"^(\\d+)(\\d{" & 3 & "})",
	"$1" & "," & "$2"
)

All the bare number 2's (not the ones inside quotes) control the number of places after the decimal separator. Change all of them if you need a different length. Airtable uses the rather limited RE2 regex syntax (for security and performance), so it can't add all thousand separators in a single replace operation. Change the bare 3's if you need different length separators. The formula supports billions, but you can keep wrapping it in more replacements to support trillions, etc.

If you need to change the currency symbol, change only contents of the first string "$", all the other dollar signs are regex instructions. To change the separators, edit only the "." and "," that are by themselves.

Hi @AlliAlosa @ thanks for this awesome code and example base!

Congratulations on your willingness to help. Your formula worked very well but we continue to have problems with values ​​below 1,000.

We are in a difficult situation here. I have a lot of difficulty understanding the code and no ability to delve deeper into Java Script.

Could you please tell us how we could correct this point or suggest other possibilities that could format values ​​in a simple $ 9.999,99 / $ 999,99 ?

Thank you very much in advance.

Lu
6 - Interface Innovator
6 - Interface Innovator

The best way to do this is with the same kind of script step as in the accepted solution, but use Intl.NumberFormat as per my answer in another thread.

@Camanzi  I also updated my formula version so that it handles negative values and amounts less than 1 currency unit. You would need to change the currency from "$" to "R$ " and then swap the commas and decimal point.

this is giving me a blank in my email automation any idea why that might be? 

Lu
6 - Interface Innovator
6 - Interface Innovator

Use console.log(...) at various points in your script to check the values of your input and output variables when testing it.

Post a new help question if you continue to have trouble, unless you can contribute more information that will help others resolve the original problem.