Skip to main content
Solved

Retrieve ARPriceWorksheetDetail in Sales Order

  • September 3, 2024
  • 2 replies
  • 26 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)

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

2 replies

darylbowman
Captain II
Forum|alt.badge.img+13
  • 1716 replies
  • 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
  • 25 replies
  • 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);

 


Reply


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