Skip to main content
Question

Line Items Looping in Report

  • 23 November 2022
  • 30 replies
  • 353 views

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

lauraj46
Rank
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.  

 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
ALEXSLUSSER15
Rank
Userlevel 2
Badge

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.  

 

Hi @lauraj46 ,

Essentially, when I run the report, it just repeats the line item over and over again, for hundreds of pages. It takes a long time to load.

I’ve also come to the conclusion that it is the joins, but I’m not specifically sure how to fix it. I’m still very new to Acumatica reports.

 

lauraj46
Rank
Userlevel 7
Badge +8

Hi @ALEXSLUSSER15 ,

Please attach your report (rpx) file to this thread.  Thanks.

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
ALEXSLUSSER15
Rank
Userlevel 2
Badge

@lauraj46 Here it is. 

 

1 Attachment
wfilipiak67
Rank
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
 

ALEXSLUSSER15
Rank
Userlevel 2
Badge

@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
 

@wfilipiak67 This is the current join table. Could you break me down the joins I need to change, add and remove? I’m a bit confused as to which ones to modify.

 

lauraj46
Rank
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.

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
wfilipiak67
Rank
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. 

 

ALEXSLUSSER15
Rank
Userlevel 2
Badge

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.

Sorry, I’m very confused. What *should* the relationships table look like? This is all very new and complex to me.

ALEXSLUSSER15
Rank
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!

lauraj46
Rank
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.

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
ALEXSLUSSER15
Rank
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.

lauraj46
Rank
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.

 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
lauraj46
Rank
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.

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
ALEXSLUSSER15
Rank
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.

ALEXSLUSSER15
Rank
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.

lauraj46
Rank
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.   

 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
lauraj46
Rank
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.

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
ALEXSLUSSER15
Rank
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.

lauraj46
Rank
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.

 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
ALEXSLUSSER15
Rank
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?

ALEXSLUSSER15
Rank
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.

lauraj46
Rank
Userlevel 7
Badge +8

@ALEXSLUSSER15 ,

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

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
ALEXSLUSSER15
Rank
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.

 

lauraj46
Rank
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.

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings