Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

Learn more about...


What fields can be pulled?


I understand that I need to edit that XML package, I've already been tooling around with it attempting to find a solution.

Looking at the query inside the file I see that "OrderedProductSKU" is being pulled, but that field contains SKU+SKUsuffix+modifiers in one string. My question is how do I narrow this down to SKUsuffix only? Is there a field in the shopping cart orders table that contains skusuffix only? I don't have database access so I can't see what fields are available to pull. I've considered trying to use a trim function of some sort but there's really no way to tell it which part of the string is the sku vs skusuffix.

Would it be a bad or good idea to join the products table using variant id and take the skusuffix from there? Just trying the best (if any) solution.

related to an answer for: Modify order receipt to show skusuffix
asked Sep 24, 2013 in ML by donttryathome (165 points)

1 Answer

+1 vote

>Would it be a bad or good idea to join the products table using variant id and take the skusuffix from there?


Close - but no cigar :-)


The SKUSuffix is actually in the ProductVariant table, so something like the following should work :-


I'm assuming that you have this already :-



    <query name="OrderItems" rowElementName="lineitem">
            select s.ShoppingCartRecID, s.OrderNumber, s.ProductID, s.VariantID,  pv.SKUSuffix, 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.SizeOptionPrompt, s.ColorOptionPrompt, s.TextOptionPrompt, 
                   s.IsTaxable, s.TaxClassID, s.TaxRate,
                   isnull(s.isakit, 0) isakit, isnull(s.isapack, 0) isapack, isnull(s.IsSystem, 0) IsSystem,
  CASE WHEN c.CustomerID IS NULL THEN 0 ELSE c.GiftRegistryIsAnonymous END AS GiftRegistryIsAnonymous
            from dbo.Orders_ShoppingCart s with (NOLOCK) 
LEFT OUTER JOIN Customer c ON c.CustomerID = s.GiftRegistryForCustomerID
JOIN ProductVariant pv ON pv.VariantID = s.VariantID
            where s.ordernumber = @ordernum
            order by s.ShippingAddressID
        <queryparam paramname="@ordernum" paramtype="runtime" requestparamname="ordernumber" defvalue="0" sqlDataType="int" validationpattern="^\d{1,9}$"/>
answered Sep 25, 2013 by BFG 9000 (1,050 points)
Sorry, formatting looks really bad.
Worked like a charm, thanks BFG!