Acumatica: Retrieving first record in a view for a display field - extraneous Order Bys

Question

We have created a screen and graph that stores custom information about a given serial number for an inventory item (INItemLotSerial).

I'm looking to be able to display the current location of the serial number, based on the location of the most recent transaction. (Ideally I'd also like to be able display if the serial number is currently in inventory, but that's probably a different question.)

Here is my view on the graph:

public PXSelect<INTranSplit, Where<INTranSplit.lotSerialNbr, Equal<Optional<INItemLotSerial.lotSerialNbr>>, 
    And<INTranSplit.inventoryID, Equal<Optional<INItemLotSerial.inventoryID>>>>, OrderBy<Desc<INTranSplit.createdDateTime>>> InventoryLocation;

And my field on the page:

<px:PXSegmentMask Enabled="False" AllowEdit="False" runat="server" ID="CstPXSegmentMask5" DataField="InventoryLocation.LocationID" ></px:PXSegmentMask>

I'm expecting the field to grab the first record and ignore the rest.

However, when I look at the generated SQL in a SQL Trace, Acumatica appears to be adding its own Order By fields:

exec sp_executesql N'SELECT [INTranSplit].[DocType], [INTranSplit].[TranType], [INTranSplit].[RefNbr], [INTranSplit].[LineNbr], [INTranSplit].[POLineType], [INTranSplit].[TransferType], [INTranSplit].[ToSiteID], [INTranSplit].[ToLocationID], [INTranSplit].[SplitLineNbr], [INTranSplit].[TranDate], [INTranSplit].[InvtMult], [INTranSplit].[InventoryID], [INTranSplit].[SubItemID], [INTranSplit].[CostSubItemID], [INTranSplit].[CostSiteID], [INTranSplit].[SiteID], [INTranSplit].[LocationID], [INTranSplit].[LotSerialNbr], [INTranSplit].[ExpireDate], [INTranSplit].[Released], [INTranSplit].[UOM], [INTranSplit].[Qty], [INTranSplit].[BaseQty], [INTranSplit].[MaxTransferBaseQty], [INTranSplit].[OrigPlanType], [INTranSplit].[IsFixedInTransit], [INTranSplit].[PlanID], [INTranSplit].[TotalQty], [INTranSplit].[TotalCost], [INTranSplit].[AdditionalCost], ( CASE WHEN  ( [INTranSplit].[TotalQty] = .0) THEN  .0 ELSE  ( [INTranSplit].[TotalCost] /  [INTranSplit].[TotalQty]) END), [INTranSplit].[CreatedByID], [INTranSplit].[CreatedByScreenID], [INTranSplit].[CreatedDateTime], [INTranSplit].[LastModifiedByID], [INTranSplit].[LastModifiedByScreenID], [INTranSplit].[LastModifiedDateTime], [INTranSplit].[tstamp], [INTranSplit].[UsrQtyForQC], [INTranSplit].[UsrQtyCoded], [INTranSplit].[UsrQtyCompleted] FROM INTranSplit INTranSplit WHERE (INTranSplit.CompanyID = 2) AND  [INTranSplit].[LotSerialNbr] = @P0 AND [INTranSplit].[InventoryID] = @P1 
ORDER BY [INTranSplit].[DocType], [INTranSplit].[RefNbr], [INTranSplit].[LineNbr], [INTranSplit].[SplitLineNbr], [INTranSplit].[CreatedDateTime] DESC OPTION(OPTIMIZE FOR UNKNOWN) /* IN.21.00.00 */',N'@P0 nvarchar(100),@P1 int',@P0=N'EOSC52270005',@P1=16067

which are causing a different record to be returned first, rather than the most recent one.

How do I convince Acumatica to run the BQL I'm asking for and drop the extra order by fields? Or is there an entirely different approach to displaying the most recent transaction location that would be preferable?

I could be mistaken but I recall when working with Selectors, default behavior is to order by keys first. In your code snippet we can see keys of InTranSplit as first order by fields. Have you tried putting the keys in your order by clause, after INTranSplit.createdDateTime? — Hugues Beauséjour

Kyle S Kyle S · Accepted Answer · 2017-06-08T20:09:45

What I would recommend doing in this particular situation if you need specialized filtering is using the IEnumerable sort

public IEnumerable inventoryLocation()
{
        PXView select = new PXView(this, true, InventoryLocation.View.BqlSelect);
        Int32 totalrow = 0;
        Int32 startrow = PXView.StartRow;
        List<object> result = select.Select(PXView.Currents, PXView.Parameters, PXView.Searches,
            PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startrow, PXView.MaximumRows, ref totalrow);
        INTranSplit latest = null;
        if (result.Count > 0)
        { 
            //We need to perform a custom order in order to get to the latest record.
            latest = result.First() as INTranSplit;
            foreach (INTranSplit row in result)
            {
                if (latest.CreatedDateTime.Value < row.CreatedDateTime.Value)
                {
                    latest = row;
                } 

            }
        }
        return new List<object> { latest };
}

Where you would grab all the records, get the first record, and then look to find the latest record.

Cheers!

Acumatica: Retrieving first record in a view for a display field - extraneous Order Bys

1 Answers