Email report sending multiple emails with duplicate orders and details

Phil_Waite
Participant V

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]>>

<>

0 18 921
18 REPLIES 18

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!!

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.

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.

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.

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.

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.

@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.

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.

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

Hi Phil,

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

Hi Phil,

Yes thank you

Phil

Top Labels in this Space