Integrating the application with Relationship Management and Sales in Microsoft Dynamics NAV 2009

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics NAV 2009 Application Design

Microsoft Dynamics NAV 2009 Application Design — Save 50%

A focused book and ebook tutorial for Microsoft Dynamics NAV application development

$35.99    $18.00
by Mark Brummel | June 2010 | Enterprise Articles Microsoft

Microsoft Dynamics NAV 2009 is a very complete ERP package, but unlike other ERP packages it has a design capable of providing an open structure and a development platform. The idea is to provide 80% of the solution out of the box and allow the other 20% to be designed by qualified business application developers.

In the first part of the three part article series we have learned how to apply reverse engineering to customize our application

In the Second part of the three part article series we have learned how to Use the journals and entries in a custom application

In this article, by Mark Brummel, author of Microsoft Dynamics NAV 2009 Application Design we will learn:

  • how to integrate our solution with the standard application, in our case Sales Invoicing
  • How to structure codeunit of our application.
  • Navigate function-it needs changing when we add new documents and ledger entries

(Further on Microsoft Dynamics NAV:here.)

Invoicing

The last issue on our to-do list is the invoicing process. For this we use a part of the standard application.

invoicing is done using a document structure with a header and a line table. This has a posting routine that will start the journal transactions.

For our application we need to create the invoice document and make sure that when posted, it updates our sub administration.

Invoice document

The sales invoice documents in Microsoft Dynamics NAV are stored in the sales header (36) and sales line (37) tables. We will create a report that will combine the outstanding reservation entries into invoices allowing the user to filter on a specific entry or any other field value in the squash ledger entry table.

Reports in Microsoft Dynamics NAV are not just for printing documents, we can also use its dataset capabilities to start batch jobs.

To enable this, our batch job needs to have a special property, ProcessingOnly (as shown in the following screenshot), so let's start a blank report and do this.

Integrating the application with Relationship Management and Sales in Microsoft Dynamics NAV 2009

The report will browse through the squash ledger entries filtered on entry type reservation and open (yes). The sorting is Open, Entry Type, Bill-to Customer No. and Reservation Date (as shown in the following screenshot):

Integrating the application with Relationship Management and Sales in Microsoft Dynamics NAV 2009

Because bill-to customer no. is the first non filtered value in the sorting we can assume that if this value changes we need a new sales header.

For every Squash Ledger Entry we will generate a sales line.

Squash Ledger Entry - OnAfterGetRecord()
IF "Bill-to Customer No." <> SalesHdr."Bill-to Customer No."
THEN
CreateSalesHdr;


CreateLn;

Sales header

The code to create a sales header looks like this:

CreateSalesHdr()
CLEAR(SalesHdr);
SalesHdr.SetHideValidationDialog(TRUE);
SalesHdr."Document Type" := SalesHdr."Document Type"::Invoice;
SalesHdr."Document Date" := WORKDATE;
SalesHdr."Posting Date" := WORKDATE;
SalesHdr.VALIDATE("Sell-to Customer No.",
"Squash Ledger Entry"."Bill-to Customer No.");
SalesHdr.INSERT(TRUE);
NextLineNo := 10000;
CounterOK := CounterOK + 1;

The function SetHideValidationDialog makes sure that we don't get pop-up messages while validating values. This is a standard function in Microsoft Dynamics NAV which is designed for this purpose.

Sales line

To create a sales line we need a minimum of this code. Please note that we added the field Applies-to Squash Entry No. to the sales line table.

CreateLn()
WITH "Squash Ledger Entry" DO BEGIN
GenPstSetup.GET("Gen. Bus. Posting Group",
"Gen. Prod. Posting Group");
GenPstSetup.TESTFIELD("Sales Account");
SalesLn.INIT;
SalesLn."Document Type" := SalesHdr."Document Type";
SalesLn."Document No." := SalesHdr."No.";
SalesLn."Line No." := NextLineNo;
SalesLn."System-Created Entry" := TRUE;
SalesLn.Type := SalesLn.Type::"G/L Account";
SalesLn.VALIDATE("No.", GenPstSetup."Sales Account");
SalesLn.Description := Description;
SalesLn.VALIDATE(Quantity, Quantity);
SalesLn.VALIDATE("Unit Price", "Unit Price");
SalesLn.VALIDATE("Unit Cost (LCY)", "Unit Cost");
SalesLn."Applies-to Squash Entry No." := "Entry No.";
SalesLn.INSERT(TRUE);
END;
NextLineNo := NextLineNo + 10000;

When you add fields to the sales and purchase document tables, make sure to also add these to the posted equivalents of these tables with the same number. This way you make sure that the information is copied to the historic data. This is done using the TRANSFERFIELDS command.

Dialog

