Question

Line Items Looping in Report

  • 23 November 2022
  • 30 replies
  • 319 views


Show first post

30 replies

Userlevel 2
Badge

Hi @ALEXSLUSSER15 ,

This sounds like there is still something not quite right with your joins.  Please post the latest rpx file.

@lauraj46 

Userlevel 2
Badge

It seems I accidentally marked the post as solved. Oops. It is not solved at this time.

Userlevel 7
Badge +8

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.

 

Userlevel 2
Badge

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.

 

@lauraj46 I am using the AMProdEvnt table to filter out all non-Material Transaction items. Unless there is a way I can do this without the AMProdEvnt table, it is necessary. You can see it in the Filters tap of the Schema Builder. Let me know what you think.

Userlevel 7
Badge +8

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

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved