uStore Walkthrough Adding Shipping Info to Email Templates Version 1.0

Introduction: This walkthrough will guide you through the process of adding shipping information to an email template in uStore XMPie Software Prerequisites

1. uStore version 11 All XMPie software must be installed based on XMPie software requirements.

Process Diagram Figure 1: Steps

Instructions • Log into uStore Admin portal

• Click on Presets tab

• Click Trigger Setup

• Look for the Event: Order Submission in Customer Application section

• Click the trigger you are currently using to create the emailed receipt. o The default trigger is called Sending mail when an order submitted

• Click the stack of papers icon at the bottom of the screen to copy the template o Refer to figure 2 if you are unsure

• Rename your new template in the pop-up box

• Click Save and Close on the second pop-up box

• On the Edit Rule Setting page Do Not Save the rule

• Click on Presets tab

• Click System Setup

Figure 2: Trigger configuration screen

• Click Message Template SQL

• Click to the last page and the last Message Template Id is the number of the newly created template • Make a note of the message template Id

Figure 3 Finding the Template Id

• Click Add New

• Input the Message Template Id

• Paste the SQL text into the corresponding box

exec (' SELECT [DeliveryTentativeID] ,[OrderID] ,[PickupAddressID] ,[Ship_Name] ,[Ship_Company] ,[Ship_Add1] ,[Ship_Add2] ,[Ship_City] ,[Ship_Country] ,[Ship_State] ,[Ship_Zip] ,[Ship_Phone] ,[Ship_Fax] ,[DeliveryMethod] ,[DeliveryServiceID] ,[DeliveryPrice] ,[Ship_AddressReference] ,[ShippingTax] ,[DeliveryTentativeUniqueID] ,[DeliveryPriceEstimated] ,DM.Name AS DeliveryMethodName ,DPS.Name AS DeliveryProviderName ,PC.Name AS StateName ,CC.Name AS CountryName FROM [uStore].[dbo].[DeliveryTentative] DT LEFT OUTER JOIN [uStore].[dbo].[Province_Culture] PC ON DT.[Ship_State] = PC.ProvinceId AND PC.CultureID = '+@CultureID+' LEFT OUTER JOIN [uStore].[dbo].[Country_Culture] CC ON DT.[Ship_Country] = CC.CountryId AND CC.CultureID = '+@CultureID+' LEFT OUTER JOIN [uStore].[dbo].[DeliveryMethod] DM ON DT.[DeliveryMethod] = DM.DeliveryMethodID LEFT OUTER JOIN [uStore].[dbo].[DeliveryProviderService] DPS ON DT.[DeliveryServiceID] = DPS.DeliveryProviderServiceID WHERE OrderId = ' +@OrderId )

• OrderShippingDetail must be pasted into the name box o The name must match EXACTLY or the code will not work

• Click Save

• Click the Back to List button

• Click into Message Template

• Click to the last page

• Click the edit/pencil icon of your new template

• Click on Edit Localized Text

• Click the edit/pencil icon of the top entry

• Expand the Message Body area of the code

• Choose where in the template the To Address should be added

o If unsure place the code between these two lines shown in Figure 4

Figure 4 Placement Recommendation

Insert the following lines of code

<table border="0" cellpadding="4" cellspacing="2" style="border-collapse:collapse; width:100%;">
	<tbody>               
		<tr>
                  	<td>To Address</td>
                  	<td> </td>
                </tr>
                <tr>
                  	<td><xsl:value-of select="//Sqls/OrderShippingDetail/Row/Ship_Name" /> </td>
			<td />
			<td />
                </tr>
                <tr>
                  	<td><xsl:value-of select="//Sqls/OrderShippingDetail/Row/Ship_Company" /></td>
                 	<td />
                </tr>
                <tr>
                  	<td><xsl:value-of select="//Sqls/OrderShippingDetail/Row/Ship_Add1" /> <xsl:value-of select="//Sqls/OrderShippingDetail/Row/Ship_Add2" /></td>
                  	<td />
                </tr>
                <tr>
                  	<td><xsl:value-of select="//Sqls/OrderShippingDetail/Row/Ship_City" />, <xsl:value-of select="//Sqls/OrderShippingDetail/Row/StateName" />, <xsl:value-of select="//Sqls/OrderShippingDetail/Row/Ship_Zip" /> </td>
                  	<td>Shipping</td>
                </tr>
                <tr>
                  	<td><xsl:value-of select="//Sqls/OrderShippingDetail/Row/CountryName" /> </td>
                 	<td>Service: <xsl:value-of select="//Sqls/OrderShippingDetail/Row/DeliveryProviderName" /> </td>
                </tr>
                <tr>
                  	<td><xsl:value-of select="//Sqls/OrderShippingDetail/Row/Ship_Phone" /> </td>
                  
                </tr>
	</tbody>
</table>

• Save the edited template o If you get an error cancel out of the template and try inserting the code again, making sure to paste in everything between the lines above

• uStore will show you a preview of the new template

• Click Presets tab

• Click Trigger Setup

• Look for the Event: Order Submission in Customer Application section

• Click the trigger you are currently using to create the emailed receipt

• At the bottom of the screen there is Select Email Template

• Click the drop down to the right of the box

• Select the new template

• Click Save Rule

• The new template is now what the customer will get when they place an order

  Caveats, Notes, and Disclaimers This walkthrough is provided as is List of Figures

Figure 1: Steps ...................................................4 Figure 2: Trigger configurations screen ...5 Figure 3: Finding the Template Id.............. 5 Figure 4: Placement Recommendation ....6

Older version below

https://github.com/xmpie-users/uStore-js/wiki/Adding-shipping-address-to-email-template

The field name is Ship_Name.

In order to get the shipping information with a SQL query please use the following code: Create a new Message Template SQL called ShippingAddress and add the following query in it:

exec('SELECT DT.Ship_Name,
DT.Ship_Company,
DT.Ship_Add1,
DT.Ship_Add2,
DT.Ship_City,
PC.Name As Ship_ProvinceName,
DT.Ship_Zip,
CC.Name as Ship_Country,
DT.Ship_Phone,
DT.Ship_Fax


From DeliveryTentative DT
JOIN Orders O ON O.OrderID = DT.OrderID
JOIN DeliveryMethod DM ON DT.DeliveryMethod = DM.DeliveryMethodId
LEFT OUTER JOIN Province_Culture PC ON PC.ProvinceId = DT.Ship_State AND PC.CultureID = ' + @CultureID + '
LEFT OUTER JOIN Country_Culture CC ON CC.CountryId = DT.Ship_Country AND CC.CultureID = ' + @CultureID + '
WHERE
DT.OrderID = ' + @OrderID + ' AND
DT.DeliveryServiceID <> 6')

After that you can work with all needed shipping data for example Company with Ship_Company or the country (translated) Ship_Country

<xsl:value-of select="//Sqls/ShippingAddress/Row/Ship_Company" />

<xsl:value-of select="//Sqls/ShippingAddress/Row/Ship_Name" />