If the combined invoicing takes some time it might be good to show the user a process bar. For this Microsoft Dynamics NAV has a standard structure.

The window shows the bill-to customer no. it is currently processing and a bar going from 1 percent to 100 percent. This is calculated by keeping a counter.

At the end of the process we show a message telling the user how many invoices were created out of the number of squash ledger entries.

Squash Ledger Entry - OnPreDataItem()
CounterTotal := COUNT;
Window.OPEN(Text000);
Squash Ledger Entry - OnAfterGetRecord()
Counter := Counter + 1;
Window.UPDATE(1,"Bill-to Customer No.");
Window.UPDATE(2,ROUND(Counter / CounterTotal * 10000,1));
...
Squash Ledger Entry - OnPostDataItem()
Window.CLOSE;
MESSAGE(Text001,CounterOK,CounterTotal);

To do this we need some variables. The Window variable is of type Dialog whilst Counter, CounterTotal and CounterOK are integers (as shown in the following screenshot):

Integrating the application with Relationship Management and Sales in Microsoft Dynamics NAV 2009

The constant Text000 has a special values #1########## and @2@@@@@@@@@@@@@ (as shown in the following screenshot). The first allows us to show and update the text; the latter is to create the process bar:

Integrating the application with Relationship Management and Sales in Microsoft Dynamics NAV 2009

The result is this:

Integrating the application with Relationship Management and Sales in Microsoft Dynamics NAV 2009

We see the following window when invoice creation process completes:

Integrating the application with Relationship Management and Sales in Microsoft Dynamics NAV 2009

bars in combination with the impact on performance at http://www.mibuso.com/howtoinfo.asp?FileID=17

Posting process

Now, our sales invoice is ready so we can start making the necessary changes to the posting process. Posting a sales document is done using a single posting codeunit and some helper objects:

  • Report 297: This report can be used to post more than one document at the same time with a filter.
  • Codeunit 80: This is the actual posting routine we are going to change.
  • Codeunit 81: This codeunit is called from the user interface and has a dialog whether the user wants to ship, invoice or both if the document is an order, and a yes/no if the document is an invoice or credit memo.
  • Codeunit 82: When the user chooses post and print this codeunit is executed which does the same as codeunit 81 plus printing a report.

So we will make a change to codeunit 80. This codeunit has a specific structure that we need to understand before we go in and make the change.

Microsoft Dynamics NAV 2009 Application Design A focused book and ebook tutorial for Microsoft Dynamics NAV application development
Published: June 2010
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

(Further on Microsoft Dynamics NAV:here.)

Analyse the object

The codeunit also has the Test Near, Test Far, Do it and Cleanup strategy so the first code is to make sure that everything is in place before the actual posting starts. Let's have a look at how this codeunit is structured.

The Sales-Post codeunit is too long to discuss in detail. We will focus on the most important parts and learning how to read this type of code routines.

This first part does the test near and a part of the test far. The Ship, Invoice and Receive fields are set in codeunit 81 and 82 but checked and completed to make sure:

Code()
...
WITH SalesHeader DO BEGIN
TESTFIELD("Document Type");
TESTFIELD("Sell-to Customer No.");
TESTFIELD("Bill-to Customer No.");
TESTFIELD("Posting Date");
TESTFIELD("Document Date");
IF GenJnlCheckLine.DateNotAllowed("Posting Date") THEN
FIELDERROR("Posting Date",Text045);


CASE "Document Type" OF
"Document Type"::Order:
Receive := FALSE;
"Document Type"::Invoice:
BEGIN
Ship := TRUE;
Invoice := TRUE;
Receive := FALSE;

END;

"Document Type"::"Return Order":
Ship := FALSE;
"Document Type"::"Credit Memo":
BEGIN
Ship := FALSE;
Invoice := TRUE;
Receive := TRUE;
END;
END;
IF NOT (Ship OR Invoice OR Receive) THEN
ERROR(...);
WhseReference := "Posting from Whse. Ref.";

"Posting from Whse. Ref." := 0;
IF Invoice THEN
CreatePrepaymentLines(...);
CopyAndCheckDocDimToTempDocDim;

The next step is to move the sales header information to the history tables for shipment, invoice, credit memo, or return receipt header. These sections are commented like this:


// Insert invoice header or credit memo header
IF Invoice THEN
IF "Document Type" IN ["Document Type"::Order,
"Document Type"::Invoice]
THEN BEGIN
SalesInvHeader.INIT;
SalesInvHeader.TRANSFERFIELDS(SalesHeader);

When this is done, the sales lines are processed. They are also moved to the various posted line tables. This is all part of the Do-it section of the posting routine.

// Lines
InvPostingBuffer[1].DELETEALL;
DropShipPostBuffer.DELETEALL;
EverythingInvoiced := TRUE;


