mibuso.com
Home› NAV/Navision Classic Client

How do I do this code?

sp4tcsp4tc Member Posts: 16
edited 2008-11-07 in NAV/Navision Classic Client
I am trying to export from the GL Budget Entry table using the Excel buffer so I wrote this

GL1 := "G/L Budget Entry"."G/L Account No.";
IF GL1 = '101000' THEN
RS1 := "G/L Budget Entry".Amount
ELSE
CurrReport.SKIP;

This works for one and exports fine my problem here is I have many GL accounts to do this for (see below) and I want one in each column going across the page

GL codes;

101000
102000
103000
104000
105000
106000
107000
108000
109000
110000
111000
112000
113000
114000
115000
116000
117000
118000
119000
120000
121000
122000
123000
124000
125000
126000
127000


Can anyone help?

Thanks in advance :shock:

Comments

  • BeliasBelias Member Posts: 2,998 
    GL1 := "G/L Budget Entry"."G/L Account No.";
if intaccounttoskip = 0 then  
  INTAccountToSkip := 101000;
IF GL1 = format(intaccounttoskip) THEN begin
  intaccounttoskip += 1000;
  if intaccounttoskip = 127000 then begin
    //CURRReport.break?
  end;
  RS1 := "G/L Budget Entry".Amount
end ELSE
  CurrReport.SKIP;
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • sp4tcsp4tc Member Posts: 16
    What if there are other GL codes I want e.g. 200001, 200005, 200008?

    Regards
  • BeliasBelias Member Posts: 2,998
    You must enter them manually in the filter...
    with my previous post, i solved ONLY your problems, with ONLY the accounts you posted :whistle:
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • garakgarak Member Posts: 3,263
    why not using a setup table where you store the GL Accounts to ignore / to use.
    If GLIgnoreTable.get(GLAccNo) then //<- to ignore, to use -> if  not GLIgnoreTable.get(GLAccNo) then CurrReport.skip
  CurrReport.skip;

    Regards
    Do you make it right, it works too!
  • sp4tcsp4tc Member Posts: 16
    sorry, I am lost.

    I have setup a table and put the GL codes in but I am unsure of how to use your code.

    I want a column per GL account in the excel output

    Can you help please?
  • SavatageSavatage Member Posts: 7,142
    how about store that account #'s using an array

    keep the Row number the same & keep incrementing the column no.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • SavatageSavatage Member Posts: 7,142
    Ok Try THis .. it's an example on how to put the account codes going across ---> then you can add what yo need to add to it. It exports it to excel.
    OBJECT Report 50092 GL Account No Export
{
  OBJECT-PROPERTIES
  {
    Date=11/06/02;
    Time=12:57:27 PM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    TopMargin=1000;
    BottomMargin=1000;
    LeftMargin=1000;
    ProcessingOnly=Yes;
    Orientation=Portrait;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table15;
        OnPreDataItem=BEGIN
                        Window.OPEN(
                          text001 +
                          '@1@@@@@@@@@@@@@@@@@@@@@\');
                        Window.UPDATE(1,0);
                        TotalRecNo := "G/L Account".COUNTAPPROX;
                        RecNo :=0;

                        TempExcelBuffer.DELETEALL;

                        CLEAR(TempExcelBuffer);
                      END;

        OnAfterGetRecord=BEGIN
                           Row := 1;
                           Column := Column +1;
                           EnterCell(Row, Column, "G/L Account"."No.", FALSE, FALSE, FALSE);

                           RecNo := RecNo + 1;
                           Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
                         END;

        OnPostDataItem=BEGIN
                         Window.CLOSE;

                         TempExcelBuffer.CreateBook;
                         TempExcelBuffer.CreateSheet(text000,text000,COMPANYNAME,USERID);
                         TempExcelBuffer.GiveUserControl;
                       END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=12000;
            SectionHeight=846;
          }
          CONTROLS
          {
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      TempExcelBuffer@1100500000 : TEMPORARY Record 370;
      Window@1100500005 : Dialog;
      Row@1100500001 : Integer;
      TotalRecNo@1100500003 : Integer;
      RecNo@1100500004 : Integer;
      PORow@1102606008 : Integer;
      xlSheet@1102606009 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020820-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Worksheet";
      Item@1000000000 : Record 27;
      Column@1000000003 : Integer;
      text001@1102606000 : TextConst 'ENU=Account No';
      text000@1102606001 : TextConst 'ENU=GL Account';

    LOCAL PROCEDURE EnterCell@2(RowNo@1000 : Integer;ColumnNo@1001 : Integer;CellValue@1002 : Text[250];Bold@1003 : Boolean;Italic@1004 : Boolean;UnderLine@1005 : Boolean) : Text[250];
    BEGIN
      TempExcelBuffer.INIT;
      TempExcelBuffer.VALIDATE("Row No.",RowNo);
      TempExcelBuffer.VALIDATE("Column No.",ColumnNo);
      TempExcelBuffer."Cell Value as Text" := CellValue;
      TempExcelBuffer.Formula := '';
      TempExcelBuffer.Bold := Bold;
      TempExcelBuffer.Italic := Italic;
      TempExcelBuffer.Underline := UnderLine;
      TempExcelBuffer.INSERT;
    END;

    EVENT xlSheet@1102606009::SelectionChange@1543(Target@1102606000 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Range");
    BEGIN
    END;

    EVENT xlSheet@1102606009::BeforeDoubleClick@1537(Target@1102606001 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Range";VAR Cancel@1102606000 : Boolean);
    BEGIN
    END;

    EVENT xlSheet@1102606009::BeforeRightClick@1534(Target@1102606001 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Range";VAR Cancel@1102606000 : Boolean);
    BEGIN
    END;

    EVENT xlSheet@1102606009::Activate@304();
    BEGIN
    END;

    EVENT xlSheet@1102606009::Deactivate@1530();
    BEGIN
    END;

    EVENT xlSheet@1102606009::Calculate@279();
    BEGIN
    END;

    EVENT xlSheet@1102606009::Change@1545(Target@1102606000 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Range");
    BEGIN
    END;

    EVENT xlSheet@1102606009::FollowHyperlink@1470(Target@1102606000 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00024431-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Hyperlink");
    BEGIN
    END;

    EVENT xlSheet@1102606009::PivotTableUpdate@2156(Target@1102606000 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020872-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.PivotTable");
    BEGIN
    END;

    BEGIN
    END.
  }
}

    Hopefully this is what you were talking about..let me know if you need fob instead.
    We don't use the table you speak of so I couldn't make a working example.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • costascostas Member Posts: 27
    If you do not want to use a table the something like this will do the job
    If Not GL1 In ['101000','102000','103000','104000','105000',
                    '106000','107000','108000','109000','110000',
                    '111000','112000','113000','114000','115000',
                    '116000','117000','118000','119000','120000',
                    '121000','122000','123000','124000','125000','126000','127000'] Then
  CurrReport.SKIP;
  • BeliasBelias Member Posts: 2,998
    costas wrote:
    If you do not want to use a table the something like this will do the job
    If Not GL1 In ['101000','102000','103000','104000','105000',
                    '106000','107000','108000','109000','110000',
                    '111000','112000','113000','114000','115000',
                    '116000','117000','118000','119000','120000',
                    '121000','122000','123000','124000','125000','126000','127000'] Then
  CurrReport.SKIP;
    which is the same thing i wrote before... :mrgreen:
    but a table is definitively better if the process is not a "one shot" thing
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
Sign In or Register to comment.