Skip to main content
Solved

How to group by and OrderBy in PXSelector

E
Varsity I

Hi Good night i have a query that works, but i need to aggregate groupBy and orderBy clauses. i saw some examples of the pdf guide but not working.

I need group by :

  • BAccount.bAccountId
  • BAcount.acctName
  • V_CEVendors.idEstimateEstimate
  • V_CEVendors.vChecked
  •  V_CEVendors.total

This is my code that works. (without group by or OrderBy).

    [PXSelector(typeof(Search2<BAccount.bAccountID,
    LeftJoin<V_CEVendors,
    On<BAccount.bAccountID,
    Equal<V_CEVendors.vendor_Id>>>,
    Where<V_CEVendors.estimateNbr.IsEqual<CEEstimate.estimateNbr.FromCurrent>.
      And<V_CEVendors.idEstimateEstimate.IsEqual<CEEstimateEstimate.id.FromCurrent>.And<BAccount.type.IsEqual<SQLConstant.ONLYVENDORC>.And<BAccount.deletedDatabaseRecord.IsEqual<SQLConstant.DELETEDRECORDBOOLC>>>>.
      Or<V_CEVendors.idEstimateEstimate.IsNull.And<BAccount.type.IsEqual<SQLConstant.ONLYVENDORC>.And<BAccount.deletedDatabaseRecord.IsEqual<SQLConstant.DELETEDRECORDBOOLC>>>>

      >
      >
      ),
      typeof(BAccount.bAccountID),
      typeof(BAccount.acctName),
      typeof(V_CEVendors.idEstimateEstimate),
      typeof(V_CEVendors.vchecked),
      typeof(V_CEVendors.total),
    SubstituteKey = typeof(BAccount.acctName),
      Filterable = true
      )]

This is my code with Order by (but it does nothing). I need to orderBy by V_CEVendors.total descending

 

    [PXSelector(typeof(Search2<BAccount.bAccountID,
    LeftJoin<V_CEVendors,
    On<BAccount.bAccountID,
    Equal<V_CEVendors.vendor_Id>>>,
    Where<V_CEVendors.estimateNbr.IsEqual<CEEstimate.estimateNbr.FromCurrent>.
      And<V_CEVendors.idEstimateEstimate.IsEqual<CEEstimateEstimate.id.FromCurrent>.And<BAccount.type.IsEqual<SQLConstant.ONLYVENDORC>.And<BAccount.deletedDatabaseRecord.IsEqual<SQLConstant.DELETEDRECORDBOOLC>>>>.
      Or<V_CEVendors.idEstimateEstimate.IsNull.And<BAccount.type.IsEqual<SQLConstant.ONLYVENDORC>.And<BAccount.deletedDatabaseRecord.IsEqual<SQLConstant.DELETEDRECORDBOOLC>>>>

      >,OrderBy<Asc<C_CEVendros.total>>
      >
      ),
      typeof(BAccount.bAccountID),
      typeof(BAccount.acctName),
      typeof(V_CEVendors.idEstimateEstimate),
      typeof(V_CEVendors.vchecked),
      typeof(V_CEVendors.total),
    SubstituteKey = typeof(BAccount.acctName),
      Filterable = true
      )]

This is my code with groupBy (with error).

    [PXSelector(typeof(Search2<BAccount.bAccountID,
    LeftJoin<V_CEVendors,
    On<BAccount.bAccountID,
    Equal<V_CEVendors.vendor_Id>>>,
    Where<V_CEVendors.estimateNbr.IsEqual<CEEstimate.estimateNbr.FromCurrent>.
      And<V_CEVendors.idEstimateEstimate.IsEqual<CEEstimateEstimate.id.FromCurrent>.And<BAccount.type.IsEqual<SQLConstant.ONLYVENDORC>.And<BAccount.deletedDatabaseRecord.IsEqual<SQLConstant.DELETEDRECORDBOOLC>>>>.
      Or<V_CEVendors.idEstimateEstimate.IsNull.And<BAccount.type.IsEqual<SQLConstant.ONLYVENDORC>.And<BAccount.deletedDatabaseRecord.IsEqual<SQLConstant.DELETEDRECORDBOOLC>>>>

      >.AggregateTo<GroupBy<BAccount.bAccountID>>
      >
      ),
      typeof(BAccount.bAccountID),
      typeof(BAccount.acctName),
      typeof(V_CEVendors.idEstimateEstimate),
      typeof(V_CEVendors.vchecked),
      typeof(V_CEVendors.total),
    SubstituteKey = typeof(BAccount.acctName),
      Filterable = true
      )]

Can you help me please.

Best answer by Naveen Boga

