Sorting without Keys possible?

duo1983 · 2006-03-27

hi...
i have a situation whereby i will need to sort my records according to many different combinations of fields. Thus setting of keys to sort the report seems to be insufficient to cover the combinations.

so does anyone knows a way to sort reports without using or creating Keys? even better if can perform sub-grouping after sorting. both SQL and Native methods are well appreciated...thx... #-o

kriki · 2006-03-27

The only thing you can do is completely handle it yourself:
-Create a table with primary key = a text field with maximum size. In the table you can add other fields you need.
-When reading the records you want to sort (in an unsorted way), you create a sort-string and put it in the new table. Later you just read the new table and the fields will be sorted.
Between the different fields, you add a separator (best one is the tab).
Sometime you will also have to do some converting. e.g. if you want to sort an integer, you cannot just use 'FORMAT("My Integer")' and put it in the sort-string. You will have to add leading zeroes and if you have negatives, you will also have to handle these.

duo1983 · 2006-03-27

thx dude...tried that method...
but due to the large amount of records, this method is quite inefficient, it takes double time to generate the report.

is there still other methods available?

kriki · 2006-03-27

I am afraid it is not possible.
BTW : for the extra table, you don't need to write the records in the DB, but make the table a temptable, like this it is a lot faster.

Savatage · 2006-03-27

There used to be a JIT Sort code (Just In Time) at www.navisioner.com/html/jitsort.htm

But it appears that site is not available anymore.
Maybe someone still has the code? try searching for "jit sort" on www.mbsonline.org

You would probably have to fiddle with the code to get it to work in the newer Navision versions. But I think the same principals still apply.

easabangan · 2006-03-28

Try using array as virtual table, make a code for sorting. but it will affect the performance of your report. I have tried this before and works but as I said it affect its performance.

davmac1 · 2006-03-29

You could do external reports using SQL Server.
Navision will be supporting SQL Server2005 reporting services in release 5 (announced at Convergence, so it is okay to reveal now).
Reporting Services will not support flowfields, but those are easy to implement in SQL joins.
Another option is to set up SQL Server views as linked objects in Navision tables which will allow you to use standard Navision reporting.

jlandeen · 2010-03-19

One other common thing that I've done is to custom build a high number table object. If you build a table outside of a customers licensed object range but never write any records to it then the licensing engine will not raise an error.

I find this really useful to build a new table with whichever fields are needed to filter, group, sort on, calculate, etc. Populate that table in memory with data and then use the setcurrentkey to apply a sort method as needed (as everything is in memory just use an integer data item in your report and read the values from the temporary recordset). Since this doesn't affect existing tables or key structures it's easy to implement and you don't have to worry about how your key may negatively impact performance.

Howdy, Stranger!

Quick Links

Categories

Sorting without Keys possible?

Comments