Need help overriding the Orders View on the Process Shipments screen

 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.

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.