Referencing Data from Data Relationship

Question

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?

Answer 1

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.