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 for data sorting and do any calculation you want. Nowadays, you have no access to base tables; therefore, the only option is to make an extension of the table definition. I won’t speak much about this since it is very nicely described in Microsoft documentation: Table Keys – Business Central | Microsoft Learn
I want to review – a few possibilities of data sorting you can use in Business Central, and here is the nice part, whether the fields are 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:
Data Sorting by SetCurrentKey function in Business Central
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 as you want. Great right? Of course, the link from the top explains why you should use keys for sorting due to performance, indexing, and so on, especially when handling a large amount of data.
The disadvantage 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 their own fields, you cannot always hardcode a field in your code because you need to know which extensions will be installed independently by the user.
Data Sorting by SetView function in Business Central
Here is a little solution. Record and RecordRef variables have the SetView function. This function uses a text formatted string for the table view; for example, on a customer, you can use 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 so the user can freely choose which fields he wants to use for sorting, by which order, and what to filter.
I added an action with some pages to build a string from the user’s perspective. You can view the complete code on my GitHub (link at the end of the article).
Using this new Action “Set Custom View” the 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)
The filter is applied on the 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 is that you do not need to hardcode anything. You can also use the GetView function to store the current view of the table while you make some operations with sorting and filters of your own and then return the view to default.
Source Code of the proof of Concept can be found on GitHub: