Hi BFG,

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.

Thanks!
related to an answer for: Modify order receipt to show skusuffix
in ML by (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">
        <sql>
            <![CDATA[
            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
            ]]>
        </sql>
        <queryparam paramname="@ordernum" paramtype="runtime" requestparamname="ordernumber" defvalue="0" sqlDataType="int" validationpattern="^\d{1,9}$"/>
    </query>
by (1.1k points)
Eurgghh
Sorry, formatting looks really bad.
Worked like a charm, thanks BFG!
...