Marketing Cloud: AMPScript One-to-Many

Question

I'm attempting to utilize AMPScript to create an invoice that lists all items on one order for a particular customer. I know this is a one-to-many AMPScript challenge question that has been asked by several others previously, but I can't seem to make it work with my scenario.

Here's my email and associated AMPScript code. The last table row of data is where I want to have the multiple items listed/repeated. I'm just having trouble deploying the LOOKUP, FIELD, ROW syntax to my situation:

%%[ 

var @firstname, @ordernum, @custfullname, @cusid, @custshiptoadd, 
@custshiptocit, @custshiptosta, @custshiptozip, @invdat, @shipdat, @itemid 
@lineitem, @qty, @price, @btlsize

Set @firstname = [FirstName]
Set @ordernum = [OrderNumber]
Set @custfullname = [FullName]
Set @cusid = [Cust_ID]
Set @custshiptoadd = [Address]
Set @custshiptocit = [City]
Set @custshiptosta = [State]
Set @custshiptozip = [Zip]
Set @invdat = [InvoiceDate]
Set @shipdat = [ShippingDate]
Set @itemid = [item_id]
Set @lineitem = [ItemName]
Set @qty = [Qty]
Set @price = [Price]
Set @btlsize = [Size]


]%%


<strong>Order Number:</strong> %%=v(@ordernum)=%%<br><br>

<strong>Customer Name:</strong> %%=v(@custfullname)=%%<br>      
<strong>Customer ID:</strong> %%=v(@cusid)=%%<br><br>

<strong>Ship to Address:</strong><br>
%%=v(@custfullname)=%%<br>
%%=v(@custshiptoadd)=%%<br>
%%=v(@custshiptocit)=%%, %%=v(@custshiptosta)=%% %%=v(@custshiptozip)=%%<br>

<br><br>
<strong> Invoice Date:</strong> %%=v(@invdat)=%%<br>             
<strong>Shipping Date:</strong> %%=v(@shipdat)=%%<br><br>

<table style="width:100%; font-family:'Century Gothic', Arial, Helvetica, sans-serif; font-size:14px; color:#252525;" cellspacing="0" cellpadding="0" border="0">
<tr>
<th style="text-align: center; width: 20%; border: 1px solid gray;">Description</th>
<th style="text-align: center; width: 20%; border: 1px solid gray;">ItemId</th>
<th style="text-align: center; width: 20%; border: 1px solid gray;">Quantity</th> 
<th style="text-align: center; width: 20%; border: 1px solid gray;">Price</th>
<th style="text-align: center; width: 20%; border: 1px solid gray;">Bottle Size</th>
</tr>

<tr>
<td style="text-align: left; width: 20%; border: 1px solid gray;">%%=v(@lineitem)=%%</td>
<td style="text-align: left; width: 20%; border: 1px solid gray;">%%=v(@itemid)=%%</td>
<td style="text-align: center; width: 20%; border: 1px solid gray;">%%=v(@qty)=%%</td> 
<td style="text-align: center; width: 20%; border: 1px solid gray;">%%=v(@price)=%%</td>
<td style="text-align: center; width: 20%; border: 1px solid gray;">%%=v(@btlsize)=%%</td>
</tr>
</table>

Here's the Data Extension I'm pulling from:

I've used these two posts as references, but can't seem to make it work with my scenario:

Using AMPScript to retrieve multiple rows from a data extension

how to achieve one to many relationship in ampscript

Any help anyone can offer, would be so greatly appreciated. Thank you very much in advance!

Eliot Harper · Accepted Answer · 2017-04-08 21:45:29Z

The following code will work. Note:

I have contained your personalization strings in AttributeValue functions; this is best practice as if the field is not available in your sendable DE, then your email will error.
This code retrieves the matching order numbers for the given OrderNumber of the current Subscriber and displays them in individual table rows.
Replace YourOrderDEName with the name of the Data Extension you are using.

<!DOCTYPE html>
<html>
<head></head>
<body>

%%[

var @firstname, @ordernum, @custfullname, @cusid, @custshiptoadd
var @custshiptocit, @custshiptosta, @custshiptozip, @invdat
var @shipdat, @itemid, @lineitem, @qty, @price, @btlsize

Set @firstname = AttributeValue('FirstName')
Set @ordernum = AttributeValue('OrderNumber')
Set @custfullname = AttributeValue('FullName')
Set @cusid = AttributeValue('Cust_ID')
Set @custshiptoadd = AttributeValue('Address')
Set @custshiptocit = AttributeValue('City')
Set @custshiptosta = AttributeValue('State')
Set @custshiptozip = AttributeValue('Zip')
Set @invdat = AttributeValue('InvoiceDate')
Set @shipdat = AttributeValue('ShippingDate')
Set @itemid = AttributeValue('item_id')
Set @lineitem = AttributeValue('ItemName')
Set @qty = AttributeValue('Qty')
Set @price = AttributeValue('Price')
Set @btlsize = AttributeValue('Size')

]%%

<strong>Order Number:</strong> %%=v(@ordernum)=%%<br><br>

<strong>Customer Name:</strong> %%=v(@custfullname)=%%<br>      
<strong>Customer ID:</strong> %%=v(@cusid)=%%<br><br>

<strong>Ship to Address:</strong><br>
%%=v(@custfullname)=%%<br>
%%=v(@custshiptoadd)=%%<br>
%%=v(@custshiptocit)=%%, %%=v(@custshiptosta)=%% %%=v(@custshiptozip)=%%<br>

<br><br>
<strong>Invoice Date:</strong> %%=v(@invdat)=%%<br>             
<strong>Shipping Date:</strong> %%=v(@shipdat)=%%<br><br>

<table style="width:100%; font-family:'Century Gothic', Arial, Helvetica, sans-serif; font-size:14px; color:#252525;" cellspacing="0" cellpadding="0" border="0">
<tr>
<th style="text-align: center; width: 20%; border: 1px solid gray;">Description</th>
<th style="text-align: center; width: 20%; border: 1px solid gray;">ItemId</th>
<th style="text-align: center; width: 20%; border: 1px solid gray;">Quantity</th> 
<th style="text-align: center; width: 20%; border: 1px solid gray;">Price</th>
<th style="text-align: center; width: 20%; border: 1px solid gray;">Bottle Size</th>
</tr>

%%[
var @orderItems, @orderQuantity, @orderRow, @i
set @orderItems = LookupRows('YourOrderDEName','OrderNumber', @ordernum)
set @orderQuantity = rowcount(@orderItems)

if @orderQuantity > 0 then

for @i = 1 to @orderQuantity do

var @orderRow, @orderLineItem, @orderLineItemId, @orderLineItemQty
var @orderLineItemPrice, @orderLineItemBtlSize

set @orderRow = row(@orderItems,@i)

set @orderLineItem = field(@orderRow,'ItemName')
set @orderLineItemId = field(@orderRow,'item_id')
set @orderLineItemQty = field(@orderRow,'Qty')
set @orderLineItemPrice = field(@orderRow,'Price')
set @orderLineItemBtlSize = field(@orderRow,'Size')

]%%

<tr>
<td style="text-align: left; width: 20%; border: 1px solid gray;">%%=v(@orderLineItem)=%%</td>
<td style="text-align: left; width: 20%; border: 1px solid gray;">%%=v(@orderLineItemId)=%%</td>
<td style="text-align: center; width: 20%; border: 1px solid gray;">%%=v(@orderLineItemQty)=%%</td> 
<td style="text-align: center; width: 20%; border: 1px solid gray;">%%=v(@orderLineItemPrice)=%%</td>
<td style="text-align: center; width: 20%; border: 1px solid gray;">%%=v(@orderLineItemBtlSize)=%%</td>
</tr>

%%[ next @i ]%%

%%[ endif ]%%

</table> 

</head>
<body>

This code will produce the following output:

I almost forgot to mention, you woudn't use this DE as your sendable DE; you would have one DE containing distinct Order Numbers, then you would use LookupRows to reference an Order Details DE (which I have done in my example). — Eliot Harper, Apr 9 '17 at 10:29
Hi Eliot, thanks for your answer and help. I'm seeing the theme with the answers here. So it looks like Best Practice is to always create a separate sendable DE with the distinct subscribers first, instead of using the DE with all of the original data? Thanks again! — Andre Lopez, Apr 9 '17 at 17:59

Adam Spriggs · Accepted Answer · 2017-04-08 23:18:28Z

I'd recommend using a Query Activity to isolate your sending audience first:

select distinct
o.emailaddress
, o.SF_Contact_ID
, o.orderNumber
from Orders o
/* name: orders_send */
/* target: orders_send */
/* action: overwrite */

