#cloud-pages

I have recently stumbled upon a particular limitation in SFMC AMPScript Lookup() function that I thought was interesting to markdown and share.

Namely, I was trying to look up a field in my data extension in a very ordinary way, checking that every field and data extension existed, but Lookup() would make my Cloud Page hang in the preview or throw Error 500 when published. It was a peculiar situation.

In the SFMC database, the lookup key field does not have an explicit length limit. It might have been the first time I've seen this, but it appears the UI allows the creation of MAX length fields by setting length to "-1".

Given the database SFMC is running on, the MAX equivalent for a text field is going to be 4000 characters. We may be able to work around this error by creating text fields with length set explicitly to that number, which achieves the same result or use another lookup key that has the length defined, which is what I ended up doing.

I took that expression and placed it in a CloudPage in a test account and was able to do a lookup on a text field of length 4000. Meanwhile, the preview wouldn't even load when I set the field length implicitly.

To cut a long story short, after some research and a chat with Salesforce support, I concluded the following:

"The Data Extension Key field for a Lookup function call is invalid. The key field has a MAX or unlimited length. Fields with MAX or unlimited length cannot be used as keys."

var @acid, @email, @dateOfBirth 

set @acid = AttributeValue("acid") 

if not empty(@acid) then   

set @email = Lookup("MAIN SUBSCRIBER", "EMAILADDRESS", "ACIDKEY", @acid)   

set @dateOfBirth = Lookup("MAIN SUBSCRIBER", "BIRTHDATE", "ACIDKEY", @acid)