Sorting customers by balance in a form

Hi All,

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. Sad

This looks like a processor intensive process - can anyone suggest a better way to do this ? Pray

By the way its on Nav 5.0 & native DB as it could be solved in SQL with SIFT index

Thanks in advance

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.

Hi David,

This is along the lines I was thinking but wanted some guidance if it was solved before. Your tip on the integer key is extremly helpful [H] Thanks !!

Hi Nuno,

If the performance of David’s suggestion proves to be bad then your suggestion is to way to go.

Thanks for the replies [:D]

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.

Glad we could help you. [:D]


, can David or somebody please tell why using decimal field it’s not the good way?

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.

Ok David thanks, just for data store, I was making my brain a mess, it’s just monday [8o|]