I am new to AmpScript and am trying to figure out how to reference data from 1 data extension with data in another data extension. I created a Data Relationship between the two but am having trouble pulling that data into an email.

Our 1st data extension includes client_email_addr (which is our primary key to email subscribers) and home_studio1.

The 2nd data extension includes home_studio1 and studio_url with home_studio1set to the primary key.

I would like to reference the 2nd data extension 2017_Master Studio List and when a customer's home_studio1 matches that list, display the coinciding studio_url within and email.

I found the lookup function syntax Lookup(1, 2, 3, 4), where I believe I would structure it as such:

%%=Lookup('2017_Master Studio List','studio_url','home_studio1',?)=%%

But I'm not sure what the #4 is in the syntax if I'm simply trying to find matching values. If I were to specifically list each location as that 4th value, I would have 100s of rows of AmpScript which seems to defeat the purpose.

Am I on the right track here?


As far as I know, the Data Relationship won't help you in AMPScript. Data Relationships are primarily for data filters.

I'd suggest using the lookupRows() function. Here's an example on how you might use it:

var @rows, @row, @rowCount
var @homeStudio
set @homeStudio = AttributeValue("home_studio1") /* value from attribute or DE column in send context */

set @rows = LookupRows("2017_Master Studio List","home_studio1", @homeStudio)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

  var @studio_url
  set @row = row(@rows,1) /* get row #1 */
  set @studio_url = field(@row,"studio_url")


studio_url is %%=v(@studio_url)=%%

%%[ else ]%%

No studio found

%%[ endif ]%%

You retrieve the current subscriber's value for home_studio1 and then use it to lookup rows in 2017_Master Studio List. The function returns a rowset and in this case, we only need the first match. We then can retrieve the value for studio_url from the row-set and assign it to a new AMPScript variable. Once you have the variable set, you can display it outside of the AMPScript block with %%=v()=%%.

There are some more lookup examples here.

  • Thanks Adam! What do you mean by setting a new AMPScript Variable? How do I do that? Nov 17 '17 at 19:52
  • You initialize a variable with var and set it with set. Nov 17 '17 at 20: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.