Sorting customer by balance in a form

DaveTDaveT Member Posts: 1,039
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
Dave Treanor

Dynamics Nav Add-ons
http://www.simplydynamics.ie/Addons.html

Answers

  • garakgarak Member Posts: 3,263
    edited 2008-05-06
    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
    Do you make it right, it works too!
  • SavatageSavatage Member Posts: 7,142
    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?
  • DaveTDaveT Member Posts: 1,039
    Hi Guys,

    Thanks for your feedback.

    @garak

    This is for a native DB so I keep your suggestion in mind if (went :wink: ) 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 ?
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • garakgarak Member Posts: 3,263
    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.
    Do you make it right, it works too!
  • SavatageSavatage Member Posts: 7,142
    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
  • SavatageSavatage Member Posts: 7,142
    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.
  • DaveTDaveT Member Posts: 1,039
    Hi Guys,

    Again thanks for the input.

    @garak - probably the most efficent solution :wink: 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"
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
Sign In or Register to comment.