I have 2 data extensions:

  1. advisers and account directors (lookup file). Table headers: ADVISOR, ACCOUNT_DIRECTOR, ADVISOR_EMAIL, ADVISOR_PHONE, ADVISOR_BRANCH.
  2. directors emails (send data). Table headers: ACCOUNT_DIRECTOR, ACCOUNT_DIRECTOR_EMAIL. In the first one each account director may have one or more adviser listed. E.g. account director John has Joe, Marry, Priscilla and Tim as advisers (rows 1, 15, 23 and 24).

I need to email each account director from the second data extension (send data file), a table with this header (all her or his advisors): ADVISOR, ADVISOR_EMAIL, ADVISOR_PHONE, ADVISOR_BRANCH

Basically, I need to pull all advisors associated to every account director.

How can I do this with AMPscript?

  • the account director repeats multiple times and because that is an unique identifier, I'm stuck at it. I know I can use multiple primary keys but I don't want to use that, not even sure if I can resolve that way.
    – Snowalker
    Feb 13, 2017 at 20:12

1 Answer 1


Here you go. Should point you in the right direction:

    <td>Advisor Email</td>
    <td>Advisor Phone</td>
    <td>Advisor Branch</td>
/* pull the rowset */
set @rows = lookuprows("Advisors and account directors", "account_director", [Account_Director])

if rowcount(@rows) > 0 then 

    /* Iterate through each row and print results to html */
    for @i=1 to rowcount(@rows) do 
        set @row = Row(@rows, @i) 
        set @advisor = field(@row, "advisor")
        set @advisor_email = field(@row, "advisor_email")
        set @advisor_phone = field(@row, "advisor_phone")
        set @advisor_branch = field(@row, "advisor_branch") 



    next @i

    /* default logic if there are no rows found for this account director */
  • Thanks David but I'm given with an error: Error 1: Script IF Not Closed An IF statement is not closed with the required ENDIF statement.
    – Snowalker
    Feb 13, 2017 at 20:59
  • Thanks, actually it works, I needed to tweak the HTML a bit because it wouldn't show anything but a blank page. Works now, thanks David and thanks Kevin for editing.
    – Snowalker
    Feb 21, 2017 at 18:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .