Skip to main content
Solved

SelectFrom with order by and where part

  • Oct 13, 2023 1:42 PM UTC
  • 3 replies
  • 221 views
  • Translate All

Forum|alt.badge.img

Hi Team,

Need know how to write bql query with order by and where part. Can someone help me?

My Sample

[PXFilterable]
        public SelectFrom<HMRCVendorRegisterDetail>.OrderBy<Desc<HMRCVendorRegisterDetail.validTo>>.Where<HMRCVendorFilter.bAccountID.FromCurrent.IsNull.Or<HMRCVendorRegisterDetail.bAccountID.IsEqual<HMRCVendorFilter.bAccountID.FromCurrent>>>.View.ReadOnly DetailsView;

Best answer by Django

I searched through the Acumatica code for .OrderBy< and found this example:

public SelectFrom<ContactAccount>
.Where<
ContactAccount.bAccountID.IsEqual<CRLead.bAccountID.FromCurrent>
.And<ContactAccount.contactType.IsEqual<ContactTypesAttribute.person>>
>
.OrderBy<
ContactAccount.isPrimary.Desc,
ContactAccount.displayName.Asc
>
.View Link_SelectEntityForLink;

 

View original

3 replies

Forum|alt.badge.img+5
  • Jr Varsity II
  • Oct 13, 2023 2:39 PM UTC

Your Where conditions should be before the OrderBy section. 

SelectFrom<Table>.Where<WhereConditions>.OrderBy<Desc<Table.Field>>.View.ReadOnly DetailsView;

 

Translate

Forum|alt.badge.img+8
  • Captain II
  • Answer
  • Oct 13, 2023 2:40 PM UTC

I searched through the Acumatica code for .OrderBy< and found this example:

public SelectFrom<ContactAccount>
.Where<
ContactAccount.bAccountID.IsEqual<CRLead.bAccountID.FromCurrent>
.And<ContactAccount.contactType.IsEqual<ContactTypesAttribute.person>>
>
.OrderBy<
ContactAccount.isPrimary.Desc,
ContactAccount.displayName.Asc
>
.View Link_SelectEntityForLink;

 

Translate

Naveen Boga
Captain II
Forum|alt.badge.img+20
  • Captain II
  • Oct 14, 2023 5:32 AM UTC

@bhagyat25  Below is the query for your query and hope this helps!!

 

        public PXSelect<HMRCVendorRegisterDetail,Where<HMRCVendorFilter.bAccountID, IsNull, Or<HMRCVendorRegisterDetail.bAccountID, Equal<Current<HMRCVendorFilter.bAccountID>>>>,
OrderBy<Desc<HMRCVendorRegisterDetail.validTo>> DetailsView;

 

Translate