How to "dump" report into a table

hendryschong
hendryschong Member Posts: 4
edited 2007-02-02 in NAV/Navision Classic Client
Hi,

I am attempting to pull out Sales Invoice Header table to Excel by means of NaVision ODBC. However the table is too huge to be pulled out by ODBC. So I tried to set up a report in NaVision to filter on the Date Filter field in the table and then to dump it into a new table, which was just limited to the date range I chose. Could someone tell me how to use the .INSERT function? Specific information as belows:
DataItem: Sales Invoice Header Table
Global Variable: Name: the new table50026 (Datatype: record; Subtype: Sls Inv Header)
Functions: Name: InsertSlsInvHeader (Parameter Name: SalesInvoiceHeader; Datatype: record; Subtype: Sales Invoice Header)
C/AL:
WITH Table50026 DO BEGIN
INIT;
"Sell-to Customer No." := SalesInvoiceHeader."Sell-to Customer No.";
"No." := SalesInvoiceHeader."No.";
"Bill-to Customer No." := SalesInvoiceHeader."Bill-to Customer No.";
"Bill-to Name" := SalesInvoiceHeader."Bill-to Name";
"Bill-to City" := SalesInvoiceHeader."Bill-to City";
"Ship-to Name" := SalesInvoiceHeader."Ship-to Name";
"Posting Date" := SalesInvoiceHeader."Posting Date";
"Posting Description" := SalesInvoiceHeader."Posting Description";
Amount := SalesInvoiceHeader.Amount;
"Amount Including VAT" := SalesInvoiceHeader."Amount Including VAT";
"Sell-to Customer Name" := SalesInvoiceHeader."Sell-to Customer Name";
"Sell-to City" := SalesInvoiceHeader."Sell-to City";
"Bill-to Post Code" := SalesInvoiceHeader."Bill-to Post Code";
"Bill-to County" := SalesInvoiceHeader."Bill-to County";
"Bill-to Country Code" := SalesInvoiceHeader."Bill-to Country Code";
"Sell-to Post Code" := SalesInvoiceHeader."Sell-to Post Code";
"Sell-to County" := SalesInvoiceHeader."Sell-to County";
"Sell-to Country Code" := SalesInvoiceHeader."Sell-to Country Code";
"Ship-to Post Code" := SalesInvoiceHeader."Ship-to Post Code";
"Ship-to County" := SalesInvoiceHeader."Ship-to County";
"Ship-to Country Code" := SalesInvoiceHeader."Ship-to Country Code";
INSERT;
END;

I've set up the destination table 50026 Sls Inv Header. Both the table and the report were compiled. However, after I ran the report, the table was empty. Could you tell me what I did wrong?

BTW, do you think if there is any other better way to export table to excel (not Copy and Paste)? Appreciate your help!

Tom

Comments

  • Lin
    Lin Member Posts: 40
    One really quick way is to save your report as HTML and use a file extension of XLS. The file then opens with excel.

    You can save a report to html by selecting FILE--> SaveasHTML from the PREVIEW screen or by using code that runs the report as follows:-

    YourReport.SAVEASHTLML('path\filename.xls');
    YourReport.RUN;

    This is only one way of creating excel from NAV - you can look in the Tips & Tricks forum for other ways.
  • Savatage
    Savatage Member Posts: 7,142
    hendryschong wrote:
    Hi,

    I am attempting to pull out Sales Invoice Header table to Excel by means of NaVision ODBC. However the table is too huge to be pulled out by ODBC.

    Does it give you a error message - or do you lose patience?

    r u using sql?
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • hendryschong
    hendryschong Member Posts: 4
    Lin wrote:
    One really quick way is to save your report as HTML and use a file extension of XLS. The file then opens with excel.

    You can save a report to html by selecting FILE--> SaveasHTML from the PREVIEW screen or by using code that runs the report as follows:-

    YourReport.SAVEASHTLML('path\filename.xls');
    YourReport.RUN;

    This is only one way of creating excel from NAV - you can look in the Tips & Tricks forum for other ways.
    I ultimately want to link the excel table to Access program. Besides, the table itself has many fields that I need, so that the report is unable to display them all even with a landscape view. Furthermore, I wish I can automate the process as possible as I can. O:)
  • hendryschong
    hendryschong Member Posts: 4
    Savatage wrote:
    hendryschong wrote:
    Hi,

    I am attempting to pull out Sales Invoice Header table to Excel by means of NaVision ODBC. However the table is too huge to be pulled out by ODBC.

    Does it give you a error message - or do you lose patience?

    r u using sql?

    Yes, the ODBC said there were too many fields. Furthermore, the source table in NaVision is building up for the time being. The excel sheet can not hold that many rows of records.

Categories