1

I'm working on creating a personalized display with multiple delivery dates based on date, country and language for every user.

I have a data extension with the dates, message for each country and the default language.

The problem is that some customers have a different language then the default language and I would like to display the message based on the Customer preferred language and the dates based on the country.

For the example in the screenshot, if I take the content based on es, then it will also pickup the country name that is implemented in the message column for es language. I can change to take up the content based on language and dates based on country but message might have another country name which might not be same as the users country because the language is not the default language

That is why I have scripted the email like if the users country and language combination from recipients DE is found in another DE then only populate the email

Is there a way to achieve the display to show the message in the language that the customer has set with the country dates that the customer has set?

If you have any questions please let me know

enter image description here

enter image description here

    %%[
SET @customerID = AttributeValue('CustomerID')

SET @user_rows = LookupRows('recipients','CustomerID',@customerID)
IF RowCount(@user_rows) > 0 THEN
SET @user_row = ROW(@user_rows,1)
SET @preferredcountryid = FIELD(@user_row,'preferredcountryid')
SET @language = FIELD(@user_row,'language')

IF EMPTY(@language) THEN
SET @language = 'en'
ENDIF

IF NOT EMPTY(@preferredcountryid) THEN

SET @date_rowsl = LookupRows('Xmas-Delivery-Dates','Language',@language,'preferredcountryid',@preferredcountryid)
IF RowCount(@date_rowsl) > 0 THEN
SET @date_rowl = ROW(@date_rowsl,1)
SET @date1_message = FIELD(@date_rowl,'LastBikeMessage')
SET @date2_message = FIELD(@date_rowl,'LastFreeMessage')
SET @date3_message = FIELD(@date_rowl,'LastPaidMessage')
SET @date4_message = FIELD(@date_rowl,'NextDayMessage')
SET @defaultdeliverymessage = FIELD(@date_rowl,'DeliveryOptionMessage')
SET @view = FIELD(@date_rowl,'VIEWDELIVERYDATES')
SET @gvhead = FIELD(@date_rowl,'giftvoucherHEADLINE')
SET @gvtext = FIELD(@date_rowl,'giftvoucherTEXT')
SET @gvshop = FIELD(@date_rowl,'giftvoucherSHOPNOW')
ELSE
RaiseError('Language and country combination not found','true')
ENDIF

ELSE

SET @date_rowsl = LookupRows('Xmas-Delivery-Dates','Language',@language)
IF RowCount(@date_rowsl) > 0 THEN
SET @date_rowl = ROW(@date_rowsl,1)
SET @date1_message = FIELD(@date_rowl,'LastBikeMessage')
SET @date2_message = FIELD(@date_rowl,'LastFreeMessage')
SET @date3_message = FIELD(@date_rowl,'LastPaidMessage')
SET @date4_message = FIELD(@date_rowl,'NextDayMessage')
SET @defaultdeliverymessage = FIELD(@date_rowl,'DeliveryOptionMessage')
SET @view = FIELD(@date_rowl,'VIEWDELIVERYDATES')
SET @gvhead = FIELD(@date_rowl,'giftvoucherHEADLINE')
SET @gvtext = FIELD(@date_rowl,'giftvoucherTEXT')
SET @gvshop = FIELD(@date_rowl,'giftvoucherSHOPNOW')
ELSE
RaiseError('Language not found','true')
ENDIF

ENDIF

IF EMPTY(@preferredcountryid) THEN
SET @mts = @defaultdeliverymessage
ELSE
SET @date_rows = LookupRows('Xmas-Delivery-Dates','preferredcountryid',@preferredcountryid)
IF RowCount(@date_rows) > 0 THEN
SET @date_row = ROW(@date_rows,1)
SET @country_name = FIELD(@date_row,'Country')
SET @date1 = FIELD(@date_row,'LastBike')
SET @date1 = FORMATDATE(@date1,'MM/DD/YYYY')
SET @date2 = FIELD(@date_row,'LastFree')
SET @date2 = FORMATDATE(@date2,'MM/DD/YYYY')
SET @date3 = FIELD(@date_row,'LastPaid')
SET @date3 = FORMATDATE(@date3,'MM/DD/YYYY')
SET @date4 = FIELD(@date_row,'NextDay')
SET @date4 = FORMATDATE(@date4,'MM/DD/YYYY')

IF EMPTY(@date1) AND EMPTY(@date2) AND EMPTY(@date3) AND EMPTY(@date4) THEN
SET @j = 1
ENDIF

SET @today = NOW()
SET @today = FORMATDATE(@today,'MM/DD/YYYY')

IF NOT EMPTY(@date1) THEN
SET @dtsc1 = DateDiff(@today,@date1,'D')
ELSE
SET @dtsc1 = 10000
ENDIF

IF NOT EMPTY(@date2) THEN
SET @dtsc2 = DateDiff(@today,@date2,'D')
ELSE
SET @dtsc2 = 10000
ENDIF

IF NOT EMPTY(@date3) THEN
SET @dtsc3 = DateDiff(@today,@date3,'D')
ELSE
SET @dtsc3 = 10000
ENDIF

IF NOT EMPTY(@date4) THEN
SET @dtsc4 = DateDiff(@today,@date4,'D')
ELSE
SET @dtsc4 = 10000
ENDIF

IF @dtsc1 < @dtsc2 AND @dtsc1 < @dtsc3 AND @dtsc1 < @dtsc4 AND @dtsc1 != 10000 AND @dtsc1 >= 0 THEN
SET @i = 1
ELSEIF @dtsc2 < @dtsc1 AND @dtsc2 < @dtsc3 AND @dtsc2 < @dtsc4 AND @dtsc2 != 10000 AND @dtsc2 >= 0 THEN
SET @i = 2
ELSEIF @dtsc3 < @dtsc1 AND @dtsc3 < @dtsc2 AND @dtsc3 < @dtsc4 AND @dtsc3 != 10000 AND @dtsc3 >= 0 THEN
SET @i = 3
ELSEIF @dtsc4 < @dtsc1 AND @dtsc4 < @dtsc2 AND @dtsc4 < @dtsc3 AND @dtsc4 != 10000 AND @dtsc4 >= 0 THEN
SET @i = 4
ELSE
SET @i = 5
ENDIF

IF @i == 1 THEN
SET @mts = @date1_message
SET @dts = FORMATDATE(@date1,'DD/MM/YYYY')
ELSEIF @i == 2 THEN
SET @mts = @date2_message
SET @dts = FORMATDATE(@date2,'DD/MM/YYYY')
ELSEIF @i == 3 THEN
SET @mts = @date3_message
SET @dts = FORMATDATE(@date3,'DD/MM/YYYY')
ELSEIF @i == 4 THEN
SET @mts = @date4_message
SET @dts = FORMATDATE(@date4,'DD/MM/YYYY')
ELSEIF @j == 1 THEN
SET @mts = @defaultdeliverymessage
ELSEIF @i == 5 THEN
SET @mts = @gvhead
SET @dts = @gvtext
SET @view = @gvshop
ELSE
ENDIF

ELSE
RaiseError('Country ID not found','true')
ENDIF
ENDIF


ELSE
RaiseError('Subscriber Not Found','true')
ENDIF
]%%

