AMPscript to populate order items

Question

I am looking to use AMPscript in Marketing Cloud to populate an email that contains order information which will be housed in the sending data extension. I will need to populate the Item Description, Quantity (in numbers such as 1,2,5) and total cost. Is there an AMPscript function to help with that if there can be multiple items in an order and multiple quantities for the given item?

Answer 1

You can use the AMPscript function LookupRows(1,2,3) to retrieve one or more rows identified by a value in a given column (your order-number for example). Iterating over the results can be done using a for-loop. An example that combines those two can be found in the AMPscript 401.

Following resources could also be helpful:

• LookupRows documentation
• AMPscript language elements (which also contains information on for-loops)

Answer 2

I generally do it like this.

%%[
var @rows, @row, @rowCount, @numToReturn, @OrderNumber, @i

set @OrderNumber = AttributeValue("orderNumber") /* lookup column from sending data extension */
set @numToReturn = 0 /* 0 means all */
set @rows = LookupOrderedRows("OrderDetails",@numToReturn,"SKU desc","OrderNumber", @OrderNumber)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

for @i = 1 to @rowCount do

    var @SKU, @Qty, @Description
    set @row = row(@rows,@i) /* get row based on loop counter */
    set @SKU = field(@row,"SKU")
    set @Qty = field(@row,"Qty")
    set @Description = field(@row,"Description")

    ]%%

    <br>Row: %%=v(@i)=%%
    <br>SKU: %%=v(@SKU)=%%
    <br>Qty: %%=v(@Qty)=%%
    <br>Description: %%=v(@Description)=%%

    %%[

next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%

Combining/de-duplicating can be accomplished with some conditionals in the code above, but it's best done in a Query Activity before the send.