Solved

How to get old value in one field ?

  • 15 September 2021
  • 14 replies
  • 946 views

Hi, i have some problem to get value from old value data, anyone have an idea ?
 

example, new SO have qty = 65 after save user edited value qty in SO become 50. i want to get value qty SO =  65.

or, how to implement query limit = 1 in acumatica ?

icon

Best answer by Naveen Boga 16 September 2021, 06:38

View original

14 replies

Userlevel 7
Badge +17

Hi @yanuaralfianus37  Question: how to implement query limit = 1 in acumatica

Which means, you wanted to fetch only records using query just like below ?

Select Top (1) * from SOOrder 

 

Question: example, new SO have qty = 65 after save user edited value qty in SO become 50. i want to get value qty SO =  65.

You can see the old values in Sales Orders Audit History screen

 

Hi @Naveen B thanks for reply. 

yes that’s right, i want is to get old value qty in SOOrder, and i do compare with the new one qty in SOOrder (old and new value qty is in one order number).

Userlevel 7
Badge +17

Hi @yanuaralfianus37  We can get the old values in below 2 ways.

  • Row Level  → Use RowUdated Event
  • Field Level → Use FieldUpdated Event

Please find the sample code for your reference. 

 

protected virtual void SOLine_OrderQty_FieldUpdated(PXCache sender, PXFieldUpdatedEventArgs e)
{
SOLine row = e.Row as SOLine;
if (row != null)
{

if (row.OrderQty != Convert.ToDecimal(e.OldValue))
{
// Add Logic
}
else
{
// Add Logic
}
}
}


protected virtual void SOLine_RowUpdated(PXCache cache, PXRowUpdatedEventArgs e, PXRowUpdated InvokeBaseHandler)
{
InvokeBaseHandler?.Invoke(cache, e);
SOLine newRow = e.Row as SOLine;
if (newRow != null)
{
SOLine oldRow = (SOLine)e.OldRow;
if (newRow.OrderQty != oldRow.OrderQty)
{
// Add Logic
}
else
{
//Add Logic
}
}
}

hi @Naveen B thanks for reply. i want to ask, how to that old value (before edit/update) save in new variable and i used it in RowPersisting event handler ? or how to trigger save in event handler RowUpdate / FieldUpdated so i can create error message ?

Userlevel 7
Badge +17

Hi @yanuaralfianus37 I think you can not the get the old values in RowPersisting Event and you can not invoke the SAVE action from event handler for this Acuminator will show us the PX1043 - Changes cannot be saved to the database from event handlers.

 

You can compare the OLD and NEW values in the FiledUpdated/RowUpdated event and throw an error message according to your logic.

Userlevel 6
Badge +5

Depending on exactly what “old value” you are looking for, you can get the previously persisted value by calling the GetOriginal method on the cache.

Hi @yanuaralfianus37 I think you can not the get the old values in RowPersisting Event and you can not invoke the SAVE action from event handler for this Acuminator will show us the PX1043 - Changes cannot be saved to the database from event handlers.

 

You can compare the OLD and NEW values in the FiledUpdated/RowUpdated event and throw an error message according to your logic.

 

Hi @Naveen B thanks for the information :relaxed:

 

Depending on exactly what “old value” you are looking for, you can get the previously persisted value by calling the GetOriginal method on the cache.

Hi @markusray17 thanks for reply. i think this method almost solved my issue. can you give me some example how to implement GetOriginal method ? can that method keep original value in new variable for i compare with current value ?

Userlevel 7
Badge +17

@yanuaralfianus37  According to me, you can not keep original value in variable and compare.

If you do that, the value may change it to NULL when you lost thread. It may work in few scenarios and BTW NOT recommended when you have huge customizations involved.

 

Please let me know if you achieve this by using this logic.

 

 

Userlevel 6
Badge +5

The GetOriginal method just takes an object from the cache and will return the previously persisted record. It is a method on the Cache object so from inside an event handler you would just use e.Cache.GetOriginal(e.Row).

If in the RowPersisting handler you wanted to compare the record being saved to the record that was previously saved you could get the previously persisted record with this method.

As far as storing it in a new variable, I’m not exactly sure what you are asking. If you mean storing it outside of the scope of the event handler, I would not recommend that(and I’m not sure why you would need to do that).

It does sound like you may just be looking to surface an error when a value changes in which case as Naveen has noted there are easier ways to accomplish that. 

 

Quick code sample:

    public void _(Events.RowPersisting<SOLine> e)
{
var line = e.Row;

if (line == null) return;

var persistedLine = (SOLine)e.Cache.GetOriginal(line);

//compare line to persistedLine
}

 

Hi @Naveen B & @markusray17 thanks for that information.

 

