1

Is there a way to prevent Salesforce Marketing Cloud from storing previous Lookup values, or to allow subsequent Lookup functions to overwrite previous values?

Problem:
I recently built a Cloud Page that completed a number of AMPScript Lookups and Upserts before displaying the final page to the Subscriber.
In some edge cases, the AMPScript was required to retrieve and then update records within a Data Extension, and then later in the page conduct another Lookup (on the same Data Extension) to ensure all values are populated.

For example:

%%[
SET @field = Lookup("TestDE","Field",'SubscriberKey',@SubKey) //@field == 0
IF @field != 1 THEN
    UpsertData("TestDE",1,'SubscriberKey',@SubKey,"Field","1") //Field is now 1
ENDIF
SET @field = Lookup("TestDE","Field",'SubscriberKey',@SubKey) //@field == ?
]%%

While testing this page I noticed a curious phenomenon.
Salesforce Marketing Cloud "stores" the records/rows returned from a Lookup function, and it will not re-process the same Lookup function when asked to later in the page - instead it returns the same values stored from the earlier Lookup.
In the example above, the second "SET @Field" function returns the value "0".

1

Workaround:
There are 5 AMPScript Lookup functions that allow you to retrieve data from a Data Extension in Salesforce Marketing Cloud:

Most of these functions only store their own values, however LookupRows appears to overwrite the value returned by Lookup.
The following AMPScript can be run on a Cloud Page to show this in practice:

Create a DE called "TestDE" containing 2 (text) columns with the names "Key" and "A".

%%[
SET @LookupCol = "A"
SET @DE = "TestDE"
SET @KeyFieldValue = "1"
SET @KeyField = "Key"

UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"0") 
SET @Field_Lookup = Lookup(@DE,@LookupCol,@KeyField,@KeyFieldValue) 
UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"1") 
SET @Field_Lookup = Lookup(@DE,@LookupCol,@KeyField,@KeyFieldValue) 
SET @Field_LookupRows = FIELD(ROW(LookupRows(@DE,@KeyField,@KeyFieldValue),1),@LookupCol) 
UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"2") 
SET @Field_LookupRowsCS = FIELD(ROW(LookupRowsCS(@DE,@KeyField,@KeyFieldValue),1),@LookupCol) 
UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"3") 
SET @Field_LookupOrderedRows = FIELD(ROW(LookupOrderedRows(@DE,1,Concat(@KeyField," ASC"),@KeyField,@KeyFieldValue),1),@LookupCol)
UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"4") 
SET @Field_LookupOrderedRowsCS = FIELD(ROW(LookupOrderedRowsCS(@DE,1,Concat(@KeyField," ASC"),@KeyField,@KeyFieldValue),1),@LookupCol)
]%%

<b>First Check:</b><br>
Field_Lookup: %%=v(@Field_Lookup)=%%<br>
Field_LookupRows: %%=v(@Field_LookupRows)=%%<br>
Field_LookupRowsCS: %%=v(@Field_LookupRowsCS)=%%<br>
Field_LookupOrderedRows: %%=v(@Field_LookupOrderedRows)=%%<br>
Field_LookupOrderedRowsCS: %%=v(@Field_LookupOrderedRowsCS)=%%<br>

%%[
SET @Field_Lookup = Lookup(@DE,@LookupCol,@KeyField,@KeyFieldValue) 
SET @Field_LookupRows = FIELD(ROW(LookupRows(@DE,@KeyField,@KeyFieldValue),1),@LookupCol) 
SET @Field_LookupRowsCS = FIELD(ROW(LookupRowsCS(@DE,@KeyField,@KeyFieldValue),1),@LookupCol) 
SET @Field_LookupOrderedRows = FIELD(ROW(LookupOrderedRows(@DE,1,Concat(@KeyField," ASC"),@KeyField,@KeyFieldValue),1),@LookupCol)
SET @Field_LookupOrderedRowsCS = FIELD(ROW(LookupOrderedRowsCS(@DE,1,Concat(@KeyField," ASC"),@KeyField,@KeyFieldValue),1),@LookupCol)
]%%

<b>Second Check:</b><br>
Field_Lookup: %%=v(@Field_Lookup)=%%<br>
Field_LookupRows: %%=v(@Field_LookupRows)=%%<br>
Field_LookupRowsCS: %%=v(@Field_LookupRowsCS)=%%<br>
Field_LookupOrderedRows: %%=v(@Field_LookupOrderedRows)=%%<br>
Field_LookupOrderedRowsCS: %%=v(@Field_LookupOrderedRowsCS)=%%<br>

Output:
First Check:
Field_Lookup: 0
Field_LookupRows: 1
Field_LookupRowsCS: 2
Field_LookupOrderedRows: 3
Field_LookupOrderedRowsCS: 4
Second Check:
Field_Lookup: 1
Field_LookupRows: 1
Field_LookupRowsCS: 2
Field_LookupOrderedRows: 3
Field_LookupOrderedRowsCS: 4

Note that all values should have returned "4", however due to the Lookup Storing, the Second Check has returned the same stored values as the First Check, with the exception of "Lookup" which gets overwritten by LookupRows.

I hope this case study is a useful guide for any SFMC Developers confronted with problems when implementing multiple Lookup functions.

|improve this answer|||||
  • Are you testing with an actual send or just send preview? – Adam Spriggs Sep 23 '19 at 12:15
  • Upserts in emails are done at the end of the send job, which is why I avoid them. – Adam Spriggs Sep 23 '19 at 12:18
  • Hey Adam - this was tested on a Cloud Page. The actual page we built was a Campaign Landing Page that generated/updated vouchers from a 3rd party vendor via APIs. Due to some complexities on how the page could be accessed by subscribers (including an API Race Condition!), looking up the same data extension a couple of times was unavoidable. It took us a while to solve this curious edge case! Agreed on the risks of conducting data upserts within an email/send job. – Cameron Robert Sep 23 '19 at 12:32
  • 1
    Duh, I see the context is a CloupPage now that I read your question closely. Interesting. I do know that SF will cache DEs with under 1000 rows. We had issues like this in an email send. Perhaps that may be at play in your scenario? They can turn it off. – Adam Spriggs Sep 23 '19 at 12:40

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.