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?
Solved
Currency Format in Email
+13Best answer by AlliAlosa
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.
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!
+35Unfortunately, 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.
Personally, I would recommend automating your emails with Make’s advanced automations for Airtable, because they offer hundreds of functions that allow you to format anything (currency, numbers. dates, text, times, etc.) in almost any way you want.
If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread.
For example, here is how you would instantly trigger a Make automation from Airtable.
I also give live demonstrations of how to use Make in many of my Airtable podcast appearances here.
— ScottWorld, Expert Airtable Consultant
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:
+13Unfortunately, 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:
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.
+13Unfortunately, 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.
Personally, I would recommend automating your emails with Make’s advanced automations for Airtable, because they offer hundreds of functions that allow you to format anything (currency, numbers. dates, text, times, etc.) in almost any way you want.
If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread.
For example, here is how you would instantly trigger a Make automation from Airtable.
I also give live demonstrations of how to use Make in many of my Airtable podcast appearances here.
— ScottWorld, Expert Airtable Consultant
Thanks, @ScottWorld - I will take a look at your thread on reformatting. I appreciate your help!
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.
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!
+13This 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!
@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 - 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:
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.
+2This 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!
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.
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.
+3It'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.
this is giving me a blank in my email automation any idea why that might be?
this is giving me a blank in my email automation any idea why that might be?
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.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.