Sorting a Generic Inquiry

Created by Damien Zwillinger, Modified on Fri, 26 Jul at 5:43 PM by Harry Lewis

Applies to:

  • Velixo NX
  • Velixo Classic
  • Acumatica, MYOB Acumatica, Cegid XRP Flex


TABLE OF CONTENTS

Overview

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

 

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

 

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

 

sort_gi1.png

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 are available in Microsoft 365 and Excel 2021 (and higher).  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.

 

 

Example 1

 

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:

 

sort_gi3.png

 

 

Example 2


The Excel 365 SORT() function also provides the ability sort by multiple columns (specifying a primary, secondary, tertiary, etc. sort column).

 

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)

 


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article