OrderDetailsUrl not available on approved email triggers?

[markb]

Hi all,

For stores that don't have an approval feature configured, we setup a trigger for event type "Order Submission in Customer Application" to send the customer a copy of their order details.

If a product is configured as a downloable product, this part below will add a download link to the email for the customer using this method:

 <xsl:if test="//OrderDetailsUrl and $DownloadableCount&gt;0">
                  <td>
                    <xsl:if test="DownloadOutput!=''">
                      <xsl:element name="a">
                        <xsl:attribute name="href">
                          <xsl:value-of select="//OrderDetailsUrl" />
                          </xsl:attribute>Download</xsl:element>
                        </xsl:if>

and this //OrderDetailsUrl will take the customer to the order in their account's dashboard (requires them to login or be logged into to download).

We have a requirement to enable an approval process for a download only product and then add the download link to the order approved email.

When I setup an Order Approved event trigger and copy and paste the same email template that works fine under Order Submission in Customer Application events, the //OrderDetailsUrl doesn't show a link at all for download products. If I simplify the logic to just:

  <a href="{//OrderDetailsUrl}">
    Download
  </a>

The text shows, but no link.

I am able to provide a download link using the OutputToken solution like https://backofficedomain.com/uStore/Controls/SDK/OrderOutputProxy.ashx?token=XXXXXXXXXXXX on the Order Approved event trigger.

Looking in the group, I see this below thread which I think also points to //OrderDetailsUrl not "available" when using an order approved event trigger:

https://groups.google.com/g/xmpie-users/c/GWKJozui5-w/m/tE2Bc6mxAAAJ

Is //OrderDetailsUrl a hard coded parameter in the application that is not "available" when an order is approved but is available if the order didn't require approval when the order was placed? Seems like there is some nuance about the events Order Approved and Order Submission in Customer Application?

[west-digital.fr]

>> Is //OrderDetailsUrl a hard coded parameter?

No, it is not: you will find the SQL query (or the call to a stored procedure?) in the "Message Template SQL" section of uStore Admin's Presets > System Setup.

The SQL query (or the call to a stored procedure) returns columns, that you can further use in your XSL email template.

Should the column you need be missing in the SQL query, or should you Message Template SQL be missing for the related Message Template, then indeed no value would be returned (or, in some circumstances, the email would simply not be composed at all).

So,

* in Presets > Triggers Setup, take note of your Email Template name

* in Presets > System Setup > Message Template, take note of your Message Template ID

*  in Presets > System Setup > Message Template SQL, filter the (long) list by Message Template ID, find the related Message Template SQL by its name, then investigate, if the SQL query (or the call to a stored procedure) really returns a column named "OrderDetailsUrl".

west-digital.fr