SalesLine.RESET;
SalesLine.SETRANGE("Document Type","Document Type");

SalesLine.SETRANGE("Document No.","No.");
LineCount := 0;
RoundingLineInserted := FALSE;
MergeSaleslines(...);

If there is a drop shipment in a purchase order this is handled here.

// Post drop shipment of purchase order
PurchSetup.GET;
IF DropShipPostBuffer.FIND('-') THEN
REPEAT
PurchOrderHeader.GET(
PurchOrderHeader."Document Type"::Order,
DropShipPostBuffer."Order No.");

Then there is a section in the article that creates the financial information in the general journal:

IF Invoice THEN BEGIN
// Post sales and VAT to G/L entries from posting buffer
LineCount := 0;
IF InvPostingBuffer[1].FIND('+') THEN
REPEAT
LineCount := LineCount + 1;
Window.UPDATE(3,LineCount);
GenJnlLine.INIT;
GenJnlLine."Posting Date" := "Posting Date";
GenJnlLine."Document Date" := "Document Date";

Then the Clean up section starts by calculating remaining quantities, VAT, and deleting the sales header and sales lines:


IF ("Document Type" IN ["Document Type"::Order,
"Document Type"::"Return Order"]) AND
(NOT EverythingInvoiced)
THEN BEGIN
MODIFY;
// Insert T336 records
InsertTrackingSpecification;
IF SalesLine.FINDSET THEN
REPEAT
IF SalesLine.Quantity <> 0 THEN BEGIN
IF Ship THEN BEGIN
SalesLine."Quantity Shipped" :=
SalesLine."Quantity Shipped" +
SalesLine."Qty. to Ship";
SalesLine."Qty. Shipped (Base)" :=
SalesLine."Qty. Shipped (Base)" +
SalesLine."Qty. to Ship (Base)";
END;

The Clean up ends by deleting the sales document and related information, and clearing the variables used:

IF HASLINKS THEN DELETELINKS;
DELETE;
...
SalesLine.DELETEALL;
DeleteItemChargeAssgnt;
...
CLEAR(WhsePostRcpt);
CLEAR(WhsePostShpt);
...
CLEAR(WhseJnlPostLine);
CLEAR(InvtAdjmt);
Window.CLOSE;

Making the change

The change we are going to make is in the section where the lines are handled:

// Squash Journal Line
IF SalesLine."Applies-to Squash Entry No." <> 0 THEN
PostSquashJnlLn;

IF (SalesLine.Type >= SalesLine.Type::"G/L Account") AND
(SalesLine."Qty. to Invoice" <> 0)
THEN BEGIN
// Copy sales to buffer

We will create new function PostSquashJnlLn. This way we minimize the impact on standard code. And when upgrade to a newer version we can easily copy and paste our function, and only need to change the calling place if required.

Always try to design for easy upgrading whenever possible. Remember that Microsoft might change this code in newer versions so the more flexible we are and the more we minimize the impact on standard code the better.

PostSquashJnlLn()
WITH SalesHeader DO BEGIN
OldSquashLedEnt.GET(
SalesLine."Applies-to Squash Entry No.");
OldSquashLedEnt.TESTFIELD(Open);
OldSquashLedEnt.TESTFIELD("Bill-to Customer No.",
"Bill-to Customer No.");

SquashJnlLn.INIT;
SquashJnlLn."Posting Date" := "Posting Date";
SquashJnlLn."Reason Code" := "Reason Code";
...
SquashJnlLn."Document No." := GenJnlLineDocNo;
SquashJnlLn."External Document No." := GenJnlLineExtDocNo;
SquashJnlLn.Quantity := -SalesLine."Qty. to Invoice";
SquashJnlLn."Source Code" := SrcCode;
SquashJnlLn.Chargeable := TRUE;
SquashJnlLn."Posting No. Series" := "Posting No. Series";
TempJnlLineDim.DELETEALL;
TempDocDim.RESET;
TempDocDim.SETRANGE("Table ID",DATABASE::"Sales Line");
TempDocDim.SETRANGE("Line No.",SalesLine."Line No.");
DimMgt.CopyDocDimToJnlLineDim(TempDocDim,TempJnlLineDim);
SquashJnlPostLine.RunWithCheck(SquashJnlLn,TempJnlLineDim);
END;

Our new function first gets the squash ledger entry it applies to, and tests if it's still open and the bill-to customer no. has not changed.

Then we populate squash journal line with the help of the sales line and the old squash ledger entry.

Then dimensions are handled and the squash journal line is posted.

Note that the journal lines are never actually inserted into the database. This is for performance and concurrency reasons. All journal transactions here are handled in the service tier cache. A journal is also never populated using Validate. This makes it very clear to see what happens.

