If you are like me, located in a non-English speaking country and would like users to be able to use other tools then the Dynamics NAV clients to access the company data you will find that all the meta data in the database is in English. This means that you will have to translate the fields and sometimes the data to your language.
The problems are:
- Field captions are unavailable in the MS SQL database
- Option values are shown as number
- Boolean is shown as number
- Global dimension do not have the correct caption
- Time is shown as DateTime
- Not easy to see the difference between normal date and closing date
The solution that I am using is to create a separate database on the same database server and create localized views in that database. What you get with the solution is:
- A date table that can be used to show all properties of a given date
- A import of the option value captions
- A selection of tables to make accessible
- A batch job to create the localized SQL view for each table
Here is a list of the fields in the date table
Field No. | Field Name | Data Type | Length |
1 | Date | Date | |
2 | Date Name | Text | 30 |
3 | Year | Integer | |
4 | Week | Integer | |
5 | Month | Integer | |
6 | Month Name | Text | 20 |
7 | Day of Week | Integer | |
8 | Day Name | Text | 20 |
9 | Day of Month | Integer | |
10 | Closing Date | Boolean | |
11 | SQL Month Name | Text | 20 |
12 | SQL Day Name | Text | 20 |
13 | Quarter | Integer | |
14 | Year Month | Integer | |
15 | Year Month Name | Text | 20 |
16 | Month Year Name | Text | 20 |
17 | Quarter Name | Text | 20 |
18 | VAT Period | Integer | |
19 | VAT Period Name | Text | 20 |
20 | Sorting Date | Integer | |
21 | HRMS Integer Start | Integer | |
22 | HRMS Integer End | Integer | |
23 | Day of Year | Integer | |
24 | Day of Half Year | Integer | |
25 | Day of Quarter | Integer | |
26 | Day of Accounting | Integer | |
27 | Half Years | Integer | |
28 | Half Year of Year | Integer | |
29 | Is Holiday | Boolean | |
30 | Is Working Day | Boolean | |
31 | Month of Half Year | Integer | |
32 | Month of Quarter | Integer | |
33 | Month of Year | Integer | |
34 | Quarters of Half Year | Integer | |
35 | Quarters of Year | Integer | |
36 | Week of Year | Integer | |
37 | Is Week Day | Boolean | |
41 | Half Year Name | Text | 20 |
42 | Week Name | Text | 20 |
102 | Fiscal Day | Date | |
103 | Fiscal Year | Integer | |
104 | Fiscal Week | Integer | |
105 | Fiscal Month | Integer | |
107 | Fiscal Day of Week | Integer | |
109 | Fiscal Day of Month | Integer | |
113 | Fiscal Quarter | Integer | |
123 | Fiscal Day of Year | Integer | |
124 | Fiscal Day of Half Year | Integer | |
125 | Fiscal Day of Quarter | Integer | |
127 | Fiscal Half Years | Integer | |
128 | Fiscal Half Year of Year | Integer | |
131 | Fiscal Month of Half Year | Integer | |
132 | Fiscal Month of Quarter | Integer | |
133 | Fiscal Month of Year | Integer | |
134 | Fiscal Quarters of Half Year | Integer | |
135 | Fiscal Quarters of Year | Integer | |
136 | Fiscal Week of Half Year | Integer | |
137 | Fiscal Week of Month | Integer | |
138 | Fiscal Week of Quarter | Integer | |
139 | Fiscal Week of Year | Integer | |
140 | Fiscal Quarter Name | Text | 20 |
141 | Fiscal Half Year Name | Text | 20 |
142 | Fiscal Week Name | Text | 20 |
143 | Fiscal Month Name | Text | 20 |
Lets take the G/L Entry table as an example. In the MS SQL the field names are in English and the data not readable for the normal user.
SELECT [Entry No_],[G_L Account No_],[Posting Date],[Document Type] ,[Document No_],[Description],[Bal_ Account No_],[Amount] ,[Global Dimension 1 Code],[Global Dimension 2 Code],[User ID] ... ,[FA Entry Type],[FA Entry No_] FROM [Dynamics NAV Demo Database (IS 2009 R2)].[dbo].[CRONUS Ísland hf_$G_L Entry]
and for example the [Document Type] will show as numbers. Here is an example of the output of my tool to create a localized view for this table.
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.ISL$CRONUS Ísland hf_$Fjárhagsfærsla')) DROP VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla] GO CREATE VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla] AS SELECT [Entry No_] As [Færslunr_] ,[G_L Account No_] As [Fjárhagsreikn_nr_] ,[Posting Date] As [Bókunardags_] ,CASE [Document Type] WHEN 0 THEN ' ' WHEN 1 THEN 'Greiðsla' WHEN 2 THEN 'Reikningur' WHEN 3 THEN 'Kreditreikningur' WHEN 4 THEN 'Vaxtareikningur' WHEN 5 THEN 'Innheimtubréf' WHEN 6 THEN 'Endurgreiðsla' END As [Tegund fylgiskjals] ,[Document No_] As [Númer fylgiskjals] ,[Description] As [Lýsing] ,[Bal_ Account No_] As [Mótreikningur nr_] ,[Amount] As [Upphæð] ,[Global Dimension 1 Code] As [Deild Kóti] ,[Global Dimension 2 Code] As [Verkefni Kóti] ,[User ID] As [Kenni notanda] ,CASE [System-Created Entry] WHEN 1 THEN 'Já' WHEN 0 THEN 'Nei' END As [Kerfisfærsla] ... ,CASE [FA Entry Type] WHEN 0 THEN ' ' WHEN 1 THEN 'Eignir' WHEN 2 THEN 'Viðhald' END As [Eignafærslutegund] ,[FA Entry No_] As [Eignafærslunr_] FROM [CRONUS Ísland hf_$G_L Entry] GO
Executing this will give me a view in my database that I can use to fetch localized data from the G/L Entry table with all the above problems solved.
By using the additional date table as dimension in OLAP or as join in a SQL query I can easily find all aspects of the “Posting Date” in the G/L Entry table and group entries accordingly.