Monday, August 25, 2014

Bulk Inserts - Dynamics NAV

Microsoft has introduced the bulk insert functionality which will allow the developers to write high performance code. 

What is Bulk Insert : 

Microsoft Dynamics NAV automatically keep all the records in a buffer and then send them to Microsoft SQL server at a one time. Before this modification, insert happen in the order of the C/AL code running and had lot of server calls which will slowdown the application and also lock the tables many number of times before completing a one transaction.

Advantages of Bulk Insert : 

 1. Number of server calls are been reduced and it directly improve the performance of the application. 
 2. Improve the scalability. This was achieved by delaying the actual insert until the last possible moment in the transaction and by doing this Mictrosoft was able to reduce the amount of time that tables are locked. 

However this functionality has few constrains and it is a must to understand them before using this functionality. 


Client will send the records to SQL server when the following occurs, 

  • When the COMMIT was called to commit the transaction
  • Call MODIFY or DELETE on the table
  • Call and FIND or CALC method on the table
Buffer Insert functionality will not used if the following conditions are met,
  • If the application is using the return value from an INSERT call,
    • IF (GLEntry.INSERT) THEN
  • If the table that you are going to insert the records into contains any of the following
    • BLOB fields
    • Fields with the AutoIncrement property set to YES
Addition to above mention constrains Dynamics NAV Debug will not stop in the exact INSERT statement but, due to delayed Bulk Insert, in a position right after the INSERT AL Statement sequence.  To read more about this issue use the following link : MSDN

 Following code example cannot use the buffer insert since it contains a FIND call on the GL/Entry table within the loop.

 IF (JnlLine.FIND('-')) THEN BEGIN  
  GLEntry.LOCKTABLE;  
  REPEAT  
   IF (GLEntry.FINDLAST) THEN  
    GLEntry.NEXT := GLEntry."Entry No." + 1  
   ELSE  
    GLEntry.NEXT := 1;  
   // The FIND call will flush the buffered records.  
   GLEntry."Entry No." := GLEntry.NEXT ;  
   GLEntry.INSERT;  
  UNTIL (JnlLine.FIND('>') = 0)  
 END;  
 COMMIT;  

Therefore Developer need to redesign the code to maximize the usage of Bulk Insert.
Following is a modified code of above example and this code segment use the bulk insert functionality.
 IF (JnlLine.FIND('-')) THEN BEGIN  
  GLEntry.LOCKTABLE;  
  IF (GLEntry.FINDLAST) THEN  
   GLEntry.Next := GLEntry."Entry No." + 1  
  ELSE  
   GLEntry.Next := 1;  
  REPEAT  
   GLEntry."Entry No.":= GLEntry.Next;  
   GLEntry.Next := GLEntry."Entry No." + 1;  
   GLEntry.INSERT;  
  UNTIL (JnlLine.FIND('>') = 0)  
 END;  
 COMMIT;  
 // The inserts are performed here.  

To read the original blog of MSDN please refer the following link
MSDN

Thank you and Regards,
Tharanga Chandrasekara.

Sunday, August 24, 2014

Using the Quick Filter in Microsoft Dynamics NAV

With the introduction of Microsoft Dynamics NAV 2013, Microsoft had changed the Quick filter functionality and this blog post is a summarize version of the article published in the blogs.msdn and to read the complete blog post please refer the original article by Mictosoft NAV team ( to link please scroll to the bottom of the post).

The following table illustrates more Quick Filter search examples in Microsoft Dynamic NAV 2013.


Search Criteria
Interpreted as…
Returns…
Man
@man*
All records that start with the string man and case insensitive.
Se
@se*
All records that start with the string se and case insensitive.
Man*
Starts with Man and case sensitive
All records that start with the string Man
'man'
An exact string and case sensitive
All records that match man exactly
*1
Ends with 1
All records that end with 1
@*man
Ends with and case insensitive
All records that end with man
@man*
Starts with and case insensitive
All records that start with man

The following table illustrates the Quick Filter search examples for the Cumulative Update 13 and later for Microsoft Dynamics NAV 2013 and Microsoft Dynamics NAV 2013 R2.


