Email Report expression

[Phil_Waite]

Hi,

I have a report that creates a running sheet for orders to be taken our by a delivery driver.

There are 3 tables.

Main table with outlets (Full List Table). Child table with orders (Orders) and Grandchild with Order details (Order Details)

There is a column in the Orders table to give the order a “Drop Number”. The user can assign a number from 1 upwards to give the order in which the deliveries shoud be made.

I can very easily create a view that shows the orders in the correct order down the page but I am having trouble getting the report that is emailed to show the orders acording to drop number.

Below is the first part of what works at the moment. The [Drop Number] column is in the main parent table as I was going to assign the drop number to the outlet but realised that it is better for the user to do that to each individual order. The below expression doesn’t place the order in a sequence becasue the [Drop Number] Column is blank for each outlet. Running Sheet 22 is a slice of the main parent table.

As the expression must return a list of Unique IDs I need to be able to return a list of the Unique IDs of Running Sheet 22 Slice and order them by the column [Drop Number] in the Child table not the parent.

<<Start:OrderBy(FILTER(Running Sheet 22,true),[Drop Number])>>

Customer Name: <<[Name]>>, <<[Short Address]>>, <<[Post code]>>

<>

Thanks

Phil

[Phil_Waite]

I have this expression to look up the [Drop Number] value int he child order table but can’t work out how to put this into the report.

LOOKUP([_THISROW].[Outlet No], “Orders”, “Outlet No”, “Drop Number”)

(Outlet No is the unique id for that table)

[Phil]

HI Phil,

Can you describe what you want to appear in the report?

Are you trying to list all of the Outlets with the Orders displayed beneath each Outlet in Drop Number order? If not, what are you trying to do?

[Phil_Waite]

Hi Philip,

Yes that is correect. Below is the full report. Running Sheet 22 is a slice of the main table that gives that unique id for the outlets wanted. After that I want to order the outlets by the [drop number] which is on the order table.

Thanks Phil

Running Sheet <<TEXT(TODAY(),”dd/mm/yy”)>>

<<Start:OrderBy(FILTER(Running Sheet 22,true),[Drop Number])>>

Customer Name: <<[Name]>>, <<[Short Address]>>, <<[Post code]>>

<>

<<Start:OrderBy(FILTER(Running Sheet 22,true),[Drop Number])>>

Customer Name: <<[Name]>>, <<[Short Address]>>

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]>> Delivery Date: <<[Delivery Date]>>

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

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

|Received By/Print:|Signature:|Payment Status:

CASH BACS

CHEQUE OTHER

<>

<>

Below are images of the first sheet that has the details of the outlets all on one page.

After that the order details are listed one to each page. Changing the order according to the drop number means that the first sheet will show the order in which the outlets will be visited and the delivery notes below will also be in order (so I don’t have to rearrange them physically after printing them)

[Phil]

Hi Phil,

You need to use nested <<Start>> expressions. The outer <<Start>> expression is for the Outlets table. The <<Start> expression for the Orders table is nested inside the Outlets <<Start>> expression. The <<Start> expression for the Order Details table is nested inside the Orders <<Start>> expression.

Something like:

<<Start: FILTER(Running Sheet 22,true)>>
Customer Name: <<[Name]>>, <<[Short Address]>>, <<[Post code]>>

<<Start: OrderBy([Related Orders], [Drop Number])>>
Order Date: <<[Order Date]>> Delivery Date: <<[Delivery Date]>>
Standing Delivery Instructions:<<Standing Delivery/Order Details>>

<<Start:[Related Order Details]>>
<<[Quantity]>>

<<End>>

<<End>>

<<End>>

[Phil_Waite · Accepted solution]

Hi Philip,

Thank you.

This below does the job

<<Start:OrderBy(Running Sheet 22[Outlet No],[Related Orders][Drop Number],FALSE)>>

Customer Name: <<[Name]>>, <<[Short Address]>>, <<[Post code]>>

<>