0

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

enter image description here

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

enter image description here

The outcome i am getting is below

enter image description here

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

enter image description here

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.

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>
1
  • Thank you Adam. This code works as intended. – Rahul G Feb 17 at 23:13

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.