Customizing Ustore confirmation email to include campaign dial values

[SKipp]

Hi All,

Hoping for some syntax help. I have a UStore product with several
campaign dials and would like to inlcude this information on the
confirmation email. I know in the Presets how to create a new
template, but am not sure how to structure the SQL query for the
specific DialID and its value.

Thanks for the help

[Bill]

The OrderProductDialValue table contains columns such as:
* DialID,
* OrderProductID,
* DialValue.

The following query might help you retrieve the value of a particular
Dial, as well as other values, which I often use in uStore emails:

SELECT
opdv.DialValue,
p.ProductID,
pc.Name,
op.TotalPrice,
op.TotalQuantity,
o.OrderAmount,
o.ShippingCharges,
o.BillAmount

FROM
OrderProduct AS op INNER JOIN
Orders AS o ON o.OrderID = op.OrderID INNER JOIN
Product AS p ON p.ProductID = op.ProductID INNER JOIN
Product_Culture AS pc ON p.ProductID = pc.ProductID INNER JOIN
OrderProductDialValue AS opdv ON opdv.OrderProductID =
op.OrderProductID

WHERE
o.OrderID = ' + @OrderId + ' AND
pc.CultureID = ' + @CultureId + ' AND
opdv.DialID = 'nnnn' AND
op.IsDraft = 0 AND
o.IsCart = 0

where 'nnnn' is the ID of the Dial, which you want to show in the
email (Dial IDs can be found from the Dial and Dial_Culture tables).

[SKipp]

Thanks Bill, I'll play with this week.

Always appreciate the help that is found here.

> > Thanks for the help- Hide quoted text -
>
> - Show quoted text -

[Michael Dobbins]

I have a client wanting somewhere to put a PO Number, I have set it up using the Product Properties so that it shows in the cart and order history, but I also need it to display in the email receipt. Would the sql below work if I knew where to find the ID of the Product Property, which I currently don't?

[tayamo]

I've had some success doing this. I've essentially created a packing
slip that mirrors the order details print out and also includes a PO
Property.

However, it is kind of tricky. uStore essentially takes the product
property you've created (the PO number) and adds it as a dial. In your
SQL, you have to actually get the value from the dial. The problem is
that you each of your products in your store will have a different
dial ID for the PO number property you've created, so this makes it
hard to use IDs to get at the data you want. Essentially, the value
you want is in the dialvalue table, so you're sql has to include the
product table joined to the dial table joined to the dialvalue table.

Instead, I suggest that you use the name of the property/dial in your
sql code. If you use the name instead of the id, you can use the same
sql code for all of your products.

If you like, send a private email to me at p_pi...@yahoo.com, and I
will send you both the SQL strings I use and the XSLT code for the
emails.

On Nov 9, 11:02 am, Michael Dobbins <mikedobb...@gmail.com> wrote:
> I have a client wanting somewhere to put a PO Number, I have set it up using
> the Product Properties so that it shows in the cart and order history, but I
> also need it to display in the email receipt. Would the sql below work if I
> knew where to find the ID of the Product Property, which I currently don't?
>

[Guy Schreiber]

using the name of the property is not bad, but i have a suggestion
that might be appealing.
Create a global product property called PO.
Then in each of the products use it.
In the DB, a new property will be created with the name PO but with
different ID.
All these properties will have the BaseDialId set to the same ID of
the global property.
And this is how you can find all of them by the ID.

Guy

On Nov 9, 8:31 pm, tayamo <p_piet...@tayamo.com> wrote:
> I've had some success doing this. I've essentially created a packing
> slip that mirrors the order details print out and also includes a PO
> Property.
>
> However, it is kind of tricky. uStore essentially takes the product
> property you've created (the PO number) and adds it as a dial. In your
> SQL, you have to actually get the value from the dial. The problem is
> that you each of your products in your store will have a different
> dial ID for the PO number property you've created, so this makes it
> hard to use IDs to get at the data you want. Essentially, the value
> you want is in the dialvalue table, so you're sql has to include the
> product table joined to the dial table joined to the dialvalue table.
>
> Instead, I suggest that you use the name of the property/dial in your
> sql code. If you use the name instead of the id, you can use the same
> sql code for all of your products.
>

> If you like, send a private email to me at p_piet...@yahoo.com, and I