I have setup an event that looks for Orders entered with a ship date with-in 4 days of the entered date. When previewing the results, everything looks good. When I try to setup the email so that it will show all the line items included on the order, it only puts the first one and sends the email. I have the Item_no column in inside the repeat start and end.

Has anyone had success working this kind of query?

Regards,
Marc

I've made this work in a similar situation (it used a different table), but it took some playing around. I can't remember exactly what I did but I think I had to add the line number to the email and then do a sort in the query.

If you like, I'll try to dig it up and email it to you or send me yours to look at. I think you can send me a private message from my profile page.


Andy

Thanks for the info on adding the line number to the email. I added it to the sort after the order number and then put it inside the repeat calls and it the email still only puts in the info on the first line item. Here's the sql statment that I'm using....

SELECT dbo.oeordlin_sql.ord_no AS "ord_no", dbo.oeordhdr_sql.cus_no AS "cus_no", dbo.oeordhdr_sql.bill_to_name AS "bill_to_name", dbo.oeordlin_sql.item_no AS "item_no", dbo.oeordlin_sql.item_desc_1 AS "item_desc_1", dbo.oeordlin_sql.qty_ordered AS "qty_ordered", dbo.oeordhdr_sql.entered_dt AS "entered_dt", dbo.oeordhdr_sql.shipping_dt AS "shipping_dt", dbo.oeordlin_sql.line_seq_no AS "line_seq_no" FROM (dbo.oeordhdr_sql LEFT OUTER JOIN dbo.oeordlin_sql ON dbo.oeordhdr_sql.ord_no = dbo.oeordlin_sql.ord_no) WHERE dbo.oeordhdr_sql.status <> 'L' AND dbo.oeordhdr_sql.entered_dt = '{%Current Date%}' AND dbo.oeordhdr_sql.shipping_dt < dbo.oeordhdr_sql.entered_dt +4 AND dbo.oeordhdr_sql.status IN ( '1' , '4' , '6' ) AND dbo.oeordhdr_sql.selection_cd = 'C' ORDER BY dbo.oeordlin_sql.ord_no ASC, dbo.oeordlin_sql.line_seq_no ASC

In the email, I start with a little text including the order number then start the repeat. I then placed the line_seq_no, item_no, item_desc_1, and qty_ordered fields on the same line then end the repeat function.

To me, it seems like they need to add a grouping function.

Regards,
Marc

You need to use the unique ID from the order line table in your query definition, not from the order header when you want the email to loop through the order details.

rbrechlin wrote:
This si the correct answer for your problem

I had the same issue and you have to select the A4glIdentity (If you are using progression).

I will warn you that you might run into some issues here. For example if you add a line item to the order the even will be triggered again but the data you receive will not represent the entire order...