<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body>
<!-- White divider --><table class="fullwidth" style="width:600px;" cellspacing="0" cellpadding="0" border="0" align="center"><tr><td style="height:10px; background-color:#ffffff;" height="10"></td></tr></table>

<!-- Express Delivery START -->

<!--<table class="fullwidth" style="width:600px;" cellspacing="0" cellpadding="0" border="0" align="center"><tr><td style="height:6px; background-color:#ffffff;" height="6"></td></tr></table>

  <table width="100%" cellspacing="0" cellpadding="0" border="0">
  <tr> 
   <td valign="top" align="center">
   <table width="600" cellspacing="0" cellpadding="0" border="0">
     <tr>
      <td valign="top" align="center">
      <table style="background-color:#ffffff;" width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#ffffff">
        <tr>
         <td style="font-family:'Roboto Condensed', Arial, Sans-Serif; font-size: 16px; color: #3c5d6c; font-weight: normal; line-height: 16px; padding-top: 5px; padding-right: 30px; padding-left: 30px;" valign="top" align="center">
           preferredcountryid: %%=TreatAsContent(@preferredcountryid)=%%<br>
           language: %%=TreatAsContent(@language)=%%<br>
           date1_message: %%=TreatAsContent(@date1_message)=%%<br>
           date2_message: %%=TreatAsContent(@date2_message)=%%<br>
           date3_message: %%=TreatAsContent(@date3_message)=%%<br>
           date4_message: %%=TreatAsContent(@date4_message)=%%<br>
           date1: %%=TreatAsContent(@date1)=%%<br>
           date2: %%=TreatAsContent(@date2)=%%<br>
           date3: %%=TreatAsContent(@date3)=%%<br>
           date4: %%=TreatAsContent(@date4)=%%<br>
           today: %%=TreatAsContent(@today)=%%<br>
           dtsc1: %%=TreatAsContent(@dtsc1)=%%<br>
           dtsc2: %%=TreatAsContent(@dtsc2)=%%<br>
           dtsc3: %%=TreatAsContent(@dtsc3)=%%<br>
           dtsc4: %%=TreatAsContent(@dtsc4)=%%<br>
           i value: %%=TreatAsContent(@i)=%%<br>
           j avlue: %%=TreatAsContent(@j)=%%<br>


          </td>
        </tr>
      </table>
      </td>
     </tr>
   </table>
   </td>
  </tr>