Search Criteria
Interpreted as…
Returns…
Man
@*man*
All records that contain the string man and case insensitive.
Se
@*se*
All records that contain the string se and case insensitive.
Man*
Starts with Man and case sensitive
All records that start with the string Man
'man'
An exact string and case sensitive
All records that match man exactly
*1
Ends with 1
All records that end with 1
@*man
Ends with and case insensitive
All records that end with man
@man*
Starts with and case insensitive
All records that start with man

To read the full article please use following link


Thank you and Regards,
Tharanga Chandrasekara. 

Saturday, August 23, 2014

Advantages of using Microsoft Dynamics NAV development environment in creating a Database

In this blog post I will not be discussing about the process of creating a NAV DB. In here I will be discussing about the advantages of using Microsoft Dynamics NAV Development Environment in creating a DB. 

Microsoft has provided main 2 ways of creating a Dynamics NAV database in SQL server. 
 1. Create the DB using SQL Server Management Studio
 2. Create the DB using Microsoft Dynamics NAV Development Window. 

What is the best way ?? It is very hard to pick a one from above two methods because both are almost same and required similar steps.

However I feel creating the DB from Microsoft Dynamics NAV environment is more safe and provide few advantages. 
As you know if you just create a DB using SQL Management studio, and didn't do any modifications to the DB creation process then the DB will create a default filegroup call PRIMARY. This filegroup will be the primary filegroup and all the tables will be created on this filegroup.

It is not recommended to use the default filegroup for our usage. Leave it for SQL and create a separate filegroup and then work on that. 

So here comes the advantage of using Microsoft Dynamics NAV development environment to create a DB. Because when you create the DB using Microsoft Dynamics NAV development environment, system will automatically create a new filegroup called "Data Filegroup 1" apart from the Default filegroup and make that new filegroup as default. Every table will be created in this new filegroup and we do not have to do anything on this. 

There are few other additional advantages as well. i will write about them in my next few posts. 

Thank you and Regards,
Tharanga Chandrasekara. 

Reset NAV 2013 or 2013 R2 security and settings

If it is required to delete users, user properties, debugger breakpoints or session event related info; then use following TSQL code. It simply delete the data from the tables directly and allow you to use the NAV as a fresh settings. 
 USE [YourNAVDatabase];  
 delete FROM [dbo].[Access Control];  
 delete FROM [dbo].[User];  
 delete FROM [dbo].[User Property];  
 delete from dbo.[User Personalization];  
 delete from dbo.[User Metadata];  
 delete from dbo.[Page Data Personalization]; 

Thank you and Regards
Tharanga Chandrasekara

Thursday, August 21, 2014

HTML Tags and Microsoft Dynamics NAV 2013 Reports

It is annoying to change the report layouts according to customer requirements, because every time developer needs to open the design view and then go to layout in the Visual Studio. 

What about giving the option to the customer to change the report presentation and the layout of the report. Does NAV directly allow that? No. 

but so far I have found two different workarounds for this, 
1. Design the report in the report designer as usual way (need very few additional modifications) then create couple of functions and tables which allow the user to change the color, size, font, spacing of the text without opening the report design view (Using HTML Tags). 
2. Create the report layout in text editor (Notepad, Wordpad, Word) and save it as a HTML file and then passing the values to the HTML file using a DLL.

Today I will describe the first method and will give more info about the second one later (will share the DLL and code with it, most probably a video guide).

Here we go..

 1. First create a table with following fields and data types.

2. Provide a object ID and name and save it. 

3. Following is the report we are going to modify.

4. Run the report and see the output of the report before do the modification.

5. Create a code unit and in that create a function called "Format Field"

6. In the function, define a record variable for the table which we created in the step 01 and 02.


7. Go to parameter tab and define 3 input variables with the data types define below.
  • ReportID_In - Integer
  • FieldName_In - Integer
  • Value_In - Text 100

8. Set the return variable name to "Return Value" and set the data type to Text 100

