Tip #56 | Fix Error : Sorting cannot be done based on the XX field(s)


Today I’ve spent some time in NAV2016 figuring out this error message:

Sorting

As you know in NAV2016 you can sort on each column even if there is no key and even if it’s a flowfield. So why am I getting this error?

In my case the Test field is a Flowfield of the type COUNT to another table. This should work fine, so why doesn’t it work here.

Reason is that the SQL Data Type is Integer instead of VarChar. A very old trick to make a SQL Database sort a Code field just like the Native database.

The SQL Data Type in the “Other” table does not match this SQL Type hence the sorting fails.

Since I could not find any info online on this message I thought it was worth sharng.

Sorting2

Advertisements
This entry was posted in Dynamics NAV, Tips and Tricks. Bookmark the permalink.

2 Responses to Tip #56 | Fix Error : Sorting cannot be done based on the XX field(s)

  1. Hi Mark,
    Had almost forgotten about the SQL Integer trick.A few years since I used it last. It has always been something I personally have consider as “cheating”, but something you just do instead of trying to convince the customer to use it the way it was designed.
    I see in the https://nav-skills.com/2014/01/15/nav2013r2-sql-integer-issues/ that you have been using it a little more frequently.
    Thanks for reporting on this, have a great weekend. 🙂

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s