Solved

How to group by and OrderBy in PXSelector

  • 27 July 2021
  • 4 replies
  • 49 views

Userlevel 1

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 B 27 July 2021, 09:39

View original

4 replies

Userlevel 7
Badge +9

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

 

Userlevel 1

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

 

my mistake was that I was using search2 instead of search5

Userlevel 7
Badge +9

Yes.. correct..

Just FYI...

Joins → Search2<>

Group By  → Search5<>

Order By but NOT Group By→ Search4<>

 

Userlevel 1

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

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 — 2020  Acumatica, Inc. All rights reserved