I have two data extensions.

Data extension A has a record for each quote created via our website in the last 24 hours. There may be multiple records per email address, if the user has elected to create multiple quotes.

Data extension B is created from data extension A, and captures unique email addresses from the last 24 hours. There is one record per email address.

I would like to send one follow-up email to the user, which includes a table containing information about all of the quotes they created in the last 24 hours. Obviously I cannot just send to data extension A, because that would send them an email for each quote record. My plan to accomplish this was creating data extension B and sending to that data extension. However, I've realized that I don't know how to code the email to reference data extension A while sending to data extension B.

Any advice for how to accomplish this task?

  • Look at the LookUpRows AMPScript function Jul 11 '18 at 16:47
  • Thank you. This works for my table, but how would I use this function if I only wanted to pull in one variable, like a personalization string? Jul 11 '18 at 17:30
  • 3
    You would utilize the Lookup() function for that. Please review the AMPscript functions available here - developer.salesforce.com/docs/… Jul 11 '18 at 18:18
  • Is it required to know what you're looking for to use the lookup function, though? I tried using it and couldn't get it to work for my use case. For example, if I wanted to insert their first name, I wouldn't write a lookup string for each individual first name in my data extension. Jul 12 '18 at 15:03
  • you would do Lookup(DE_A, 'FirstName', 'email_address', emailaddr). This would grab the first name in Data Extension A associated with the current sendable email address Jul 12 '18 at 21:12

To help anyone else that runs across this issue, I am turning the comments into an answer.

You would use the LookupRows() AMPScript function if you need to grab multiple values returned from a different DE

Sample: (multiple results)

   SET @Rowset = LookupRows('DEa','PrimaryKey', PrimaryKey)

   FOR @i = 1 TO ROWCOUNT(@Rowset) DO
     SET @Row = Row(@Rowset,@i)
     SET @fName = Field(@Row, 'FirstName')
     SET @lName = Field(@Row, 'LastName')

     OUTPUT(CONCAT(@fName, " ", @lName, "<br />")
     /* This will gather the First and Last Name of each row and Output it */

   NEXT @i

Sample: [single result using LookupOrderedRows()]

   SET @Rowset = LookupOrderedRows('DEa', 1, 'EmailAddress ASC','EmailAddress', emailaddr) 

   /* Returns only a single row meeting the WHERE criteria of EmailAddress = emailaddr 
   and ordered by Email Address Ascending */

     SET @Row = Row(@Rowset,1)
     SET @fName = Field(@Row, 'FirstName')
     SET @lName = Field(@Row, 'LastName')
     SET @otherValue = Field(@Row, 'OtherValue')

     /* Set the fields you want from the 'Row' (@Row) using the Field() function */


Or you would use the Lookup() AMPScript function if you only need a single value returned from a different DE.


SET @fName = Lookup(DE_A, 'FirstName', 'email_address', emailaddr) /* Will return the First Name only from a single entry*/

  • Thank you. I tried this and it worked, but as you said, it only provides the results from a single entry. Is there a way to get the results from all of the applicable records, besides in a table format? Jul 13 '18 at 20:38
  • you would do the LookupRows or LookupOrderedRows(). Then use field to get it. I will add an extra example to my answer. Jul 13 '18 at 21: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.