Skip to main content
Question

Line Items Looping in Report


Show first post

30 replies

ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • December 1, 2022
lauraj46 wrote:

Hi @ALEXSLUSSER15 ,

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

@lauraj46 


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • December 1, 2022

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


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 511 replies
  • December 1, 2022

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
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • December 1, 2022
lauraj46 wrote:

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.


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 511 replies
  • December 1, 2022

@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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings