Solved

How to group by and OrderBy in PXSelector

  • 27 July 2021
  • 4 replies
  • 713 views
E
Rank
Userlevel 2

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.

icon

Best answer by Naveen Boga 27 July 2021, 09:39

View original

4 replies

E
Rank
Userlevel 2

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

Naveen Boga
Rank
Userlevel 7
Badge +17

Yes.. correct..

Just FYI...

Joins → Search2<>

Group By  → Search5<>

Order By but NOT Group By→ Search4<>

 

Naveen B
E
Rank
Userlevel 2

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

 

my mistake was that I was using search2 instead of search5

Naveen Boga
Rank
Userlevel 7
Badge +17

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

Reply


Terms & ConditionsCookie 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