Email template from order table and order details ...

Phil_Waite · 10-22-2021 05:00 AM

Hi,

I have an email template that is based on user defined inputs to selct the correct orders and order details for list of orders to be printed based on delivery date and georgraphical location.

This works fine.

I want to be able to send an email with a template that is similar but from an individual order data from the Order table and the child Order Details table.

The action button is on the Orders table and used when in an order.

Below is the template that works. I have tried replacing the first start expression to <<Start:SELECT(Orders[Order Id], TRUE)>> and various others but can’t get anythig to work.

Current template -

<<Start:OrderBy(Running Sheet 22[Outlet No],[Running Sheet Report Calc],FALSE)>>

Drop No: <<[Running Sheet Report Calc]>>

Customer Name: <<[Name & Town]>>, <<[Post code]>>

<<Start:(SELECT([Related Orders][Order Id], [Delivery Date] =[Running Sheet Delivery Date]))>>

<<Start:[Related Order Details]>>

Product Name: <<[Product Name]>> <<[Quantity]>>

<>

By Product

<<Start:SELECT(Products[Unique Product Id],([New Virtual Column 2]>0), true)>>

Product Name: <<[Product Name]>>, <<[New Virtual Column 2]>>

<>

<<Start:Orderby(Running Sheet 22[Outlet No],[Running Sheet Report Calc],FALSE)>>

Customer Name: <<[Name & Town]>>

Address: <<[Address 2]>>, <<[Address 3]>>, <<[Town]>>, <<[County]>>, <<[Post code]>>

Phone: <<[Primary Contact No]>>, <<[Secondary Contact No]>>

Email: <<[Contact Email]>>

<<Start:(SELECT([Related Orders][Order Id], [Delivery Date] =[Running Sheet Delivery Date]))>>

<<Name & Town>>

Order Date: <<[Order Date]>> Invoice Number: <<[Invoice Number]>> Delivery Date: <<[Delivery Date]>>

Standing Delivery Instructions:<<Standing Delivery/Order Details>>

Product	Quantity	Additional Notes	Payment Type	Gyle 1	Gyle 2
<<Start:[Related Order Details]>><<[Product Name]>>	<<[Quantity]>>	<>	<><>

Print Name: Signature:

<>

Phil_Waite

I’m looking to show the following but based on the order the action button is clicked from and the corresponding paretn and child table.

<<Start:Orderby(Running Sheet 22[Outlet No],[Running Sheet Report Calc],FALSE)>>

Customer Name: <<[Name & Town]>> (parent)

Address: <<[Address 2]>>, <<[Address 3]>>, <<[Town]>>, <<[County]>>, <<[Post code]>> (parent)

Phone: <<[Primary Contact No]>>, <<[Secondary Contact No]>> (parent)

Email: <<[Contact Email]>> (parent)

<<Start:(SELECT([Related Orders][Order Id], [Delivery Date] =[Running Sheet Delivery Date]))>>

<<Name & Town>> (order table)

Order Date: <<[Order Date]>> Invoice Number: <<[Invoice Number]>> Delivery Date: <<[Delivery Date]>> (order table)

Standing Delivery Instructions:<<Standing Delivery/Order Details>> (order table)

(below from the child table)

Product	Quantity	Additional Notes	Payment Type	Gyle 1	Gyle 2
<<Start:[Related Order Details]>><<[Product Name]>>	<<[Quantity]>>	<>	<><>

Print Name: Signature:

<>

Phil_Waite

This is what I have currently

<<Start:Full List Table[Outlet No]>>

Customer Name: <<[Name & Town]>>

Address: <<[Address 2]>>, <<[Address 3]>>, <<[Town]>>, <<[County]>>, <<[Post code]>>

Phone: <<[Primary Contact No]>>, <<[Secondary Contact No]>>

Email: <<[Contact Email]>>

<<Start:(SELECT([Related Orders][Order Id], [OUtlet No] =[_THISROW].[Outlet No]))>>

<<Name & Town>>

Order Date: <<[Order Date]>> Invoice Number: <<[Invoice Number]>> Delivery Date: <<[Delivery Date]>>

Standing Delivery Instructions:<<Standing Delivery/Order Details>>

Product	Quantity	Additional Notes	Payment Type	Gyle 1	Gyle 2
<<Start:[Related Order Details]>><<[Product Name]>>	<<[Quantity]>>	<>	<><>

