Solved

Fields not showing data after Left Join

  • 27 March 2024
  • 2 replies
  • 38 views
H
Rank
Userlevel 1

Hello Community;
I’m creating a form to retrieve Invoices and Memos infos and calculate some taxes,
I’m doing a Left join between ARInvoice table and MasterInvoice where master invoice is a table inherting from ARInvoice, like this : 

public class MasterInvoice : PX.Objects.AR.ARInvoice
{
}


SelectFrom<ARInvoice>
                .LeftJoin<Customer>.On<ARInvoice.customerID.IsEqual<Customer.bAccountID»
                .LeftJoin<MasterInvoice>.On<ARInvoice.masterRefNbr.IsEqual<MasterInvoice.refNbr»


The project is published successfully and the MasterInvoice fields are there, but those fields showing no data.

Any ideas?

icon

Best answer by Dmitrii Naumov 27 March 2024, 15:09

View original

2 replies

Dmitrii Naumov
Rank
Userlevel 7
Badge +5

There are several issue with this code.

  1. For ARInvoice the primary key is composite, consisting of DocType and RefNbr fields. So, to join tables you must always use both fields, not just RefNbr.
  2. If you take a look at the generated SQL for this request, you’ll see that it looks like ARInvoice.RefNbr = ARInvoice.RefNbr. It’s because the MasterInvoice.refNbr in C# is equivalent of  ARInvoice.refNbr. To make it different you need to redefine  refNbr in the MasterInvoice as the following  
    public class MasterInvoice : PX.Objects.AR.ARInvoice
    { 
        public new abstract class refNbr : PX.Data.BQL.BqlString.Field<refNbr> { } 
    }
  3. I’m not really sure why you need to join ARInvoice to itself.
H
Rank
Userlevel 1

Thank you for your help, Dimitri. the problem was solved.
I needed to join ARInvoice to itself to refer the splitted invoices ref nbr to the MasterRefNbr.

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