We're struggling with our ampscript for our order confirmation emails. The error we get when previewing is
Invalid index (parameter 2) passed to Row function. Index must be less than or equal to the row count. Index Value: 1 Row Count: 0 Function: Row(BuildRowSetFromXML(@xml, "/OrderTransaction/FirstName"),1)
Here's the xml that's generated:
<?xml version="1.0" encoding="utf-16"?><OrderTransaction
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://cipd.co.uk/webServices/CIPDNAT"> <BillingAddress> <string>CIPD</string> <string>151 The Broadway</string> <string /> <string>Wimbledon</string> <string> </string> <string>SW19 1JQ</string> <string /> <string>GB</string> </BillingAddress> <DeliveryAddress /> <PaymentMethod>invoice</PaymentMethod> <FirstName>Chris </FirstName> <Surname>Mathews</Surname> <OrderDate>2018-01-18T10:32:18+00:00</OrderDate> <OrderId>123456789</OrderId> <OrderTotal>598.8</OrderTotal> <OrderTotalExVAT>499</OrderTotalExVAT> <OrderTotalVAT>99.8</OrderTotalVAT> <Items> <TransactionItem> <ItemName>Law On Tour Spring 2018</ItemName> <Price>598.8</Price> <Quantity>1</Quantity> <PriceExVAT>499</PriceExVAT> <PriceVAT>99.8</PriceVAT> <URL>http://shop.cipd.co.uk/shop/cipd-training/law-on-tour?___SID=U</URL> <ProductType>COURSE</ProductType> </TransactionItem> </Items> <PostageAndPacking>0</PostageAndPacking> <PostageAndPackingExVAT>0</PostageAndPackingExVAT> <PostageAndPackingVAT>0</PostageAndPackingVAT> <ProductType>COURSE</ProductType></OrderTransaction>
Here's the html for the email including our amscript:
%%[
var @xml, @orderNumber, @items, @numItems, @itemName, @rowItem, @price, @quantity, @priceExVat, @priceVat, @url, @productType
var @orderTotal, @orderTotal, @orderTotalExVat, @orderTotalVat
var @firstName, @surname, @paymentMethod
var @tempxml, @xmlItem, @pitems_rows
set @xml = "<OrderTransaction><FirstName>Chris </FirstName><PaymentMethod>invoice</PaymentMethod><OrderId>123456789</OrderId><OrderTotal>598.8</OrderTotal><OrderTotalExVAT>499</OrderTotalExVAT><OrderTotalVAT>99.8</OrderTotalVAT><Items><TransactionItem><ItemName>Law On Tour Spring 2018</ItemName><Price>598.8</Price><Quantity>1</Quantity><PriceExVAT>499</PriceExVAT><PriceVAT>99.8</PriceVAT> <URL>http://shop.cipd.co.uk/shop/cipd-training/law-on-tour?___SID=U</URL><ProductType>COURSE</ProductType></TransactionItem></Items></OrderTransaction>"
set @xml = confirmation_xml
set @orderNumber = order_number
set @firstName = Field(Row(BuildRowSetFromXML(@xml, "/OrderTransaction/FirstName"),1), "Value")
set @paymentMethod = Field(Row(BuildRowSetFromXML(@xml, "/OrderTransaction/PaymentMethod"), 1), "Value")
set @items = BuildRowSetFromXML(@xml, "/OrderTransaction/Items")
set @tempxml = concat("<root>", Field(Row(@items, 1), "xml"), "</root>")
set @items_rows = BuildRowSetFromXML(@tempxml, "//TransactionItem")
set @numItems = RowCount(@items_rows)
set @orderTotal = Format(Field(Row(BuildRowSetFromXML(@xml, "/OrderTransaction/OrderTotal"),1), "Value"), "#,#.00;-#,#.00")
set @orderTotalExVat = Format(Field(Row(BuildRowSetFromXML(@xml, "/OrderTransaction/OrderTotalExVAT"),1), "Value"), "#,#.00;-#,#.00")
set @orderTotalVat = Format(Field(Row(BuildRowSetFromXML(@xml, "/OrderTransaction/OrderTotalVAT"),1), "Value"), "#,#.00;-#,#.00")
]%%
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<meta content="width=device-width; initial-scale=1.0; maximum-scale=1.0;" name="viewport" />
<meta content="telephone=no" name="format-detection" />
<meta content="IE=9; IE=8; IE=7; IE=edge" http-equiv="X-UA-Compatible" />
<title>Confirmation Email</title>
<body>
<table style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt;" border="0" cellspacing="0" cellpadding="0" width="100%">
<tbody>
<tr>
<td style="padding: 0 0 20px 0; font-family: Arial,sans-serif; font-size: 24px; color: #da1d52; line-height: 34px;">Order Confirmation - %%order_number%%</td>
</tr>
<tr>
<td><b>Dear %%=v(@FirstName)=%%</b></td>
</tr>
<tr>
<td style="padding: 0 0 15px 0; font-family: Arial,sans-serif; font-size: 14px; color: #303b44; line-height: 23px;">Thank you for your order ID %%=v(@orderNumber)=%%, this has now been processed.%%[ if @paymentMethod == "invoice" then ]%% Your invoice will be emailed within 2 working days.%%[ endif ]%%
</td>
</tr>
<tr>
<td style="padding: 0 0 15px 0; font-family: Arial,sans-serif; font-size: 14px; color: #303b44; line-height: 23px;">Your course booking is now subject to terms and conditions as agreed. Notification of transfer or cancellation must be made in writing and failure to attend will result in any applicable penalty fees.</td>
</tr>
<tr>
<td style="padding: 0 0 15px 0; font-family: Arial,sans-serif; font-size: 14px; color: #303b44; line-height: 23px;">Joining instructions will be sent to you via email three to four weeks before any event which will confirm the programme details and start time. They will also include a map for the venue, any applicable pre-course reading and details of any other requirements. Joining instructions are sent once the venue has been confirmed so please do not book travel or accommodation until you are in receipt of these. If you have not received these 10 days prior to the course please contact the Events Registrar Team on the number below. Lunch is provided on all CIPD training courses. The dress code is ‘smart casual’.</td>
</tr>
<tr>
<td style="padding: 0 0 15px 0; font-family: Arial,sans-serif; font-size: 14px; color: #303b44; line-height: 23px;">
<table class="cta" align="center" border="0" cellspacing="0" cellpadding="0" width="95%">
<tbody>
<tr>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444; text-decoration: underline;" align="left" width="90"><b></b></td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444; text-decoration: underline;" align="left" width="*"><b>Product Name</b></td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444; text-decoration: underline;" align="left" width="10"><b>Quantity</b></td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444; text-decoration: underline;" align="right" width="80"><b>Price</b></td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444; text-decoration: underline;" align="right" width="60"><b>VAT</b></td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444; text-decoration: underline;" align="right" width="80"><b>Total</b></td>
</tr>
<tr>
<td align="left" width="*"> </td>
<td align="left" width="50"> </td>
</tr>
%%[ for @i = 1 to @numItems do
set @rowItem = Row(@items_rows, @i)
set @xmlItem = concat("<root>", Field(@rowItem, "xml"), "</root>")
set @itemName = Field(Row(BuildRowSetFromXML(@xmlItem, "//ItemName"),1), "Value")
set @price = Format(Field(Row(BuildRowSetFromXML(@xmlItem, "//Price"),1), "Value"), "#,#.00;-#,#.00")
set @quantity = Field(Row(BuildRowSetFromXML(@xmlItem, "//Quantity"),1), "Value")
set @priceExVat = Format(Field(Row(BuildRowSetFromXML(@xmlItem, "//PriceExVAT"),1), "Value"), "#,#.00;-#,#.00")
set @priceVat = Format(Field(Row(BuildRowSetFromXML(@xmlItem, "//PriceVAT"),1), "Value"), "#,#.00;-#,#.00")
set @url = Field(Row(BuildRowSetFromXML(@xmlItem, "//URL"),1), "Value")
set @productType = Field(Row(BuildRowSetFromXML(@xmlItem, "//ProductType"),1), "Value")
]%%
<tr>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="left" valign="top" width="90">
Training
</td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="left" width="*">%%=v(@itemName)=%%</td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="left" width="10">%%=v(@quantity)=%% </td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">£ %%=v(@priceExVat)=%%</td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">£ %%=v(@priceVat)=%%</td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">£ %%=v(@price)=%%</td>
</tr>
%%[ next ]%%
<tr>
<td align="left" width="*"> </td>
<td align="left" width="50"> </td>
</tr>
<tr>
<td align="left" width="*"> </td>
<td align="left" width="50"> </td>
</tr>
<tr>
<td align="left" width="*" colspan="3"><b>Order Total:</b></td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">£ %%=v(@orderTotalExVat)=%%</td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">£ %%=v(@orderTotalExVat)=%%</td>
<td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">£ %%=v(@orderTotal)=%%</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td style="padding: 0 0 15px 0; font-family: Arial,sans-serif; font-size: 14px; color: #303b44; line-height: 23px;"><b>Short course purchase queries</b></td>
</tr>
<tr>
<td style="padding: 0 0 15px 0; font-family: Arial,sans-serif; font-size: 14px; color: #303b44; line-height: 23px;">
If you have a question about your short course purchase, please <a href="mailto:[email protected]?subject=Short%20course%20purchase%20query" style="color:#da1d52;">email our events registrars team</a> or call us on +44 (0)208 612 6095 Monday to Friday, 09:00 - 17:30.
</td>
</tr>
<tr>
<td style="padding: 0 0 15px 0; font-family: Arial,sans-serif; font-size: 14px; color: #303b44; line-height: 23px;">Thank you</td>
</tr>
<tr>
<td style="font-family: Arial,sans-serif; font-size: 14px; color: #303b44; line-height: 23px;"><b>CIPD</b></td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td style="padding: 20px; font-family: Arial,sans-serif; font-size: 22px; color: #ffffff; line-height: 32px;" bgcolor="#da1d52"></td>
</tr>
<tr>
<td style="padding: 20px;" bgcolor="#fafafa">
<table style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt;" border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td style="padding: 0 0 10px 0; font-family: Arial,sans-serif; font-size: 12px; color: #8f9194; line-height: 21px;" align="center">Chartered Institute of Personnel and Development (CIPD), 151 The Broadway, London SW19 1JQ. Incorporated by Royal Charter. Registered as a charity in England & Wales (1079797) and Scotland (SC045154).</td>
</tr>
<tr>
<td style="font-family: Arial,sans-serif; font-size: 12px; color: #8f9194; line-height: 21px;" align="center">CIPD Enterprises Limited values your privacy. To view how we use or share your information and to change your privacy preferences visit <font color="#da1d52"><a style="font-family: Verdana, Arial, Helvetica, sans-serif; text-align: center; color: #da1d52; background-color: #f6f6f6;" href="http://www.cipd.co.uk/site-terms/privacy-policy.aspx" target="_blank"><font color="#da1d52">www.cipd.co.uk/site-terms/privacy-policy.aspx</font></a></font><span style="font-family: Verdana, Arial, Helvetica, sans-serif; text-align: center; background-color: #f6f6f6;"> This is subject to CIPD Enterprises Limited shopping terms and conditions. Please refer to our website: <font color="#da1d52"><a style="color: #da1d52;" href="http://www.cipd.co.uk"><font color="#da1d52">www.cipd.co.uk</font></a></font></span></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<!--[if (gte mso 9)|(IE)]>
</td>
</tr>
</table>
<![endif]--></td>
</tr>
</tbody>
</table>
</body>
</html><custom name="opencounter" type="tracking"/>
Any help much appreciated!
Chris