Now when we post an invoice we can see that the invoice entries are created:

Integrating the application with Relationship Management and Sales in Microsoft Dynamics NAV 2009

Navigate

We have now covered everything that is necessary for our Squash Court application to run but there is one special function of Microsoft Dynamics NAV that needs changing when we add new documents and ledger entries—the Navigate function.

The object is a single page (344) in the application that requires two changes.

FindRecords

The first function we change is FindRecords. This browses though the database finding all possible combinations of document no. and posting date.

FindRecords()
...
// Squash Ledger Entries
IF SquashLedgEntry.READPERMISSION THEN BEGIN
SquashLedgEntry.RESET;
SquashLedgEntry.SETCURRENTKEY("Document No.",
"Posting Date");
SquashLedgEntry.SETFILTER("Document No.",DocNoFilter);
SquashLedgEntry.SETFILTER("Posting Date",PostingDateFilter);
InsertIntoDocEntry(
DATABASE::"Squash Ledger Entry",0,
SquashLedgEntry.TABLECAPTION,SquashLedgEntry.COUNT);
END;
// Squash Ledger Entries


DocExists := FINDFIRST;

The function first checks if we have permission to read the Squash Ledger Entry table. If our system administrator does not allow us to see this table it should not show up.

The filtering is done on the Document No. and Posting Date. When ready the system inserts the number of found records in the result table.

ShowRecords

Second function to change is ShowRecords. This makes sure we see the squash ledger entries when we push the show action.


ShowRecords()
...
DATABASE::"Warranty Ledger Entry":
FORM.RUN(0,WarrantyLedgerEntry);
//* Squash Ledger Entries
DATABASE::"Squash Ledger Entry":
FORM.RUN(0,SquashLedgEntry);
END;
END;

Testing

Now when we navigate from invoice we posted that was generated from our combine invoicing report we get this result:

Integrating the application with Relationship Management and Sales in Microsoft Dynamics NAV 2009

Summary

In this article,we have learned:

  • how to integrate our solution with the standard application, in our case Sales Invoicing
  • how to structure codeunit of our application.
  • Navigate function-it needs changing when we add new documents and ledger entries

Further resources on this subject:

Microsoft Dynamics NAV 2009 Application Design A focused book and ebook tutorial for Microsoft Dynamics NAV application development
Published: June 2010
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Mark Brummel

Mark Brummel is an all-round Microsoft Dynamics NAV specialist. He started in 1997 as an end user but quickly moved to the other side of the table. For ten years he has worked for resellers, specializing in designing and maintaining add-on systems. Some of these add-on systems exceed the standard product in size and complexity. Also coaching colleagues and troubleshooting 'impossible' problems are a passion and part of his day-to-day work. Mark has trained most of the experienced NAV developers for the NAV 2009 product in The Netherlands and Belgium and he has been hired by almost every NAV reseller in the Benelux.

Mark works as a freelancer. His main focus area is helping out end users in implementations all over the world.
Mark was the first worldwide to use the NAV 2009 (CTP3) product in a production system, feeding back valuable information to Microsoft. Today he is still involved in projects to provide input for future versions and test new CTP releases.

A special project has been performance tuning of the Dynamics NAV product on SQL Server. From 2007 to 2009 he was involved in the development of the 'SQL Perform Tools' as business partner of SQL Perform Benelux. As a unique specialist he has done break-through research in improving the performance of Dynamics NAV on SQL Server.

When time is left, Mark maintains his blog on www.brummelds.com. This blog contains a wide range of articles about both the Microsoft Dynamics NAV and SQL Server products. He is also a frequent speaker at Microsoft events and writer for independent Dynamics NAV websites and user groups. In 2006 Mark was rewarded by Microsoft with the Most Valuable Professional award for his contribution to the online and offline communities. In 2007 and 2009 he reviewed Programming Microsoft Dynamics NAV and Programming Microsoft Dynamics NAV 2009.

Books From Packt


Joomla! 1.5: Beginner's Guide
Joomla! 1.5: Beginner's Guide

Refactoring with Microsoft Visual Studio 2010
Refactoring with Microsoft Visual Studio 2010

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

Microsoft Silverlight 4 Business Application Development: Beginner’s Guide
Microsoft Silverlight 4 Business Application Development: Beginner’s Guide

Programming Microsoft Dynamics NAV 2009
Programming Microsoft Dynamics NAV 2009

Microsoft Dynamics AX 2009 Programming: Getting Started
Microsoft Dynamics AX 2009 Programming: Getting Started

Oracle JRockit: The Definitive Guide
Oracle JRockit: The Definitive Guide

Microsoft Dynamics AX 2009 Development Cookbook
Microsoft Dynamics AX 2009 Development Cookbook


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
6
a
Q
u
8
x
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software