</table>

  <br><br><br>-->
<table width="100%" cellspacing="0" cellpadding="0" border="0">
  <tr> 
   <td valign="top" align="center">
   <table width="600" cellspacing="0" cellpadding="0" border="0">
     <tr>
      <td valign="top" align="center">
      <table style="background-color:#ffffff;" width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#ffffff">
        <tr>
         <td style="font-family:'Roboto Condensed', Arial, Sans-Serif; font-size: 16px; color: #3c5d6c; font-weight: normal; line-height: 16px; padding-top: 5px; padding-right: 30px; padding-left: 30px;" valign="top" align="center">%%=TreatAsContent(@mts)=%% </td>
        </tr>
      </table>
      </td>
     </tr>
   </table>
   </td>
  </tr>
</table>
%%[ IF @j == 1 THEN]%%
%%[ ELSE ]%%
<table width="100%" cellspacing="0" cellpadding="0" border="0">
  <tr>
   <td valign="top" align="center">
   <table class="wrap100pc" width="600" cellspacing="0" cellpadding="0" border="0">
     <tr>
      <td valign="top" align="center">
      <table style="background-color:#ffffff;" width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#ffffff">
        <tr>
         <td style="font-family:'Roboto Condensed', Arial, Sans-Serif; font-size: 16px; color: #3c5d6c; font-weight: bold; line-height: 30px; padding-right: 30px; padding-left: 30px;" valign="top" align="center">%%=TreatAsContent(@dts)=%%</td>
        </tr>
      </table>
      </td>
     </tr>
   </table>
   </td>
  </tr>
</table>
%%[ ENDIF ]%%
<table width="100%" cellspacing="0" cellpadding="0" border="0">
  <tr>
   <td valign="top" align="center">
   <table class="wrap100pc" width="600" cellspacing="0" cellpadding="0" border="0">
     <tr>
      <td valign="top" align="center">
      <table style="background-color:#ffffff;" width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#ffffff">
        <tr>
         <td style="font-family:'Roboto Condensed', Arial, Sans-Serif; font-size: 11px; color: #91b9ca; font-weight: normal; line-height: 22px; padding-right: 30px; padding-left: 30px;" valign="top" align="center"><a target="_blank" href="https://www.google.com" style="outline:none; text-decoration:underline; font-family:'Roboto Condensed', Arial, Sans-Serif; color:#91b9ca;" border="0">%%=TreatAsContent(@view)=%%</a></td>
        </tr>
      </table>
      </td>
     </tr>
   </table>
   </td>
  </tr>
</table>


<table style="width:600px" cellspacing="0" cellpadding="0" border="0" align="center"><tbody><tr><td style="height:10px;background-color:#ffffff" height="10"></td></tr></tbody></table></body>
<code>%%[ if 0 == 1 then ]%% 
%%Member_Busname%%
%%Member_Addr%%
%%Member_City%%
%%Member_State%%
%%Member_PostalCode%%
%%Member_Country%%
</code>&lt;a href=&quot;%%profile_center_url%%&quot; alias=&quot;Update Profile&quot;&gt;Update Profile&lt;/a&gt;&lt;div style=&quot;text-align: center;&quot;&gt;
<code>%%[ endif ]%%</code>
<custom name="opencounter" type="tracking"/>
</html>
  • May I ask why didn't you use preferredcountryid from the target DE directly with AttiributeValue or just with %%preferredcountryid%%? – Selim Sevim Nov 27 '19 at 12:43
  • I could but that wouldn't have helped in solving my issue – Duicug Nov 27 '19 at 13:15
  • You are getting the error because "@preferredcountryid" is empty. Hence, I thought it might have been the issue. – Selim Sevim Nov 27 '19 at 13:23
2

I found it a bit hard to understand the question, but if I got it now, then the key question is this:

a)Is there a way to achieve the display to show the message in the language that the customer has set

b)with the country dates that the customer has set?

So if I understand correctly, you need to treat language and country independently UNLESS the combination is found...

So basically:

1) check the country the user gives (OK)

2)check the language the user gives (OK)

3)check the language that you have in your DE next to the user's country. (NEW)

