find out total amount in a report from G/L entry and sales invoice line table.

Business Central/NAV Developers Forum
1

i want to calculate total amount from G/L entry table . data will be retrieve from G/L entry and Sales Invoice Line according to document no.

if posting date=current date then it will calculate tolal amount from amount field.

i want to show this in a report single page.

i add datalink reference with Sales Invoice Line table and it is showing many page.

but it is a calculation report and it must be one page.

how can i do this?

please help me.

2

Hi Md…

Have you tried the Totalfields property or even CREATETOTALS

eg

CurrReport.CREATEOTALS(Amount);

3

hello, at first ,I need this report in a page.

i still did not write code for rerport.

i had added dataitem Sales Invoice Line and G/L Entry. it is showing 7 pages.

i tried .but it is not working.

4846.report.jpg
4846.report.jpg1280×1024 169 KB

where should i add this code?

i want to show this report within a page.

please help me.

4

The report must be showing all the records.

Set the link between both the tables and filter records at the runtime to show the needed records.

5

UNDER GL entry onpredataitem trigger:

CurrReport.CREATETOTALS( totalcash, “G/L Entry”.Amount);

under gl_entry onaftergetrecord:

IF (CurrReport.PAGENO = 1) THEN BEGIN

CurrReport.QUIT;

END;

but it is not working.

6

Hi…

What was your link between Sales Invoice Line and G/L Entry Table?

7

Document No

8

Have you done CREATETOTALS for G/L Entry.Amount on the Sales Invoice Line preDataitem?

your code earlier didn’t show you filtering for posting date…Are you ding this at run time?

9

IF “G/L Entry”.FIND(’-’) THEN BEGIN

REPEAT

CASE “Payment Method Code” OF ‘CASH’:

BEGIN

totalcash:=totalcash+“G/L Entry”.Amount;

END; ’

i am trying to findout total cash, total credit card and total other according to payment method code.

but it is not working and it shows only 0.

please help me.

‘CREDCARD’:

BEGIN

totalcredit:=totalcredit+“G/L Entry”.Amount;

END;

ELSE BEGIN

totalother:=totalother+“G/L Entry”.Amount;

END;

END;

UNTIL “G/L Entry”.NEXT<=0;

END;

10

Hi Md…

This is something I used when summing totals whilst filtering on Posting Groups… strip out these bits out and should work.

recGLEntry.RESET;
recGLEntry.SETCURRENKEY(...); // try to get a key that can be used to
find the filtered data in a fast way
recGLEntry.SETRANGE(.....); // put your filters
IF recGLEntry.FINDSET THEN
  REPEAT
    // "tmpGLEntry" is the temptable we will use to store the grouping
totals
    tmpGLEntry.RESET;
 
    // try to get a good key for the filters
    IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Bus. Posting Group") THEN
      IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Prod. Posting Group") THEN ;
      
    // I filter on the records for which I want to group the records
    tmpGLEntry.setrange("Gen. Bus. Posting Group",recGLEntry."Gen. Bus.
Posting Group");
    tmpGLEntry.setrange("Gen. Prod. Posting Group",recGLEntry."Gen. Prod.
Posting Group");
 
    IF NOT tmpGLEntry.FINDFIRST THEN BEGIN
      // I didn't find a record, so I have to create a new one.
      // Remember that to insert a record, you have to respect a unique
primary key.
      // This is done, because EACH "recGLEntry" I read is unique, so I
can just insert
      // "recGLEntry" in my temptable.
      tmpGLEntry := recGLEntry;
      tmpGLEntry.insert(FALSE);
    END
    ELSE BEGIN
      // I found the record with combination I wanted, so I add the
field(s) I am SUMming
      tmpGLEntry.Amount += recGLEntry.Amount;
      tmpGLEntry.MODIFY(FALSE);
    END;
  UNTIL recGLEntry.NEXT = 0;
 
// In the temptable I now have records that contain the SUM for the
combination I wanted.
 
// Now you can just read the temptable and do what you want to do with
your totals
tmpGLEntry.RESET;
FORM.RUNMODAL(0,tmpGLEntry);

P...[:)]