1

I am using the LookupOrderedRows function in an email to pull in Apartment information such as Address, Number of Guests, Available Date, and Price.

The issue is that in some instances, the function doesn't call in the correct data and I don't understand how that's possible, since in the majority of cases the data is correct.

For example, you can see in the images attached that the Month Date is incorrect ("0ar" should be "Mär") and there are single-digit prices on the apartments (the Wilhelminenhofstrasse apartment should be €1916 instead of €2).

I need to understand why the function is serving up incorrect information and why it's inconsistent.

Has anyone else experienced LookupOrderedRows behaving inconsistently like this or could there be something in my script that I am doing wrong?

enter image description here

and...

enter image description here

and here's one that displays the correct info:

enter image description here

Here's an example of one of the LookupOrderedRows AMPscript that pulls in apartments of a certain price range. There are many other price range scripts that are exactly the same as this, but are referencing a different price range Filtered Data Extension.

    %%[
var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i,

set @lookupValue = AttributeValue("Pre_Platform_Inquiry__c:Apartment__r:City__c") /* value from attribute or DE column in send context */
set @lookupValue2 = '1' /* value from attribute or DE column in send context */
set @code_from_de = AttributeValue("Pre_Platform_Inquiry__c:Apartment__r:Code__c")
set @MonthlyRent__c = AttributeValue("Pre_Platform_Inquiry__c:Apartment__r:Price__c")
set @numRowsToReturn = 3 /* 0 means all, max 2000 */
set @rows = LookupOrderedRows("Filter_Available_Apartments_1500_1600",@numRowsToReturn,"Price__c, Code__c, Street__c, Postal_Code__c, CurrencyIsoCode, Country_Code__c, Homelike_ID__c, Cover_picture__c, Number_of_allowed_residents__c, Number_of_bedrooms__c, Number_of_bathrooms__c, Enabled_Booking_Modes__c, Cancellation_Flexibility__c, Security_Deposit__c, Available_From__c,","City__c", @lookupValue,"Number_of_bedrooms__c",@lookupValue2)
set @rowCount = rowcount(@rows)

if @rowCount > 0 AND @code_from_de != @Code__c then

  for @i = 1 to @rowCount do

    set @row = row(@rows,@i) /* get row based on counter */
    set @Code__c = field(@row,"Code__c")
    set @Street__c = field(@row,"Street__c")
    set @Postal_Code__c = field(@row,"Postal_Code__c")
    set @Country_Code__c = field(@row,"Country_Code__c")
    set @City__c = field(@row,"City__c")
    set @Homelike_ID__c = field(@row,"Homelike_ID__c")
    set @Price__c = field(@row,"Price__c") 
    set @Cover_picture__c = field(@row,"Cover_picture__c") 
    set @Number_of_allowed_residents__c = field(@row,"Number_of_allowed_residents__c")
    set @Number_of_bedrooms__c = field(@row,"Number_of_bedrooms__c")
    set @Number_of_bathrooms__c = field(@row,"Number_of_bathrooms__c")

    set @Enabled_Booking_Modes__c = field(@row,"Enabled_Booking_Modes__c")    
    set @CurrencyIsoCode = field(@row,"CurrencyIsoCode")         
    set @policy = field(@row,"Cancellation_Flexibility__c")         
    set @deposit = field(@row,"Security_Deposit__c")
    set @Available_From__c = field(@row,"Available_From__c")

Plus here's the price and date scripts:

    /*-- apartment price --*/
IF EMPTY(@Price__c) THEN
  SET @aptpricecalc = "0"
 ELSE
  SET @aptpricecalc = multiply(@Price__c,1)
 ENDIF

 IF @Language__c == 'German' THEN SET @aptprice = FormatNumber(v(@aptpricecalc),"N0","de-DE")
ELSEIF @Language__c == 'Spanish' THEN SET @aptprice = FormatNumber(v(@aptpricecalc),"N0","es-ES")
ELSEIF @Language__c == 'French' THEN SET @aptprice = FormatNumber(v(@aptpricecalc),"N0","fr-FR")
ELSEIF @Language__c == 'Dutch' THEN SET @aptprice = FormatNumber(v(@aptpricecalc),"N0","nl-NL")
ELSEIF @Language__c == 'English' THEN SET @aptprice = FormatNumber(v(@aptpricecalc),"N0","en-GB")
ELSEIF @Language__c == '' THEN SET @aptprice = FormatNumber(v(@aptpricecalc),"N0","en-GB")
ENDIF

IF @Language__c == 'German' THEN SET @currencylocale = "de-DE"
ELSEIF @Language__c == 'Spanish' THEN SET @currencylocale = "es-ES"
ELSEIF @Language__c == 'French' THEN SET @currencylocale = "fr-FR" 
ELSEIF @Language__c == 'Dutch' THEN SET @currencylocale = "nl-NL"
ELSEIF @Language__c == 'English' THEN SET @currencylocale = "en-GB" 
ENDIF

SET @dayNumber = FormatDate(@Available_From__c,"dd")
set @monthShortName = FormatDate(@Available_From__c,"MMM")
1
  • Hi I've taken the liberty to edit the headline for future reference; this is almost definitely format function behavior and not lookup related. Nov 30, 2021 at 7:49

1 Answer 1

1

I suspect the errors are in the format functions, not the lookup.

At least for the prices, I think I might have a suitable explanation as the problem pattern seems to be with German formatting:

In German, ',' not "." is the decimal separator in a number. I think what you are seeing is numbers being rounded up when enforcing "whole numbers in german formatting" (N0, de-DE) (so german 1,916 = english 1.916 turns into 2).

I would need to see the source field to give a definite solution, but I suspect they contain commas?

Edit, on the months:

I have played around with this for a minute and have a working solution for the month, at least when coming off a static date, assuming your "available_from__c" is a date.

You can shift to using Format() function, as seen here: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/dateTimeFormatting.htm

%%[ 
Set @date = "2021-03-22"
Set @monthShortName = Format(@date,"MMM","Date","de-DE")
]%%%%=v(@monthShortName)=%%

This returns "Mrz" which to my German tastes looks valid enough. The three letter abbreviation is uncommon here to begin with I would say, but that output is equally good/bad as "Mär". It's definitely understandable.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .