Using AMPScript to retrieve multiple rows from a data extension

Question

I'm trying to create an email that provides sellers on a website with a breakdown of sales they have made the previous day.

We have a data extension we will be sending to, and a data extension we need to pull the content from. They are related by a shared user ID value, but the latter table is 1:many, as the user can sell multiple items (1 row per item). We therefore need to concatenate all possible values associated with a user ID - to be ordered alphabetically.

I tried something like this, but I think I'm way off:

<!--%%[SET @UID = [User_ID]]%% -->
<!--%%[
SET @Rows = LookupRows("DAILY SALES DE","User_ID",@UID)
IF RowCount(@Rows) > 0 THEN
 SET @resource_name = FIELD(ROW(@Rows,1),"resource_name")
 SET @store_purchases = FIELD(ROW(@Rows,1),"store_purchases")
THEN DO
 SET @resource_list = "<li style='border-bottom: 1px solid #ebeced;'>%%=v(resource_name)=%%<span style='font-style:italic; font-weight:400; color:5c656e;'> %%=v(@store_purchases)=%%</span></li>"
ELSE 
  SET @resource_list = "&nbsp;"
ENDIF
]%% -->

The AMPscript within AMPscript is a problem here, as it doesn't display properly.

I'm sure I'm going about this the wrong way however; is there an easy way to concatenate all the associated values from the content data extension and display them as list items specified above?

Any info would be greatly appreciated.

Answer 1

Welcome to SFSE, Antonio!

Props for posting your WIP code in your question.

Here's how I would code it:

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @uid, @i

set @uid = AttributeValue("user_id")
set @numRowsToReturn = 0 /* all */
set @rows = LookupOrderedRows("DAILY SALES DE",@numRowsToReturn,"resource_name asc, store_purchases asc","User_ID", @uid)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @resource_name, @store_purchases
        set @row = row(@rows,@i) 
        set @resource_name = field(@row,"resource_name")
        set @store_purchases = field(@row,"store_purchases")

        if @i == 1 then
          outputline(concat("<ul>"))
        endif

        ]%%

           <li style="border-bottom: 1px solid #ebeced;">
                %%=v(@resource_name)=%%&nbsp;<span style="color:#5c656e;"><i><b>%%=v(@store_purchases)=%%</b></i></span>
           </li>

        %%[ 

        if @i == @rowcount then
          outputline(concat("</ul>"))
        endif


    next @i 

]%%

%%[ else ]%%

No rows found

%%[ endif ]%%

You won't want to comment out anything since the AMPScript needs to output HTML.

Reference:

• AMPScript Lookup Examples
• LookupOrderedRows() AMPScript function
• Prettying Up AMPScript in Emails