[SalesForce] Using AMPScript to retrieve multiple rows from a data extension

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.

Best Answer

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:

Related Topic