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 ]%%
It's only fair to share...Tweet about this on TwitterShare on FacebookEmail this to someoneShare on Google+
This entry was posted in AmpScript, Email Dev, ExactTarget and tagged , , . Bookmark the permalink.