Solved

PXSelector with left outer

  • 5 October 2023
  • 1 reply
  • 36 views

Userlevel 3
Badge

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))]

 

icon

Best answer by Django 5 October 2023, 19:19

View original

1 reply

Userlevel 7
Badge +5

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:

 

Reply


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