Getting the last but one record from the table

Hi all,

I have developed a report which works on ‘Bank account ledger entry’ and ‘Bank account statement’ tables. Main table is ‘Bank account ledger entry’ and ‘Bank account statement’ is a record variable.

My requirement is after all the filters, it should find the last record ‘Statement date’ and go back by only one record and give me the statement date. I tried with this logic, but its showing me the last record only.

My piece of code,

recBAS.RESET;

recBAS.SETRANGE(recBAS.“Bank Account No.”,“Bank Account Ledger Entry”.“Bank Account No.”);

recBAS.SETRANGE(recBAS.“Statement No.”,“StatementNo.”);

recBAS.ASCENDING(FALSE);

IF recBAS.FIND(’+’) THEN

BEGIN

REPEAT

Count1 := Count1 + 1;

recBAS.NEXT(-1);

PreStmtDate := recBAS.“Statement Date”;

UNTIL Count1 = 1;

END;

Kindly help with what is wrong in this…

Thanks & regards,

Divya

Did you try UNTIL recBAS.NEXT(-1) = 0; instead of UNTIL Count1 = 1;?

Yup… tried that also, even then its giving the last record date only…

May be the last before record also has the same date. Please try to show/check other details of the record

The table has only 3 records…

Bank Acc. No. Statement No. Balance Statement Date

IOB 1 200 25.04.15

IOB 2 300 26.04.15

IOB 3 400 27.04.15

I am running the report for Statement No. 3. It has to give the date as 26.04.15… But its giving 27.04.15 only :frowning:

You have set filter on Statement No.

recBAS.SETRANGE(recBAS.“Statement No.”,“StatementNo.”);

then how does it go to previous record?

Hi Divya,

I notice three things which can not make it work.

Why do you need this filter? The filter on Bank Account No. is the only one you need. FIND(’+’) will get you the last record for this bank account number. So just leave this statement out.

Then the following statement will sort your recordset in descending order.

This is not what you want, but should be:

But rather leave this statement out as the default sort order will be ASCENDING. Saying so I realize I do not know the definition of the primary key of the Bank Account Statement table.

Then, third and last: why need a REPEAT … UNTILL construction? If you have found the last one - using FIND(’+’) - then just call NEXT(-1) or FIND(’<’) to get the previous one.

Or

Thanks for the replies…

Oh ya… But i want the report to run on statement no. only. I want the last record of the statement no. which is given in the request form not the last record of the table, so I have filtered on statement no.

Regards,

Divya

There is only one record for Statement No. 3 then how do you get previous record for statement 3?

Then you should lift the filter on the Bank Account No. first, before you execute the FIND(’<’) or NEXT (-1):

Luc,

That question was to Divya [;)]

I know, but just used your phrase to answer to Divya :sunglasses:

[:)][:)][8-)]

Yes… U both are right, but am confused how I have to go on…

I want the previous record of the statement no. which is given in the request form irrespective of the statement no…How should I go on now.Kindly help.

Regards,

Divya

Use your intelligence to rewrite your code based on our tips.

Hi,

Thanks for both of your tips… I solved the issue.

But my issue is, with this code the output record is repeating twice. Can u pls tell me where I am wrong.

I have shown the code in the Body - OnPreSection() of the ‘Bank account ledger entry’ record.

recBAS.RESET;

recBAS.SETRANGE(recBAS.“Bank Account No.”,“Bank Account Ledger Entry”.“Bank Account No.”);

recBAS.SETRANGE(“Statement No.”,“StatementNo.”);

IF recBAS.FIND(’+’) THEN

BEGIN

PresentDate := recBAS.“Statement Date”;

recBAS.SETRANGE(“Statement No.”);

recBAS.NEXT;

PreStmtDate := recBAS.“Statement Date”;

recBALE.RESET;

recBALE.SETCURRENTKEY(“Bank Account No.”,“Statement No.”,“Posting Date”);

recBALE.SETRANGE(recBALE.“Bank Account No.”,recBAS.“Bank Account No.”);

recBALE.SETFILTER(recBALE.“Statement No.”,’<>%1’,“StatementNo.”);

recBALE.SETFILTER(recBALE.“Posting Date”,’>=%1&<%2’,PresentDate,PreStmtDate);

IF recBALE.FIND(’-’) THEN

BEGIN

PostingDate := recBALE.“Posting Date”;

DescBALE := recBALE.Description;

ChequeNo := recBALE.“Cheque No.”;

CreditAmt := recBALE.“Credit Amount (LCY)”;

END;

END;

The fields which are made bold are the ones which are shown in the body part.

It is showing like…

Posting Date Description Cheque No. Credit Amt

25.04.15 ABC 10 100

25.04.15 ABC 10 100

Logically it has print only once. Kindly help…

Thanks & regards,

Divya

Good that you did continue yourself. You will learn from that, much more than letting others solve it for you.

Looking at your code I made some changes. See my notes below.

recBAS.SETRANGE(recBAS.“Bank Account No.”,“Bank Account No.”);
recBAS.SETRANGE(“Statement No.”,StatementNo);
IF recBAS.FIND(’+’) THEN BEGIN
PresentDate := recBAS.“Statement Date”;
recBAS.SETRANGE(“Statement No.”);
//recBAS.NEXT;
recBAS.NEXT(-1);
PreStmtDate := recBAS.“Statement Date”;

recBALE.RESET;
recBALE.SETCURRENTKEY(“Bank Account No.”,“Statement No.”,“Posting Date”);
recBALE.SETRANGE(“Bank Account No.”,recBAS.“Bank Account No.”);
recBALE.SETFILTER(“Statement No.”,’<>%1’,StatementNo);
recBALE.SETFILTER(“Posting Date”,’>=%1&<%2’,PresentDate,PreStmtDate);
IF recBALE.FIND(’-’) THEN BEGIN
PostingDate := recBALE.“Posting Date”;
DescBALE := recBALE.Description;
ChequeNo := recBALE.“Cheque No.”;
CreditAmt := recBALE.“Credit Amount (LCY)”;
END;
END;

Notes

See that I …

  1. changed NEXT to NEXT(-1)
    I thought you wanted to get the previous record so use NEXT(-1); NEXT will always get next record in filtered record set, but as you did a FIND(’+’) NEXT will not find anything so recBAS still contains the data of the last record (due to FIND(’+’))
    But maybe I am wrong on this …
  2. removed various record references in your code as they are not needed because of implicit references
  3. renamed StatementNo. to StatementNo. Best practice in NAV is not to use diacritical characters in variable names
  4. have reformatted the code as per the C/AL coding guidelines
  5. have left the naming of your record variables even though they do not comply with C/AL coding guidelines

Question

Why are you using these variables PostingDate/DescBALE/etc. to store these field values where you could use the fields themselves?

No getting to your question:

I can only answer this having the report itself …