Question

Line Items Looping in Report

  • 23 November 2022
  • 30 replies
  • 318 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 7
Badge +8

Hi @ALEXSLUSSER15 ,

Thanks for attaching the report.  I see some problems with your schema that are causing the duplicates and performance issues.  

  1. I don’t see any data field links for some of the relationships in your schema, for example the AMProdItem and the AMOrderType tables should be linked by the OrderType field.  
  2. There is also no relationsip defined between AMProdEvnt and AMProdItem.  Every table in the schema needs to be joined to the other tables by the key fields, otherwise the query will return what is called a “cross join” (the combination of every record on one table with every record in the other table).  

You could look at the standard report Production Order Labor Efficiency to see an example of the joins to production order transactional records.  

Often it’s easier to build a query with the correct relationships using a Generic Inquiry.  To understand the data, try adding a filter so that you are only looking at one are a couple of production orders.  Once you understand the data tables and relationships, then you can use the function “Export as Report” from the GI to create the schema for your report.  

Hope this helps.

Userlevel 7
Badge +8

@ALEXSLUSSER15 ,

I looked again and he very briefly shows his data links tab but that doesn’t help me because, at least from what I can tell, he does not discuss how to link the data fields properly.

You need to add the links in the lower grid.  The lower grid is dependent on the row that is selected up above.  The tricky part can be working out how the tables are related, which is where the suggestions in S150 can be helpful. 

When you’re first getting started with the Report Designer, it’s helpful to try to find a report which is most similar.  That is why I suggested you might want to take a look at the Production Order Labor Efficiency report, because this report drills down to the transactional level for labor transactions.

Userlevel 7
Badge +8

Hi @ALEXSLUSSER15 ,

I don’t know that there’s a natural relationship between AMProdEvnt and AMMTran.  Are you sure that you need both to get the information required for your report?  You may only need AMMTran.

 

Userlevel 2
Badge

Hi @ALEXSLUSSER15 ,

I don’t know that there’s a natural relationship between AMProdEvnt and AMMTran.  Are you sure that you need both to get the information required for your report?  You may only need AMMTran.

 

@lauraj46 I can try removing it. How should I join AMMTran? should I join it to AMProdItem like the others are?

@lauraj46 I looked into it, and yes, I need the AMProdEvnt. I managed to solve the looping issue. I’m honestly not sure how I did it.

I’m now having different issues that I’m not sure are resolvable without severe modification to Acumatica on the backend, but I’ll keep digging into them.

Userlevel 7
Badge +8

Hi @ALEXSLUSSER15 ,

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

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

Hi @ALEXSLUSSER15 ,

How have you defined a relationship between AMProdEvnt and AMMTran?  If by “looping” you mean duplicate records it is most likely because of an issue with the joins.  

 

Userlevel 5
Badge +2

@ALEXSLUSSER15 , 
The issue will definitely by in the joins. AMProdItem to AMProdEvnt would be joined on Ordertype, ProdOrdId to OrderType, ProdOrdID. 
 
I am not sure about the join to AMMTran from AMProdEvnt. 
maybe OrderType, ProdOrdID, LineNbr for each table
 

Userlevel 5
Badge +2

@ALEXSLUSSER15 , 
You only have a join between the first two tables in your rpx. 
You need to define how each of the tables are related to each other.
In the Production Order Performance Report, the joins are defined for the first 2 relations, AMProdItem to AMProdTotal & AMProdItem to AMOrderType. 

So from those 3 tables, you’ll need to 1) add the table(s) that have the data you are looking for & 2) define the relationships so that you an get from one table to the other. 

Unfortunately, my table knowledge in the Production area is not as strong as other modules. 

I would suggest using a GI to build & define the tables & relationships between them.
The GI does let you have access to tolls to define related tables & view the DAC of the tables. 

 

Userlevel 2
Badge

@lauraj46 Hey Laura- I’m a bit confused about what you meant when you said I needed Data Field Links? How do I add those?

Thanks!

Userlevel 7
Badge +8

Hi @ALEXSLUSSER15 ,

Sorry for the confusing terminology :).  You need to add joins between the tables, same as @wfilipiak67 mentioned in his post.  These can be added in the lower grid where it says “Enter the data field links for the active relation:”.  You will need to highlight each of the relationships and specify the joins between the key fields.

Userlevel 2
Badge

