We have several data extensions that have data relationships with each other.

How can I send an email using one data extension while pulling data from a second data extension to personalize my email with ampscript?

I've tried Lookup in many different ways, but I've only managed to pull one specific row (the same row which is not personalized to each user) or all rows at once. I haven't been able to pull personalized data for each user.

In practice, I want to pull data from a DE called "Survey". Each user has a specific ID and some users have an "AnswerID", which varies. I want to to create IF/THEN statements based on that AnswerID.

Thanks, Sylvie

  • So, you're doing a send to a group of users, all of which have an AnswerID. Sometimes AnswerID is null, other times it has a real value and if it has a real value, you process it with conditional AMPscript. Is this correct? – Timothy Jan 21 '15 at 20:22
  • Also, please post some code. I've pulled from more than one DE at send time. – Timothy Jan 21 '15 at 20:34
  • I can't post this with at signs, so I'm using (at). That's right. As for code, I tried things as simple as this: %%=Lookup("Survey","Answer","QuestionID",168947)=%% And other things like: %%[ SET (at)rows = LookupRows ("Survey","QuestionID",168947) FOR (at)i = 1 TO RowCount((at)rows) DO Set (at)currentRow = Row((at)rows,(at)i) Set (at)AnswerID = Field((at)currentRow, "AnswerID")]%% %%=v((at)AnswerID)=%% %%[ next (at)i ]%% Eventually I came to see that, though I managed to access the data extension, I wasn't really getting what I wanted out of it. I'm relatively new at AmpScript... – Sylvie Jan 21 '15 at 21:41

The "Lookup()" function should give you the results needed. This will allow you to send to any list or data extension and pull back records from a completely different data extension. In order for this to work, your Unique Subscriber ID on the Lookup DE would need to relate back to the subscriber you're sending to. For example, if your Specific ID matches up with Subscriber Key on your account, you can setup something similar to the below.

More info can be found here as well: Data Extension Ampscript Functions

The below creates 3 variables. "SubscriberAnswer" does your lookup to pull the value from your other data extension. This says, pull the answer from the row of data where the Column "UniqueID" matches the value of "_SubscriberKey" for the subscriber being sent to. I've added the variable "Survey" to generate a standard response when their answer is not null. Then I perform an IF/Then statement in the variable "Answer" to say, If the variable "SubscriberAnswer" is null, pull back "Please fill out Survey", otherwise just pull back the value for "SubscriberAnswer". Hope that is clear and useful. Thanks!

Var @SubscriberAnswer, @Survey, @Answer
Set @SubscriberAnswer = Lookup("Survey","Answer","UniqueID",_subscriberkey)
Set @Survey = Concat("Thank You.  Your results can be found here:", "  ", @SubscriberAnswer)
Set @Answer = IIF(Empty(@SubscriberAnswer), "Please fill out Survey", @Survey)

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.