Hi @eddiedaco  Below code might help you that Selector is with Group by with Order by.

 

[PXSelector(typeof(Search5<BAccount.bAccountID,
                                   LeftJoin<Customer, On<Customer.bAccountID, Equal<BAccount.bAccountID>>,
                                   InnerJoin<Address, On<Address.bAccountID, Equal<BAccount.bAccountID>>,
                                   InnerJoin<Contact, On<Contact.bAccountID, Equal<BAccount.bAccountID>>>>>,   
                                   Where2<Where<BAccount.type, NotEqual<BAccountType.branchType>,
                                   And<BAccountExt.usrKNAcmBranchID, Equal<Current<SOOrder.branchID>>,
                                   And<BAccount.defAddressID, Equal<Address.addressID>,
                                   And<BAccount.defContactID, Equal<Contact.contactID>>>>>, 
                                   Or<Where<BAccount.type, Equal<BAccountType.branchType>,
                                   And<BAccount.defAddressID, Equal<Address.addressID>,
                                   And<BAccount.defContactID, Equal<Contact.contactID>>>>>>, 
                                   Aggregate<GroupBy<BAccount.bAccountID>>, OrderBy<Asc<BAccount.acctCD>> 
                                   >),
                                   typeof(BAccount.acctCD),
                                   typeof(BAccount.acctName),
                                   typeof(Address.addressLine1),
                                   typeof(Address.addressLine2),
                                   typeof(Address.postalCode),
                                   typeof(Contact.phone1),
                                   typeof(Address.city),
                                   typeof(Address.countryID),
                                   typeof(BAccount.taxRegistrationID),
                                   typeof(Customer.curyID),
                                   typeof(Contact.salutation),
                                   typeof(Customer.customerClassID),
                                   typeof(Customer.status), SubstituteKey = typeof(BAccount.acctCD), DescriptionField = typeof(BAccount.acctName))]

 

View original
Did this topic help you find an answer to your question?

4 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Naveen Boga
  • Captain II
  • 3417 replies
  • Answer
  • July 27, 2021

Hi @eddiedaco  Below code might help you that Selector is with Group by with Order by.

 

[PXSelector(typeof(Search5<BAccount.bAccountID,
                                   LeftJoin<Customer, On<Customer.bAccountID, Equal<BAccount.bAccountID>>,
                                   InnerJoin<Address, On<Address.bAccountID, Equal<BAccount.bAccountID>>,
                                   InnerJoin<Contact, On<Contact.bAccountID, Equal<BAccount.bAccountID>>>>>,   
                                   Where2<Where<BAccount.type, NotEqual<BAccountType.branchType>,
                                   And<BAccountExt.usrKNAcmBranchID, Equal<Current<SOOrder.branchID>>,
                                   And<BAccount.defAddressID, Equal<Address.addressID>,
                                   And<BAccount.defContactID, Equal<Contact.contactID>>>>>, 
                                   Or<Where<BAccount.type, Equal<BAccountType.branchType>,
                                   And<BAccount.defAddressID, Equal<Address.addressID>,
                                   And<BAccount.defContactID, Equal<Contact.contactID>>>>>>, 
                                   Aggregate<GroupBy<BAccount.bAccountID>>, OrderBy<Asc<BAccount.acctCD>> 
                                   >),
                                   typeof(BAccount.acctCD),
                                   typeof(BAccount.acctName),
                                   typeof(Address.addressLine1),
                                   typeof(Address.addressLine2),
                                   typeof(Address.postalCode),
                                   typeof(Contact.phone1),
                                   typeof(Address.city),
                                   typeof(Address.countryID),
                                   typeof(BAccount.taxRegistrationID),
                                   typeof(Customer.curyID),
                                   typeof(Contact.salutation),
                                   typeof(Customer.customerClassID),
                                   typeof(Customer.status), SubstituteKey = typeof(BAccount.acctCD), DescriptionField = typeof(BAccount.acctName))]

 

Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
E
1 person likes this
  • E
    eddiedaco
E
Varsity I
  • eddiedaco
  • Author
  • Varsity I
  • 32 replies
  • July 28, 2021

@Naveen B  A lot of thanks!!! it works!!!

 

my mistake was that I was using search2 instead of search5

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Naveen Boga
  • Captain II
  • 3417 replies
  • July 28, 2021

Yes.. correct..

Just FYI...

Joins → Search2<>

Group By  → Search5<>

Order By but NOT Group By→ Search4<>

 

Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
E
1 person likes this
  • E
    eddiedaco
E
Varsity I
  • eddiedaco
  • Author
  • Varsity I
  • 32 replies
  • July 30, 2021

@Naveen B  I really appreciate your help, thank you very much!

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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