Custom virtual attribute to obtain a value from another table via PXDBScalar is not working correctly

Question

For the AP Bill screen AP301000 I want to show the Customer for each line item in the 'Document Details' grid. To do this I am creating a customisation project with a custom virtual attribute on the APTran table. I have tried many permutations of the attribute, the below is the latest.

[PXString]
[PXUIField(DisplayName="Customer", Enabled=false)]
[PXDBScalar(typeof(Search2<BAccountR.legalName,
InnerJoin<PMProject, On<PMProject.contractID, Equal<APTran.projectID>>,
InnerJoin<AR.Customer, On<PMProject.customerID, Equal<AR.Customer.bAccountID>>,
InnerJoin<BAccountR, On<AR.Customer.bAccountID, Equal<BAccountR.bAccountID>>>>>>))]

It 'should' return the customer legal name from the BAccount table through the relevant joins. However it returns a vendor name instead (not in the customer table).

The generated SQL code is below, the system has made a mess of it. For some reason it has an inner join to the vendor table, which is plainly not in my PXDBScalar attribute decoration.

How can I adjust the attribute decoration to obtain the customer name?

(SELECT TOP (1) [BAccount_BAccount].[legalname]
                FROM   ( [vendor] [BAccount_Vendor]
                         INNER JOIN [baccount] [BAccount_BAccount]
                                 ON ( [BAccount_BAccount].[companyid] = 2 )
                                    AND
                         [BAccount_BAccount].[deleteddatabaserecord] = 0
                                    AND [BAccount_Vendor].[baccountid] =
                                        [BAccount_BAccount].[baccountid]
                         LEFT JOIN [fsxvendor] [BAccount_FSxVendor]
                                ON ( [BAccount_FSxVendor].[companyid] = 2 )
                                   AND
                         [BAccount_FSxVendor].[deleteddatabaserecord] = 0
                                   AND [BAccount_Vendor].[baccountid] =
                                       [BAccount_FSxVendor].[baccountid] )
                       INNER JOIN [contract] [PMProject]
                               ON ( [PMProject].[companyid] IN ( 1, 2 )
                                    AND 8 = Substring([PMProject].[companymask],
                                            1, 1) & 8 )
                                  AND [PMProject].[deleteddatabaserecord] = 0
                                  AND ( [PMProject].[companyid] IN ( 1, 2 )
                                        AND 8 = Substring(
                                                [PMProject].[companymask], 1, 1)
                                                & 8
                                      )
                                  AND [PMProject].[deleteddatabaserecord] = 0
                                  AND [PMProject].[contractid] =
                                      [APTran_APTran].[projectid]
                       INNER JOIN ( [customer] [Customer_Customer]
                                    INNER JOIN [baccount] [Customer_BAccount]
                                            ON ( [Customer_BAccount].[companyid]
                                                 = 2 )
                                               AND
                                    [Customer_BAccount].[deleteddatabaserecord]
                                    = 0
                                               AND
                                    [Customer_Customer].[baccountid] =
                                    [Customer_BAccount].[baccountid]
                                    LEFT JOIN [fsxcustomer]
                                              [Customer_FSxCustomer]
                                           ON (
[Customer_FSxCustomer].[companyid] = 2 )
                                              AND
[Customer_FSxCustomer].[deleteddatabaserecord] = 0
          AND [Customer_Customer].[baccountid] =
              [Customer_FSxCustomer].[baccountid] )
ON [PMProject].[customerid] = [Customer_BAccount].[baccountid]
AND ( [Customer_Customer].[companyid] = 2 )
AND [Customer_Customer].[deleteddatabaserecord] = 0
INNER JOIN [baccount] [BAccountR]
ON ( [BAccountR].[companyid] = 2 )
AND [BAccountR].[deleteddatabaserecord] = 0
AND ( [BAccountR].[companyid] = 2 )
AND [BAccountR].[deleteddatabaserecord] = 0
AND [Customer_BAccount].[baccountid] =
  [BAccountR].[baccountid]
WHERE  ( [BAccount_Vendor].[companyid] = 2 )
AND [BAccount_Vendor].[deleteddatabaserecord] = 0
ORDER  BY [BAccount_BAccount].[legalname]) AS [UsrCustomerName],

Sean Prouty Sean Prouty · Accepted Answer · 2021-05-14T23:05:05

Try creating a new class for the BAccountR table and using the PXBreakInheritance attribute, and then use this new table to do your lookup.

namespace Custom.Extension
{
    [PXBreakInheritance]
    public class CustomBAccountR: BAccountR { }

    public sealed class APTranExtension : PXCacheExtension<APTran>
    {
        #region ProjectCustomerID

        public abstract class projectCustomerID : BqlInt.Field<projectCustomerID> { }

        [PXInt]
        [PXUIField(DisplayName="Customer Legal Name", Enabled=false)]
        [PXSelector(typeof(SearchFor<CustomBAccountR.bAccountID>),
            SubstituteKey = typeof(CustomBAccountR.acctCD),
            DescriptionField = typeof(CustomBAccountR.legalName))]
        [PXDBScalar(typeof(Search2<CustomBAccountR.bAccountID,
            InnerJoin<PMProject,
                On<PMProject.contractID.IsEqual<APTran.projectID>>,
            InnerJoin<CustomBAccountR,
                On<CustomBAccountR.bAccountID.IsEqual<PMProject.customerID>>>>,
            Where<CustomBAccountR.bAccountID.IsNotNull>>))]
        public int? ProjectCustomerID { get; set; }

        #endregion
    }
}

You can now use the description field in your ASPX to display out the value.

Custom virtual attribute to obtain a value from another table via PXDBScalar is not working correctly

1 Answers