Skip to main content
Solved

how do I prevent PXSelect from returning an old row (ARSalesPrice)?


Forum|alt.badge.img+2

I’m currently getting an ARSalesPrice (PX.Objects.AR) object using this:

                            sp = PXSelect<ARSalesPrice, Where<ARSalesPrice.inventoryID, Equal<Required<ARSalesPrice.inventoryID>>,
                                   And<ARSalesPrice.siteID, Equal<Required<ARSalesPrice.siteID>>>>>.Select(newGraph, InventoryID, “WHSE01”);

 

However, this returns the first (old) row in the Sales Prices (AR202000) screen.  How can I get the newer Effective/Expiration Date row?

Best answer by jinin

Hi @bpgraves ,
 

You may use the following sample BQL query to retrieve the  Salesprice. Please adjust the conditions according to your requirement.

 ARSalesPrice objSalesprice = PXSelectJoin<ARSalesPrice,
                                      InnerJoin<InventoryItem, On<ARSalesPrice.inventoryID, Equal<InventoryItem.inventoryID>, And<InventoryItem.itemStatus, Equal<INItemStatus.active>>>>,
                                      Where2<Where<ARSalesPrice.effectiveDate, LessEqual<Current<AccessInfo.businessDate>>, And<ARSalesPrice.expirationDate, GreaterEqual<Current<AccessInfo.businessDate>>>>,
                                      Or2<Where<ARSalesPrice.effectiveDate, LessEqual<Current<AccessInfo.businessDate>>, And<ARSalesPrice.expirationDate, IsNull>>,
                                      Or<Where<ARSalesPrice.expirationDate, GreaterEqual<Current<AccessInfo.businessDate>>, And<ARSalesPrice.effectiveDate, IsNull,
                                      Or<ARSalesPrice.effectiveDate, IsNull, And<ARSalesPrice.expirationDate, IsNull>>>>>>>,
                                      OrderBy<Desc<ARSalesPrice.lastModifiedDateTime>>>.Select(this);

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

Forum|alt.badge.img

You can try adding orderby for the expiration date field in descending

https://help.acumatica.com/(W(16))/Help?ScreenId=ShowWiki&pageid=30788146-9fb9-613a-5ce8-ec43716ffdbd

 

Hope this helps!


darylbowman
Captain II
Forum|alt.badge.img+13

It seems like maybe you’d want to filter for only un-expired values by including an appropriate date.


jinin
Pro I
Forum|alt.badge.img+11
  • Pro I
  • August 30, 2024

Hi @bpgraves ,
 

You may use the following sample BQL query to retrieve the  Salesprice. Please adjust the conditions according to your requirement.

 ARSalesPrice objSalesprice = PXSelectJoin<ARSalesPrice,
                                      InnerJoin<InventoryItem, On<ARSalesPrice.inventoryID, Equal<InventoryItem.inventoryID>, And<InventoryItem.itemStatus, Equal<INItemStatus.active>>>>,
                                      Where2<Where<ARSalesPrice.effectiveDate, LessEqual<Current<AccessInfo.businessDate>>, And<ARSalesPrice.expirationDate, GreaterEqual<Current<AccessInfo.businessDate>>>>,
                                      Or2<Where<ARSalesPrice.effectiveDate, LessEqual<Current<AccessInfo.businessDate>>, And<ARSalesPrice.expirationDate, IsNull>>,
                                      Or<Where<ARSalesPrice.expirationDate, GreaterEqual<Current<AccessInfo.businessDate>>, And<ARSalesPrice.effectiveDate, IsNull,
                                      Or<ARSalesPrice.effectiveDate, IsNull, And<ARSalesPrice.expirationDate, IsNull>>>>>>>,
                                      OrderBy<Desc<ARSalesPrice.lastModifiedDateTime>>>.Select(this);


Forum|alt.badge.img+2
  • Semi-Pro I
  • August 30, 2024
jinin wrote:

Hi @bpgraves ,
 

You may use the following sample BQL query to retrieve the  Salesprice. Please adjust the conditions according to your requirement.

 ARSalesPrice objSalesprice = PXSelectJoin<ARSalesPrice,
                                      InnerJoin<InventoryItem, On<ARSalesPrice.inventoryID, Equal<InventoryItem.inventoryID>, And<InventoryItem.itemStatus, Equal<INItemStatus.active>>>>,
                                      Where2<Where<ARSalesPrice.effectiveDate, LessEqual<Current<AccessInfo.businessDate>>, And<ARSalesPrice.expirationDate, GreaterEqual<Current<AccessInfo.businessDate>>>>,
                                      Or2<Where<ARSalesPrice.effectiveDate, LessEqual<Current<AccessInfo.businessDate>>, And<ARSalesPrice.expirationDate, IsNull>>,
                                      Or<Where<ARSalesPrice.expirationDate, GreaterEqual<Current<AccessInfo.businessDate>>, And<ARSalesPrice.effectiveDate, IsNull,
                                      Or<ARSalesPrice.effectiveDate, IsNull, And<ARSalesPrice.expirationDate, IsNull>>>>>>>,
                                      OrderBy<Desc<ARSalesPrice.lastModifiedDateTime>>>.Select(this);

Thanks but this doesn’t handle my initial query where I am selecting by a specific InventoryID and Price Code. Is that what I need to add to the InnerJoin above?


Forum|alt.badge.img+2
  • Semi-Pro I
  • August 30, 2024
darylbowman wrote:

It seems like maybe you’d want to filter for only un-expired values by including an appropriate date.

I don’t have a specific date.  I need the most recent.


Forum|alt.badge.img+2
  • Semi-Pro I
  • August 30, 2024
varthinibhaskaran18 wrote:

You can try adding orderby for the expiration date field in descending

https://help.acumatica.com/(W(16))/Help?ScreenId=ShowWiki&pageid=30788146-9fb9-613a-5ce8-ec43716ffdbd

 

Hope this helps!

This solution doesn’t consider that the most recent Sales Price might have a NULL Expiration Date.  When I order Descending like this, will the Select return the row with an expiration date of NULL (most recent) or will it return the row having an expired date (old row):

                            sp = PXSelect<ARSalesPrice, Where<ARSalesPrice.inventoryID, Equal<Required<ARSalesPrice.inventoryID>>,
                                   And<ARSalesPrice.siteID, Equal<Required<ARSalesPrice.siteID>>>>,OrderBy<Desc<ARSalesPrice.expirationDate>>>.Select(newGraph, InventoryID, “WHSE01”);

 


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