Creating/Populating the Sales & Purchase Journal from Ledger/Buffer Table in NAV

Hi all,

Today i came across with different type of requirement, Sometimes we need to populate/Create Journals thru codding.

Bellow are the whole codeunit with business logic.

OBJECT Codeunit 50081 Populate Journals

{

  OBJECT-PROPERTIES

  {

    Date=10/05/16;

    Time=[ 6:33:05 AM];

    Modified=Yes;

    Version List=Sbinesh;

  }

  PROPERTIES

  {

    OnRun=BEGIN

            CLEARLASTERROR;

            IF NOT PopulateSalesJournal THEN

              ERROR('Not populated, for more info read bellow lines\\%1',GETLASTERRORTEXT);

            IF NOT PopulatePurchaseJournal THEN

              ERROR('Not populated, for more info read bellow lines\\%1',GETLASTERRORTEXT);

          END;

  }

  CODE

  {

    [TryFunction]

    PROCEDURE PopulateSalesJournal@1000000000();

    VAR

      GenJournalTemplate@1000000006 : Record 80;

      GenJournalLine@1000000005 : Record 81;

      GenJournalBatch@1000000004 : Record 232;

      CustLedgerEntry@1000000003 : Record 21;

      CustLedgerEntryBuffer@1000000002 : Record 80021;

      GenJournalTemplateName@1000000001 : Text;

      GenJournalBatchName@1000000000 : Text;

      LineNo@1000000007 : Integer;

      MyDialog@1000000008 : Dialog;

      CountL@1000000009 : Integer;

    BEGIN

      IF GenJournalTemplate.GET('SALES') THEN

        GenJournalTemplateName := GenJournalTemplate.Name;

      IF GenJournalBatch.GET(GenJournalTemplateName,'DEFAULT') THEN

        GenJournalBatchName := GenJournalBatch.Name;

      CustLedgerEntryBuffer.RESET;

      IF CustLedgerEntryBuffer.FINDSET THEN BEGIN

        GenJournalLine.RESET;

        GenJournalLine.SETRANGE(GenJournalLine."Journal Template Name",GenJournalTemplateName);

        GenJournalLine.SETRANGE(GenJournalLine."Journal Batch Name",GenJournalBatchName);

        IF GenJournalLine.FINDLAST THEN

          LineNo := GenJournalLine."Line No."

        ELSE

          CLEAR(LineNo);

        MyDialog.OPEN('Record populating... #1###############');

        REPEAT

          LineNo += 10000;

          GenJournalLine.INIT;

          GenJournalLine.VALIDATE(GenJournalLine."Journal Template Name",GenJournalTemplateName);

          GenJournalLine.VALIDATE(GenJournalLine."Journal Batch Name",GenJournalBatchName);

          GenJournalLine.VALIDATE("Line No.",LineNo);

          GenJournalLine.VALIDATE(GenJournalLine."Posting Date",CustLedgerEntryBuffer."Posting Date");

          GenJournalLine.VALIDATE(GenJournalLine."Document Type",CustLedgerEntryBuffer."Document Type");

          GenJournalLine.VALIDATE(GenJournalLine."Document No.",CustLedgerEntryBuffer."Document No.");

          GenJournalLine.VALIDATE(GenJournalLine."Account Type",GenJournalLine."Account Type"::Customer);//Bcoz in Buffer table 1 option is there.

          GenJournalLine.VALIDATE(GenJournalLine."Account No.",CustLedgerEntryBuffer."Account No.");

          GenJournalLine.VALIDATE(GenJournalLine.Description,CustLedgerEntryBuffer.Description);

          GenJournalLine.VALIDATE(GenJournalLine.Amount,CustLedgerEntryBuffer."Remaining Amount");

          GenJournalLine.VALIDATE(GenJournalLine."Bal. Account Type",GenJournalLine."Bal. Account Type"::"G/L Account");

          GenJournalLine.VALIDATE(GenJournalLine."Bal. Account No.",'31040');

          GenJournalLine.VALIDATE(GenJournalLine."External Document No.",CustLedgerEntryBuffer."External Document No.");

          GenJournalLine.VALIDATE(GenJournalLine."Due Date",CustLedgerEntryBuffer."Due Date");

          GenJournalLine.VALIDATE(GenJournalLine."Document Date",CustLedgerEntryBuffer."Document Date");

          GenJournalLine.VALIDATE(GenJournalLine."Territory Code",CustLedgerEntryBuffer."Territory Code");

          GenJournalLine.VALIDATE(GenJournalLine."Ship-to Territory Code",CustLedgerEntryBuffer."Ship-to Territory Code");

          GenJournalLine.VALIDATE(GenJournalLine."Salespers./Purch. Code",CustLedgerEntryBuffer."Salesperson Code");

          GenJournalLine.VALIDATE(GenJournalLine."Ship-to Salesperson Code",CustLedgerEntryBuffer."Ship-to Salesperson Code");

          GenJournalLine.VALIDATE(GenJournalLine."Mark Text",CustLedgerEntryBuffer."Mark Text");

          GenJournalLine.VALIDATE(GenJournalLine."Payment Terms Code",CustLedgerEntryBuffer."Payment Terms Code");

          GenJournalLine.INSERT(TRUE);

          CountL += 1;

          MyDialog.UPDATE(1,CountL);

        UNTIL CustLedgerEntryBuffer.NEXT = 0;

      END;

      MyDialog.CLOSE();

      MESSAGE('[%1] record(s) populated!',CountL);

    END;

    [TryFunction]

    PROCEDURE PopulatePurchaseJournal@1000000002();

    VAR

      GenJournalTemplate@1000000006 : Record 80;

      GenJournalLine@1000000005 : Record 81;

      GenJournalBatch@1000000004 : Record 232;

      VendorLedgerEntry@1000000003 : Record 25;

      VendorLedgerEntryBuffer@1000000002 : Record 80025;

      GenJournalTemplateName@1000000001 : Text;

      GenJournalBatchName@1000000000 : Text;

      LineNo@1000000007 : Integer;

      MyDialog@1000000008 : Dialog;

      CountL@1000000009 : Integer;

    BEGIN

      IF GenJournalTemplate.GET('PURCHASES') THEN

        GenJournalTemplateName := GenJournalTemplate.Name;

      IF GenJournalBatch.GET(GenJournalTemplateName,'DEFAULT') THEN

        GenJournalBatchName := GenJournalBatch.Name;

      VendorLedgerEntryBuffer.RESET;

      IF VendorLedgerEntryBuffer.FINDSET THEN BEGIN

        GenJournalLine.RESET;

        GenJournalLine.SETRANGE(GenJournalLine."Journal Template Name",GenJournalTemplateName);

        GenJournalLine.SETRANGE(GenJournalLine."Journal Batch Name",GenJournalBatchName);

        IF GenJournalLine.FINDLAST THEN

          LineNo := GenJournalLine."Line No."

        ELSE

          CLEAR(LineNo);

        MyDialog.OPEN('Record populating... #1###############');

        REPEAT

          LineNo += 10000;

          GenJournalLine.INIT;

          GenJournalLine.VALIDATE(GenJournalLine."Journal Template Name",GenJournalTemplateName);

          GenJournalLine.VALIDATE(GenJournalLine."Journal Batch Name",GenJournalBatchName);

          GenJournalLine.VALIDATE("Line No.",LineNo);

          GenJournalLine.VALIDATE(GenJournalLine."Posting Date",VendorLedgerEntryBuffer."Posting Date");

          GenJournalLine.VALIDATE(GenJournalLine."Document Type",VendorLedgerEntryBuffer."Document Type");

          GenJournalLine.VALIDATE(GenJournalLine."Document No.",VendorLedgerEntryBuffer."Document No.");

          GenJournalLine.VALIDATE(GenJournalLine."Account Type",GenJournalLine."Account Type"::Vendor);

          GenJournalLine.VALIDATE(GenJournalLine."Account No.",VendorLedgerEntryBuffer."Account No.");

          GenJournalLine.VALIDATE(GenJournalLine.Description,VendorLedgerEntryBuffer.Description);

          GenJournalLine.VALIDATE(GenJournalLine.Amount,VendorLedgerEntryBuffer."Remaining Amount");

          GenJournalLine.VALIDATE(GenJournalLine."Bal. Account Type",GenJournalLine."Bal. Account Type"::"G/L Account");

          GenJournalLine.VALIDATE(GenJournalLine."Bal. Account No.",'31040');

          GenJournalLine.VALIDATE(GenJournalLine."External Document No.",VendorLedgerEntryBuffer."External Document No.");

          GenJournalLine.VALIDATE(GenJournalLine."Due Date",VendorLedgerEntryBuffer."Due Date");

          GenJournalLine.VALIDATE(GenJournalLine."Document Date",VendorLedgerEntryBuffer."Document Date");

          GenJournalLine.VALIDATE(GenJournalLine."Payment Terms Code",VendorLedgerEntryBuffer."Payment Terms Code");

          GenJournalLine.VALIDATE(GenJournalLine."Salespers./Purch. Code",VendorLedgerEntryBuffer."Purchaser Code");

          GenJournalLine.INSERT(TRUE);

          CountL += 1;

          MyDialog.UPDATE(1,CountL);

        UNTIL VendorLedgerEntryBuffer.NEXT = 0;

      END;

      MyDialog.CLOSE();

      MESSAGE('[%1] record(s) populated!',CountL);

    END;

    BEGIN

    {

      October, 05 2016 :  This codeunit is developed for creating the Sales & Purchase Journal from buffer table.

                          Cust. Ledger Entry Buffer table contains the mandatory fields from Cust. Ledger Entry.

                          Vendor Ledger Entry Buffer table contains the mandatory fields from Vendor Ledger Entry.

      Solution Developed by Binesh Singh Rajput

    }

    END.

  }

}

You can download this solution from here.

 

EXPORT DATA FROM SQL SERVER 2014 WIZARD

EXPORT DATA FROM SQL SERVER 2014 WIZARD

Hi all,

Today i came across with different type of requirement, Sometimes we need to move/Copy our NAV data from one Database to others, OR One server to other server.

we can do also this task using "Departments -> Administration -> Application Setup -> RapidStart Services for Microsoft Dynamics NAV -> Config. Package Card.

But in this scenario, BLOB fields are not copied, Due to overcome this we have to do using SQL Server.

Run the SQL Server Import and Export Wizard to copy data between a variety of sources and destinations.

If you don't have Microsoft SQL Server installed on your computer, you can install the SQL Server Import and Export Wizard by installing SQL Server Data Tools (SSDT). 

Steps for exporting table data from one server to another server database table.

Step 1:

In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine, expand Databases, right-click a database, point to Tasks, and then click one of the following options [See the bellow screenshot].

Step 2:

Click NEXT Button and select the Source, See the bellow screenshot for better understanding.

NOTE:

After clicking Next button, you will get destination wizard, here you have various option to export the data.

Like: MS Excel, MS Access, Flat File destination, Sql Server e.t.c, In my demo i select SQL SERVER.

Step 3:

In this wizard select the Destination (Client, Server Name, Authentication, Database) And then click on NEXT Button,  See the bellow screenshot for better understanding.

