Order confirmation emails - ampscript

Question

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 &lsquo;smart casual&rsquo;.</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="*">&nbsp;</td>
                            <td align="left" width="50">&nbsp;</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">&pound;&nbsp;%%=v(@priceExVat)=%%</td>
                            <td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">&pound;&nbsp;%%=v(@priceVat)=%%</td>
                            <td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">&pound;&nbsp;%%=v(@price)=%%</td>
                        </tr>
                        %%[ next ]%%
                        <tr>
                            <td align="left" width="*">&nbsp;</td>
                            <td align="left" width="50">&nbsp;</td>
                        </tr>
                        <tr>
                            <td align="left" width="*">&nbsp;</td>
                            <td align="left" width="50">&nbsp;</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">&pound;&nbsp;%%=v(@orderTotalExVat)=%%</td>
                            <td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">&pound;&nbsp;%%=v(@orderTotalExVat)=%%</td>
                            <td style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; line-height: 14pt; color: #444444;" align="right" width="60">&pound;&nbsp;%%=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 &nbsp;<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;">&nbsp;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

Adam Spriggs · Accepted Answer · 2018-01-19 16:20:01Z

I'd suggest checking the rowcount() of before using the field() function on each of your fields:

%%[

/* other AMPScript */

if rowCount(Row(BuildRowSetFromXML(@xml, "/OrderTransaction/FirstName"),1)) > 0 then 
  set @firstName = Field(Row(BuildRowSetFromXML(@xml, "/OrderTransaction/FirstName"),1), "Value")
endif

/* other AMPScript */

]%%

Here's a full example

%%[/* Modify to view AMPScript <div style="display:none"> */

var @xml, @isXML, @nodes, @rowCount
set @xml = AttributeValue("xml")

if indexOf(@xml,"<cart>") > 0 then

  set @nodes = BuildRowsetFromXML(@xml,"/cart/items/item",0)
  set @rowCount = rowcount(@nodes)

  if @rowCount > 0 then

    for @i = 1 to @rowCount do

      var @sku
      var @name
      var @url

      set @nodepath = concat("/cart/items/item[",@i,"]/")

      if rowcount(BuildRowsetFromXML(@xml,concat(@nodepath,"sku"))) > 0 then
          set @sku = Field(Row(BuildRowsetFromXML(@xml,concat(@nodepath,"sku"),0),1),'Value')
      endif

      if rowcount(BuildRowsetFromXML(@xml,concat(@nodepath,"name"))) > 0 then
          set @name = Field(Row(BuildRowsetFromXML(@xml,concat(@nodepath,"name"),0),1),'Value')
      endif

      if rowcount(BuildRowsetFromXML(@xml,concat(@nodepath,"url"))) > 0 then
          set @url = Field(Row(BuildRowsetFromXML(@xml,concat(@nodepath,"url"),0),1),'Value')
      endif

      if not empty(@sku) and not empty(@name) and not empty(@url) then

      ]%%

       <br><a href="%%=redirectto(@url)=%%">%%=v(@sku)=%%</a> - <a href="%%=redirectto(@url)=%%">%%=v(@name)=%%</a>

      %%[

      endif

    next @i

  else

   outputline(concat("<br>no products found"))

  endif

else

  outputline(concat("<br>no XML found"))

endif

Here's the payload:

<?xml version="1.0" encoding="UTF-8"?>
<cart>
      <items>
            <item>
                  <sku><![CDATA[123]]></sku>
                  <name><![CDATA[Square]]></name>
                  <url><![CDATA[https://spmvs.com?sku=123]]></url>
            </item>
            <item>
                  <sku><![CDATA[456]]></sku>
                  <name><![CDATA[Circle]]></name>
                  <url><![CDATA[https://spmvs.com?sku=456]]></url>
            </item>
            <item>
                  <sku><![CDATA[789]]></sku>
                  <name><![CDATA[Triangle]]></name>
                  <url><![CDATA[https://spmvs.com?sku=789]]></url>
            </item>
      </items>
</cart>

Thanks Adam, we're starting to wonder if the issue is with reading the xml. We're unable to get the data from the confirmation_xml field although we're able to get the data from other fields, such as order_number. This is the error message we get when previewing because it seems @xml is set an empty xml so no rows are returned. — Chris Mathews, Jan 22, 2018 at 9:14

Chris Mathews · Accepted Answer · 2018-01-31 13:53:56Z

We found that by removing the xml tags from the confirmation_xml our ampscript was able to read the xml. This is what went into our confirmation_xml field in the DE, and now works:

 <OrderTransaction 
     <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>

Stack Exchange Network

Order confirmation emails - ampscript

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
marketing-cloud
ampscript
.

Hot Network Questions

Order confirmation emails - ampscript

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged marketing-cloudampscript.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
marketing-cloud
ampscript
.