Skip to main content
Question

Joining ARInvoice to MasterInvoice

  • Mar 18, 2024 1:32 PM UTC
  • 9 replies
  • 130 views
  • Translate All

Forum|alt.badge.img

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”

9 replies

Vignesh Ponnusamy
Acumatica Moderator
Forum|alt.badge.img+5

Hi @hmzamr,

Can you please more details like steps to reproduce, a simple customization package, current behavior and expected behavior? 

Translate

Forum|alt.badge.img
  • Author
  • Freshman I
  • Mar 20, 2024 2:20 PM UTC

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

Translate

Vignesh Ponnusamy
Acumatica Moderator
Forum|alt.badge.img+5

Hi @hmzamr,

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?

Translate

Atiq
Jr Varsity III
Forum|alt.badge.img
  • Jr Varsity III
  • Mar 22, 2024 4:01 PM UTC

Hi @hmzamr ,

                           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 ?

Translate

Forum|alt.badge.img
  • Author
  • Freshman I
  • Mar 22, 2024 11:33 PM UTC

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?

Translate

Atiq
Jr Varsity III
Forum|alt.badge.img
  • Jr Varsity III
  • Mar 22, 2024 11:44 PM UTC

Hi @hmzamr ,

                         Can you please elobarate, what information are you displaying on the grid?

Translate

Forum|alt.badge.img
  • Author
  • Freshman I
  • Mar 22, 2024 11:57 PM UTC

i’m displaying Tax information; Installments, TaxTotal, amounts, terms and i’m doing calculated fields to calculate installments taxes based on Terms

 

Translate

Atiq
Jr Varsity III
Forum|alt.badge.img
  • Jr Varsity III
  • Mar 23, 2024 12:03 AM UTC

Based on What you are pulling the data, i mean how are you bringing the data?

Translate

Forum|alt.badge.img
  • Author
  • Freshman I
  • Mar 24, 2024 1:53 PM UTC

Hi @Atiq 
Here is the statment i’m using :
SelectFrom<ARInvoice>
                .LeftJoin<Customer>.On<ARInvoice.customerID.IsEqual<Customer.bAccountID>>
                .Where<ARInvoice.status.IsEqual<ARDocStatus.closed>……..etc 

Translate