Pulling data from a data extension like a Relational table

Pulling data from a data extension:

Pulling data from a data extension outside of the recipient list DE.

Let’s say I have a DE that is my list of email recipeints and there is another DE with a list of data about these recipients.

In this example, there is a list of animal shelters that will receive a list of animals that came from their shelters with info about each animal.

The Shelters list has a field called SHELTER_ID which is the primary key (unique).

The Anmials list also has a field called SHELTER_ID. The field is not unique, as more than one animal can have the same SHELTER_ID.

I will relate the two lists with this field. I will get records from the Animals list where SHELTER_ID is equal to SHELTER_ID in the Shelters list.

Data Extension Names:
Animals
Shelters

First I need to initialze all the variables I will need. I need variables to hold the results of the row lookup function (@multiRows), the individual row items as I loop through the rows(@row), the loop counter(@i), and the animal name and animal breed fields (@AnimalName, and @AnimalBreed).

VAR @multiRows, @row, @i, @AnimalName, @AnimalBreed

Then, I need to query the DE to get all the rows I’m looking for.

SET @multiRows = LookupRows(“Animals”,”SHELTER_ID”,[SHELTER_ID])

The code above is looking up rows in the Animals DE where “SHELTER_ID” field is equal to the “SHELTER_ID” field in the Shelters DE. And it assigns the results to the @multiRows variable.

Next, we will loop through the rows of data held in the @multiRows varible. During each iteration of the loop we will asign the data in the fields to the variables we created (@AnimalName, and @AnimalBreed).

%%[ for @i = 1 to RowCount(@multiRows) do Set @row = Row(@multiRows,@i) Set @AnimalName = Field(@row,”AnimalName”) Set @AnimalBreed = Field(@row,”AnimalBreed”) ]%%

While the loop is still open, we will render the data to the page (email)

%%[ Output(v(@CLINIC_NAME)) ]%%
%%[ Output(v(@CLINIC_EXT_ID)) ]%%

Then we close the loop.

%%[ next @i ]%%

Thank you for reading!

It's only fair to share...Tweet about this on Twitter
Twitter
Share on Facebook
Facebook
Email this to someone
email
This entry was posted in AmpScript, Email Dev, ExactTarget and tagged , , . Bookmark the permalink.