Sign Up for Training |
Jet Global Company Site
Community
Downloads
Training
Submit a Request
Become a Jet Insider
Give Feedback

The "Period Name" field is not present in the "Date" table when using a NAV SQL Server connector


Jet reports which have been designed with the NAV Web Services connector and which use the Period Name field in the Date table will need to be modified when switching to the NAV SQL Server connector.

It will be necessary to modify these reports to use the Period No. field instead of the Period Name field. The Period No. field contains the same information as Period Name, just in a numeric format.

This is only an issue when the period type is Date or Month.  The following Excel formulas can be used to convert these values into period names:

 

Where cell B2 = 1

Period Type Formula Output
Date =TEXT(B2+1,"dddd") Monday
Month =TEXT(DATE(2014,B2,1),"mmmm") January

Background

The NAV 2013 SQL connector for Jet Essentials provides substantial performance increases compared to a NAV Web Services data source connector.

Connections that directly use the NAV system (such as the Web Services connector for NAV 2013 and the NAV C/Front connector for NAV 2009 R2 and earlier) have access to NAV's virtual "Date" and "Integer" tables.  Virtual tables are created by NAV in memory, rather than being stored in NAV's database.  Included in the "Date" table is the "Period Name" field that is populated with language-specific values based on the language setting within NAV.

The Jet NAV SQL Server connector does not directly utilize NAV but, rather, retrieves data directly from the SQL database.  For convenience and backward compatibility, Jet Global has also created a separate database with an Integer table as well as a Date table that includes all fields found in NAV's virtual date table (with the exception of the "Period Name" field).   Instead of querying the NAV SQL database for these 2 tables, Jet queries its own database.  Since this database is the same for all users, it it not possible to include all, possible, language-specific "Period Name" field values.

 

 
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments