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!