Solved

How to connect the SOShippingAddress table to the ARTran table

  • 21 October 2022
  • 6 replies
  • 142 views
M
Rank
Userlevel 5
Badge +1

I’m trying to create a GI to show sales transactions using the ARTran table along with the postal code from the shipping address related to that transaction. What is the best way to connect the SOShippingAddress (where the PostalCode is found) to the ARTran table? Or is there a better way around this?

icon

Best answer by BenjaminCrisman 21 October 2022, 16:38

View original

6 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

Hi @mikeho! I’m not seeing a SOShippingAddress table, but I do see SOAddress, is this the one?

Here’s what to remember about the different contact tables, the main data is stored in Contact and displayed on Customer/Vendor pages.

You can get the address information by joining Customer and Contact.
SOAddress, ARAddress, APAddress are all related to specifically the address details which appear on the documents from that module, this way you can easily show that the main address is X while the SO document is using Y.

That being said, you are looking to join the SO table to ARTran?

If we start at the ARInvoice and link to ARTran (DocType = TranType, RefNbr = RefNbr) then we have all the ARInformation (though ARInvoice could be replaced with ARRegister). To link to Sales Orders from there I checked the Merged DAC Relations (SM402000):
 

From SOOrder you can link to the SOAddress

M
Rank
Userlevel 5
Badge +1

Thank you, @BenjaminCrisman! I was looking at the SOShippingAddress table based on the element properties in the SO:

 

But you’re right, I was able to connect the SOOrder table to the SOAddress table and get the information I needed!

 

B
Rank
Userlevel 1
Badge

Has anyone managed to join the ARTran, SOOrder and SOLine tables without causing duplicates?

C
Rank
Userlevel 7
Badge +9

Hi @Bree612 Please find the attached Generic Inquiry as a solution.
We could join the ARTran, SOOrder and SOLine without causing duplicates with the below limitations.

Limitation: The Sales Order has one Shipment and One Invoice.

Screenshots for reference:

 

 

1 Attachment
Chandra M
BenjaminCrisman
Rank
Userlevel 7
Badge +4

Has anyone managed to join the ARTran, SOOrder and SOLine tables without causing duplicates?

@Bree612 I’m not sure it’s possible in a GI if there are multiple lines on the order, having SOLine and ARTran in the same GI will return multiples of the same details I’m sure.

Typically, if this type of data is needed to show the necessary data, it’s recommended to use a report where the table causing the duplicates is instead added to a subreport and this is used instead of trying to join all the tables into the same report relations.

B
Rank
Userlevel 1
Badge

Thank you!. @ChandraM and @BenjaminCrisman

Your comments helped me solve the issue using a different approach.

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