Category: Solutions

The problems and solutions in Dynamics NAV code

Posted on

Import from ODBC into Dynamics NAV

Importing data from ODBC database into Dynamics NAV is a regular task.  The most common problem is where the external database has data types and null values that are not supported by Dynamics NAV.

I have solved this with a function that builds the query command.  The function selects all fields from the table and then loops through them, checks the data type and builds an new query command using both “ISNULL” and “CONVERT” functions.

The attached example is where I import customers and vendors from Pervasive database with DK business data.  The create function requires the table name and the primary key.  If the external table has to many fields to fit into one query command more queries will be created for remaining fields along with the primary key.

Here you can download a ZIP file with the example codeunit.

Posted on

Collapse and Expand in Classic Client

In our Payroll design work I have tried a new solution inspired by the Role Tailored Client. I want to be able to collapse and expand the tab control to give more space for the subform.

I put a button with an up arrow and another with a down arrow on the form.  I also put a menu item in the Functions menu button to toggle between collapse and expand with a shortcut key Alt-F6, the same key that is used in RTC.

The buttons
[code]{ 57;CommandButton;15400;880;550;550;Name=DownBtn;
HorzGlue=Right;
Focusable=No;
ParentControl=<TabControlID>;
InPage=0;
ShowCaption=No;
Bitmap=1863;
OnPush=BEGIN
UpdateTabs(NOT TabExpanded);
END;
}
{ 58;CommandButton;15400;880;550;550;Name=UpBtn;
HorzGlue=Right;
Focusable=No;
ParentControl=<TabControlID>;
InPage=0;
ShowCaption=No;
Bitmap=1862;
OnPush=BEGIN
UpdateTabs(NOT TabExpanded);
END;
}[/code]
The function
[code]PROCEDURE UpdateTabs@51(Expanded@1 : Boolean);
BEGIN
TabExpanded := Expanded;
IF Expanded THEN BEGIN
CurrForm.HeadTab.HEIGHT := 6160;
CurrForm.BatchPanel.HEIGHT := 6160;
CurrForm.WageEarnerLines.YPOS := 6490;
CurrForm.WageEarnerLines.HEIGHT := CurrForm.HEIGHT – 7370;
CurrForm.DownBtn.VISIBLE := FALSE;
CurrForm.UpBtn.VISIBLE := TRUE;
END ELSE BEGIN
CurrForm.HeadTab.HEIGHT := 1320;
CurrForm.BatchPanel.HEIGHT := 1320;
CurrForm.WageEarnerLines.YPOS := 1650;
CurrForm.WageEarnerLines.HEIGHT := CurrForm.HEIGHT – 2530;
CurrForm.DownBtn.VISIBLE := TRUE;
CurrForm.UpBtn.VISIBLE := FALSE;
END;
END;[/code]
Where 7370 is the space on the form needed for other objects when the tab control is expanded and 2530 when the tab control is collapsed.

And in OnOpenForm trigger
[code]OnOpenForm=BEGIN
UpdateTabs(TRUE);
END;[/code]
And finnally the Global Variable
[code]TabExpanded@53 : Boolean;[/code]

Posted on

Added to Job Queue E-Mail

Since the report that I posted earlier simply sends an PDF copy of the Invoice and Credit Memo to the recipient via SMTP mail you do not have a copy of that E-Mail in your inbox or sent items.

I added a single line of code to send a carbon copy of the E-Mail to the sender.

[code]

IF CompanyInformation."Ship-to Contact" <> ” THEN BEGIN
SMTPMail.AppendBody(CRLF);
SMTPMail.AppendBody(Text004);
SMTPMail.AppendBody(CRLF);
SMTPMail.AppendBody(CompanyInformation."Ship-to Contact");
SMTPMail.AppendBody(CRLF);
END;

SMTPMail.AddCC(CompanyInformation."E-Mail"); // Add this line
SMTPMail.AddAttachment(Directory + FileName);
SMTPMail.Send;
[/code]

Posted on

E-Mailing Invoices and Credit Memos with Job Queue

This solution uses PDFCreator to automatically email all invoices and credit memos to customers that have an email address entered.

The first step is to download PDFCreator and install on the machine running NAV Application Server.  Then you start PDFCreator – PDF Print Monitor.

Go to Printer and Options (Ctrl+O).  Under General Settings 2 you change the Check Update interval to Never.

This will make sure that an update question will not interrupt the Job Queue execution.  You need to add an Integer field “No. E-Mailed” to tables 112 Sales Invoice Header and 114 Sales Cr. Memo Header.  In here this field is number 10017243 as this field is a part of a solution sold as granule no. 10,017,060 by Advania.  You should be able to create this field in another number and change the following solution accordingly.

This solution uses the Name, E-Mail address, Ship-to Contact in Company Information and sends the email via SMTP.  You will need to name the correct email server in table 409 SMTP Mail Setup.

Here is a ZIP file with the NAV Object.

The last step is to create a Job Queue Entry for a recurring job that executes report 50001 Job Queue E-Mailer.  I choose to execute the task every 240 minutes from eight in the morning to eight in the evening.

Posted on

Import G/L Journal from Excel

Image being able to send your staff an Excel document and get it returned with data for your General Journal.  Data from outside sources are usually available in Excel.  By using this batch, you can import that data into NAV General Journal.

Clicking on the “Create Template” button will start Excel with a document template.

On each cell in the header line there is an comment that helps with the data format.

Here is a zip file with the source code needed.

Posted on

Job Queue Execution

The Job Queue granule (3,810) in version 5.0 and later can be used to automate tasks by starting Nav Application Server with the parameter JOBQUEUE.  Everyting that Job Queue starts is started with a record in the table Job Queue Entry.  However, there are built in batches, like report 795, Adjust Cost – Item Entries, that Job Queue can start with some help.  Here is a Report that I use to start built in batches from Job Queue.

Here is a zip file with the code needed.