Equivalent fields

  • 29 March 2024
  • 5 replies

Userlevel 4

I am in the process of desiging a new invoice and my question is what and where are the SOLine.DiscPrice and SOLine.CuryLineAmt. I have SOOrder and SOLine in the relationship and have tried a Left Joiin, Right Join, Inner Join and the report doubled up the line items, as there should have been two line items, there is now four. I do have ARTran and ARInvoice and have been experimenting with those, but I’m still a noob in Acumatica.


Best answer by mbarrett 29 March 2024, 17:42

View original

5 replies

Badge +18


It may help if you show the joins.  When a report has too few or too many results, incorrect joins are a common cause.

When joining a header table like SOOrder to SoLine, make sure you are joining on both OrderType and OrderNbr.

Start with just these two tables,and when you have the correct # lines showing in your results, proceed to attach additional tables one at a time, making sure the results don’t duplicate.

Are you saying that the Screen shows DiscPrice and CuryLineAmt but you don’t see these fields in the report choices?  I found these fields in the SOLine table.  This screenshot below is from a generic inquiry (quicker for me to show, and GI’s contain the same fields as report tables).

Good luck!


Userlevel 4

I am enclosing a screen shot of the joins.I have ARTran joined with SOLine using TranType → InvoiceType, RefNbr → InvoiceNbr and LineNbr → InvoiceLineNbr. I did, at one point have the SOrder → SOLine but that was throwing the extra lines in. Maybe I am way off base here.


Userlevel 4

I put the SOOrder Left Join with the SOLine back and it comes up with doubling the line items again. I’ve tried a Left, Right, Inner, same thing


Userlevel 3



You cannot link ARTran to both the SOOrder and SOLine tables at the same time. This is where the duplicates are coming from. Instead, you should flow the links like so:

  • First Link - ARTran Left SOLine
    • SOOrderType = OrderType
    • SOOrderNbr = OrderNbr
    • SOOrderLineNbr = LineNbr
  • Second Link - SOLine Left SOOrder
    • OrderType = OrderType
    • OrderNbr = OrderNbr

Also, The Discount and Line amounts fields can also be found in the ARTran table which would avoid the need to link to the Sales Order Line for this information.


Userlevel 4

I found the values that I needed in the ARTran before getting your answer back, but I appreciate the help. Initially I had totally missed those two fields in ARTRan, and I kept thinking that they must exist somewhere. Again thank you for clarifying that.


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