Using NAV sorting with SQL Views

stiasta

Hello
Problem:
I have found that if i have a Dynamics NAV Table that is connected to a SQL View, there is a problem with sorting.
While sorting on the first table field everything is fine.
If i use a different sorting on something else than the first field, the form keeps changing the lines when i browse from for example a line with first field as 1 to a line with the first field with a 2.
It looks like the view and the dynamics NAV table is having a little fight about what type of sorting is beeing used.
the SQL view wants the sorting to be the primary index, and Dynamics NAV wants sorting on something else.
Does anyone know what to do here?
(If the problem was unclear tell me and ill try to explain further)

Sog

I guess it's because the sorting is defined in the view, while NAV treats it as a table.
I think you ought to monitor the queries that NAV sends to SQL. And see if the order by clause is treated correctly within SQL.

kriki

PS: @Sog: it is not possible to put an ORDER BY-clause in a view (except when you have a TOP clause).

I think it is because the view is not a real table and browsing the form does some weird things on the SQL server side.

You might try to (manually) create an index on the view to match the key you defined in NAV.

bbrown

You can create a key on the linked table object. Just don't maintain it in SQL.

stiasta

Kriki: I dont think the TOP command is allowed either.
I have tried to manually tried to make an index, but the "smarties" that made the view has put a bunch of unions in the view. Unions are not allowed to be used when creating an index on the view.


bbrown: I will try ur suggestion.

I'll keep you posted.

stiasta

I have tried to create an index without maintaining SQL Index. I have made a screenshot of what happends and some properties that i hope can help with troubleshooting.
On the top image is a print screen from the table when i run it. I have set the sorting on the key that is from the image down and to the left. Shipment date, sales company, reqdeldate (Bokf.dato, salgsselseskap, ReqDelDate). When i go to the top of the page using CTRL+Home and then scroll down using arrow down it starts changing the lines when i hit a tabell id other than 36. In this case when i hit 112 it changes the line.

kriki

stiasta wrote:

Kriki: I dont think the TOP command is allowed either.

In a view, you can use TOP. I know for sure because it was one of the daily questions of www.SQLservercentral.com some time ago.

Try this:Change your SQLIndex so, that you don't have doubles in it. So to your fields, add the primary key fields at the end.

stiasta

Aright, i have been able to work around the sorting issue. I have made the forms use help tables(Temporary tables) that is a normal NAV table and not a view. For each time the form is opened the posts are loaded into the table. For views with alot of data, you can set filters on the view and it should go faster to load.
Thank you for your help.