Sorting customer by balance in a form

DaveT

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.

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

By the way its on Nav 5.0 & native DB

Thanks in advance

garak

you can also create a view (requ. SQL). this is based on customer table and on one of the SIFT Table of customer ledger entry (or directly from the customer ledger entry (SUM) ).

Then you can create a index on this view.

To know how to use view and use it in navision, take a look at "Application Designer’s Guide"

Regards

Savatage

How about trying to change the Top __ Customer List.

Instead of showing purely sales you can alter a copy of it to work on the balance.

I know it's not a form, but maybe it will fill the need you;re looking for?

DaveT

Hi Guys,

Thanks for your feedback.

@garak

This is for a native DB so I keep your suggestion in mind if (went ) they go to SQL

@Harry

Had a look at the top 10 listing and it's using a temp table of Customer Amount - something along the lines I was thinking but this is such a simple table they get away without performance issues. :-k

Anyone have any other suggestions ?

garak

you can also create a non calculated decimal field on customer table.
Then in the Codeunit 12 where the customer ledger entries are written / modified you fill the new field with the new Value. But with this solution you have ever a write transaction (temporary lock) on the customer table.
For this solution you must know, how many writes you have on this table.

Savatage

just a thought how about a new decimal field in the cust table - Curr Balance.

Make that a key. so you can sort on it.
On your form have a button that runs a function that updates the
Curr Balance with the Balance field.

but it will keep needing updating. perhaps on open form?

never tried - just an idea.
:-k

Savatage

you can easily modify the Top__Customer report to handle more than 10 try changing the code to 99 or something.

Then if the point of this form is to keep tabs on the high balance accounts you can take the results from the Top report see the lowest balance you want to deal with. Run the a/r aging and use that figure in the filter of the report.

DaveT

Hi Guys,

Again thanks for the input.

@garak - probably the most efficent solution but I try to avoid modifing posting codeunit if I can help it.

@Harry - I will try out your Curr Balance suggestion and see what the performance is like


It's really caught between a rock and a hard place - I suspose this post is "Resolved" as opposed to "Solved"