Solved

Duplicated Line Items after adding SOLine.ShippedQty - SO, Invoice

  • 4 November 2021
  • 15 replies
  • 108 views

 

icon

Best answer by arjan 17 November 2021, 17:13

View original

15 replies

 

Userlevel 2

Hi - you are getting this because the SO is the parent of SOLine (One to many) and so if there is more than on line in the SO - you get duplicates.

Try joining the ARTan to the SOLine first - and then to the SO - that way you maintain the one to one with the ARTran - and use the SO as a ‘lookup’

Also - your join is not correct - you need to join SOLine to SO by RefNbr 

Hint : Try this in a GI first - you can use the related tables feature to find how to join the tables.

Best

G

Userlevel 7
Badge +10

Hi @arjan   The SOOrder and SOLine join is wrong, and it should be like below.

 

 

Hi @Naveen B ,

 

it is still the same after following all the guidance.

Userlevel 7
Badge +10

Hi @arjan If that is the case,  we need to check all the joins conditions, whether we have properly applied or not.

I’m seeing one more on this issue with the joins. Please find the details below.

  • REMOVE the Join records between (ARTran and SOOrder) and (SOOrder and SOLine) 
  • Add new join between ARTran and SOLine with the below conditions (since you wanted to see only SOline Shipped Qty → no need to join with SOOrder). 

Please find the screenshot for reference.

 

 

 

Userlevel 7
Badge +10

Hi @arjan  The above issue is NOT with the newly added JOINs, you might need to check the other areas where it is causing this issue. 

Hi Naveen,

can you please expand more on other areas that need attention?

report preview works when changes above haven’t made.

 

Userlevel 7
Badge +10

Hi @arjan  In report, what value you have provided for the SHIP ? 

@Naveen B     SOLine.ShippedQty

 

along with your suggestion above, i also added table relation for ShippedQty. throws same error.

Userlevel 7
Badge +10

Hi @arjan  There is something wrong! Can you please share your report here.

@Naveen B  please see attached zip file.

Userlevel 7
Badge +10

Hi @arjan  I just reviewed your report, and it seems you have NOT added the SOLine Joins in the RELATIONSHIPS tab.

You made join between ARTran and SOOrder but NOT ARTran and SOLine, hence you are getting that issue.

Add the SOLine with ARTran as suggested abouve and verify. 

 

Here is the screenshot for your reference.

 

Hi @Naveen B i sent the original report, before making those changes. thought maybe there is a issue with the report itself and how it was setup in first place. However, when i have both ARTran-SOOrder and ARTran-SOLine (LineNbr added) setup, report works just fine with ShippedQty and no duplication.

 

Userlevel 7
Badge +10

Awesome @arjan  Thanks for sharing the update.

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 — 2020  Acumatica, Inc. All rights reserved