Overview

When defining a Generic Inquiry in Acumatica, we have the option to specify how the data is to be sorted when displayed.

This sort order is ignored by Acumatica when the data is returned through an OData connection (such as is used by Velixo for retrieving Acumatica data).

As a result, the data returned by the Velixo GI() function appears with no discernable sorting:

If we have Microsoft 365, however, we have the ability to automatically sort the results of a GI() function

How-To

Note: The SORT function is part of the new Excel Dynamic Arrays family and at the time of this writing, Dynamic Arrays are only available in Microsoft 365. Other editions (including Excel 2019) do not have the Dynamic Array functions.

By embedding the Velixo GI() function inside of an Excel 365 SORT() function, we have full control over how that data is sorted.

Let's assume that we want our example sorted by Customer Name - the first column

The format for the Excel 365 SORT() function is:

=SORT(ARRAY, [SORT_INDEX], [SORT_ORDER], [BY_COL])

where:

ARRAY	is the data to be sorted
SORT_INDEX	represents he column(s) by which we want to sort the data
SORT_ORDER	is either: 1 (ascending) or -1 (descending) Default: 1
BY_COL	is either: TRUE (sort by columns) or FALSE (sort by rows) Default: FALSE

Since we want to sort by the first column, our SORT() function would look like this:

  =SORT({our GI function},1,1)

Once that is done, our results are now sorted by the defined column and direction:

The Excel 365 SORT() function also provides the ability sort by multiple columns.

We can place the column numbers within curly braces in the order we want to apply sorting.

For example, if we wanted to first sort by Customer Name (the first column) and then Amount (the third column), our function would look like this:

=SORT({our GI function},{1,3},1)

Note: Because the Excel 365 SORT() function sorts EVERYTHING in the array (including a header, if we include it in our GI() function), we want to *not* include the header in our GI() function. The header can be typed in separately - as is done in the examples show in this article.