Delivery details in email templates

[Nicolas Le Gall]

Hi

I'm willing to create a template email that would send delivery details when an ordered is approved, so that we would not have to get in the Backoffice to 

Delivery #1

Delivery adresses, zip, city

Products in this delivery

Delivery #2

Delivery adresses, zip, city

Products in this delivery

And so on

in fact, i'd like to get in this email template exactly the same informations that I get in the "Order Receipt" 


I managed to create my trigger, to duplicate an email template, but I can't manage to create the SQL query that could give me those informations so that I can insert them in my Email template


Has anyone a solution ? Is it possible anyway ?

Thanks

[Braam Ekermans]

Hi

I had to this urgently for a customer as well. So I did it this way, but it can be done much simpler by using 1 query though. Im sure someone else will have a simpler solution but here is my quick input.

I first created stored procedures:
CREATE PROCEDURE [dbo].[Custom_GetDeliveryAdd1]     
     
 @OrderID INT     
AS     
BEGIN     
 DECLARE @Value AS NVARCHAR(256)     
 SELECT @Value=CASE WHEN ISNULL(DT.Ship_Add1, '') = '' THEN '' ELSE ISNULL(DT.Ship_Add1, '') END     
 FROM DeliveryTentative DT     
 JOIN Orders O ON O.OrderID = DT.OrderID     
 WHERE DT.OrderID = @OrderID    
      
 IF ISNULL(@Value,'') = ''     
 BEGIN     
  SELECT @Value=CASE WHEN ISNULL(O.Bill_Add1, '') = '' THEN '' ELSE ISNULL(O.Bill_Add1, '') END     
  FROM Orders O     
  WHERE O.OrderID = @OrderID      
 END     
      
 SELECT @Value AS [DeliveryAddress]     
END


(Obviously you need to create a stored proc for each of the entries below, changing it slightly, if you are not sure you can reply again and I will send them all to you.)

I created a SQL message template for each of the rows:

exec('Custom_GetDeliveryAdd1 ' + @OrderId)

DeliveryAdd1

exec('Custom_GetDeliveryAdd2 ' + @OrderId)

DeliveryAdd2

exec('Custom_GetDeliveryCity ' + @OrderId)

DeliveryCity

exec('Custom_GetDeliveryZip ' + @OrderId)

DeliveryZip

exec('Custom_GetDeliveryProvince ' + @OrderId + ', ' + @CultureId)

DeliveryProvince

exec('Custom_GetDeliveryCountry ' + @OrderId + ', ' + @CultureId)

DeliveryCountry

exec('Custom_GetDeliveryReference ' + @OrderId + ', ' + @CultureId)

DeliveryReference

And then used it in my Email template as follows:
                 <li>Delivery Address:
                            <br/>
                            <br/>
                            <xsl:if test="$deliveryName != ''"><xsl:value-of select="$deliveryName" /><br/></xsl:if>
                            <xsl:if test="$deliveryCompany != ''"><xsl:value-of select="$deliveryCompany" /><br/></xsl:if>
                            <xsl:if test="$deliveryAdd1 != ''"><xsl:value-of select="$deliveryAdd1" /><br/></xsl:if>
                            <xsl:if test="$deliveryAdd2 != ''"><xsl:value-of select="$deliveryAdd2" /><br/></xsl:if>
                            <xsl:if test="$deliveryCity != ''"><xsl:value-of select="$deliveryCity" /><br/></xsl:if>
                            <xsl:if test="$deliveryProvince != ''"><xsl:value-of select="$deliveryProvince" /><br/></xsl:if>
                            <xsl:if test="$deliveryCountry != ''"><xsl:value-of select="$deliveryCountry" /><br/></xsl:if>
                            <xsl:if test="$deliveryZip != ''"><xsl:value-of select="$deliveryZip" /><br/></xsl:if>
                            <xsl:if test="$deliveryReference != ''"><xsl:value-of select="$deliveryReference" /><br/></xsl:if>
                        </li>

Hope this helps

Confidential: This email and its attachments may contain confidential and privileged information. If you are not the intended recipient, you are not authorized to retain, copy, or distribute the email message or its attachments. If you received this email in error, please notify the sender immediately.

---

From: "Nicolas Le Gall" <nicolas....@gmail.com>
To: "XMPie Interest Group" <xmpie...@googlegroups.com>
Sent: Tuesday, April 19, 2016 11:21:20 AM
Subject: [xmpie-users] Delivery details in email templates

--
You received this message because you are subscribed to the Google Groups "XMPie Interest Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xmpie-users...@googlegroups.com.
To post to this group, send email to xmpie...@googlegroups.com.
Visit this group at https://groups.google.com/group/xmpie-users.
For more options, visit https://groups.google.com/d/optout.