Solved

Invoices generating duplicate line items

  • 20 October 2023
  • 5 replies
  • 109 views
E
Rank

Hi everyone

Our custom invoice report  has started generating with duplicate line items for certain customers. The problem begins with a customer for no apparent reason, and then remains consistent for that customer. The duplication does not affect calculations, it simply appears on the report.

I have read about similar issues reported in other topics that refer to incorrect or missing joins/relations in the schema. However it seems like each case is slightly different and their solutions do not apply to our case. I have picked the report apart and can’ t seem to find the problem, although I suspect it has to do with ARTran : SOOrder. Here is a screenshot of the report with duplications highlighted:

Schema in report designer:

 

Links for the ARTran - SOOrder relationship

 

Attached is the rpx file of the report if you would like a closer look.

Any help or suggestions would be massively appreciated!

Thanks in advance

Martin

 

icon

Best answer by hkabiri 20 October 2023, 20:14

View original

5 replies

E
Rank

Here is the attachment with the rpx file

Thank you!

1 Attachment
StevenRatner
Rank
Userlevel 5
Badge

@espmartin- The issue is your joins.  What are your joins from ARInvoice to ARTran?  This is most likely your issue.  Please make sure you have the ARTran fields in the detail section.  This will most likely remove your duplicate issue.

Steve - Klear Systems
E
Rank

@espmartin- The issue is your joins.  What are your joins from ARInvoice to ARTran?  This is most likely your issue.  Please make sure you have the ARTran fields in the detail section.  This will most likely remove your duplicate issue.

Hi @StevenRatner 

Thank you so much for your reply. These are the ARInvoice:ARTran joins:

 

All the fields in the detail section being duplicated are from ARTrans:

 

Can you see anything wrong with the joins?

Thanks

Martin

 

hkabiri
Rank
Userlevel 6
Badge +5

@espmartin  The issue with your report is the relation between customer and ARBalances and that is why it makes the lines duplicated.

If you check ARBalances Table you can see you have different lines for different customer locations and that means you need to add the customerLocationID in your relation.

However, customer Table which you used is not helpful, you can use ARInvoice instead and relation of ARInvoice.CustomerID to ARBalnces.CustomerID as well as ARInvoice.CustomerLocationID to ARBalances.CustomerLocationID (below screenshot )

This change should help.

SAM KABIRI | L2 Support Engineer | hkabiri@acumatica.com | www.acumatica.com
E
Rank

@espmartin  The issue with your report is the relation between customer and ARBalances and that is why it makes the lines duplicated.

If you check ARBalances Table you can see you have different lines for different customer locations and that means you need to add the customerLocationID in your relation.

However, customer Table which you used is not helpful, you can use ARInvoice instead and relation of ARInvoice.CustomerID to ARBalnces.CustomerID as well as ARInvoice.CustomerLocationID to ARBalances.CustomerLocationID (below screenshot )

This change should help.

@hkabiri Thank you so much - I made the changes you recommended and the report is generating correctly now with no duplicates...

Much appreciated!

Reply


Terms & ConditionsCookie settings
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