Question

Line Items Looping in Report

  • 23 November 2022
  • 30 replies
  • 243 views

Userlevel 2
Badge

Hey all,

I’m working on modifying the Production Order Performance report. I had some issues with it repeating lines yesterday, but ended up fixing them. After making further changes (necessary ones) it has started looping again. I can’t repeat the same solution, as the solution would simultaneously undo the changes as they conflict. It has to do with the relationships of tables. A screenshot is included below. I need to have the AMProdEvnt and AMMTran tables involved. They are necessary for the changes I am making. I don’t know how to properly configure them as to not cause looping. Any help is greatly appreciated.

 


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

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

@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