Question

How to join two unrelated tables to a custom table

  • 12 October 2021
  • 6 replies
  • 62 views

I would like to create a GI showing all the AP Invoices and AR Invoices for a custom table having a field called Housebill. I can get it to work showing only AP invoices or only AR invoices, but not both at the same time. I attached screenshots of the existing GI. Any help would be appreciated.


6 replies

Userlevel 7
Badge +9

Hi @donnamcintosh49  Unable to upload this GI in 21 R1 version. Can you please let me know which version you built this GI ?

2020 R2 20.217.0022

Hi @Naveen B , Is there any other information or data I can provide that would be any assistance to you?

Thanks,

Donna

Userlevel 7
Badge +9

Hi @donnamcintosh49  I see that you have attached empty document, can you please upload the XML format of GI, that will help me to review from my end.

Naveen - I have attached a XML file and the correct Word document. Here is a ‘close’ example of the end result. I want to open this GI from another GI that is a simple list of Date and HAWB. If I have a condition set in the AR AP by HAWB GI for a specific HAWB, I get immediate results. If I remove the condition, I get a time out message. Please let me know where I need to go from here.

Thank you.

Userlevel 7
Badge +9

Hi @donnamcintosh49  Since I do not have the below DAC, hence I’m unable to upload the provided GI to my local instance.

But here is my suggestion, could you please change your joins like below. Since I have verified in my local instance, I cannot say 100% this will fix the issue.

Recap → Left Join → ARInvoice

ARInvoice → Inner Join → AP Invoice

 

 

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