Adding Custom Product Properties to Email Template in uStore

[Brooks]

I'm in the process if setting up an online ordering system with uStore
for a customer. Both static and customizable/variable print documents
will be sold along with some promotional items. Several of the
promotional items are wearable, for which I've created a new global
product property that can be added to any of these products which
allows for the end users to pick a size for the product being ordered
(Small, Medium, Large etc.).

I now need to be able to add the chosen value to the email that is
sent to the end user once an order is placed. I've found the value in
the OrderProductDialValue table of the uStore database and able to
pull the correct value for an order with the following query:

SELECT dbo.OrderProductDialValue.DialValue
FROM dbo.Orders INNER JOIN
dbo.OrderProduct ON dbo.Orders.OrderID =
dbo.OrderProduct.OrderID INNER JOIN
dbo.OrderProductDialValue ON
dbo.OrderProduct.OrderProductID =
dbo.OrderProductDialValue.OrderProductID
WHERE dbo.Orders.OrderID = 4255 and
dbo.OrderProductDialValue.OrderProductID = 1925 and
dbo.OrderProductDialValue.FieldOptionID is null

I know I can substitute the session variable @OrderID for the hard-
coded value of 4255 above, but am not sure how or even if I can pull
the OrderProductID of a particular product within an order.

Has anyone been able to accomplish what I'm trying to do here? Any
suggestions or guidance would be greatly appreciated.

[Igor Vorobeychik]

If I understand we are talking about email template that can be
triggered once order is placed.
Order can have multiple OrderProductID's so that's why there is no
@OrderProductID passed as environmental parameter.
Using @OrderID you can get list of all orderproducts and their
properties.
Igor.

[Dan Sideen]

Igor, the store really, really needs order-level properties. Many of our clients want to put their Purchase order number, or other order identifier on the order.
Can you use your influence to get this added as a feature request?

--
You received this message because you are subscribed to the Google Groups "XMPie Interest Group" group.
To post to this group, send email to xmpie...@googlegroups.com.
To unsubscribe from this group, send email to xmpie-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/xmpie-users?hl=en.

This message is intended only for the addressee, it may contain privileged or confidential information. Any unauthorized disclosure is strictly prohibited. If you have received this message in error, please notify us immediately so that we may correct our internal records. Please then delete the original message. Thank you.

Ce message est une communication confidentielle et protégée et l'information qu'il contient est réservée à l'usage exclusif du destinataire. Si vous n'êtes pas le destinataire visé, vous n'avez aucun droit de divulguer cette information. Si cette communication vous a été transmise par erreur, veuillez la détruire et nous en aviser immédiatement. Merci.

[Brooks]

Dan, I agree, order level properties would be a GREAT addition. We
developed a custom "PO Number" clearing model specifically for the
collection of a PO Number, unfortunately, the client we developed this
for does not want show pricing, and if pricing is hidden, so is the
clearing option. We developed this prior to uStore 4.0 becoming
available, and with the new ability to have pricing but hide it from
the end user, that we could still have a clearing option but sadly
this is not the case.

Igor, you are correct, I am referring to the confirmation email that
is triggered when an order is placed. Would you happen to have an
example of pulling the value of a global product property for a
specific product within an order and displaying the value in the
confirmation email?

> For more options, visit this group athttp://groups.google.com/group/xmpie-users?hl=en.

[Igor Vorobeychik]

Ok,
we have 2 issues here :
1) email template and how to get values
2) feature request that was already discussed here

1)
First it was my bad @OrderProductId are passed but on State Transition
Filter and it is logical since it works on the item level.
So if you are going to use different trigger you'll get that one.

Second you can look in our default Order Submission in Customer App -
Detailed temlpate and its message SQL , it has an example of the
Product List

Third you can get more advanced example looking into our
[Order_DetailsXml] storeprocedure that used for XML temlate , so you
can create your own SP that gets only properties , for example this
will get all product properties and values (I took it out of the above
sp)
select
Prop.[DialID] "Property/@id",
PropC.[FriendlyName] "Property/DisplayName",
PV.[DialValue] "Value"
from
Dial Prop JOIN Dial_Culture PropC ON (Prop.DialID = PropC.DialID AND
PropC.CultureID=1),
OrderProductDialValue PV
where
PV.[OrderProductId] = @OrderProductID and
PV.[DialID] = Prop.[DialID] and
Prop.[IsProperty] = 1

2) As for this feature request I mentioned it several times and R&D
aware of this request and they do its best to bring it in the future
versions.

Igor.

> > Ce message est une communication confidentielle et protégée et l'information qu'il contient est réservée à l'usage exclusif du destinataire. Si vous n'êtes pas le destinataire visé, vous n'avez aucun droit de divulguer cette information. Si cette communication vous a été transmise par erreur, veuillez la détruire et nous en aviser immédiatement. Merci.- Hide quoted text -
>
> - Show quoted text -

