Skip to main content
Solved

Report is showing multiple of the same line item

  • February 25, 2025
  • 6 replies
  • 47 views

Forum|alt.badge.img

One of our reports, the print invoice report, is now showing multiple of the same item. I assume its from a bad join, but I’m not sure where it is or how to fix it.

 

Best answer by BenjaminCrisman

@kkraus There are two things going on with the relations, but I think only one of them is affecting the output. The first issue is that the relation from SOOrder to SOOrderShipment is missing the link on OrderType. Always make sure to include all available key linking fields.

The second issue is that joining in SOPackageDetailEx is going to cause duplicates and should instead be included by adding a subreport and using this table in the subreport. Anytime there is a one-to-many type relation which is unavoidable due to needing to bring the data into the report, it is necessary to use a subreport for this. Are you familiar with this process?

 

View original
Did this topic help you find an answer to your question?

6 replies

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • Acumatica Support Team
  • 691 replies
  • Answer
  • February 25, 2025

@kkraus There are two things going on with the relations, but I think only one of them is affecting the output. The first issue is that the relation from SOOrder to SOOrderShipment is missing the link on OrderType. Always make sure to include all available key linking fields.

The second issue is that joining in SOPackageDetailEx is going to cause duplicates and should instead be included by adding a subreport and using this table in the subreport. Anytime there is a one-to-many type relation which is unavoidable due to needing to bring the data into the report, it is necessary to use a subreport for this. Are you familiar with this process?

 


Forum|alt.badge.img
  • Author
  • Freshman I
  • 33 replies
  • February 25, 2025

@BenjaminCrisman It was those two tables that were causing the problem. I’ve broken them off into a sub table per your suggestion. However, I’m having trouble brining in only the tracking number for that specific invoice. I’ve attached the two reports for refence on what I tried.

 


Forum|alt.badge.img
  • Author
  • Freshman I
  • 33 replies
  • February 25, 2025

Figured it out ​@BenjaminCrisman. Thanks.


WillH
Varsity II
Forum|alt.badge.img+1
  • Varsity II
  • 45 replies
  • March 3, 2025

@BenjaminCrisman  - checking my understanding so I can make sure I’m giving good advice to anyone who asks me.

 

I think you can achieve the deduplication without a sub report if you’re very careful with Grouping formulas, and there are times where I’d recommend this for performance reasons vs embedding subreports.

Do you agree that my method would at least work in principle?


BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4

@WillH Yeah, I’m sure it’s possible, but the problem is that when you are including these results in the grouping, most of the time there is a quantity or amount field also included and grouping the values will Sum these amounts. It might be possible with a variable I suppose but subreports are so much easier in my opinion. I do understand about the performance, but for this report the most common way to add the package and/or tracking details to the report is via subreport


WillH
Varsity II
Forum|alt.badge.img+1
  • Varsity II
  • 45 replies
  • March 7, 2025

Thanks ​@BenjaminCrisman , appreciate the opinion shared.

I think it reflects the specific ERP product I learnt to write reports in, and my experience unpicking legacy calculations. 


For reference incase anyone wants to pursue the kind of logic I’m mentioning and shows up in this thread reply:
You should be able to achieve this kind of thing by using the variable logic and Groups in various Acumatica reports. 
If you’re going to pursue this, make sure you’re dealing with the identity key values when you create your groups, since you’ll be needing to transfer the value you need into the variable & sum that going forward, rather than summing the individual line value.

 

 

I think Benjamin is 100% right that the logic is easier if you program this with subreports, once you wrap your head around subreports.  My concerns/preferences are generally based around performance impacts due to the way subreports are called.


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