Retrieving multiple records from sendable DE

Question

I have a DE which can contain one or multiple rows for the same customer & this depends on the number of products they subscribe in an application. We want to email them the status of these products as and when they become Active.

Example DE below

I looked up Adam's blog and am using the following code.

var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i

set @lookupValue = AttributeValue("NAME")
set @numRowsToReturn = 0
set @rows = LookupOrderedRows("Test_welcome_rahul",@numRowsToReturn,"Product desc, Account_Status__c asc","NAME", @lookupValue)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

  for @i = 1 to @rowCount do

    var @Product, @Status
    set @row = row(@rows,@i) /* get row based on counter */
    set @Product = field(@row,"Product")
    set @Status = field(@row,"Account_Status__c")

    ]%%

   

    %%[ 
  next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%

In the email, I have a table like below

The outcome i am getting is below

The desired outcome is below. (assuming the first two rows in the DE are the same contact)

So essentially i want the query to lookup the Name column then display the Product and its respective Status in the same row. 1 x row per product & if there are multiple products then it will be multiple rows etc

I have tried a few variations with the code but not making much progress. Any help on this is appreciated.

Answer 1

You'll need to output the table rows inside your loop:

<table border="1">
  <tr>
    <td>Product</td>
    <td>Status</td>
  </tr>

%%[

var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i

set @lookupValue = AttributeValue("NAME")
set @numRowsToReturn = 0
set @rows = LookupOrderedRows("Test_welcome_rahul",@numRowsToReturn,"Product desc, Account_Status__c asc","NAME", @lookupValue)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

  for @i = 1 to @rowCount do

    var @Product, @Status
    set @row = row(@rows,@i) /* get row based on counter */
    set @Product = field(@row,"Product")
    set @Status = field(@row,"Account_Status__c")

    ]%%

    <tr>
       <td>%%=v(@product)=%%</td>
       <td>%%=v(@status)=%%</td>
    </tr>

    %%[ 
  next @i ]%%

%%[ else ]%%

    <tr>
       <td>no rows found</td>
       <td></td>
    </tr>

%%[ endif ]%%

</table>