Sort Report by third table value

Ello

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

FDickschat

2 possibilities:

1.
Add the field walksortnumber to the sales line populating it when the item is validated. Add a new index.

2.
Use a temporary table in your report.
First read all the necessary data and write everything into a temporary table. Then print based on your temp table.

vaprog

Do you need the Items here? Isn't ShelfNo foreign key to Shelves, connecting Sales Lines directly to Shelve?

Then you might loop over your Sales Lines, get the associated Shelves and either mark them or copy them to a temporary Shelve table. Then create a dataitem for the marked or temporary Shelve table and a nested Sales Line dataitem with the output section.

Ello

thank you, I will give it a try