9. Go to Format Field in the code unit and write the following code
 Format Field(ReportID_In : Integer;FieldName_In : Text[30];Value_In : Text[100]) ReturnValue : Text[100]  
 recReportFormatting.RESET;  
 recReportFormatting.SETFILTER(recReportFormatting."Report ID",'%1',ReportID_In);  
 recReportFormatting.SETFILTER(recReportFormatting."Field Name",FORMAT(FieldName_In));  
 IF recReportFormatting.FINDFIRST THEN BEGIN  
   ReturnValue := '<html><font size =';  
   ReturnValue := ReturnValue + recReportFormatting."Font Size" + ' color =' + recReportFormatting."Font Color" + '>';  
   ReturnValue := ReturnValue + ' ' + Value_In;  
   ReturnValue := ReturnValue + '</font></html>';  
 END ELSE BEGIN  
   ReturnValue := Value_In;  
 END;  

10. Compile and save the code unit and you are done with most of the coding necessary.
Now lets move to the next part of the development. Which is the easy part.

11. Go to the report and go to the design view of it.

12. Then click on the "Cust. Ledger Entry" data item and go to the code of the trigger.
 We are going to modify the customer name according to the parameters set in the earlier defined table.
In this report we need to do few more modifications. We are going to write the coding in the highlighted area and before that we need to define few more variables.  


13. Define following variables as globe variables in the report.
  • cduReportFormat - Codeunit - Report Formatting (50000)
  • textname - Text 100
  • ReportID - Integer
  • CustomerName - Text 50
  • CusNameForReport - Text 100

14. Write the following code in the report where I have highlighted before. 
This will pass the report ID and Value to the function we defined early and will return a HTML tag text.


1:  EVALUATE(ReportID,(COPYSTR(CurrReport.OBJECTID(FALSE),8)));  
2:  CustomerName := CustAddr[1];  
3:  CusNameForReport := cduReportFormat."Format Field"(ReportID,'Customer Name',CustomerName);  



15. Go to design view of the report (dataitem) and add the following variable to the report data set. 

16. Then open the report in Report Layout mode and as you can see there are various text boxes with different values. We are going to change the format of the customer name column. Therefore we do not need the existing column. Just delete the first text box in the upper left corner.  



17. Now we need a new data holder to add the customer name, and that place holder should be able to read and format our HTML string. Can we do that easily or do we have to modify lot of things? No we don't!
Just add a text box and double click on it and then right click. 



Then click on the "Create Placeholder"

18. In the Placeholder property window click on the HTML- Interprete HTML tags as styles radio button. 
This will tell the placeholder to read the HTML tags and convert them to the styles. 

19. Then go to the Value and select the Customer name data field from the data set. 

20. Close the designer and compile and save the report.

21. Now go to the table which we have created at the early stage of this demo. 
22. Run the table and insert the values as following. 




23. Now go back to the reporting and run the report. 


As you can see customer name styling is changed accordingly. 

We can use this functionality very creative way. This is just a demo to show that we can use the HTML tags as styles. If you have the creativity you can do marvelous  thing to satisfy and exceed customer satisfaction. 


DONE!! 

Happy testing guys!!

Thank you and Regards,
Tharanga Chandrasekara. 











Cumulative Update 17 for Microsoft Dynamics NAV 2013 and Cumulative Update 10 for Microsoft Dynamics NAV 2013 R2 has been released.

Please use the following link to download and read about the new release of Microsoft Dynamics NAV 2013. 
NAV 2013

Please use the following link to download and read about the new release of Microsoft Dynamics NAV 2013 R2. 
NAV 2013 R2

Thank you and Regards,
Tharanga Chandrasekara. 

Wednesday, August 20, 2014

Elimination of Certification - MPN Silver/Gold Competency requirements - Dynamics NAV

Effective from August 13, 2014, all the certifications and assessments for the Dynamics NAV will no longer required to obtain  MPN Silver/Gold Competency. 

This includes pre-sales and sales assessment as well as the other implementation and technical certifications. 

Following are the competency requirements,
  • Minimum revenue requirements for SPA
  • Minimum revenue and BREP Revenue Recapture Rates for MPN ERP Gold and Silver
  • Customer references for MPN ERP Gold and Silver
  • Participation in Customer Satisfaction Survey for MPN ERP Gold

I personally do not agree with removing certification requirements, however we need to follow it..  

For more information please check below link
partnersource


Thank you and Regards,
Tharanga Chandrasekara. 

Compatibility of SQL Server 2014 with NAV2013 and NAV2013R2





