"Order By" When Retrieving From Acumatica Web Service API

Question

I was wondering if there was a way to add an "Order By" clause when retrieving data from Acumatica through the Web Service API?

IN202500Content IN202500 = oScreen.IN202500GetSchema();
oScreen.IN202500Clear();

Command[] oCmd = new Command[] {IN202500.StockItemSummary.ServiceCommands.EveryInventoryID,
                                IN202500.StockItemSummary.InventoryID,
                                IN202500.StockItemSummary.Description, 
                                IN202500.StockItemSummary.ItemStatus, 
                                IN202500.GeneralSettingsItemDefaults.ItemClass, 
                                IN202500.GeneralSettingsItemDefaults.LotSerialClass,
                                IN202500.PriceCostInfoPriceManagement.DefaultPrice,
                                };
               
Filter[] oFilter = new Filter[] {new Filter 
                                 {
                                   Field = new Field {ObjectName = IN202500.StockItemSummary.InventoryID.ObjectName,
                                                      FieldName = "LastModifiedDateTime"},
                                                      Condition = FilterCondition.GreaterOrEqual,
                                                      Value = SyncDate
                                 }
                                };

String[][] sReturn = oScreen.IN202500Export(oCmd, oFilter, iMaxRecords, true, false);

I would like to sort the results for example by DefaultPrice, so that I can retrieve the Top 200 most expensive items in my list (using iMaxRecords = 200 in this case)

I haven't seen any parameters that allows me to do the sorting yet.

Allen Craig Allen Craig · Accepted Answer · 2016-01-14T14:06:50

I ran into this when I developed a round robin assignment system and the short answer is using the Acumatica API you cant do a sort on the results you have to do it outside of the API (This info came from a friend closely tied to the Acumatica product).

I came up with two options:

Query your DB directly... There are always reasons not to do this but it is much faster than pulling the result from the API and will allow you to bypass the BQL Acumatica uses and write an SQL statement that does EXACTLY what you want providing a result that is easier to work with than the jagged array Acumatica sends.

You can use some Linq and build a second array[][] that is sorted by price and then trim it to the top 200 (You would need all results from Acumatica first).

// This is rough but should get you there. 
string[][] MaxPriceList = sReturn.OrderBy(innerArray =>
    {
        if () // This is a test to make sure the element is not null
        {
            decimal price;
            if (//test decimal is not null))
                return price;
        }

        return price.MaxValue;
    }).Take(200).ToArray(); //Take 200 is a shot but might work

"Order By" When Retrieving From Acumatica Web Service API

1 Answers