Get Highest amount in customer ledger entry

karuchua · 2013-05-03

hi all,
am trying to write a function to get the highest amount in the customer ledger entry table
how do i get this?

Isitar · 2013-05-03

Hi

first of, where do you need it? in a table? then make a flowfield.
in a function?
loop trough the records:

local variable maxEntry , type Record
// RANGES
IF Findset then
  repeat
    IF (Amount > maxEntry.Amount) THEN
      maxEntry := Rec;
  until next() = 0;
  // maxEntry now contains the record with the highest amount

cheers

karuchua · 2013-05-03

i have tried this but not working

       LCustREC.INIT;
       IF LCustREC.GET(PAccNo) THEN BEGIN
           LCustREC.CALCFIELDS("Balance (LCY)");
        IF LCustREC."Balance (LCY)"<0 THEN  BEGIN
        LCustLedgEntryREC.RESET;
        LCustLedgEntryREC.SETCURRENTKEY("Customer No.","Posting Date","Document Type");
        LCustLedgEntryREC.SETRANGE("Customer No.",PAccNo);
        LCustLedgEntryREC.SETFILTER("Document Type",'%1',LCustLedgEntryREC."Document Type"::Payment);
        LCustLedgEntryREC.SETRANGE(Open,TRUE);
     //   LCustLedgEntryREC.SETFILTER("Posting Date",'%1..%2',LDate,PAgeingDate);
        IF LCustLedgEntryREC.FINDSET THEN
        REPEAT
        IF  (LCustLedgEntryREC."Amount (LCY)">LAmount) THEN

        LAmount:=LCustLedgEntryREC."Amount (LCY)";
        
   UNTIL LCustLedgEntryREC.NEXT=0
END

END

Isitar · 2013-05-03

Hi

Amount LCY is a flowfield, you have to calculate it everytime.

...
repeat
  LCustLedgEntryREC.calcfields("Amount (LCY)")
  IF  (LCustLedgEntryREC."Amount (LCY)">LAmount) THEN
    LAmount:=LCustLedgEntryREC."Amount (LCY)";
until LCustLedgEntryREC.NEXT() = 0;

If you're using NAV13 I think you could use setautocalcfields

Savatage · 2013-05-03

What are you looking for when you say Highest Amount?

Are you looking for the largest individual order?
Are you looking for the customer with the Largest Balance?
Are you looking for the customer with the Largest Sales?

Have you looked at the Report "Top __ Customer List"?

karuchua · 2013-05-06

am looking for the customer with the highest negative balance?

Savatage · 2013-05-06

Did you try the top customer list?
Are you saying you have over 100 customers with a negative balance?
else you can under Customer tab Balance (lcy) <0
options tab select balance (lcy).

If this was something that we needed on a regular basis. I would probably create a report that exports to excel & in the click of a button sort is as I would like.

Alex_Chow · 2013-05-07

Savatage wrote:

Did you try the top customer list?
Are you saying you have over 100 customers with a negative balance?
else you can under Customer tab Balance (lcy) <0
options tab select balance (lcy).

If this was something that we needed on a regular basis. I would probably create a report that exports to excel & in the click of a button sort is as I would like.

This report is only available in the NA version.

einsTeIn.NET · 2013-05-07

I think Harry is talking about Report 111 which should be available in W1.

Savatage · 2013-05-07

Just trying to figure out the easiest & fastest way to achieve his goal.

We also don't know how often he will need such a list.

If 'once in a while' then no need to get into coding & sorting & arrays, etc.
Finding an existing report that can accomplish this task would be great.
Also, You could easily add "Balalce (Lcy)" to the customer list (if not already).
Filter on <0 & copy & paste the results into excel.

Highest Negative Balance

karuchua · 2013-05-10

thank you for the contributions, i managed to get the highest negative balance.

but as you will note, in the code below, am using that value to get another value

        IF LCustLedgEntryREC.FINDFIRST THEN
        REPEAT
          LCustLedgEntryREC.CALCFIELDS("Amount (LCY)");
          LAmount:=LCustLedgEntryREC."Amount (LCY)";
        IF (LCustLedgEntryREC."Amount (LCY)">LAmount) THEN       
           Engineer:=LCustLedgEntryREC."Job No./Sales Engineer Name"
        ELSE 
             Engineer:=LCustLedgEntryREC."Job No./Sales Engineer Name";
   UNTIL LCustLedgEntryREC.NEXT()=0 ;

Get Highest amount in customer ledger entry

Answers

Categories