Skip to main content
Answer

Retrieve ARPriceWorksheetDetail in Sales Order

  • September 3, 2024
  • 2 replies
  • 30 views

Forum|alt.badge.img

I added custom column in Sales Price Worksheet.

I need to fetch that value from sales order.

I am able to join and check date I wants to filter by inventory id as well.

 

protected virtual void _(Events.FieldUpdated<SOLine, SOLine.inventoryID> e)
{
if (e.Row == null || e.Row.InventoryID == null) return;

ARPriceWorksheet objSalesprice = PXSelectJoin<ARPriceWorksheet,
InnerJoin<ARPriceWorksheetDetail, On<ARPriceWorksheet.refNbr, Equal<ARPriceWorksheetDetail.refNbr>>>,
Where2<Where<ARPriceWorksheet.effectiveDate, LessEqual<Required<ARPriceWorksheet.effectiveDate>>,
And<ARPriceWorksheet.expirationDate, GreaterEqual<Required<ARPriceWorksheet.expirationDate>>>>,
Or2<Where<ARPriceWorksheet.effectiveDate, LessEqual<Required<ARPriceWorksheet.effectiveDate>>, And<ARPriceWorksheet.expirationDate, IsNull>>,
Or<Where<ARPriceWorksheet.expirationDate, GreaterEqual<Required<ARPriceWorksheet.expirationDate>>, And<ARPriceWorksheet.effectiveDate, IsNull,
Or<ARPriceWorksheet.effectiveDate, IsNull, And<ARPriceWorksheet.expirationDate, IsNull>>>>>>>,
OrderBy<Desc<ARPriceWorksheet.lastModifiedDateTime>>>.Select(this.Base, e.Row.InventoryID, orderDate, orderDate, orderDate, orderDate);


}

I wants to put where condition on InventoryID as well.

Best answer by darylbowman

Something like this:

ARPriceWorksheet objSalesprice = SelectFrom<ARPriceWorksheet>.
InnerJoin<ARPriceWorksheetDetail>.
On<ARPriceWorksheet.refNbr.IsEqual<ARPriceWorksheetDetail.refNbr>>.
Where<ARPriceWorksheetDetail.inventoryID.IsEqual<P.AsInt>.
And<ARPriceWorksheet.effectiveDate.IsLessEqual<P.AsDateTime>.
And<ARPriceWorksheet.expirationDate.IsGreaterEqual<P.AsDateTime>>>.
Or<ARPriceWorksheet.effectiveDate.IsLessEqual<P.AsDateTime>.
And<ARPriceWorksheet.expirationDate.IsNull>>.
Or<ARPriceWorksheet.effectiveDate.IsNull.
And<ARPriceWorksheet.expirationDate.IsGreaterEqual<P.AsDateTime>>>.
Or<ARPriceWorksheet.effectiveDate.IsNull.
And<ARPriceWorksheet.expirationDate.IsNull>>>.
OrderBy<ARPriceWorksheet.lastModifiedDateTime.Desc>.
View.Select(this.Base, e.Row.InventoryID, orderDate, orderDate, orderDate, orderDate);

Note, this uses F-BQL (using PX.Data.BQL.Fluent)

2 replies

darylbowman
Captain II
Forum|alt.badge.img+15
  • Answer
  • September 3, 2024

Something like this:

ARPriceWorksheet objSalesprice = SelectFrom<ARPriceWorksheet>.
InnerJoin<ARPriceWorksheetDetail>.
On<ARPriceWorksheet.refNbr.IsEqual<ARPriceWorksheetDetail.refNbr>>.
Where<ARPriceWorksheetDetail.inventoryID.IsEqual<P.AsInt>.
And<ARPriceWorksheet.effectiveDate.IsLessEqual<P.AsDateTime>.
And<ARPriceWorksheet.expirationDate.IsGreaterEqual<P.AsDateTime>>>.
Or<ARPriceWorksheet.effectiveDate.IsLessEqual<P.AsDateTime>.
And<ARPriceWorksheet.expirationDate.IsNull>>.
Or<ARPriceWorksheet.effectiveDate.IsNull.
And<ARPriceWorksheet.expirationDate.IsGreaterEqual<P.AsDateTime>>>.
Or<ARPriceWorksheet.effectiveDate.IsNull.
And<ARPriceWorksheet.expirationDate.IsNull>>>.
OrderBy<ARPriceWorksheet.lastModifiedDateTime.Desc>.
View.Select(this.Base, e.Row.InventoryID, orderDate, orderDate, orderDate, orderDate);

Note, this uses F-BQL (using PX.Data.BQL.Fluent)


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • September 3, 2024

Let me share minor change.

Actually I added new column in ARPriceWorksheetDetail table. I forgot to mention.

DateTime? orderDate = this.Base.CurrentDocument.Current.OrderDate;

ARPriceWorksheetDetail apPriceWorksheetDetail = SelectFrom<ARPriceWorksheetDetail>.
InnerJoin<ARPriceWorksheet>.
On<ARPriceWorksheet.refNbr.IsEqual<ARPriceWorksheetDetail.refNbr>>.
Where<ARPriceWorksheetDetail.inventoryID.IsEqual<P.AsInt>.
And<ARPriceWorksheet.effectiveDate.IsLessEqual<P.AsDateTime>.
And<ARPriceWorksheet.expirationDate.IsGreaterEqual<P.AsDateTime>>>.
Or<ARPriceWorksheet.effectiveDate.IsLessEqual<P.AsDateTime>.
And<ARPriceWorksheet.expirationDate.IsNull>>.
Or<ARPriceWorksheet.effectiveDate.IsNull.
And<ARPriceWorksheet.expirationDate.IsGreaterEqual<P.AsDateTime>>>.
Or<ARPriceWorksheet.effectiveDate.IsNull.
And<ARPriceWorksheet.expirationDate.IsNull>>>.
OrderBy<ARPriceWorksheet.lastModifiedDateTime.Desc>.
View.Select(this.Base, e.Row.InventoryID, orderDate, orderDate, orderDate, orderDate);