Line Items Looping in Report

@lauraj46 

Hi @ALEXSLUSSER15 ,

The AMProdItem table has a row for each Production Order.

To find the transactions for that Production Order you can join (as you do) with AMMTran on the OrderType and the ProdOrdID.  This will return a detail row for each transaction. 

The reason that you are having problems is because you have also joined AMProdItem with AMProdEvnt. That join will also return a detail row for each event. 

Behind the scenes, the Report Designer schema builder is building a SQL query.  In a SQL query, when you join a header record with two different detail tables the query returns all combinations.  So for example if there are two material transactions (AMMTran records) on the production order and there are two events on the production order (AMProdEvnt records), the query will return 4 detail rows:

• Tran 1 / Event 1
• Tran 1 / Event 2
• Tran 2 / Event 1
• Tran 2 / Event 2

You mentioned that the AMProdEvnt table is needed on the report, but I didn’t see where it is used.  My recommendation would be to remove this table from the report and that may help.  Or if it is needed then you will need to find the correct fields to join a transaction record with a unique event record.  As @wfilipiak67 mentioned you could try the LineNo field which is on both tables, but I’m not sure if that’s right.  I would recommend that you use a Generic Inquiry to explore the data and that may help you to sort this out.

Also, I noticed that on some of your joins you are referencing the same table and field on both parent and child.  This shouldn’t cause a problem, but it is not adding anything useful to the schema.

@ALEXSLUSSER15 ,

You can use the DAC Schema Browser (available on the Tools menu from most screens in Acumatica) to examine the AMMTran data and learn more about the fields.  Here you can see that the doctype = ‘M’ identifies material transactions.