Has anyone managed to sort a customer list by balance. I know the balance is a flowfield so it’s not allowed to add this to a key.
My idea is to have a normal field for sortbalance (as decimal) and create a temporary table (based on the customer, sorted by sortbalance) and has this field updated from the balance (lcy) on inserting the temporary record.
This looks like a processor intensive process - can anyone suggest a better way to do this ?
By the way its on Nav 5.0 & native DB as it could be solved in SQL with SIFT index
Its a pretty common request, and depending on th enumber of customers, I just normally add a new field “Balance Amount” as an integer, and a batch to update it. Then add its as a key and sort decending. (PS don’t use decimal, the perfromance difference is significant).
You could also add that batch has an automated batch job at night. Most customers that I know after a few days go by that way. In huge tables take can long.
My answer was related to David’s answer. When you run that report it can take a few minutes and users can start to complain. Moving that report to a night batch job gives users instant performance.
Firstly there is no need for decimal, I am sure that you don’t need to sort customers to that level. Integers are stored as 4 byte alligned double words, decimals in Native are 12 bytes, and SQL 17 bytes, so this will be much slower.
