Solved

Need help overriding the Orders View on the Process Shipments screen

  • 11 February 2024
  • 5 replies
  • 42 views

Userlevel 6
Badge +3

I added an unbound custom field to the Orders grid on the Process Shipments screen.

I want to set a value in the custom field by overriding the Orders view.  I used the post from Sergey on how to do this (https://asiablog.acumatica.com/2016/06/using-pxview-in-dataview-delegate.html).

In debug I am seeing that the result set has records.

 

 When the foreach fires, I get an exception. This is the block of code I am taken to when the error occurs.  This may not really be an error, but I thought I’d show it in case it does matter.

 

This error on the screen:

In the graph, this is the actual line for the Orders view:

public PXFilteredProcessing<SOShipment, SOShipmentFilter> Orders;

I tried putting SOShipmentFilter in the foreach but that causes a cast error too.  Sergey’s example shows the foreach containing all the DACS in the original View so I tried that too. 

foreach (PXResult<SOShipment, SOShipmentFilter> row in result)

 

Since the result set only shows SOShipment records, it looks like my code is correct.

Any ideas?

 

icon

Best answer by darylbowman 12 February 2024, 16:20

View original

5 replies

Badge +10

Try 

foreach (SOShipment row in result)

Userlevel 7
Badge +5

Hi @Joe Schmucker  

It’s hard to say what’s wrong here, maybe it’ll be better if you provide the stack trace for the exeption.

 

However, I have a doubt the approach you’ve chosen is the best one. Since you only need to populate one field, wouldn’t it be better to do it in FieldSelecting. Or even just using PXDBScalar attribute, since the field value seem to be retrieved with a subselect. 

Userlevel 6
Badge +3

 A little extra info here.  They are trying to display the values from a multi-select DDL created as a User Defined Field on the USER DEFINED FIELDS tab of the Sales Order screen.  This means a lot of tables need to be joined to get the values.  Since there is a one-many-many and there needs to be a group by on the SOOrders table (one shipment could have multiple orders), this is just a PIG of a requirement.  As in, 5 or more minutes to return data to the grid.

For example:

@Dmitrii Naumov, I tried this in the FieldSelecting and because the join to get the data is a one to many to many, this code KILLS the screen.  As in 4 minutes to return data to the screen on a high powered laptop).  They have over 6K records returned by the view.  They import orders from Shopify and they do a LOT of orders.  Behind the scenes, it looks like the native view is actually filtering from over 65,000 records to return 6K records to the grid. 

Although bad practice, (just for testing) I was able to pull the info using the RowSelected handler.  It doesn’t have a huge impact on performance, but the column in the grid cannot be filtered or sorted on because the value assigned to my unbound Usr field on the grid happens AFTER the view is created.

Repeating my above statement, they are trying to pull a UDF from a Sales Order that has a multi select DDL called Hold ID.  To join a shipment to the SO and pull that field, there can be multiple orders per shipment, and each UDF on each order can have multiple values.  This is the select statement to return just one value from the multi select UDF grouped by SO Order number.  I have to use a Group By to prevent the view from displaying the shipment multiple times in the Process Shipments grid.  FYI, I used a SQL View to pull the value to show in the Usr field on my DAC extension just because it is WAY easier to do it in SQL than in a BQL.  I don’t think that affects performance here, but I could be wrong.

	Select 
SOOrderShipment.CompanyID,
SOOrderShipment.ShipmentNbr,
MAX(CSAttributeDetail.Description) AS AttributeHOLDID
From
dbo.SOOrderShipment
INNER JOIN
dbo.SOOrder
ON SOOrder.OrderNbr = SOOrderShipment.OrderNbr
AND SOOrder.OrderType = SOOrderShipment.OrderType
AND SOOrder.CompanyID = SOOrderShipment.CompanyID
INNER JOIN
dbo.SOOrderKvExt
ON SOOrderKvExt.RecordID = SOOrder.NoteID
AND SOOrderKvExt.CompanyID = SOOrderShipment.CompanyID
INNER JOIN
dbo.CSAttributeDetail
ON 'Attribute' + CSAttributeDetail.AttributeID = SOOrderKvExt.FieldName
AND CSAttributeDetail.ValueID = SOOrderKvExt.ValueString
AND CSAttributeDetail.CompanyID = SOOrderShipment.CompanyID
WHERE
SOOrderKvExt.FieldName = 'AttributeHOLDID'

Group By
SOOrderShipment.CompanyID,
SOOrderShipment.ShipmentNbr

Putting this load on the select for every line returned is just not possible in my opinion. screen..  

I am going back to them and tell them that what they are asking for is not doable due to the sheer volume of records they are processing.  If they had 10 shipments they processed per day, no big deal.  But 6K is not going to work.

I am going to try to create a custom ddl field (NOT multi-select) on the SOOrder table and see if that would work without killing the screen.  I suspect it still will as each selected row will require a group by since there is a one to many relationship between the SOShipments and SOOrder tables.

@darylbowman, Your suggestion works! However, it still KILLS the screen.  I’m marking your suggestion as the solution to this because you solved my issue!  Thank you!

Time to complain a bit...I spent 8 hours on this yesterday trying to find a way to get this to work.  I probably tried 10 different approaches with God knows how many compile-test iterations for each approach.  All to find that due to their transaction volume, it is a no go.  Compiling and retrying takes so long for each iteration, you can only compile and test about 13 times in one hour due to the time it takes Acumatica to restart after each compile.  I’ve tried every web.config option there is to speed it up, but it is a HUGE time suck.  I wish there was a way to do a hot load from VS compiler changes.  

Based on my comments above, I’d love to know if you agree that what they are asking for is just not reasonably possible (regardless of which approach I take) so that when I go back to them, I can be confident in my news to them.  Maybe one of you have the skills to do this in an efficient way, but I don’t see a way.

 

 

Userlevel 7
Badge +5

@Joe Schmucker  well, my usual recommendation is that if some data is too heavy to calculate dynamically, you need to make it persistent. 

E.g. if you populate the field on the shipment from SO during the shipment creation and save it to the DB, you’ll not need to do it dynamically every time you read shipments. 

Of course, it adds some complexity in case the field value on SO can be modified after the shipment creation, so it needs to be thought through carefully. 

 

 

Userlevel 6
Badge +3

@Joe Schmucker  well, my usual recommendation is that if some data is too heavy to calculate during runtime, you need to make it persistent. 

E.g. if you populate the field on the shipment from SO during the shipment creation and save it to the DB, you’ll not need to do it dynamically every time you read shipments. 

Of course, it adds some complexity in case the field value on SO can be modified after the shipment creation, so it needs to be thought through carefully. 

 

 

Great idea.  If they come back and say “If we can’t do this, then we are really angry”, I will try that approach!  I actually really like this idea!  As to the last sentence, I actually wanted to point that out to them.  If they put an “on hold” reason in the UDF of the original order such as “Balance Due to large” etc., then ship the order, I guess that value on the SO is somewhat useless after they create the shipment.  I actually questioned the “value” of doing this in the first place.  My suggestion was, if you have a UDF with a “Hold” description, put the order on hold and don’t create the shipment until the hold is resolved.

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