Hi @ALEXSLUSSER15 ,

Sorry for the confusing terminology :).  You need to add joins between the tables, same as @wfilipiak67 mentioned in his post.  These can be added in the lower grid where it says “Enter the data field links for the active relation:”.  You will need to highlight each of the relationships and specify the joins between the key fields.

@lauraj46 

What should this look like? As I mentioned above this is all very new to me. I tried adding some data field links and it caused the report to return an error when run. I can provide a screenshot of what I tried if needed.

Userlevel 7
Badge +8

Hi @ALEXSLUSSER15 ,

There is an example the first screen shot that you posted on this thread.  In that screenshot you can see that the joins between the AMProdItem and AMProdTotal are on the ProdOrdID and the OrderType.  I would recommend that you go through course S150 on Open University.  Chapter 4 of the training materials has detailed information about how to join multiple tables.

 

Userlevel 7
Badge +8

Hi @ALEXSLUSSER15 ,

There is an example in the screenshot that you posted to start this thread.  Take a look at the relationship between AMProdItem and AMProdTotal.  In the grid at the bottom there are joins between the ProdOrdID and the OrderType fields.

I would also suggest going through course S150 on the Acumatica Open University.  Chapter 4 of the training materials discusses how to join multiple data access classes in a report.

Userlevel 2
Badge

Hi @ALEXSLUSSER15 ,

There is an example in the screenshot that you posted to start this thread.  Take a look at the relationship between AMProdItem and AMProdTotal.  In the grid at the bottom there are joins between the ProdOrdID and the OrderType fields.

I would also suggest going through course S150 on the Acumatica Open University.  Chapter 4 of the training materials discusses how to join multiple data access classes in a report.

@lauraj46 I looked at the course you mentioned, and looked at Lesson 4, but did not see any mention of data links.

Userlevel 2
Badge

I looked again and he very briefly shows his data links tab but that doesn’t help me because, at least from what I can tell, he does not discuss how to link the data fields properly.

Userlevel 7
Badge +8

Hi @ALEXSLUSSER15 ,

Chapter 4 in course S150 describes how to find the relationships between the data access classes.  Once you’ve done so, you will need to specify these joins in the Report Designer schema.

Alternatively, you can build a query inside of Acumatica as a Generic Inquiry and then Export as Report to create a schema.  The Generic Inquiry screen in Acumatica has the ability to “add a related table” which makes it easier to get the joins right.Course S130 is about building Generic Inquiries.   

 

Userlevel 2
Badge

@ALEXSLUSSER15 ,

I looked again and he very briefly shows his data links tab but that doesn’t help me because, at least from what I can tell, he does not discuss how to link the data fields properly.

You need to add the links in the lower grid.  The lower grid is dependent on the row that is selected up above.  The tricky part can be working out how the tables are related, which is where the suggestions in S150 can be helpful. 

When you’re first getting started with the Report Designer, it’s helpful to try to find a report which is most similar.  That is why I suggested you might want to take a look at the Production Order Labor Efficiency report, because this report drills down to the transactional level for labor transactions.

@lauraj46 Do you have any ideas as to what links may be needed and for what relations? I’m testing out some lniks for the ProdEvnt to AMMTran relation to no results. Any help is greatly appreciated.

Userlevel 2
Badge

Hi @ALEXSLUSSER15 ,

I don’t know that there’s a natural relationship between AMProdEvnt and AMMTran.  Are you sure that you need both to get the information required for your report?  You may only need AMMTran.

 

@lauraj46 I can try removing it. How should I join AMMTran? should I join it to AMProdItem like the others are?

Userlevel 7
Badge +8

@ALEXSLUSSER15 ,

Glad to hear you are making progress.  Best of luck with the report.  

Userlevel 2
Badge

Hey @lauraj46, figured you might be able to help with this. There is no longer hundreds of repeating line items. However- there is still some looping/misprinting occurring. In the Production Order Performance report, I have selected the report parameters for a work order. It should only be printing 1 line item here- the correct line item is second from bottom, with 48.0000 in the material used column. I’ve shown only a small portion here as the other information in the rows are correct, it’s just that it is, for whatever reason, printing other rows that should not be there. It should only be printing Material Transactions, from the AMProdEvnt table, but for some reason it is printing all of them despite me adding a report-wide filter to only print Material Transactions. Any advice? I can send the RPX file if needed.

 

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