Story about Sorting - Microsoft Dynamics 365 Business Central

                Back in the days it was so easy, right? Design a table, add any field as a secondary key, and use it as you like to sort the data and do any calculation you like. Nowadays you have no access to base tables therefore the only option is to make extension of table definition. I won’t speak much about this since it is very nicely described in Microsoft documentation: Table Keys - Business Central | Microsoft Learn

                What I want is to review are a few possibilities of data sorting you can use in Business Central and, here is the nice part, regardless of are the fields part of the keys or not!

Let’s start by creating a simple table for this purpose:

And also, the page:

Publish and enter some data for example like this:

Notice that data is always sorted by primary key by default. So, let’s play.

I will use actions to sort the data differently, so let’s add some actions to sort the date using the SetCurrentKey function:

SetCurrentKey Function (Record) - Dynamics NAV | Microsoft Learn

Using any of these actions will result in sorting the data by the fields stated in the SetCurrentKeyFunction:

Sort By Code:

Sort By Date:

Sort By Date and Text:

And the nice thing is that you do not need to use a field from the keys to sort the data like you really want. Great right. Of course, in the link from the top it is explained why you should use keys for sorting due to performance, indexing and so on especially in case when handling large amount of data.

The disadvantage of all of this is that the field is hardcoded and cannot be changed dynamically so, how to sort more dynamically?
In the era where one tenant can have multiple extensions with theirs own set of fields, you cannot always hardcode a field in your code because, you cannot know which extensions will be installed independently by the user.

Here is a little solution. Record and RecordRef variables have SetView function. This function uses a text formatted string for table view, like for example on customer you can use like this: “Sorting(Name) Order(Ascending) Where(No.=Const(10000..20000))” same as for the property SourceTableView.

Record.SetView(Text) Method - Business Central | Microsoft Learn

RecordRef.SetView(Text) Method - Business Central | Microsoft Learn

Now let’s play with string a little and create an input environment for this string in order for the user to freely choose which fields he wants to use for sorting, by which order and what to filter.

I added an action with some pages and so on to build a string from the user perspective. You can view the complete code on my GitHub (link at the end of article).

Using this new Action “Set Custom View” Result is:

Select sorting Fields:

Select Order:

Select Filter Fields and Conditions:

And we have a view string:

And it works like a charm when we apply Rec.SetView(SetViewText)

Filter is applied on Integer field and data is sorted by Date and Code.

Data Sorting by GetView function in Business Central

As I see this proof of concept, there are many other possibilities to use this in many different scenarios and the best part of it is that you do not need to hardcode anything. You can also use GetView function to store the current view of the table while you make some operation with sorting and filters of your own and then return the view to a default.

Source Code of the proof of Concept can be found on GitHub:

SarkeSrb/Sorting (github.com)