if(row != null){
foreach(SOShipLine line in Base.Transactions.Select()){
SOLine soli = PXSelect<SOLine,
Where<SOLine.lineNbr, Equal<Required<SOLine.lineNbr>>,
And<SOLine.orderNbr, Equal<Required<SOLine.orderNbr>>>>>
.Select(this.Base, line.OrigLineNbr, line.OrigOrderNbr);
InventoryItem inv = PXSelect<InventoryItem,
Where<InventoryItem.inventoryID, Equal<Required<InventoryItem.inventoryID>>>>
.Select(this.Base, line.InventoryID);

var soliExt = PXCache<SOLine>.GetExtension<SOLineExt>(soli);

decimal? allqty = 0;
decimal? oldqty = 0;
decimal? newqty = 0;
decimal? leftqty = 0;


allqty = line.ShippedQty + soli.ShippedQty;

foreach (SOShipLine ship in PXSelect<SOShipLine,
Where<SOShipLine.shipmentNbr, Equal<Required<SOShipLine.shipmentNbr>>>>
.Select(this.Base, line.ShipmentNbr)){
if(ship.InventoryID == line.InventoryID){
oldqty = oldqty + ship.ShippedQty;
}
}

newqty = line.ShippedQty;

if((e.Operation & PXDBOperation.Command )== PXDBOperation.Insert){
leftqty = soliExt.Usrshippingquota - (allqty - (oldqty - newqty));
}

if(leftqty < 0){
throw new PXException("Shipment Quantity is over limit in Sales Order, Please Cek data in Inventory ID " + inv.InventoryCD + line.TranDesc);
PXTrace.WriteInformation("Shipment Quantity is over limit in Sales Order, Please Cek data in Inventory ID " + inv.InventoryCD + line.TranDesc);
}

if(soliExt.Usrkuotapengiriman == null){
throw new PXException("Shipment Quantity is Empty in Sales Order, Please Cek data in Inventory ID " + inv.InventoryCD + line.TranDesc);
PXTrace.WriteInformation("Shipment Quantity is Empty in Sales Order, Please Cek data in Inventory ID " + inv.InventoryCD + line.TranDesc);
}

allqty = 0;
oldqty = 0;
newqty = 0;
leftqty = 0;
}
}

 this is script i used it, i have custom field “shipping quota”. Qty on Shipment in screen Sales Order isn’t greater than custom field “shipping quota”. error message show in create shipment.
 

DO : compare value from ShippedQty (Shipment Screen) to Custom field (Sales Order Screen).

Achieve : that script can show error message after trigger save in shipment screen if ShippedQty >= Shipping Quota.

Problem :  after save, i get Shipnumb and Qty on Shipment in screen Sales Order is increases according to value ShippedQty in Shipment Screen. but if im edit value ShippedQty in Shipment Screen more than shipping quota in Sales Order Screen and i will click button save, error message cant show in shipment Screen.

 

anyone have an idea to solved that case ?

Userlevel 6
Badge +5

Is there a particular reason you only want to show the error when someone is saving instead of when the field is updated or verified?

 

And if I am understanding you correctly you basically have a custom field(Shipping Quota) on SOLine that you want to use to enforce a limit on how much of the ordered quantity can be shipped? In that case all you would need to do would be compare the Shipped Quantity on the Shipment to the Shipping Quota of the original SOLine.

 

As far as setting an error/warning you would want to throw a PXSetPropertyException(you can find plenty of examples in the source code) object not just a generic PXException. Error level will cancel the input and show an error, Warning level will allow the input and show a warning. 

Hi @markusray17 thanks for reply.

Q : Is there a particular reason you only want to show the error when someone is saving instead of when the field is updated or verified?

field updated the data.

Q: And if I am understanding you correctly you basically have a custom field(Shipping Quota) on SOLine that you want to use to enforce a limit on how much of the ordered quantity can be shipped? In that case all you would need to do would be compare the Shipped Quantity on the Shipment to the Shipping Quota of the original SOLine.

yes that’s right. i will compare Shipped qty in Shipment with custom field(Shipping Quota) on Sales Order Line.


 As far as setting an error/warning you would want to throw a PXSetPropertyException(you can find plenty of examples in the source code) object not just a generic PXException. Error level will cancel the input and show an error, Warning level will allow the input and show a warning. 

 

okay i will try it, thanks for your advise. 

Userlevel 6
Badge +5

I would recommend doing that in the Field Verifying event of SOShipLine.ShippedQty. I believe if you set an error level exception on a field it will automatically prevent the user from persisting the document. And based on your description above I don’t think you would need the old value but it would be available within the handler(e.NewValue is the new value, (SOShipLine)e.Row.ShippedQty is the old/current value).

In theory you should be able to get the matching SOLine then get the value of the Shipping Quota(via the cache extension) then compare it to e.NewValue. If the new ShippedQty is greater than the Shipping Quota you can throw an error. I haven’t used the verifying event extensively but I believe it will automatically clear the error if it runs the event and no error is thrown. 

Unless there are additional requirements it should be less code than you had above. 

Thanks @Naveen B @markusray17. my problem is solved.

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