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.
Solved
Equivalent fields
Best answer by mbarrett
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.
+19Hello,
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!
Laura
Laura ~ Klear Systems, Inc. Irvine, CA. ~ Click my logo to Follow me ~
1 person likes this
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.
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
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.
- Matthew Barrett ~ SWK Technologies Consultant
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.
William J Matthews
1 person likes this
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.