Hello Community,
i’m working on a customization project and i want a grid to show some results of joining 2 tables, I wanted to ask how to join ARinvoice to MasterInvoice, because when i do it the form shows “ARInvoice duplicated many times in FROM clause”
Joining ARInvoice to MasterInvoice
Userlevel 1
Userlevel 7
+4
Hi
Can you please more details like steps to reproduce, a simple customization package, current behavior and expected behavior?
Userlevel 1
Hi Vignesh,
i’m making a new form to show a grid showing ARInvoices, i want to join the ARInvoice to it’s MasterInvoice where ARInvoice.masterRefNbr is equal to MasterInvoice.refNbr
SelectFrom<ARInvoice>
.LeftJoin<MasterInvoice>.On<ARInvoice.masterRefNbr.IsEqual<MasterInvoice.refNbr»
the project was validated and published successfully, but when it comes to show the form or add fields to it, it says ‘Duplicated use of ARInvoice_ARInvoice in one FROM clause ’
because basically MasterInvoice is AR.ARInvoice
Userlevel 7
+4
Hi
I don’t see the MasterInvoice table or DAC. Is it a custom DAC? Can you possibly share a customization package using which we can reproduce the behavior?
Userlevel 3
Hi
The Master Table for ARInvoice is ARRegister, and the Relation you are looking for is ARInvoice.RefNbr=ARRegister.refnbr
Is this what you are looking for ?
Userlevel 1
in a simple word, i want to join ARInvoice to itself where the masterRefNbr is equal to refNbr.
Is there a way where i can do that?
Userlevel 3
Hi
Can you please elobarate, what information are you displaying on the grid?
Userlevel 1
i’m displaying Tax information; Installments, TaxTotal, amounts, terms and i’m doing calculated fields to calculate installments taxes based on Terms
Userlevel 3
Based on What you are pulling the data, i mean how are you bringing the data?
Userlevel 1
Hi
Here is the statment i’m using :
SelectFrom<ARInvoice>
.LeftJoin<Customer>.On<ARInvoice.customerID.IsEqual<Customer.bAccountID>>
.Where<ARInvoice.status.IsEqual<ARDocStatus.closed>……..etc
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.