Using ODBC with Navision & Serenic

Following are instructions for setting up ODBC drivers in Navision or Serenic Navigator Software 3.7. Please note that a new ODBC driver is now available for Navision 4.0 and Serenic Navigator 4.0 as of May 2005. - J. Carlton Collins

Introduction

What is ODBC?

ODBC is an abbreviation for Open DataBase Connectivity, a standard database access method developed by Microsoft Corporation.  The goal of ODBC is to make it possible to access any data from any application regardless of which database management system (DBMS) is handling the data.  ODBC manages this by inserting a middle layer called a database driver between an application and the DBMS.  The purpose of this layer is to translate the application’s data queries into commands that the DBMS understands.  For this to work, both the application and the DBMS must be ODBC-compliant – that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them. 

What is C/ODBC?

C/ODBC is Navision Software’s implementation of Open DataBase Connectivity (ODBC).  C/ODBC lets you transfer data between a Navision Financials database and any program that supports ODBC.

C/ODBC simply provides other applications that support ODBC, a method of communicating with Navision Solutions.  Hence applications such as a spreadsheet or a word processing program can retrieve needed data by communicating with Navision Solutions through C/ODBC driver.

Navision provides the driver that allows the Window’s ODBC manager to connect to the Navision Database and retrieve and/or update data within that Database. You can install this ODBC Driver from the Navision Product CD.  To tell ODBC which driver to use and to specify the database and company you want to connect to, you must create a Data Set Name (DSN) within the application you are using or within the ODBC manager itself.

Why use ODBC?

Integration with other applications is the main reason for using ODBC with Navision.  Through the use of ODBC you can use other applications such as a word processing program to create mail merge documents or a spreadsheet application to pull in account information that you wish to graph.

Installation

In order to use ODBC you must first install the C/ODBC driver.  Drivers are the components that process ODBC requests and return the data to the application.  If necessary, drivers modify an application’s request into a form that is understood by the data source.

Follow this procedure to install the C/ODBC driver:

*Please note that the client and the C/ODBC version must be the same.

Start the setup program  (To start the program insert the Navision Financials Software disc, Click on Tools, C/ODBC and then Click on the installation for C/ODBC for Windows 98, Windows NT 4.0 (Intel), Windows 2000 and Windows XP and the following window will appear:

Ø      Click Next and the following dialog box will appear:

Ø      Enter the User Name and Organization and click Next.

This dialog box will now appear:

Ø      Click Install and the program will install C/ODBC. 

The files will be copied to the windows\system folder on Windows 98 computers, winnt\system32 on windows NT and Windows 200 computers, C/ODBC is registered and a sample C/ODBC data source is installed.

The installation is complete when the following dialog box appears:

Ø      Click Finish to close the program.

Ø      You may or may not need to restart your computer.

Setting Up a Data Source

Once you have installed all the files, you are now ready to create a Data Source Name or DSN.  Data Sources are the databases or files accessed by a driver and are identified by a data source name (DSN).  Use the ODBC Data Source Administrator to add, configure, and delete data sources from your system. 

Types of Data Sources:

·         User DSN – Stores information about how to connect to the Data Provider.  A User DSN is only visible to you, and can only be used from the current machine.

·         System DSN – Stores information about how to connect to the Data Provider.  A System DSN is visible to all users on this machine.

·         File DSN – Allows you to connect to the Data Provider.  A File DSN can be shared by all users who have the same drivers installed.

To set up a DSN:

Ø      Go to your Control Panel and select Administrative Tools (this name may be different depending on the version of windows that you are using).

Ø      Click on Data Sources (ODBC).

 The following screen will appear:

The User DSN is used when you only want certain users on this machine to see the DSN.  A User DSN is what’s created by the C/ODBC example that gets installed.  We are going to use a System DSN. 

Ø      Select the System DSN tab and click the Add button.

The following screen will appear:

Ø      Select C/ODBC 32 bit driver and click Finish.

The system will now prompt you to enter the following information:

C/ODBC Setup

The previous example is for a local connection as used for our class exercises.  More than likely you will have to set up a connection to a server when you set this up back at your office.  When connecting to a server your set up may look like this:

Options

When you setup the C/ODBC driver, you should consider the options that can influence the performance of C/ODBC. 

C/ODBC Options

**Important Note:  Always set up the data source to be a READ ONLY connection.  We do not support or recommend writing to the database since the data is not validated.

Security

As mentioned in the table above it is recommend that you leave the User ID and Password in the C/ODBC setup blank to ensure that only users that you wish to connect to Navision are allowed to connect.  Furthermore, Navision will ensure that the users only have access to those areas that you’ve granted them access.  In other words, whether accessing the database from within Navision or outside Navision using ODBC, the user will only be able to access the tables as already defined in your Security. 

You will need to create roles for the C/ODBC users that are not SUPER users. 

To set up a new role:

Ø      Click on Tools, Security, Roles.

Ø      Add a new role called C/ODBC, C/ODBC Users or whatever ID and name you wish to use.

Ø      Assign the role the following permissions:

These permissions will allow users to execute the C/ODBC drivers and retrieve data using applications outside Navision. 

Ø      You will need to assign this role to anyone who will be using ODBC that is not a SUPER user.

Queries

After you have configured your C/ODBC driver, you will be able to query the Navision Solutions database. 

What is a query?

 A query is a means of finding the records that answer a particular question you ask about the data stored in a data source.

Microsoft Query

Microsoft Query is a query program that can be used to connect programs and databases.  In mail merge, we use Microsoft Query to retrieve information from a Navision Financials database and deliver it to a Microsoft Word document.  In Microsoft Excel, we use Microsoft Query to run or create a database query and return the data to a spreadsheet.

It may be helpful read the Help section in order to better understand Microsoft Query and all the various features it offers.  This will assist you in retrieving data in a more efficient manner.

Class Exercise 1:  Create a Report in Excel using One Navision Table

We are going to create an Open Invoice Report in Excel using the only one table from Navision. 

Ø      Start Microsoft Excel and Open a New Worksheet.

Ø      Click on Data, select Get External Data, and then select New Database Query.

Ø      Choose the Data Source and click OK.

The following Query Wizard (which is part of Microsoft Query) will now appear:

The most difficult part of using ODBC is knowing which tables in Navision to pull the data from.  Before using Query, it is suggested that you map out the data that you wish to query field by field.  Once you have decided what information is needed, go into Navision and find the data, noting where the data is located.

Ø      Find the table named Cust_Ledger Entry and double click on it to see a listing of all the fields in this table.

Ø      Choose the columns as shown above (Customer No_, Due Date, Document Type, Document No_, Document Date, Amount, and Open) and click Next.

 Next we will need to determine what data we want to filter on:

In this example, we only want to see the open invoice entries, so we will set our filters as shown above.

Ø      Click on Open located in the Column to filter.  (Notice how Open will appear in the Only include rows where section.) 

Ø      Click the drop down box and select equals and enter a 1.  (False = 0 and True = 1)

Ø      Click on Document Type and enter equals Invoice and click on Next.

Now we want to determine how we would like the data to be sorted.

Ø      Enter Customer No_ in Sort by and enter Due Date in Then by.

Ø      Click Next.

The Query Wizard will now ask whether we want to return the data to Excel or if we would like to view and/or edit the query.

Ø      Select View data or edit query in Microsoft Query.

Ø      Click Finish.

The query will look something like this:

Ø      After you have verified the query click on File and select Return Data to Microsoft Excel.

Your data will be displayed in Microsoft Excel and you may format the spreadsheet as desired.  In this case we added another column called Days which will display the days past due.

Ø      Save your spreadsheet as Class Exercise 1.

Class Exercise 2:  Create a Report using Two Navision Tables

In this example, we would like to create a report that shows customer sales orders that have been shipped but not invoiced.  As you may or may not know Navision stores sales order information in two tables:  the Sales Header and Sales Line tables.

Ø      Start Microsoft Excel and Open a New Worksheet.

Ø      Click on Data, Get External Data, and Create New Query

Ø      Select the Data Source and click OK.

We are going to be using fields from two different tables. 

Ø      From the Sales Header table choose the following fields:  No., Sell-to Customer No.,  Sell-to Customer Name, Posting Date, and Order Date.

Ø      From the Sales Line table select the following fields:  Type, No., Description, Qty_Shipped Not Invd_, and Shipped Not Invoiced ($).

Ø      Click Next and click Next again.

Ø      Choose No. and Ascending for your sort order.

Ø      Click Save Query and name the query Class Exercise 2.

Ø      Select View data or edit query in Microsoft Query and click Finish.

The query will look something like this:

When using two tables, Microsoft Query will automatically search the fields in each table and look for names that match. It will then create a join for that match.  This is because it needs to find a common field in order to link or join the two tables together.

In this example, the system found No. in the Sales Header table and No. in the Sales Line table.  We know that No. in the Sales Header table is the sales order number and No. in the Sales Line table is the item/resource/account number.  Therefore, these two fields are not common fields, and we will need to delete this join and create a new one.

Ø      Click on the line that joins the two tables, so that it is highlighted and click delete.

Ø      Find No_ in the Sales Header table and Document No_ in the Sales Line table, so that they are visible in each table.

Ø      Click on No_, hold down the mouse button and drag it to the Document No_ field and let go of the button.  You should now have a line connecting the two fields, and data should appear in your query.

Next, we only want to show sales order lines that have been shipped but not invoiced.  To do this we are going to add Criteria to the query.  Criteria is Microsoft Query’s equivalent to a Navision filter.

Ø      Click on Criteria, Add Criteria.

Ø      Fill in the following:

Hint:  The Field: field displays the name of the table, a period, and then the field name.  For example, Sales Line.Qty_Shipped Not Invoiced.  The name may also be difficult to see since it extends past the window.  Therefore, it is best to click the column that you wish to filter on before adding criteria thereby omitting the need to search for and select the field name.

Ø      Click Add and then Close.

The query will now only show the invoice lines do not have a quantity of zero in the Qty. Shipped Not Invoiced field.  We also could have accomplished the same thing by using the filter when setting up the initial query.  To add additional criteria you can click on Criteria, Add Criteria or you can click in the Criteria Field Box to add more filters.

To remove a field:

Click on the column and press Delete or click on Records, Remove Column.

Ø      Delete the column labeled Type.

Helpful Hint:

This tip was taken from the Microsoft Query help:

Microsoft Query automatically runs a query and refreshes the data in the result set each time you add a field to the Data pane or add or change criteria in the Criteria pane. If your query contains a large number of records, you might want to wait to run the query until you've finished making changes to the query.

·          To run a query automatically each time you add new criteria or a field, make sure that the Auto Query button is pressed in.

·          To wait to run a query until after you've finished adding new criteria or fields, make sure that the Auto Query button is not pressed in. When you're ready to run the query, click Query Now.

To add a field:

There are several ways to add a field to the query:  one is to select Records, Insert Column and add the field and another is to go to a blank column at the end of the query and using the drop-down box choose the field.  However the quickest way is to double click on the field name in the table, which adds it to the query.

Ø      Double click on the Type field in the Sales Line table to add it back to our query.

You may also move fields around in the query.  To move a field, while holding down the left mouse button position your cursor on the field header.  You will see a rectangle if you are doing this correctly.  Now move the column where you want it and let go of the button.

Ø      Move the Type field in front of the Description field.

We are now ready to look at our data in Excel.

Ø      Save the Query.

Ø      Click on File, Return Data to Microsoft Excel.

As you can see Query is displaying the Order and Posting dates as dates and times.  You can format the date in Excel to only display the date and have the system preserve this formatting upon refresh. (See Tip #2 below.)

Ø      Format the field names and dates in your worksheet and save as Class Exercise 2.

Additional Tips

Tip # 1:

In Class Exercise 1 we did not save the query.  You can however, open the spreadsheet and refresh the data.  To do this:

Ø      Open the Class Exercise 1 spreadsheet

Ø      Click on Data, Refresh Data.

Tip # 2:

It may also be helpful to read over the External Data Properties that can be found by clicking on

Data, Get External Data, Data Range Properties and the following will appear:

Using the Refresh control options you can have Excel Refresh the query at set intervals or you have the ability to Refresh your data automatically whenever the file is opened.

Data formatting and layout can be used to preserve field names, column layouts, and cell formatting.  This is useful when using date fields, as Navision returns a date and a time.  The cell can be formatted to only show the date

Tip #2:

In Class Exercise 2, we saved the query.  Therefore, you will have the capability to reuse the query in another spreadsheet without having to recreate the query.  This may be useful if you know that you may reuse the query by just making a few minor changes.

Class Exercise 3:  Create Mailing Labels in Microsoft Word

For this exercise, we are going to create some mailing labels using Avery labels, so that we can mail some pre-printed information to our customers.

Ø      Start Microsoft Word and Open a New Document.

Ø      Select Tools, Mail Merge, and the Mail Merge Helper will appear:

Ø      For Step 1, click Create, Mailing Labels, and select Active Window.

Ø      For Step 2, click Get Data, Open Data Source, and click on the MS Query button located in the dialog box.

Ø      Choose a Data Source and click OK.

Ø      Find the Customer table and choose the following fields:  Name, Address, Address 2, City, State, and Zip Code.

Ø      Hit Next, Next, Next, and Finish.

Ø      Click Set Up Mail Document and choose Laser and ink jet.

Ø      Select Avery Standard labels number 5160 and click OK.

Ø      Now click on Insert Merge Field and arrange the fields in an address format. 

Ø      Hit OK and click Close.  The document will now be formatted.

Ø      To have Word read the database, click the View Merged Data icon (<<ABC>>) located on the toolbar.

Ø      Save the document as Labels.

Class Exercise 4:  Create a Form Letter in Microsoft Word

In this example, we are sending a letter to all of our customers regarding their tax exemption number.

Ø      Start Microsoft Word and Open a New Document.

Ø      Select Tools, Mail Merge, and the Mail Merge Helper will appear.

Ø      For Step 1, click Create, Form Letter, and select Active Window.

Ø      For Step 2, click Get Data, Open Data Source, and click on the MS Query button located on the right side of the file open dialog box.

Ø      Choose a Data Source and click OK.

Ø      Find the Customer table and choose the following fields:  Name, Address, Address 2, City, State, Zip Code, Contact, Tax Liable, and Tax Exemption No.

Ø      Click Next and Filter on Tax Liable = 0 and Tax Exemption No_ = ‘’ and click Next.

Ø      Sort by the Customer Name and click Next.

Ø      Click Save Query and name it Form Letter.

Ø      Select View data or edit query in Microsoft Query and Finish.

Ø      Add the Salesperson/Purchaser table and join a common field.

Ø      Add Name from the Salesperson/Purchaser table to the query by double-clicking on it.

Ø      Click on File, Save and save the query as Form Letter.

Ø      Select File, Return Data to Microsoft Word.

Ø      Click Edit Main Document and type the following letter, inserting the Merge fields as you type:

June 1, 2003

«Name»

«Address»

«Address_2»

«City», «State» «ZIP_Code»

Dear «Contact»,

According to our records we do not currently have your Tax Exemption number on file.  Please take a moment to complete the attached document and return as soon as possible. 

Please feel free to contact me if you have any questions.

Sincerely,

«Name1»

CRONUS USA, Inc.

Now we are going to merge the text from the form letter with the data from Navision.  To do so:

Ø      Click on Tools, Mail Merge, and press the Merge button under Step 3. 

A dialog box will appear that lets you start or interrupt the mail merge.  You can also choose to send the result to the printer or to a new document that contains all the letters.  (You can save this document as a template.)

Ø      Choose Merge to New document and click Merge.

The information will now be merged and you will have two documents.  One is the form letter or template and the other contains the actual letters to be printed and mailed.  If you choose, you can browse through the letters and add information before you print them.

Ø      Save the template as Form Letter Template.

Ø      Save the letters as Form Letters.

Class Exercise 5:  Accessing Navision Data in Microsoft Access

Ø      Start Microsoft Access, select Blank Database, and then click OK.

Ø     

Name the new database Class Example and click Create.  The new database will appear.

Ø      Select File, Get External Data, and Import.

Ø      At the bottom of the Import dialog box you will see Files of Type.  Click on the drop down box and select ODBC Databases(), and the Select Data Source dialog box will appear.

Ø      Choose the Data Source and click OK.  The following dialog box will appear:

Ø      Select the Customer table and click OK.  The system will now import the data from the Navision Customer table into an Access table.

If you select Open, you will see the data within the table.

Ø      Use Microsoft Access as you normally would.

Exercise 1:  Create a Report in Excel

Our sales manager wants to see a Resource Sales report for Thomas Stevenson.  Specifically the sales manager wants to see a list of invoices by customer that have purchased his services this year.  Include the invoice quantity and amount.  Create a report showing him this information. 

The report should look something like this:

Hints:

·         We will be using the Sales Invoice Header and Sales Invoice Line tables. 

·         When joining the tables you will need to find the common field.  What field will link the two tables?

·         You will need to filter on a least two fields.

·         Formatting can be done in Excel.

Exercise 2:  Create a Form Letter in Word

We would like to send out a letter to all of our 1099 vendors updating their Federal ID No. for tax purposes. 

Create the following Form Letter:

June 4, 2003

«Contact»

«Name»

«Address»

«Address_2»

«City», «State» «ZIP_Code»

Dear «Contact»,

We are currently updating our records and would like to verify the following Federal ID No.:«Federal_ID_No_».  If the Federal ID No. listed is incorrect or blank, please notify us immediately with the correct number.

Thank you,

Mary Smith

Accounts Payable

CRONUS USA, Inc.

Hints:

Ø      Use Mail Merge.

Ø      Save the Form Letter (template) for future use.

Exercise 3:  Create the same Report Used in Exercise 1 in Access.

Create the same report that we designed in Microsoft Excel in Microsoft Access. 

The report should look something like this:

Hints:

Ø      Import the two tables first.

Ø      In Queries, use the Simple Query Wizard to filter, sort, and add a relationship for the tables.

Ø      Use the Report Wizard to design your report. 

Ø      When selecting the table in the Report Wizard to pull data from, you will use the query you created and not the original tables.

Ø      Don’t forget to sum your fields.