This week, the pattern is familiar to most C/AL developers, but if you are new to Microsoft Dynamics NAV, or if you need a refresher, here is the pattern behind journal templates, batches, and lines.
The role of a journal line is to temporarily hold transaction data until the transaction is posted. Before posting, the entries are in a draft state, which means that they are available for corrections and/or deletion. As soon as the entries are posted, they are converted to ledger entries.
Journal templates are used to specify the underlying journal structure and to provide the default information for the journal batches. Journal batches usually serve to group journal lines, such as lines created by two different users.
Journal templates and journal batches are used if there is a need to create and post one or more entries. They are implemented in multiple areas of the application, like Sales, Purchases, Cash Receipts, Payments, Fixed Assets1.
The journal templates are located on the Journal Template page. A Journal Template definition contains a series of attributes, such as:
The Journal Template table stores the relevant attributes that define the nature and behavior of the journal templates, for example:
Journal Template Table Field
Test Report ID
The journals offer the possibility of running test reports3. The role of a test report is to simulate the posting process. The verification criteria for the journal lines is ran, and the report can be displayed, all without doing the actual posting. This helps finding and correcting any errors that might exist in the data.
The name of the test report is the same with the name of the corresponding journal, plus the suffix " - Test". For example, the General Journal has the associated test report named General Journal - Test.
Posting Report ID
This report is printed when a user selects Post and Print4.
For some journals, more UI objects are required. For example, the General Journals have a special page for bank and cash.
Here you can enter a Trail Code for all the postings done through this Journal4.
Whenever you post lines from a recurring journal, new lines are automatically created with a posting date defined in the recurring date formula.
Each journal template defines a default value of those attributes. The values that are defined in a template will be inherited by the journal batches, which will be created from a journal template.
Microsoft Dynamics NAV is released with a number of standard journal templates predefined in the Journal Templates page. More templates can be defined by the users.
Journal batches are created with the help of the journal templates.
A journal batch is typically used to make a distinction between collections of logically grouped journal lines. A typical design is to have a journal batch for each user who enters lines. The batches are used during the posting process, in order to post one or multiple lines at once.
Journal lines contain the actual business data (posting dates, account numbers, amounts) that will be posted as ledger entries.
During posting, only the information from the journal lines is needed. However, the information has been created with the help of the journal templates and grouped together using the journal batches.
Posting creates ledger entries from the temporary content that is stored in the journal lines. Ledger entries are not created directly. Instead, they are posted from journal lines.
There is a 1:n aggregation relationship between journal templates and journal batches, as well as between journal batches and journal lines. Deleting a template will cascade deletion of the related batches and lines. Deleting a batch will cascade into deletion of related lines.
Read more on NAV Patterns Wiki...
Bogdana Botez at Microsoft Development Center Copenhagen
This pattern shows a solution for integrating W1 features to pre-existing country features that use different tables to achieve similar functionality.
It sometimes happens that certain features are requested in a country/region that is supported by Microsoft, but they are not initially considered generic enough to be included in the W1 build. This is how local features, such as Subcontracting in Italy and India, were created or specific banking and payments functionality in Italy, France, Spain, and others.
Then, at some point in time, a decision is made to create a W1 feature that is closely related to the local functionality but uses a completely different set of tables, pages, etc. The developers now face the following problem: How to enable the newly-developed W1 feature into a country, such that the customers who are accustomed to their local structures can seamlessly continue working without completely (or immediately) switching to the W1 objects.
This was the issue that was tackled in the Microsoft Dynamics NAV 2013 R2, in relation to the SEPA Credit Transfers functionality.
The generic Proxy pattern is "a class functioning as an interface to something else" (Wikipedia).
Figure 1. Proxy in UML
The Microsoft Dynamics NAV data model translation of the proxy pattern can be used as explained below.
In the diagram, RealSubject is the Microsoft Dynamics NAV data model. Variations in table structures, relationships, and numbers are particular to each country. The W1 model is the base for the localized data models. However, some countries have heavy localizations which cannot be directly processed by the W1 core objects.
The proxy is a codeunit that gathers data from wherever it is stored and transforms it to fit into a standard table, which is later used across all localizations.
The interface is the fixed form in which the data is presented to be consumed by the client.
The client can be an XML port that is fed from the common data interface. It can also be any other data processor (a codeunit fed to another table, etc.) or data display object (page or report).
In Microsoft Dynamics NAV 2013 R2, Continue reading on NAV Patterns Wiki...
Bogdan Sturzoiu at Microsoft Development Center Copenhagen
Many of you that have installed Microsoft Dynamics NAV 2013 R2 have for sure noticed that the quick filter behavior has been redesigned in this release and that is why, in this post, we will go through all the changes and the intention behind them.
So let’s start with a bit of history; the first version of the quick filter was released in the Microsoft Dynamics NAV 2009 Windows client, where we replaced the filter window available in the development environment with a filter pane integrated on all Microsoft Dynamics NAV pages. Here the user both has the option of entering filter criteria as plain text and the ability of composing an advanced filter. If you would like to quickly search for a certain record, the recommended practice is to use the quick filter.
Let’s say, for example, that we want to find a contact in the contact list starting with “Man”. We can construct the filter by selecting the Name field and entering the text “man” in the search field.
Notice that all the contacts starting with “man” now appear in the results list. How is this working? Whatever we add to this filter will be internally translated to a string by adding ‘@’ in front and ‘*’ in the end. So our filter string ‘man’ becomes ‘@man*’ and the Windows client filters for any contact name that starts with “man” in upper or lower case.
The following table illustrates more Quick Filter search examples in Microsoft Dynamic NAV 2013.
All records that start with the string man and case insensitive.
All records that start with the string se and case insensitive.
Starts with Man and case sensitive
All records that start with the string Man
An exact string and case sensitive
All records that match man exactly
Ends with 1
All records that end with 1
Ends with and case insensitive
All records that end with man
Starts with and case insensitive
All records that start with man
As part of our development process we regularly perform usability studies and some of them showed the users instinctively thought of the quick filter as a search field and that is why we decided to modify the quick filter behavior to a “contains” rather than a “starts with”. So what does this mean?
Let’s construct the same filter in the Microsoft Dynamics NAV 2013 R2. Notice that the search results list includes contact names which start with “Man” and even have “Man” in the middle of the name.
What has changed? The entered filter will be translated to a string by adding ‘@*’ in front and ‘*’ in the end. So our filter string ‘man’ becomes ‘@*man*’ and the Windows client filters for any contact name that contains “man” in upper or lower case.
To start with in the RTM version of Microsoft Dynamics NAV 2013 R2 we have considered the simple user approach where the special characters in the filter criteria are ignored. However, in the Cumulative Update 13 for Microsoft Dynamics NAV 2013, we have refined the user experience and now respect the entered filter criteria.
The following table illustrates the Quick Filter search examples for the Cumulative Update 13 and later for Microsoft Dynamics NAV 2013.
All records that contain the string man and case insensitive.
All records that contain the string se and case insensitive.
We encourage you to check out the Cumulative Update 13 and we hope that this blog demystifies some of the behavioral differences of the quick filter across Microsoft Dynamics NAV product versions.
It’s becoming less desirable to install any client side software and ease of deployment is becoming a key factor when choosing software. Modern software needs hybrid options and using deployment methods like ClickOnce, it looks likely to remain that way - for a while longer at least.
Equisys has now released an update to Zetadocs Express that includes the option of a Document FactBox installed on the server than runs Microsoft Dynamics NAV server to support the Microsoft Dynamics NAV Web client. It is also automatically available to the Microsoft Dynamics NAV Windows client, deployed remotely using ClickOnce, and Microsoft Dynamics NAV Windows clients that are installed directly on the desktop. This cross-client support now gives partners the option of zero client-side software deployment to achieve simple document management across all Microsoft Dynamics NAV clients.
One of the main benefits of Zetadocs Express is the ability to drag and drop related documents which are stored in the electronic archive for viewing in context within Microsoft Dynamics NAV. However, drag and drop to capture is very much a Windows invention for enhanced usability.
A Microsoft Dynamics Web client in a browser on a tablet makes that more complicated, especially when you want to drag and drop emails and attachments from Outlook to Microsoft Dynamics NAV records. That sort of action - between two separate programs - is just not possible yet in the web world, especially with most touch devices.
Consequently touch and choose was born as the way to add documents in the Microsoft Dynamics NAV Web client, which works just as well irrespective of the device you happen to be using.
If PC-based users using the Microsoft Dynamics NAV Windows client want the ability to capture emails using drag and drop from Outlook they can with an additional client-side install.
The Zetadocs Document FactBox detects whether the Zetadocs Client is installed locally on the desktop, and if so it automatically shows the additional drag and drop functionality and Outlook email integration.
Both add-ins happily coexist in the same Microsoft Dynamics NAV system - you just need to decide if the additional functionality is needed by the client and install Zetadocs Client on those desktops that need it.
Zetadocs Express is the foundation member of the Zetadocs for the Microsoft Dynamics NAV family of products available at no additional cost to users of Microsoft Dynamics NAV 2013 & Microsoft Dynamics NAV 2009 on a Business Ready Enhancement Plan, download it and try it with the Microsoft Dynamics NAV 2013 R2 Web client today!
Today’s pattern changes the paradigm of how we’ve done error processing in Microsoft Dynamics NAV earlier by providing less-intrusive, more user-productive error processing.
This pattern describes an optimized way to handle invalid, incomplete, or inconsistent data that users enter in journals.
Scenario: A user has entered data on a journal line and proceeds to invoke a processing action on it, such as posting or exporting to electronic payments. Microsoft Dynamics NAV validates the data before it is committed. If any validation errors are found, the user must be informed of validation errors in the most optimal way.
One design is that when an error is found, stop execution and prompt the user to correct the error. After correcting the error, the user restarts processing and is stopped again at the next error, and so on. Stopping and showing each error is time-consuming and frustrating for the user.
Another design is that processing does not stop when an error is found. Instead, all errors are gathered in a table and displayed all at once at the end of processing. This way, the processing is ideally invoked only once, reducing the time and effort spent by the user to expose and correct all data validation errors.
In both designs, the processing is not finalized if any errors are found (for example, exporting to electronic payments is not done, until the data error is resolved).
This document describes how to implement the second error-handling design: Showing all errors at the end.
The example below comes from the implementation of SEPA Credit Transfer.
After setting up SEPA-specific configurations, the user can start entering vendor payments that will later be exported to the payment file. (The setup depends on the country, but generally involves choosing number series for SEPA export files, choosing the export format, and enabling SEPA Credit Transfer.)
In the W1 solution (and most of the country-/region-specific versions), payment lines are created in the Payment Journal page, from where the user can invoke the Export Payments to File action, which will attempt to create a SEPA-compliant XML file containing the description of the journal payments that are to be made by the bank.
When the Export Payments to File function is invoked, Microsoft Dynamics NAV validates the journal line data. If the data must be completed or updated, then no file will be created and the user sees the following message:
To give a visual overview, the lines that need corrections are highlighted in red. The factbox is context-sensitive, meaning that it shows only the errors that relate to the currently selected line.
When the first payment journal line is selected, the FactBox show errors for the first line.
When the second payment journal line is selected, the FactBox shows errors for the second line.
In the following table, the Generic Object column contains the objects that you can use as a base for your implementation.
Sample W1 implementation of SEPA Credit Transfer*
This is the journal list page where the user invokes the processing action.
Action on Page
The processing action invoked by the user on the journal list page.
Export Payments to File
Errors Page List Part
A FactBox that displays any journal line validation errors.
To improve user experience, the developer can highlight the lines with errors in red and conveniently sort the lines with errors at the top.
Payment Journal Errors Part
Contains code that checks that the journal line contains correct, complete, and coherent data and that the line is ready for whatever process must be done next.
SEPA CT-Check Line
Executes the processing of the journal lines.
SEPA CT-Export File
Journal Error Text Table
Other related information can be added, such as document number of the original source document, if the current journal line originates from a document.
An extra improvement would be to add a drilldown or a link to the page where the user can fix the error. This would significantly simplify the scenario by excluding manual navigation and investigation by the user to find the page where the error can be fixed.
Payment Jnl. Export Error Text
* The W1 implementation of file export for SEPA Credit Transfer contains the generic SEPA functionality. However, due to differences in data models and user scenarios in various country implementations, the selected local versions contain adaptations of the generic functionality.
Find below a diagram describing the flow between the objects involved in the journal error processing.
Following the flow above, the code (in the SEPA Credit Transfer example) is as follows.
Read more on NAV Wiki...
The NAV Application Patterns team
On MSDN it is written that the Owner ID field became obsolete and should be removed from specific pages. This means that anyone can start up the Windows Client and configure a Role Center without any restrictions. Reasoning behind this is explained in this small blog posting among a new approach that does give a better handling of this.
The Owner ID was removed from the profile table during the multitenant work. The Profiles are in the application table and can’t have references to users which are in the tenants. The feature itself was not that well thought through. On all other records we have a last person saving wins.
The proposed workaround / solution is to use a permission set per profile and use record level security to restrict access to that particular profile ID.
To resolve the issue and get the old behavior back, you need to create a new Permission Set. E.g.:
TEST PROFILE Following details: Object Type Object ID Object Name R/I/M/D Security FIlter Table Data 2000000072 Profile Metadata Yes Profile: Profile ID=ACCOUNTING MANAGER
After logging on with a user in configuration mode that has two permission sets applied (BASIC and TEST PROFILE) with a profile called IT Manager, it is no longer possible to change the role center and make changes.
Marco Mels Microsoft Dynamics Netherlands
Microsoft Customer Service and Support (CSS) EMEA
Cumulative update 7 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 R2.
The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:
You can download cumulative update 7 from KB 2964528 – Cumulative Update 7 for Microsoft Dynamics NAV 2013 R2 (Build 36703 - our apologies for originally posting the wrong build number).
For a full list of all hotfixes included in cumulative updates for Microsoft Dynamics NAV 2013 R2, see the following CustomerSource and PartnerSource pages:
For more information about cumulative updates for Microsoft Dynamics NAV 2013 R2, see Announcement of update rollups for Microsoft Dynamics NAV 2013 R2.
Cumulative update 14 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013. The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:
You can download cumulative update 14 from KB 2964546 – Cumulative Update 14 for Microsoft Dynamics NAV 2013 (Build 36605).
For a full list of all hotfixes included in the cumulative update, see the following CustomerSource and PartnerSource pages:
After a bit of a delay, here is the latest Microsoft Dynamics NAV design pattern, brought to you by the NAV Design Patterns team.
This pattern shows how the new query object type introduced in Microsoft Dynamics NAV 2013 allows you to replace costly loops when inspecting data from two or more tables.
One of the core operations in a relational database is joining two or more tables. For example, you might need to extract all sales lines in the database together with information regarding the related sales header. This requires joining the Sales Header and Sales Line tables using Sales Header No. as the connecting field.
The join operation has traditionally been done in C/AL by record looping. When Microsoft Dynamics NAV 2013 introduced the query object, it allowed us to produce a data set that is the result of a join operation between two or more tables. This simplifies the problem of finding related records in two tables linked through a foreign key.
1. Two or more tables that contain records linked through a foreign key: Table 1, Table 2, Table n.
2. A query object Query X, that joins Table 1, Table 2, etc. based on the connecting key.
3. A processing codeunit that loops through the query records (or any other code-bearing object).
1. Run the query on the connected tables.
2. Loop through the records returned by the query.
3. Process the records.
The following diagram illustrates the elements of the pattern.
The Bank Acc. Reconciliation Line table (274) and the Bank Account Ledger Entry table (271) are connected through the Bank Account No. field. Identify the matching pairs of records based on having the same remaining amount and transaction date.
The classic C/AL approach is to:
1. Set the necessary filters on the left table, i.e. table 274.
2. Loop through the filtered records.
3. For each record in the filter, find the related records in the right table (table 271) and set the required filters on it.
4. For each pair of records from the left and right table, decide if they are a solution and if so, apply them to each other.
BankAccRecLine@1005 : Record 274;
BankAccLedgerEntry@1006 : Record 271;
BankAccEntrySetReconNo@1007 : Codeunit 375;
BankAccRecLine.SETRANGE(Type,BankAccRecLine.Type::"Bank Account Ledger Entry");
IF BankAccRecLine.FINDSET THEN
BankAccLedgerEntry.SETRANGE("Bank Account No.",BankAccRecLine."Bank Account No.");
BankAccLedgerEntry.SETRANGE("Statement Status",BankAccLedgerEntry."Statement Status"::Open);
IF BankAccLedgerEntry.FINDSET THEN
IF (BankAccRecLine.Difference = BankAccLedgerEntry."Remaining Amount") AND (BankAccRecLine."Transaction Date" = BankAccLedgerEntry."Posting Date") THEN BankAccEntrySetReconNo.ApplyEntries(BankAccRecLine,BankAccLedgerEntry, Relation::"One-to-One");
UNTIL BankAccLedgerEntry.NEXT = 0;
UNTIL BankAccRecLine.NEXT = 0;
The new query-based approach involves:
1. Define a query that returns the full filtered join of tables 271 and 274.
3. For each query record, decide if it represents a solution and then connect the two table records that formed it through an application.
BankRecMatchCandidates@1001 : Query 1252;
BankRecMatchCandidates.SETRANGE(Rec_Line_Bank_Account_No,BankAccReconciliation."Bank Account No.");
IF NOT BankRecMatchCandidates.OPEN THEN
Read more on NAV Design Patterns Wiki...
Bogdan Sturzoiu, at Microsoft Development Center Copenhagen
NOTE: The term “update rollup”, which has been used until now for hotfix releases, has been replaced with the term “cumulative update”. The meaning of the term is unchanged.
Cumulative update 6 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 R2.
You can download cumulative update 6 from KB 2955941 – Cumulative Update 6 for Microsoft Dynamics NAV 2013 R2 (Build 36366).
Note: Implementing this cumulative update will require a database conversion unless you have already implemented update rollup 5.
NOTE: The term “update rollup”, which has been used until now for hotfix releases, has been replaced with the term “cumulative update”. The meaning of the term is unchanged.
Cumulative update 13 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013. The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:
You can download cumulative update 13 from KB 2955943 – Cumulative Update 13 for Microsoft Dynamics NAV 2013 (Build 36347).
For more information about cumulative updates for Microsoft Dynamics NAV 2013, see Announcement of new hotfix process for Microsoft Dynamics NAV 2013.
After a short delay, here is the latest design pattern, brought to you by the Microsoft Dynamics NAV Design Patterns team.
This pattern generates the data to be displayed dynamically by combing/processing several data sources. It then displays the resulting dataset without writing to the database.
While writing reports in Microsoft Dynamics NAV, we have the luxury of using a built-in iterator. So, once we define the dataitem and the ordering, the runtime takes care of the iteration.
The iterator has one shortcoming: It can only run through records written into the database. There are situations, however, where we want to display temporary datasets created at runtime by processing data from different sources. That is where the Temporary Dataset Report pattern can be used.
This pattern takes a two-step approach to displaying the data:
In this step, we would process the existing data to create a temporary recordset. The three most common techniques to do this are discussed in the following paragraphs.
The first technique is mostly used when we want to build the report based on one or more source tables. A lot of processing is required, and we therefore want to store and display information from a temporary recordset. With this technique, we create a data item of the source record and then iterate through this data item to create the temporary recordset. An advantage of this technique is that it allows the user to perform additional filtering on the data source tables since they are added as additional data items and therefore will have their tabs on the request page by default.
The second technique was made available with NAV 2013 when queries were introduced as a tool to help us combine data from different sources.
Read the entire pattern on NAV Wiki...
Abhishek Ghosh from the Microsoft Dynamics NAV Design Patterns team
Update rollup 12 for Microsoft Dynamics NAV 2013 (Build 36207) has been released.
Update rollup 12 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013. With update rollup 12, local hotfixes for additional countries have been added, and update rollups for Microsoft Dynamics NAV 2013 now include hotfixes that apply to all countries and hotfixes specific to the following local versions:
New in update rollup 12
Beginning with update rollup 12, the update rollups for Microsoft Dynamics NAV 2013 now also include hotfixes for the upgrade toolkit.
Where to find update rollup 12
You can download update rollup 12 from KB 2937998 - Update Rollup 12 for Microsoft Dynamics NAV 2013 (Build 36207).
The hotfixes that have been released since update rollup 11 are listed in KB 2937998. For a full list of all hotfixes included in the update rollup, see the following CustomerSource and PartnerSource pages:
For more information about update rollups for Microsoft Dynamics NAV 2013, see Announcement of new hotfix process for Microsoft Dynamics NAV 2013.
Microsoft Dynamics NAV 2013 R2 was dispatched with a brand new feature that introduces big challenges to all of the Microsoft Dynamics NAV channel: Multitenancy. In simple words, multitenancy allows partners to deal with hosting scenarios on-premises and in cloud services in an easier and more flexible way than in the past.
Before Microsoft Dynamics NAV 2013 R2, partners and customers could only use a single-tenant scenario (here also called Legacy mode).
Below a short explanation how table synchronization used to work in earlier versions.
A synchronization failure would typically be reported with an error like “The Object Metadata does not exist. Identification and values … “ when running the Microsoft Dynamics NAV Windows client.
The multitenancy feature has also changed the design how Microsoft Dynamics NAV developers has to deal with object changes, overall related to table objects. Multitenancy implies that the table structure definition has to be stored in the application database and this needs to be applied on one or more separate storage databases called Tenants. From a development perspective, this means that any modification that are made to a table object in C/SIDE are NOT DIRECTLY applied to the SQL Server structure but there is a need of performing a secondary action to apply and made persistent these modification at SQL Server side: this process is called Synchronization. Microsoft Dynamics NAV 2013 R2, then, comes with a time decoupling between table metadata creation (C/SIDE) and data structure changes (SQL Server).
In order to simplify the current design, the Microsoft Dynamics NAV development team decided to handle single- and multitenant scenarios in the same way (roughly speaking a single-tenant / Legacy mode is handled as a multitenant scenario with a single tenant database constantly mounted against an application database).
Below a short explanation how this is working in practice.
Prompting for Synchronization happens when
- Performing ANY Microsoft Dynamics NAV client action.
For example, if a user opens a Microsoft Dynamics NAV Windows client, then Microsoft Dynamics NAV Server is starting applying the relevant structure changes to SQL Server, and the Microsoft Dynamics NAV Windows client is not shown until all the changes are done on SQL Server side.
- Running the Sync-NAVTenant Windows PowerShell cmdlet.
Setting the “Prevent data loss from table changes” C/SIDE switch to “No” has been intended to be used as last resource in a pure multitenancy scenario and in Test or Staging environments when partners does not have any business data database mounted against the application database. All other usages that deviate from this statement might lead to unpredictable results and even undesired data loss scenarios in upgrades or, even worse, production environments.
Never change for any reason this parameter to “No” when developing against a single-tenant / Legacy mode database.
Since no validation is made against SQL Server (“Prevent data loss from table changes” was set to “No”) there might be chances that this will result in:
Same as Scenario 1 for point 1. and point 2.
When prompting for SYNCHRONIZATION, changes will be triggered and applied to the SQL Server data structure.
Prompting for synchronization in a pure multitenant deployment happens when
- Performing ANY Microsoft Dynamics NAV client action
- Running the Sync-NAVTenant Windows PowerShell cmdlet
- Mounting a tenant database
Based on the scenario depicted above, there might be risks of data loss and/or missing synchronization issues if handling C/SIDE development (namely dealing with Table objects) in a way that deviate by the prospected paradigm.
Data loss issues:
These might arise typically in one of the following scenarios:
thinking about potential data loss and synchronization issues is a brand new big challenge in the development environment, and so some consideration and following best practice might be advisable. These applies to developing solutions for both single- and multitenant deployments.
Recommended Column Filters:
Bottom line. Worth mentioning that if a Microsoft Dynamics NAV Client hang / disconnect happens due to a missing synchronization issue or there were a synchronization transaction running behind the transaction rollback SQL Server side will take a higher amount of time in comparison with the same committed transaction, depending on the type of changes, resources available, etc.
Just in case you fall back in this situation, it is warmly advisable to do not stop nor restart Microsoft Dynamics NAV Server and check through a SQL Server Profiler trace and/or via SQL Server Management Studio if the transaction has successfully rollback.
Another blog post will follow this one, related to synchronization challenges and best practice while upgrading to Microsoft Dynamics NAV 2013 R2 from previous versions.
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
Gerard Conroy - Microsoft Dynamics UK
Abdelrahman Erlebach - Microsoft Dynamics Germany
Duilio Tacconi - Microsoft Dynamics Italy
Jasminka Thunes - Microsoft Dynamics Norway
A special thanks to Jorge Alberto Torres & Jesper Falkebo from the Microsoft Dynamics NAV development team
I have seen several reports about the Microsoft Dynamics NAV RDLC reports dropping symbols out of decimals when printing out a report. One of the most common occurrences, that I have seen, is when the de-CH locale is being used by the end user. After some investigation, it appears that this problem is due to some changes between Windows 7 and Windows 8 (and Windows 2012).
Windows 7 (de-CH): 1’745.55
Windows 8 (de-CH): 1 745.55 [Note: the thousand separator is not printed]
During my investigation I found some references to using “Custom Locales” to resolve this issue. In order to use a custom locale you must build a replacement locale using the Locale Builder 2.0 tool. There is a link to this tool at the following referenced URL. Once this is created, you then need to replace your existing locale. If you are interested in more information, please follow this link (http://msdn.microsoft.com/en-us/library/windows/desktop/dd317785(v=vs.85).aspx ).
There is another way that doesn’t involved changing out your locale. All it takes is a little code and some familiarity with editing RDLC reports. For this blog I am going to use Microsoft Visual Studio 2012 and Microsoft Dynamics NAV 2013 R2. I have outlined the required steps below …
1) After designing a report in the Microsoft Dynamics NAV 2013 R2 development environment, load the RDLC Layout (View\Layout). Depending on your setup, this will either load Microsoft Visual Studio 2012 or Report Builder 3.0 (if you are using Microsoft SQL Server 2012).
2) Once the layout is open, display the properties of the report. This can be done by opening the Properties window (CTRL+W,P) and clicking in the blank area around the Report or right click in the blank area around the report and select Report Properties.
3) You should see a Properties window like this …
Notice the highlighted Code property – click on the value and then click on the ellipse () that appears. This will open up the Code window.
4) Scroll to the bottom of the existing code functions and add the following code …
Public Function FormatDecimalString(ByVal Amount as Decimal, ByVal FormatString as String,ByVal Language as String) as String
Dim CultureInfo as New System.Globalization.CultureInfo(Language)
The new function will convert a decimal amount to a specific cultural locale based on the format string provided in the dataset.
5) Next, select a textbox that needs to have its printed format corrected. Once the textbox has its border highlighted, right click and select the Expression option. This will open the Expressions window.
6) Now, copy the following code into the “Set expression for: Value” textbox. The following code will need to replace the reference to the current value.
Make sure to replace <FIELDNAME.VALUE> with the appropriate value reference and replace <FIELDNAMEFORMAT.VALUE> with the appropriate format reference. This code will replace the comma in the en-US format with an apostrophe to match the de-CH format.
For Example: If you use the GLBalance field from the Dataset of REPORT 4 – Detail Trial Balance, then the code would look like this …
When using the de-CH locale, the number format is similar to what en-US uses, except that the apostrophe in the de-CH format is replaced with the comma in the en-US format.
de-CH 1’745.55 ==> en-US 1,745.55
The basic premise behind these code changes is to convert the decimal value to a locale format that is then returned as a text. Once in the text format, it is now possible to replace the values for the thousands and decimal separators as needed.
Hopefully, this will make your reporting experience in Microsoft Dynamics NAV 2013 R2 a little easier.