Email report sending multiple emails with duplicate orders and details

Hi,

I have multiple emails and duplicate orders and details coming through when I hit run on a report and not sure why.

template as below

Running Sheet Report

Open Orders Report <<NOW()>>

<<Start:FILTER(Running Sheet 21,true)>>

Order

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

Order Id: <<[Order Id]>>

Order Date: <<[Order Date]>>

Delivery Date: <<[Delivery Date]>>

Order Total: <<[Complete Order Total Inc Vat]>>

OrderDetail

<<Start:[Related Order Details]>>

Order Status: <<[Order Status]>>

Product: <<[Product Name]>>

Quantity: <<[Quantity]>>

Price: <<[Price]>>

Ext Price: <<[Quantity] * [Price]>>

<>

This probably means that there are multiple updates triggering the Workflow that sends the email. If that is true you can resolve one of three ways:

  1. If there is some data differences between the updates, you can use the difference to further restrict the trigger criteria on the Workflow.

  2. Try to see if the updates can be combined so that only 1 update is made. Fro example, some create multiple Actions to perform column updates. Each creates a separate row update. You can combine the column updates into one Action and get just a single row update.

  3. If neither above will work then you will probably need to create a trigger flag for sending. This is just a Yes/No column that when set to “Yes” triggers the work flow. You would then create a Grouped Action to set the flag to “Yes” then immediately set it to “No”. Adjust the workflow to send ONLY when this flag is “Yes” plus any other necessary trigger criteria. Among all the updates, there will only be 1 where the flag is “Yes” triggering the Workflow only once. EDIT:: One critical point, you’ll want the set flag actions to happen AFTER any other critical updates that are being made to trigger the Workflow.

I hope this helps!!

1 Like

Hi Willow,

Thank you. I have just 1 report that I am triggering by hiting “run”. It produces not only more than one email but the contant of the email has duplicate orders and order details. Some of them are listed 3 times. I don’t have any triggers to launch the report or workflows connected to it.

Many thanks

Phil

Ok, so you are using a Reports rule instead of a Workflow rule. Is that correct?

If so can you post an image of the Reports Rule criteria that you are using to trigger the report?

Hi Willow,

I don’t belive I have any rules or triggers. I have attached screen shots of the report set up.

This is the first order in the email that shows twice.

Running Sheet Report

Open Orders Report 20/12/2019 16:24:34

Order

Name AND Town: Plough Inn Lupton

Order Id: f424de59

Order Date: 09/12/2019

Delivery Date: 19/12/2019

Order Total: £183.60

OrderDetail

Order Status: Exported To Xero

Product: Lister’s Christmas Ale 9G 4.4%

Quantity: 1

Price: £79.00

Ext Price: £79.00

Order Status: Exported To Xero

Product: Lister’s Best Bitter 9G 3.9%

Quantity: 1

Price: £74.00

Ext Price: £74.00

Order

Name AND Town: Plough Inn Lupton

Order Id: f424de59

Order Date: 09/12/2019

Delivery Date: 19/12/2019

Order Total: £183.60

OrderDetail

Order Status: Exported To Xero

Product: Lister’s Christmas Ale 9G 4.4%

Quantity: 1

Price: £79.00

Ext Price: £79.00

Order Status: Exported To Xero

Product: Lister’s Best Bitter 9G 3.9%

Quantity: 1

Price: £74.00

Ext Price: £74.00

Here is the Log, It shows 2 workflow actions triggered by hitting the run button on the report but I don’t have any workflows or actions relating to this and not sure why they would trigger from me hitting run.

Ok, first I do not have experience with Reports. But here is what I believe is happening.

You have a Report rule defined that is running daily at 11pm against the Slice “Running Sheet 21”. It is set to run for the entire Slice and no other criteria to further filter rows.

EDIT: This part below is INCORRECT! I have informed myself that when the option of “ForEntireTable” is selected, the report DOES NOT send any rows to the template. You must select the rows by using a START/END block in the template itself.

THIS PART IS INCORRECT!!!
When the report is run, it is feeding all the rows into the report template.  The template has as the first statement a Start/End block with a FILTER(), that will AGAIN select ALL the rows in the Slice.  

I don't think you need that top Start/End block because that part is already happening from the report rule. 

As to why, you are getting multiple emails, I don’t see anything in what you posted that explains that. But the report rule wasn’t fully posted so I can’t see if there are multiple steps maybe or if there is something int he Email Content section that might explain it.

