I have a data extension with decimal numbers (seperated by comma) and formatted as text (changeable).
Now I want to use that data and put it with FormatCurrency()
into the right shape for the countries.
e.g. 50,00 –> culturecode de_AT € 50,00
I tried different things, but failed.
I tried to convert a decimal number (seperated by comma) into the correct curreny:
%%[
var @priceStr, @priceFormatted
set @priceStr = "20,50" /* for testing */
set @priceFormatted = formatCurrency(@priceStr,"de-AT")
]%%
the result is € 2.050,00
. I was hoping for € 20,50
.
Then I tried to reference directly to the data extension:
set @priceFormatted = FormatCurrency(@priceStr,"de-AT")
The error message:
An error occurred when attempting to evaluate a FormatCurrency function call. The input value () cannot be formatted as a number. The first parameter value for a FormatNumber call must be a numeric type supported by the requested format. Value: Format: C
I get that message by using textfields, decimals or numbers in the data extension.
update:
My source data field from a data extension: p3_Product1_Preis_Text1 (formatted as text) e.g. 17,99
My goal: convert that data into the currency format of the specific country by using the culture-code
My approach:
var @priceStr, @priceFormatted /generate new var´s
set @priceStr = replace(@p3_Product1_Preis_Text1,',','.') / replace the comma by a dot to make it fittable for the cuurency function
set @priceFormatted = FormatNumber(@priceStr,"C","de-DE") / using the function
The error message:
the replace-function is working. Thanks! Now when I´m connecting the AMP with the real data extension I´m still getting that error message: "The error message: An error occurred when attempting to evaluate a FormatCurrency function call. The input value () cannot be formatted as a number. The first parameter value for a FormatNumber call must be a numeric type supported by the requested format. Value: Format: C
I get that message by using textfields, decimals or numbers in the data extension.
Thanks in advance!
—-Update—-
Hi Johannes, sorry but I´m kind of new in the Amp-world.
With your last answer I tried that in the initialization:
set @P3_Product1_Preis_Text1= lookup(@source,"P3_Product1_Preis_Text1","countrylangkey",@countrylangkey)
set @priceStr = lookup(@source,"P3_Product1_Preis_Text1","countrylangkey",@countrylangkey)
var @priceStr2
set @priceStr2 = Replace(@priceStr,',','.')
I filled that in the content block: %%=FormatCurrency(@priceStr2,"fr_FR")=%%
and received the error message: An error occurred when attempting to evaluate a FormatCurrency function call. See inner exception for details.
Function Call: FormatCurrency(@priceStr2,"fr_FR")
An error occurred when attempting to evaluate a FormatCurrency function call. The input value () cannot be formatted as a number. The first parameter value for a FormatNumber call must be a numeric type supported by the requested format.
Value:
Format: C
What am I doing wrong? 🙁
Best Answer
I have tried this on a cloudpage.
Result
I believe you did something wrong. maybe a typical mistake like this one
%%=FormatCurrency(50,00,"fr_FR")=%%
In this case the comma will be used to seperate the parameters of the function and will not be used for the currecy value.If you have troubles with , and . inside the variable use the AMP function replace.
Code Example 2:
Result 2:
When you want to have this value while Sending you should request it with a LookUp or directly from the DataExtension (if you are sending from it). For reference of LookUp: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/lookup.htm