If 2 and 3 match, good. Lookup by user country and user language.

If they don't match - fallback: lookup the content by user country and the language you have in the DE next to it (see 3)

I added indentation and comments, please see the bit of code I changed below. Code is not tested, but I hope it gets the idea across.

Hope this helps, let me know in case I misunderstood something.

 %%[
SET @customerID = AttributeValue('CustomerID')

SET @user_rows = LookupRows('recipients','CustomerID',@customerID)

IF RowCount(@user_rows) > 0 THEN
  SET @user_row = ROW(@user_rows,1)
  SET @preferredcountryid = FIELD(@user_row,'preferredcountryid')

/* find the users language */
  SET @language = FIELD(@user_row,'language')

/* find the language in the Data Extension to the users country */

/*updated code*/
  SET @languageFoundForCountryROWSET = LookupRows('Xmas-Delivery-Dates','Language',@language,'preferredcountryid',@preferredcountryid)
  SET @languageFoundForCountryROW = ROW((@languageFoundForCountryROWSET,1)
  SET @languageFoundForCountry = FIELD(@languageFoundForCountryROW,'language')
/* end update */


  /* set default language */
  IF EMPTY(@language) THEN
    SET @language = 'en'
  ENDIF

  If @language == @languageFoundForCountry THEN
    /* the language the user has given matches the language next to the user's given country in the DE, so we can use it in a lookup */
    SET @date_rowsl = LookupRows('Xmas-Delivery-Dates','Language',@language,'preferredcountryid',@preferredcountryid)
  ELSE
    /* the language the user has given + the country he has given are a combination that we don't have in our DE. So we just lookup the texts by what language we have in the DE for the user's country: @languageFoundForCountry  */

    SET @date_rowsl = LookupRows('Xmas-Delivery-Dates','Language',@languageFoundForCountry,'preferredcountryid',@preferredcountryid)
  ENDIF
  IF RowCount(@date_rowsl) > 0 THEN
    SET @date_rowl = ROW(@date_rowsl,1)

    SET @date1_message = FIELD(@date_rowl,'LastBikeMessage')
    SET @date2_message = FIELD(@date_rowl,'LastFreeMessage')
    SET @date3_message = FIELD(@date_rowl,'LastPaidMessage')
    SET @date4_message = FIELD(@date_rowl,'NextDayMessage')
    SET @defaultdeliverymessage = FIELD(@date_rowl,'DeliveryOptionMessage')
    SET @view = FIELD(@date_rowl,'VIEWDELIVERYDATES')
    SET @gvhead = FIELD(@date_rowl,'giftvoucherHEADLINE')
    SET @gvtext = FIELD(@date_rowl,'giftvoucherTEXT')
    SET @gvshop = FIELD(@date_rowl,'giftvoucherSHOPNOW')
  ELSE
    RaiseError('Language not found','true')
  ENDIF

/*....rest of code */

ELSE
  RaiseError('Subscriber Not Found','true')
ENDIF
]%%
  • Thanks for taking the time, I'm getting this error with your code Failed to convert parameter value from a DataRow[] to a String. Object must implement IConvertible. – Duicug Nov 28 '19 at 9:06
  • Hi, please try again. I highlighted the change with a comment /*updated code*/ Problem must have been that I tried to work with a rowset, without singling out ROW and FIELD in the comparison line "If language == languageFoundForCountry THEN" – Jonas Lamberty Nov 28 '19 at 11:24
  • unfortunately I get an error again There is an error in your email. Please contact your customer service representative. Error 1: Script IF Not Closed<br>An IF statement is not closed with the required ENDIF statement.<br>Invalid Content: <br>IF RowCount(user_rows) > 0 THEN SET user_row = ROW(user_rows,1) SET preferredcountryid = FIELD(user_row,'preferredcountryid') SET language = FIELD(user_row,'language') SET ;anguageFoundForCountryROWSET = LookupRows('Xmas-Delivery-Dates','Language',language,'preferredcountryid',@preferredc – Duicug Nov 28 '19 at 11:41
  • Sorry, that I can't help much with. the code above should have all IF statements closed, so the error is somewhere in the code I didn't touch. Make sure to indent your code as seen in my example (helps to check whether all IF statements are complete), and isolate the issue by commenting out bits and pieces until the code works again. Note also, there might be a typo here: SET ;anguageFoundForCountryROWSET - doublecheck that this line is correct. – Jonas Lamberty Nov 28 '19 at 11:47
  • thanks anyway, the typo is because I had to remove the "@" from the comment and I deleted the first letter of the language, but it isnt in the code – Duicug Nov 28 '19 at 11:52

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.