0

I´m quit new in the Amp-world and I´m hoping for help. I have newsletters with a lot of products and prices. Right now I have the content in data extension fields with the final value and currency format filled manually (e.g. 12,66 € for de-DE). That’s much to much work .

Therefore I am converting the prices into the correct currency format by using that term in the Amp-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,"pl_PL")=%%

That is almost working but the culture-code in the FormatCurrency-function is hard coded and I need it dynamic by customer.

In my sending data extension I have the field "country" to identify the needed culture code.

  • Value (field "country") and needed SF-culture code
  • Value = “001”  culture code “de_DE“
  • Value = “002”  culture code “pl_PL “
  • Value = “003”  culture code “sv_SE “

Is there a way to make that dynamic for each customer and the approximately 20 price fields in the newsletter? And do I have to do it seperatly for each price data field in AMP-initialization or is there a way to define the culture-code field only once for the whole mail?

Thanks in advance!

---update----

the error message: An error occurred when attempting to evaluate a TreatAsContent function call. See inner exception for details. An error occurred when attempting to evaluate a FormatCurrency function call. See inner exception for details. Function Call: FormatCurrency('5','003')

StackID: 10 EnterpriseID: 100004210 Content Type: HTML MachineName: DFW1S10CORSVC20 An error occurred when attempting to retrieve the culture's numeric format for a FormatCurrency function call. The passed culture value may not be valid. The third parameter value for a FormatNumber call must be a recognized culture name such as en-US, en-GB, es-ES, fr-FR, de-DE, or ja-JP. See inner exception for detail. Culture: 003 Culture is not supported. Parameter name: name 003 is an invalid culture identifier.

What is the link between my internal value "003" and the translation to the culture code? I´m looking forward to get smarter ;-)

--update---

I have tried your code and I think it is almost working. The relation the country and customer seems to work.

But I´m still getting that error message: An error occurred when attempting to evaluate a FormatCurrency function call. See inner exception for details. Function Call: FormatCurrency('','de_CH') 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 used different data types of the field “P3_Product1_Preis_Text1” (e.g. 17,99). I tried decimal, text, number. But it doesn´t work.

FYI: The used code (FYI: “Land” is the original field name instead of “country”)

set @P3_Product1_Preis_Text1= lookup(@source,"P3_Product1_Preis_Text1","countrylangkey",@countrylangkey) 
set @priceStr = lookup(@source,"P3_Product1_Preis_Text1","countrylangkey",@countrylangkey) 
set @priceStr2 = Replace(@priceStr,',','.')

set @country = trim(AttributeValue('Land'))

    if @country == '004' then
      set @code = "de_DE"
    elseif @country == '038' then
      set @code = "de_AT"
    elseif @country == '039' then
      set @code = "de_CH"

    endif

Set @dynamicPrice = Concat("%", "%=FormatCurrency('", @priceStr2,"','",@code,"')=%", "%")]%%

Thanks!

2

Although dynamic culture codes are not supported in the default functionality of FormatCurrency(), you can "hack" it by utilizing the concat() and TreatAsContent() functions. The code below sets the @country variable to the 'country' value in the data extension, then we're checking the value of that variable to decide which culture code should be used and assigning that value to the @code variable. We then build out the FormatCurrency() function as a concatenated string. Then TreatAsContent() is used to force the function to run. I included the html table to show the result of the concat() and then the actual result of the FormatCurrency() when TreatAsContent() is used.

%%[
set @P3_Product1_Preis_Text1= lookup(@source,"P3_Product1_Preis_Text1","countrylangkey",@countrylangkey) 
set @priceStr = lookup(@source,"P3_Product1_Preis_Text1","countrylangkey",@countrylangkey) 
set @priceStr2 = Replace(@priceStr,',','.')

set @country = trim(AttributeValue('country'))

if @country == '001' then
  set @code = "de_DE"
elseif @country == '002' then
  set @code = "pl_PL"
elseif @country == '003' then
  set @code = "sv_SE"
endif

Set @dynamicPrice = Concat("%", "%=FormatCurrency('", @priceStr2,"','",@code,"')=%", "%")

]%%
<table border="1" cellpadding="1" cellspacing="1">  
<tr><td>@dynamicPrice</td><td>%%=v(@dynamicPrice)=%%</td></tr>
<tr><td>TreatAsContent(@dynamicPrice)</td><td>%%=TreatAsContent(@dynamicPrice)=%%</td></tr>
</table>
  • thanks Jake. Unfortunatly it is not working but to be honest I still don´t understand your approach. I´ve updated the error message in the description. – Maik Jul 25 '18 at 17:38
  • 1
    Neat approach! Wouldn't have thought of splitting the double percents to suppress the function evaluation inside the concat statement... – Stephan de Paly Jul 25 '18 at 17:43
  • @Maik, I misread your original post and thought the culture code was in the "country" field in the sending DE. I edited my code to check the value of variable named "country" and set the value of the variable named "code" to the appropriate culture code. – jake314159 Jul 25 '18 at 18:13
  • @Jake, thanks. The if else if seems to work but I´m still getting an error message. Can´t be a big thing...See the details in the description. – Maik Jul 26 '18 at 9:19
  • @Maik, the error message refers to this function call: FormatCurrency('','de_CH'). I would guess that your priceStr2 variable is null/empty in that case. There should be a value populated inside the first set of single quotes in the FormatCurrency() function. – jake314159 Jul 26 '18 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.