Skip to main content
Answer

Prevent Duplicate Lines in Report Designer When ARTran Joins with Multiple ARTax Lines

  • June 25, 2025
  • 2 replies
  • 69 views
T
Captain I
Forum|alt.badge.img+3

I'm working in Acumatica Report Designer and building a report that joins ARTran with ARTax. The issue I'm facing is that when a transaction line has multiple tax records (i.e., multiple ARTax rows per ARTran), the detail section repeats the same ARTran line for each tax row.

I only want to show one line per transaction (ARTran), and conditionally add the tax amount only when the TaxCategoryID is 'OTP'. Additionally, the ARTax.TaxID can vary by state (e.g., MSOTP, AROTP, TNOTP66SALES, etc.).

I’ve used this expression in the detail section:

=$DrCrSign * ( [ARTran.CuryTranAmt] + IIF(InStr([ARTax.TaxID], 'OTP') > 0, [ARTax.TaxAmt], 0) )

 

Please guide thanks

Best answer by tahayabali

Thanks. I have created a new view “Tax View” Saved every tran in that table and used that.

 

 

Taha Yab Ali

2 replies

A
Jr Varsity III
Forum|alt.badge.img+2

 

  1. Join Structure: Keep the join from ARTran to ARTax but:

    • Set ARTax join as optional (LEFT JOIN).

    • Ensure the join includes ARTran.LineNbr = ARTax.LineNbr.

  2. Group the report by ARTran.TranType, ARTran.RefNbr, and ARTran.LineNbr.

  3. Use a summary function to aggregate the tax amount conditionally:

    • Instead of putting your expression in the detail section, use a calculated field at the group level or use group summary fields.

 

=$DrCrSign * ( [ARTran.CuryTranAmt] + Sum(IIf(InStr([ARTax.TaxID], 'OTP') > 0, [ARTax.TaxAmt], 0)) )

 

Make sure this expression is placed in the group footer or group header, not the detail section, to avoid repeating lines.

Hope it helps!

Abhishek Nikam https://www.linkedin.com/in/abhisheknikam3
Chris Hackett
T
Captain I
Forum|alt.badge.img+3
  • tahayabali
  • Author
  • Captain I
  • Answer
  • June 25, 2025

Thanks. I have created a new view “Tax View” Saved every tran in that table and used that.

 

 

Taha Yab Ali
Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
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