Print Name: Signature:

<>

Suvrutt_Gurjar

Do you wish to include only the current order and its order details by tapping the action button from the detail view of that order in the email or pdf?

Phil_Waite

Hi yes that is correct

Suvrutt_Gurjar

In that case , I think you do not need the first start statement

You may simply have the remaining template.

Phil_Waite

I don’t get anythign with it for example below results in nothing

<<Start:Full List Table[Outlet No]>>

Customer Name: <<[Name & Town]>>

Address: <<[Address 2]>>, <<[Address 3]>>, <<[Town]>>, <<[County]>>, <<[Post code]>>

Phone: <<[Primary Contact No]>>, <<[Secondary Contact No]>>

Email: <<[Contact Email]>>

<>

Phil_Waite

The action button I click adds a row to another table.

A bot sends an email with the template when a new row is added to the second table. Is that breaking the link between [_THISROW].

Should the action be adding a new row to a table with all the column data to be used in the template?

Phil_Waite

Hi sorry,

No I was wrong.

the columsn below are from the parent table

Address: <<[Address 2]>>, <<[Address 3]>>, <<[Town]>>, <<[County]>>, <<[Post code]>>

Phone: <<[Primary Contact No]>>, <<[Secondary Contact No]>>

Email: <<[Contact Email]>>

Phil_Waite

To clarify - which table is the template starting from? Is it the [Orders] table as that is where the action button is or does it start at the parent table?

Phil_Waite

The below expression give me the name and town for every order in the table.

should it not just give the name and town with the order id that match the order id of the row from which the action button is clicked?

<<Start:Orders[Order Id],[Order Id]=[_THISROW].[Order Id]>>

<<Name & Town>>

<>

Suvrutt_Gurjar

Could you please detail out the table hierarchies you have in terms of parent/child relationships. It sounds that you are pulling certain address and name fields from the Customer table?

Then could you mention what table the action is placed in. Is it correct to understand you wish to send / create report only for one order and associated order details?

Phil_Waite

Hi

Parent is

Full List Table

Child is

Orders

greanchild is

Order details

The action is placed in the Child “Orders” table

thanks

Suvrutt_Gurjar

Thank you. Could you update what values if any you wish to pull from Full List Table

And what exact problem you are facing.

Phil_Waite

Hi

Values to pull from Full List Table Are below

Address: <<[Address 2]>>, <<[Address 3]>>, <<[Town]>>, <<[County]>>, <<[Post code]>>

Phone: <<[Primary Contact No]>>, <<[Secondary Contact No]>>

Email: <<[Contact Email]>>

Can we try and write the expression to just pull this?

Suvrutt_Gurjar

Yes sure. In general, from the Full List Table you can pull values in the Order table as below.

From your description so far, it sounds that the template can be something like below. The example below shows some columns from the grandparent, parent table, and child table. You could replicate the pattern for more columns from respective tabls

Customer Name: <<[Name & Town]>>

Address: <<[Ref Column Referecing Full List Table in Orders Table].[Address 2]>>,

Phone :<<[Ref Column Referecing Full List Table in Orders Table].[Primary Contact No]>>

Email: <<[Ref Column Referecing Full List Table in Orders Table].[Contact Email]>>

Order Date: <<[Order Date]>> Invoice Number: <<[Invoice Number]>> Delivery Date: <<[Delivery Date]>>

Product	Quantity	Additional Notes	Payment Type	Gyle 1	Gyle 2
<<Start:[Related Order Details]>><<[Product Name]>>	<<[Quantity]>>	<>	<><>

Phil_Waite

Audit log says unable to find [Name & Town]

Every table has that column

Suvrutt_Gurjar

In Orders table , is that column referenced from the grand parent table?

Also, does the template work without that column?

If that column also needs to be pulled from the Full List Table, then please use the same expression to pull it in the Orders table.

Customer Name: << [Ref Column Referecing Full List Table in Orders Table].[Name & Town]>>

Phil_Waite

It is sugesting User ID as a column which isn’t in the Orders table. It is referring to a column in the 4th reports table that has a row added to make the bot send an email!

I need to add the Order Id from the order that the action buttton is clicked on to that reports table then select order details etc based on that.

Email template from order table and order details table