[Brooks]

Thank you for your response Igor. I don't have any experience with
stored procedures so i guess it's time to dive in and learn something
new. Hopefully with a bit of digging i can figure things out. Thanks
for pointing me in the right direction.

[Brooks]

I seem to have made some progress by editing the original detailed
OrderProductList SQL by adding a join to the OrderProductDialValue
table and extracting the value of DialValue:

SELECT OPDV.DialValue as 'Size', OP.ProductID, PC.Name, P.ExternalId,
TotalPrice = dbo.fn_GetFormattedPrice(OP.TotalPrice, C.Symbol,
CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator),
OP.TotalQuantity, OrderAmount =
dbo.fn_GetFormattedPrice(O.OrderAmount, C.Symbol,
CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator),
ShippingCharges = dbo.fn_GetFormattedPrice(O.ShippingCharges,
C.Symbol, CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator),
BillAmount = dbo.fn_GetFormattedPrice(O.BillAmount, C.Symbol,
CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator)
FROM OrderProduct OP JOIN
Orders O on O.OrderId = OP.OrderId JOIN
Product P on P.ProductId = OP.ProductId JOIN
Product_Culture PC on P.ProductId=PC.ProductID LEFT JOIN
CultureInfo CI ON CI.StoreID = O.StoreID AND BaseCultureId = ' +
@CultureId + ' LEFT JOIN
Currency C ON C.CurrencyId = CI.CurrencyId JOIN
OrderProductDialValue OPDV ON OPDV.OrderProductID =
OP.OrderProductID
WHERE O.OrderId = ' + @OrderId + ' AND PC.CultureID = ' + @CultureId +
' AND OP.IsDraft = 0 AND O.IsCart = 0

My problem is that now the statement returns multiple rows for each
product, one row for each unique DialValue for any given product
within an order. My first thought was to narrow my search by
specifying the DialID, but that changes based on the product. Any
thoughts?

On Nov 22, 2:31 pm, Igor Vorobeychik <vori1...@gmail.com> wrote:

[Igor Vorobeychik]

All product properties that inherited from global property will have
same BaseDialID that is id of original global property so you can use
this one.
In any case you'll get several of them since each product will
probably have this property or you can use it's name .
Igor.

> > > - Show quoted text -- Hide quoted text -

[Brooks]

I was finally able to achieve what i was after, utilizing the
BaseDialID was part of the solution. Here's the final Query if anyone
is interested:

SELECT OP.ProductID, PC.Name, P.ExternalID, (SELECT top 1
opdv.dialvalue from orderproductdialvalue opdv JOIN
Dial D ON D.DialID = OPDV.DialID where d.basedialid = 888 and
OPDV.OrderProductID = OP.OrderProductID) as SizeValue, TotalPrice =

dbo.fn_GetFormattedPrice(OP.TotalPrice, C.Symbol,
CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator),
OP.TotalQuantity, OrderAmount =
dbo.fn_GetFormattedPrice(O.OrderAmount, C.Symbol,
CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator),
ShippingCharges = dbo.fn_GetFormattedPrice(O.ShippingCharges,
C.Symbol, CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator),
BillAmount = dbo.fn_GetFormattedPrice(O.BillAmount, C.Symbol,
CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator)
FROM OrderProduct OP JOIN
Orders O on O.OrderId = OP.OrderId JOIN
Product P on P.ProductId = OP.ProductId JOIN
Product_Culture PC on P.ProductId=PC.ProductID LEFT JOIN

CultureInfo CI ON CI.StoreID = O.StoreID AND BaseCultureId = 1 LEFT

JOIN
Currency C ON C.CurrencyId = CI.CurrencyId

WHERE O.OrderId = ' + @OrderId + ' AND PC.CultureID = ' + @CultureId
+' AND OP.IsDraft = 0 AND O.IsCart = 0

Thanks again for everyone's help!

[markb]

Hi everyone, If there are others researching how to add global product properties to email receipts, this older thread is still a good method to study. I've added the below select statements to the stock OrderProductList Message Template SQL and works well. For what I am doing, I needed to target the FriendlyValue in the OrderProductDialValue table as apposed to DialValue, but that's about all I changed.

(

SELECT

opdv.friendlyvalue

FROM

orderproductdialvalue opdv

JOIN Dial D ON D.DialID = OPDV.DialID

WHERE

d.basedialid = 11675

AND OPDV.OrderProductID = OP.OrderProductID

) AS UrgentExplain,

 (

SELECT

opdv.friendlyvalue

FROM

orderproductdialvalue opdv

JOIN Dial D ON D.DialID = OPDV.DialID

WHERE

d.basedialid = 11628

AND OPDV.OrderProductID = OP.OrderProductID

) AS UrgentDate