Skip to main content
Answer

PXSelector with left outer

  • October 5, 2023
  • 1 reply
  • 45 views
B
Jr Varsity II
Forum|alt.badge.img

Hi Team,

I need to filter out vendors who are not in the my custom table (HMRCVendorRegisterDetail). I wrote following but it is not showing me the results. Any alternative for this?

Only one vendor code is exists in the HMRCVendorRegisterDetail table 

 

[PXSelector(typeof(Search2<BAccount.bAccountID, LeftJoin<HMRCVendorRegisterDetail, On<BAccount.bAccountID, 
            Equal<HMRCVendorRegisterDetail.bAccountID>>>, Where<BAccount.type, Equal<BAccountType.vendorType>,And <HMRCVendorRegisterDetail.bAccountID, Equal<Null>>>
            >),
                    typeof(BAccount.acctCD),
                    typeof(BAccount.acctName),
        SubstituteKey = typeof(BAccount.acctCD),
        DescriptionField = typeof(BAccount.acctName))]

 

Best answer by Django

You’ll want to use IsNull which will generate the correct SQL:

[PXSelector(typeof(Search2<BAccount.bAccountID, LeftJoin<HMRCVendorRegisterDetail, On<BAccount.bAccountID, 
            Equal<HMRCVendorRegisterDetail.bAccountID>>>, Where<BAccount.type, Equal<BAccountType.vendorType>,And <HMRCVendorRegisterDetail.bAccountID, IsNull>>
            >),
                    typeof(BAccount.acctCD),
                    typeof(BAccount.acctName),
        SubstituteKey = typeof(BAccount.acctCD),
        DescriptionField = typeof(BAccount.acctName))]

 

Edit: This answer here does a great job of explaining the why:

 

    1 reply

    D
    Captain II
    Forum|alt.badge.img+7
    • Django
    • Captain II
    • Answer
    • October 5, 2023

    You’ll want to use IsNull which will generate the correct SQL:

    [PXSelector(typeof(Search2<BAccount.bAccountID, LeftJoin<HMRCVendorRegisterDetail, On<BAccount.bAccountID, 
                Equal<HMRCVendorRegisterDetail.bAccountID>>>, Where<BAccount.type, Equal<BAccountType.vendorType>,And <HMRCVendorRegisterDetail.bAccountID, IsNull>>
                >),
                        typeof(BAccount.acctCD),
                        typeof(BAccount.acctName),
            SubstituteKey = typeof(BAccount.acctCD),
            DescriptionField = typeof(BAccount.acctName))]

     

    Edit: This answer here does a great job of explaining the why:

     

    Chris Hackett
    Terms & ConditionsCookie settingsAccessibility statement
    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