0

I am using lookup to search records.

In Data extension 1, I have EmailAddress set as primary key In Data extension 2, I am allowing multiple rows of same email address

Data extension 1 data example: Email State [email protected] TX [email protected] TX

Data e xtension 2 data example: Email Group [email protected] A [email protected] B [email protected] C

So, in the search results by lookup, i want to display ALL rows that meets the search criteria.

In this example, if I lookup search for email "[email protected]"

The search result should be:

[email protected] A [email protected] C

This is the ampscript that I am using and currently the issue is that i am getting ONLY 1 row for the search term when there should be more than 1 row as the search term (email) exists more than once in dataextension2.

   %%[
var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i, @j, @rowTWO, @lookupValueTWO, @row2ndDE 

set @lookupValue = "[email protected]"
set @numRowsToReturn = 0
set @rows = Lookuprows("Dataextension1","Email", @lookupValue)
set @rowsTWO = Lookuprows("Dataextension1","Email", @lookupValue)
set @rowCount = rowcount(@rows)



if @rowCount > 0 then

for @i = 1 to @rowCount do

var @DEColumn1, @DEColumn2, @DEColumn3
set @row = row(@rows,@i) 
set @row2ndDE = row(@rowsTWO,@i) 
set @DEColumn1 = field(@row,"Email")
set @DEColumn2 = field(@row,"State")
set @DEColumn3 = field(@row2ndDE,"Group")

]%%

Email is %%=v(@DEColumn1)=%%, State is %%=v(@DEColumn2)=%%, Group is %%=v(@DEColumn3)=%%<br/>

%%[

next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%
1
  • Shouldn't the @rowsTWO lookup be referencing a different data extension? Feb 24, 2017 at 21:29

1 Answer 1

2

You can't mix row sets like that. You'll need to include an additional LookupRows():

%%[
var @rows, @row, @rowCount, @i
var @rows2 @row2, @rowCount2, @j

set @email = AttributeValue("_subscriberkey")
set @rows = LookupRows("DataExtension1","Email", @email)

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @EmailAddress, @State, @Group
        set @row = row(@rows,@i)

        set @EmailAddress = field(@row,"Email")
        set @State = field(@row,"State")

        outputline(concat("<br>", @i , ". Email: ", @EmailAddress))
        outputline(concat("<br>", @i , ". State: ", @State))        

        set @rows2 = LookupRows("DataExtension2","Email", @email)
        set @rowCount2 = rowcount(@rows2)

        if @rowsCount2 > 0 then

          for @j = 1 to @rowCount2 do

            set @row2 = row(@rows2,@j) 
            set @Group = field(@row2,"Group")

            outputline(concat("<br>", @j , ". Group: ", @Group))

          next @j

        endif

    next @i 

else

    outputline(concat("<br>no rows found"))

endif 

]%%
1
  • Thank you for the updated ampscript - I am able to get the desired result :) Thank you so much! Just a note if anyone else is looking for a similar solution. In the above code - a comma (,) is missing in declaration (@rows2 @row2) should be (@rows2, @row2) and at the end, please add --> ]%%
    – kuml
    Mar 1, 2017 at 15:59

You must log in to answer this question.

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