Step 4:

Specify weather to copy one or more tables/views or to copy the results of the query from the data source.

In this wizard we have 2 options.

Option 1:             Copy data from one or more tables or views

Using this option, We can copy multiple tables/views data at a time, but in this we can't filter data.

Option 2:             Write query to specify the data to transfer

Using this option, we can filter the data, but in this option we can copy single tabale data ata time.

In my demo i used 2nd option where we can write query, And click on NEXT Button, See the bellow screenshot for better understanding.

Step 5:  Provide a source query for fetching the data from source server And then click on NEXT Button, see the bellow screenshot.

Step 6:  Select Source tables/views for mapping the data to destination.3

NOTE:

First verify the Destination Tables is selected or not, if not then, manually click and select the correct table or you can write also, Then Click on Edit Mapping, And Map your fields Source to destination, you can also skip the filed(s) that you don't want to copy, In my case, I skipped the timestamp field.

Once Mapped fields then click on OK button.

You can also verify your data by clicking on Preview [Optional].

Now Click on NEXT button, see the bellow screenshot.

Step 7:  Save and Run Package,

In this wizard we can run our query for transferring the source data to desatination.

We can also save our package for further execution or documention.

In my demo i select both option RUN & Save, you can ommit the SAVE option if you don’t want to create package.

If you selected Save option then only Save dialog will display, See the bellow screenshot for better understanding.

Click on NEXT button. A Complete the Wizard dialog box display, now click on Finish button, see the bellow screenshot.

After successfully executing the all query, A completion report will display, You can save your report,or  Email.

Print & Open any document in NAV using DotNet Variables.

Hi all,
Today i came across with different type of requirement, where someone asking for how could we Print & Open any document in NAV using DotNet Variables.

This Generic solution for Running/Opening and Printing any document, without any custom dlls, that always works, whenever, however, with any software, as long as you have “any” software installed that can Open & print that “any”-document with any extension ...
In-case if not Open or Print, it should give an error notification regarding failure...

To achieve these requirement i created two functions, and declared local dotnet variables for both functions, where as FileNameWithPathG is a Global Text variable.

See the bellow screenshot for better understanding.