[SalesForce] AMPScript Lookup and LookupRows being stored

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".

Best Answer

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.

Related Topic