0

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 '17 at 20:12
2

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

<table>
    <tr> 
    <td>Advisor</td>
    <td>Advisor Email</td>
    <td>Advisor Phone</td>
    <td>Advisor Branch</td>
    </tr> 
%%[
/* 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") 

        ]%%
        <tr> 
        <td>%%=v(@advisor)=%%</td>
        <td>%%=v(@advisor_email)=%%</td>
        <td>%%=v(@advisor_phone)=%%</td>
        <td>%%=v(@advisor_branch)=%%</td>
        </tr> 

        %%[

    next @i

else
    /* default logic if there are no rows found for this account director */
endif 
]%%
</table> 
  • 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 '17 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 '17 at 18:43

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.