Sort Report Result by Third Table Value

I have a report which displays lines sorted by linenumber, but I want to sort the results a kind different.

I have the following setup:

Dynamics NAV 2009 SP1 / Dynamics NAV 6.0 SP1 Classic Client

Table: Sales Line
Relevant Fields: Document Type, Document No, Line No., Type, No., ShelfNo(foreign key to item.ShelfNo)

Table: Item
Relevant Fields: No, ShelfNo(foreign key to table shelves)

Table: Shelves
Relevant Fields: ShelfNo (Code10), WalkSortNumber (int)

Now I want to the have the result of the Report ordered by the value of Shelves.WalkSortNumber

Here is my whished result explained in SQL-ish


select
[...]
from
[Sales Line] sl
inner join
[Item] it on item.No = sl.No
inner join
[Shelves] shf on it.ShelfNo = shf.ShelfNo
where
[...]
order by
shf.WalkSortNumber ASC

You have actually presented your self with a possible solution. Use the “Linked Table” feature of NAV along with a view created with your SQL statement above. Then use that table in the report.

I am afraid i can not use a solution based on SQL. I have to do it natively with navision internal tools

You just need to create the view in SQL. Creating the table and linking it to the view is all done in NAV, This is a fully supported and documented NAV approach.

One way:
Create a [new] SortTable with Primary key Shelves.WalkSortNumber + LineNo.
Create fields to Sales Lines Primary keyfields (DocType, DocNo, LineNo); this way you can refer to the original record.
Read your Sale Lines and copy relevant fields into this new table - as temporary.

Now you can read this SortTable [in desired order], retreive the original Sales Line - and output.

I am actually going to use this method myself for a customer that also uses ShelfNo’s.

It it also possible to use some standard buffer-table (e.g. #49). Is have used theese table a lot.

If you were using NAV 2013, you could use the new object type Query, which handles joins.