Thread: Trying to add Product.MiscText to Order Notes area of Receipt

I'm looking for a little help adding Product.MiscText to the OrderNotes area of the receipt. Since we can't access the purchased product area in the actual receipt without source code, I'm forced to try to put a note to the customer in the Order Notes.

I have placed this in the SQL area of the receipt.xml

<query name="getProductinfo" rowElementName="getProductinfo">
<sql>
<![CDATA[
select * From dbo.Product with (NOLOCK) where productid = @productid
]]>
</sql>
<queryparam paramname="@ordernum" paramtype="runtime" requestparamname="ordernumber" defvalue="0" sqlDataType="int" validationpattern="^\d{1,9}$"/>
<queryparam paramname="@productid" paramtype="runtime" requestparamname="productid" defvalue="0" sqlDataType="int" validationpattern="^\d{1,9}$"/>

</query>

and I've added this to the notes area to display the miscText field.

<xsl:value-of select="$getProductinfo/misctext" />


However, nothing is showing up in the order notes. Plus, would this display multiple MiscTexts in the order notes area or only the last item if multiple items are purchased?

I'm more of a Classic ASP/SQL guy, not XML. Any help is appreciated.

Tom

I am basing this off 9.3.1.0 the notification.receipt.xml.config xmlpackage but previous versions should still be similar.

Before the example there is one issue I want to explain. The productid is not a stock run time parameter of the notification.receipt.xml.config xmlpackage. Mainly because the order uses a table of products. This table is called in a sql query called OrderItems. Therefore we want to make the OrderItems query be this:

Code:

<query name="OrderItems" rowElementName="Item">
		<sql>
		  <![CDATA[
        SELECT  s.ShoppingCartRecID, 
                s.OrderNumber, 
                s.ProductID, 
                s.VariantID, 
                s.Quantity, 
                s.ChosenColor, 
                s.ChosenSize, 
                s.OrderedProductName, 
                s.OrderedProductVariantName, 
                s.OrderedProductSKU, 
                s.OrderedProductPrice, 
                s.OrderedProductRegularPrice, 
                s.OrderedProductSalePrice, 
                s.OrderedProductExtendedPrice, 
                s.OrderedProductQuantityDiscountName, 
                s.OrderedProductQuantityDiscountID, 
                s.OrderedProductQuantityDiscountPercent, 
                s.IsShipSeparately, 
                s.IsDownload, 
                s.FreeShipping, 
                s.TextOption, 
                s.ShippingMethod, 
                s.Notes, 
                s.ExtensionData, 
                s.CustomerEntersPrice, 
                s.GiftRegistryForCustomerID, 
                s.ShippingAddressID, 
                s.ShippingDetail, 
                s.SizeOptionPrompt, 
                s.ColorOptionPrompt, 
                s.TextOptionPrompt, 
                s.IsTaxable, 
                s.TaxClassID, 
                s.TaxRate,
                ISNULL(s.IsAKit, 0) AS IsAKit, 
                ISNULL(s.IsAPack, 0) AS IsAPack, 
                ISNULL(s.IsSystem, 0) AS IsSystem,
                ISNULL(p.UsageText, '') AS UsageText,
				p2.misctext
        FROM dbo.Orders_ShoppingCart s WITH (NOLOCK)
		Join dbo.Product p2 On p2.ProductID = s.ProductID
        Left Join dbo.PromotionLineItem pli On pli.ShoppingCartRecordId = s.ShoppingCartRecId
        Left Join dbo.PromotionUsage pu On pu.Id = pli.PromotionUsageId
        Left Join dbo.Promotions p On p.Id = pu.PromotionId
        WHERE s.ordernumber = @ordernum
        ORDER by s.ShippingAddressID
      ]]>
		</sql>
		<queryparam paramname="@ordernum" paramtype="runtime" requestparamname="ordernumber" defvalue="0" sqlDataType="int" validationpattern="^\d{1,9}$"/>
		<queryparam paramname="@VatEnabled" paramtype="runtime" requestparamname="VAT.Enabled" defvalue="false" sqlDataType="bit" validationpattern=""/>
	</query>

Next is to add the misctext to the html ouput. Do this by going to the Item template then add this code to the desired column:

Code:

<xsl:value-of select="misctext" />

Hope that helps.

Hi Tom,

Having a look at this thread should provide some guidance: http://forums.aspdotnetstorefront.co...-Order-Receipt

Note: It is important to keep in mind, that most product data displayed on the receipt, is stored in the Orders_ShoppingCart table. One useful purpose this serves, is storing product data at the time of purchase. Since certain attributes - like price - may change over time, retrieving the latest product data to display on receipt could cause inaccuracy.

FYI, there are two main issues with your query:
1) By default, product ID is not passed to that xml package as a parameter. Order number (ordernumber) and Customer id (customerid) are the only ones being passed. You could retrieve product ID from the Orders_ShoppingCart table using the order number.
2) I suspect that you are trying to retrieve MiscText for each product within a given order. If so, your current query would limit it to a single product.


Let me know if you have any questions.
Robert

Thanks. I'll try this.

I guess ideally I should use a variant text option, but of course, there really is no option other than maybe feed. I just want to be able to add pick up instructions to the receipt.

It seems there should be product/variant notes that pass on to the receipt automatically, but there is no such option and everything requires source code.

Thanks again.