1 Like

And By the Way, in the Log the name “Action 1” is referring to the Report Rule step the action being taken within the Flow of the rule. You can actually give them custom names by entering the Workflow action name.

You can have more than one step in the Report Rule (and Workflows as well). When you do, by default they are named “Action 2”, “Action 3”, etc. BUT there is nothing preventing someone from giving them all the same name.

1 Like

I am wrong!! I began playing with a Report and reading the article and realize that you DO need the START/END block. Sorry for that!!

I will correct my posts above.

1 Like

I am not sure if the application has changed since this was posted, but I see a problem with the attachment template for Report “Running Sheet Report”.

In the attachment template there is a table to display Order Details. In that table in the leftmost cell under the “Product” header you should specify:
<<Start:[Related Order Details]>><<[Product Name]>>

In the table’s rightmost cell under the “Total Price” header you should specify:
<<[Quantity] * [Price]>><<End>>

There are two <<End>> tokens following the table. There should only be one <<End>> to match the <<Start>> near the top of the attachment template.

Essentially you need to move the <<End>> from below the table into the rightmost cell in the table.

When you are using a table, the <<Start>> should be in the leftmost cell of the table’s data row.
The <<End>> should be in the rightmost cell of the table’s data row.
That is telling us to repeat everything between the <<Start>> and <<End>> in that table row for each Order Detail record.

2 Likes

One clarification. It appears that the Body template is also undergoing change. I was not sure whether to look at it or not. I think that is the template that you and John are working on currently.

I just looked at the Body template and it seems correct at this point.
Are you seeing bad output from it?

The reason you saw multiple emails and Audit History records when you ran the report at 12/20/2019 4:24:36 PM was that it was run using version 71 of your application. In that version, the “For each row in table” option was specified and the Report was using the Orders table. There were 8 rows in the Orders table that matched, so you got eight reports in eight separate emails.

2 Likes

@Phil_Waite, @Phil has got you covered! For what, its worth I did get a test Report created and run successfully. I created a slice and ran a template against it that was fairly complex - the initial START/END block to select the report rows and then I had 4 different START/END’s for 4 different Related Child tables. All worked as expected.

One other point of clarification, when the log entries are inserted for each report run, they may not be in chronological order of execution. For example, below is an excerpt from my Log. At first glance it appears that the “Action 1” report step ran twice. Actually, it ran once each time its just log entries are not in consistent order - you can look at the timestamps here and see that.

If your report at one point ran as Phil stated, “once for each row” and there were 8 rows, there would have been 8 “Action 1” step entries all with the same timestamp and back to back - but still there is no guarantee of order related to the “Scheduled report rule” log entry. It could have been written in the middle of those 8!!

Anyway, sorry for any confusion I caused. And I hope Phil got you on the right track.

2 Likes

Hi John,

Thanks for your help. I am able to view both the Audit History and the Report templates so that makes it much easier for me to see what is happening. You provide outstanding help. Please keep doing it.

1 Like

Hi Guys,

Thank you both for your help. Yes you’re right I had it set to every row rather than entire sheet! That was why i was getting duplicates.

You’re right Phil, my attachment template isn’t working. I will make the changes you have suggested.

Having a problem with the body template for the report though now. It hasn’t changed but log is saying “complete order total inc vat” column not found.

And also “Related Order Details” not found. Not sure what’s changed. Solved the duplicate error but template not working now.

Here is the template again

Running Sheet Report

Open Orders Report <<NOW()>>

<<Start:FILTER(Running Sheet 21,true)>>

Order

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

Order Id: <<[Order Id]>>

Order Date: <<[Order Date]>>

Delivery Date: <<[Delivery Date]>>

Order Total: <<[Complete Order Total Inc Vat]>>

OrderDetail

<<Start:[Related Order Details]>>

Order Status: <<[Order Status]>>

Product: <<[Product Name]>>

Quantity: <<[Quantity]>>

Price: <<[Price]>>

Ext Price: <<[Quantity] * [Price]>>

<>

<>

Hi,

I’ve realised that the report is being produced from the “order details” table not the “orders” table which is why it can’t find the child table as it is the child table.

Cheers

Phil

1 Like

Hi Phil,

Glad you figured that out. Is everything working properly now?

Hi Phil,

Yes thank you

Phil