Then in your email, the scripting would be something like this:

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

set @SFContactID = AttributeValue("SF_Contact_ID")
set @orderNumber = AttributeValue("orderNumber")
set @numRowsToReturn = 0 /* 0 means all */
set @rows = LookupOrderedRows("Orders",@numRowsToReturn,"OrderNumber desc ","SF_Contact_Id", @SFContactID, "orderNumber", @orderNumber)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @firstname, @ordernum, @custfullname, @cusid, @custshiptoadd, 
        @custshiptocit, @custshiptosta, @custshiptozip, @invdat, @shipdat, @itemid 
        @lineitem, @qty, @price, @btlsize

        set @row = row(@rows,@i) /* get row based on loop counter */

        Set @firstname =  field(@row,"FirstName")
        Set @ordernum =  field(@row,"OrderNumber")
        Set @custfullname =  field(@row,"FullName")
        Set @cusid =  field(@row,"Cust_ID")
        Set @custshiptoadd =  field(@row,"Address")
        Set @custshiptocit =  field(@row,"City")
        Set @custshiptosta =  field(@row,"State")
        Set @custshiptozip =  field(@row,"Zip")
        Set @invdat =  field(@row,"InvoiceDate")
        Set @shipdat =  field(@row,"ShippingDate")
        Set @itemid =  field(@row,"item_id")
        Set @lineitem =  field(@row,"ItemName")
        Set @qty =  field(@row,"Qty")
        Set @price =  field(@row,"Price")
        Set @btlsize =  field(@row,"Size")

        ]%%

         %%[ if @i == 1 then ]%%

            <strong>Order Number:</strong> %%=v(@ordernum)=%%<br><br>
            <strong>Customer Name:</strong> %%=v(@custfullname)=%%<br>      
            <strong>Customer ID:</strong> %%=v(@cusid)=%%<br><br>
            <strong>Ship to Address:</strong><br>
            %%=v(@custfullname)=%%<br>
            %%=v(@custshiptoadd)=%%<br>
            %%=v(@custshiptocit)=%%, %%=v(@custshiptosta)=%% %%=v(@custshiptozip)=%%<br>
            <br><br>
            <strong> Invoice Date:</strong> %%=v(@invdat)=%%<br>             
            <strong>Shipping Date:</strong> %%=v(@shipdat)=%%<br><br>
            <table cellspacing="0" cellpadding="0" border="0">
                <tr>
                    <th>Description</th>
                    <th>ItemId</th>
                    <th>Quantity</th> 
                    <th>Price</th>
                    <th>Bottle Size</th>
                </tr>

        %%[ endif ]%%

        <tr>
            <td>%%=v(@lineitem)=%%</td>
            <td>%%=v(@itemid)=%%</td>
            <td>%%=v(@qty)=%%</td> 
            <td>%%=v(@price)=%%</td>
            <td>%%=v(@btlsize)=%%</td>
        </tr>

         %%[ if @i == @rowCount then ]%%

            </table>  

        %%[ endif ]%%

        %%[

    next @i ]%%


%%[ else ]%%

    Order not found

%%[ endif ]%%

I have some other AMPScript lookup examples here on my blog.

Hi Adam, Thank you very much for your response. I'm sorry, but I'm a real newbie and am learning on the fly. Seems like there are two different components to your solution that I'm not quite following. I normally do guided sends and send straight to the DE I'm pulling information from. In regards to what you propose, once I isolate my sending audience, how do I relate it to the email? — Andre Lopez, Apr 9 '17 at 8:41
You'd create a two step Automation in Automation Studio. Step 1 is the Query Activity. Step 2 is the Send Definition that associates your sending audience and the email like in a Guided Send. — Adam Spriggs, Apr 9 '17 at 11:29
Awesome, got it, thanks Adam. reading your answer here salesforce.stackexchange.com/questions/122449/… helped me better understand it all. Thanks again! — Andre Lopez, Apr 9 '17 at 17:43

Stack Exchange Network

current community

your communities

more stack exchange communities

Marketing Cloud: AMPScript One-to-Many

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged marketing-cloud email ampscript or ask your own question.

Linked

Hot Network Questions

Marketing Cloud: AMPScript One-to-Many

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged marketing-cloud email ampscript or ask your own question.

Linked

Related

Hot Network Questions