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?
Answer
Fields not showing data after Left Join
Best answer by Dmitrii Naumov
There are several issue with this code.
- 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.
- 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> { }
} - I’m not really sure why you need to join ARInvoice to itself.
+7There are several issue with this code.
- 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.
- 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> { }
} - I’m not really sure why you need to join ARInvoice to itself.
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.