SQL Server 2014 is released earlier this year with many new and very interesting capabilities. I will get back to you all about that with a separate article and for now lets just focus on comparability of NAV and SQL.

After testing the Microsoft Dynamics NAV with Microsoft SQL Server 2014, NAV team has finally confirmed that following versions are compatible with Microsoft SQL 2014
  • Microsoft Dynamics 2013, 
  • Microsoft Dynamics 2013 R2 
  • Microsoft Dynamics NAV 2009 R2
They are yet to confirm the compatibility of Microsoft Dynamics 2009 SP 01.
Please not that this announcement only applies to following editions of Microsoft SQL Server,
  • Standard Edition
  • Enterprise Edition.

Thank you and Regards,
Tharanga Chandrasekara. 

CRETE - Microsoft Dynamics NAV 2015

CRETE

According to the "waldo" Microsoft is going to launch Dynamics NAV 2015 also known as CRETE



During the Convergence 2014 presentation in March, Microsoft committed to shipping yearly releases for NAV, beginning with NAV 2015.
Following is the roadmap that Microsoft has presented


According to the waldo this is a one major release and we can expect lot of changes to platform, development and application. Still Microsoft has not officially realeased any articals regarding this. 

According to the current information following changes will include in the new release.


  • Devices: we mean: clients for devices. And with devices, we mean “tablets”.
  • Expressive tiles: the cues in rolecenter get more value: you can use them as KPI’s, like this screenshot:


  • Report: An additional way to create reports: so there is going to be an alternative for RDLC, which is going to be “Word Templates”: you are going to be able to create documents reports by designing them in Word! It’s not to replace RDLC, but you have an alternative. Customers will be able to do changes now far easier.




Crete’s main emphasis is on Power Business Intelligence (BI), integration with Office 365 and document reporting.

  •  NAV 2015 Business Intelligence : With the new release of Crete, Microsoft is mainly focusing on providing the capability for the small business to use BI. With the Power BI, users will be able to use common tools such as SharePoint, Excel and mpbile device. 

Following are the key features in the Power BI module includes ( According to http://www.clientsfirst-us.com )

  • Power Query - This feature will enable users to access data from public and corporate data sources.
  • Power Pivot – This module extends the capability of Excel by adding key Performance Indicators (KPIs), hierarchies, custom measures and relationships. Pivot’s in-built memory can store generated models, hence enabling users to analyze millions of data fast.
  • Power View – Provides interactive views of Excel data or reports in form of charts and graphs for easy understanding.
  • Power Map – Enables end users to integrate Excel with geospatial data to create a 3D map experience.

For Additional information please read the original articles from

Thank you and Regards,
Tharanga Chandrasekara

Tuesday, August 19, 2014

How to setup default company for NAS ( NAV 2013 R2 )


In Microsoft Dynamics NAV 2013 Service Configuration Panel we can set the NAS Default company under the NAS Service tab. But with the new version this input box was missing and this is how it look like in the NAS Service tab. Default Company Text box is missing in 2013 R2 Configuration Panel!! 

NAV 2013 R2 Service Configuration Panel - NAS Service TAB

Did actually Microsoft removed it from the Service Panel? 
No they didnt. They actually moved it to some other tab.

To which tab?Microsoft has moved this to the general tab. 




There we go! 

Thank You and Best Regards,
Tharanga Chandrasekara

NAV 2013 R2 Image Library


How many of you go through the hassle of selecting the perfect and preferred image for your page actions? I think many of us go through this hassle because we really don't know the exact image library of Dynamics NAV 2013 R2.

But now thanks to "Arend-Jan Kauffmann" we have a very easy way of selecting the image we want. 

He has developed a small application where it display and allow us to browse, search, copy and save all NAV 2013 R2 images. 

This is how this awesome application looks,




Following is the download link to mibuso site,
Download from here

Thank You and Best Regards,
Tharanga Chandrasekara


Customize the Dynamics NAV Splash Screen - NAV 2013 & 2013 R2


I am sure everyone of you who use Dynamics NAV has seen the following startup screen.
Original Splash Screen

Have you seen a screen like this ?
Modified Splash Screen


Is it possible to change this screen as we want?  YES we can !!

Does it take lot of effort ? NOPE !! Only take less than 5 min. 

Then how to do it?  Here we go..

Microsoft has placed this splash screen image in our installation folder. Following is the path to the image file (default).
C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailored Client\Images.

After you locate the image file, do open it in any image editor and modify the image as you want and save the file back. (prefer if you take a backup of the file before modify the image)

You are done! Start the role-tailored client and check.


Thank you and Best Regards,
Tharanga Chandrasekara

Styling Dynamics NAV 2013 Page Values

ERP is all about providing critical information to the users with very user friendly manner. Therefore It is always essential to draw attention to the important values, fields in a page. It allows the user to use the ERP very user friendly manner and allow to provide very critical information in very simple manner.

If the customer want conditional coloring of rows based on some conditions, as developers didn't had the functionality support from the earlier versions of Dynamics NAV Role-Tailored Client.

With the new versions of Dynamics NAV, this was allowed and was able to change the color of the text depending on the predefined conditions. 
Even though the functionality was allowed, still have limitations for the formatting. Such as, the number of colors support to this functionality is fixed.

Following is a sample for the styled text page in the Dynamics NAV 2013 R2

Sample of Styled Page in Dynamics NAV

Formatting is done by using a property value for style on a field and setting it to a pre-defined value.
Go to the design view of a page and select a field that you want to change the color and then go to the properties of the field.
(Design Page -> Property of the Field want to change the color)
As you see 'StyleExpr" was already set to "FALSE" and system will show the value in default manner.



To modify the style follow the following steps,
1. Design the page
2. Define a global variable of type text ( StyleText Text 20 )
3. Assign the Style Code value to defined variable in the "OnAfterGerRecord" trigger depending on the condition.
Following is a sample code.

4. Then go to the property of the field and scroll down to the bottom of the property page and assign the variable to the "StyleExpr"


5. Compile and Run the page. (Following is the final output - web client)






Thank You and Best Regards
Tharanga Chandrasekara

Saturday, August 16, 2014

Microsoft Dynamics NAV Versions

Following are the versions of Microsoft Dynamics NAV,
  • Navision Financials 1.00: 1.00, 1.00A, 1.00B, 1.10, 1.10A, 1.20, 1.30 
  • Navision Financials 2.00: 2.00, 2.00A, 2.00B, 2.00C, 2.01, 2.01A, 2.01B 
  • Navision Financials 2.50: 2.50 
  • Navision Financials 2.60: 2.60, 2.60A, 2.60B, 2.60C, 2.60D, 2.60E 
  • Navision Solutions 3.00: 3.00 
  • Navision Attain 3.01: 3.01, 3.01A, 3.01B 
  • Navision Attain 3.10: 3.10, 3.10A, 3.10B 
  • Navision Attain 3.60: 3.60, 3.60A 
  • Microsoft Business Solutions Navision 3.70: 3.70, 3.70A, 3.70B 
  • Microsoft Business Solutions NAV 4.00: 4.00, 4.00 SP1, 4.00 SP2, 4.00 SP3 
  • Dynamics NAV 5.00: 5.00, 5.00 SP1 
  • Dynamics NAV 2009: ("6.00") 2009, 2009 SP1, 2009 R2 
  • Dynamics NAV 2013: ("7.00") 2013, ("7.1") 2013 R2
Taken From  : Dynamics NAV Versions

What is C/SIDE and C/AL

Stands for: 

  • C/SIDE - Client/Server Integrated Development Environment
  • C/AL -  Client/Server Application Language
Description.
C/AL is the programming language that used within the development environment for Microsoft Dynamics NAV, and the development environment is called as C/SIDE. C/AL is a database specific programming language and it primarily used to retrieve, insert, and modify the records in the dynamics NAV Database. C/AL is extended from the PASCAL language and original C/AL compiler was written by Michael Nielsen.

When changing the NAV application according to the requirements, developer does not change the executable s (.exe files), but the "Objects". 
Dynamics NAV database contain definitions of the each object and each object contain business logic to run the ERP application. 

About Me

My Name is C.M.Tharanga N. Chandrasekara and working as a Microsoft Dynamics NAV technical consultant.

View Tharanga N. Chandrasekara's profile on LinkedIn