Wednesday, December 9, 2015

Auto Load Dynamics NAV Modules On PowerShell Startup

dialog-148815_1280

Dynamics Navision PowerShell cmdlets were introduced with the release of version 2013 and with every new release they have added more cmdlets to manage Navision.  There are several of them which can be used to create instances, to install, backup, upgrade, and automate several tasks.  I will discuss several of those cmdlets and some scripts in my next series of blog on PowerShell.

In this post let’s discuss how can we load those cmdlets. When you open PowerShell console or PowerShell ISE, by default it does not load the NAV cmdlets automatically you need to use the following cmdlet to import them

Import-Module 'C:\Program Files\Microsoft Dynamics NAV\71\Service\NavAdminTool.ps1' 

If you develop any other modules, custom functions or scripts you need to execute the same import-module cmdlet for your every .ps1 file path.

How can we automate this so that it will load all custom scripts when you open the Console or ISE without executing the commands ?

 

We can create aliases (which are short name to a cmdlet) but those are only kept for the current session and there are few short-comings which I will discuss in my next post. The best option to load the custom modules,functions or scripts automatically is to use a Profile. A profile is a windows PowerShell script which runs automatically when you start a new session. There are different type of profiles, but let’s concentrate today on current user profile which is stored in the following directory

%UserProfile%\My Documents\WindowsPowerShell\

 

To know your profile path you can type $Profile which will return the profile path. To check if the profile file exists or not use the following cmdlet

test-path $profile (Which will return true if the file exists or else false)

If the file does not exist, then create a new profile file using the below cmdlet, it will overwrite the existing file if file already exists.

new-item –path $profile –itemtype file –force

(If you don’t the syntax of any command please use help or Get-Help)

One tip on the Help command, sometimes it is hard to scroll through the help when it displays in the console, you can use the parameter –ShowWindow to open help in a new GUI window

For Ex: to know more about Test-Path use

Help Test-Path –ShowWindow  which will open the help in a new window which is easy to scroll and search as shown below

image

 

Create a new folder in the C:\ drive and call it UploadScripts. Copy all your custom scripts or functions and in our case we will copy NAVAdminTool.ps1 and will paste in the newly created folder.

Now open the profile file which is created in

%UserProfile%\My Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1

Add the following code so that everytime the profile is loaded it will load all our scripts.

Set-ExecutionPolicy remoteSigned

$uploaddir = “C:\UploadScripts”

Get-ChildItem “${uploaddir}\*.ps1” | %{.$_}

Write-Host -fore Green "All Custom Scripts Loaded"

Every time now you open the console or ISE now it will load all the scripts in that folder.

Please leave your comments, feedback or any suggestions you have for me to improve my blog and also if you have any questions, feel free to post.

Share:

Wednesday, December 2, 2015

How to fix : “CA Dollar” on Amount Description Line in Navision Check Report

question-686336_1920

 

When ever you print a check in Navision does it print the amount description line as CA Dollar instead of US Dollar

For example:

****Ten Thousand Twenty Eight CA Dollars and 20/100

instead of

****TEN THOUSAND TWENTY EIGHT AND 20/100 US DOLLARS

 

I have come across this issue many times and recently I had one more occurrence, so I decided to write this post so that I can reference back and also help others who encounters the same issue

To fix the above issue we need to setup the following:

1. On the Company information card, on the payments tab make sure you fill-up

  • US Country/Region Code
  • Canada Country/Region Code
  • Mexico Country/Region Code

 

image

 

 

 

 

 

2. On the Bank Account Card, Fill the Country/Region code on the General Tab.

The above two setups will fix the issue and will print US Dollar instead of CA Dollar.

Please leave your comments, feedback or any suggestions you have for me to improve my blog and also if you have any questions, feel free to post..

Share:

Sunday, November 29, 2015

How to modify the Sales Invoice Report to print Serial No./Lot No.


question-686336_1920
There are several requests on the Microsoft Dynamics Community forum to get an example or for the logic to print the serial no. or lot no. on the sales invoice report. In this post I will explain how to modify the standard sales invoice report (Report 10074: NAV 2015) to print the serial no. or lot no. on the report.
For this purpose I have added a new function in the report 10074, called GetSerialLotNo
LOCAL PROCEDURE GetSerialLotNo@1240060002();
    VAR
      ItemTrackingMgt@1240060000 : Codeunit 6500;
      TempItemLedgEntry@1240060001 : TEMPORARY Record 32;
      InvoiceRowID@1240060002 : Text[150];
      LineNo@1240060003 : Integer;
      Inserted@1240060004 : Boolean;
      SerialLot@1240060005 : Code[60];
      ValueEntryRelation@1240060006 : Record 6508;
      ValueEntry@1240060007 : Record 5802;
      ItemLedgEntry@1240060008 : Record 32;
    BEGIN
      
      IF TempSalesInvoiceLine.Type <> TempSalesInvoiceLine.Type::Item THEN
        EXIT;
      TempItemLedgEntry.RESET;
      TempItemLedgEntry.DELETEALL;
      InvoiceRowID := TempSalesInvoiceLine.RowID1;
      ValueEntryRelation.RESET;
      ValueEntryRelation.SETCURRENTKEY("Source RowId");
      ValueEntryRelation.SETRANGE("Source RowId",InvoiceRowID);
      IF ValueEntryRelation.FIND('-') THEN BEGIN
        REPEAT
          ValueEntry.GET(ValueEntryRelation."Value Entry No.");
          ItemLedgEntry.GET(ValueEntry."Item Ledger Entry No.");
          TempItemLedgEntry := ItemLedgEntry;
          TempItemLedgEntry.Quantity := ValueEntry."Invoiced Quantity";
          IF TempItemLedgEntry."Entry Type" IN [TempItemLedgEntry."Entry Type"::Purchase,TempItemLedgEntry."Entry Type"::Sale] THEN
            IF TempItemLedgEntry.Quantity <> 0 THEN
              TempItemLedgEntry.INSERT;
        UNTIL ValueEntryRelation.NEXT = 0;
      END;
      IF TempItemLedgEntry.FINDFIRST THEN
        REPEAT
          SerialLot := TempItemLedgEntry."Serial No." + ' ' + TempItemLedgEntry."Lot No.";
          WITH TempSalesInvoiceLine DO BEGIN
            INIT;
            "Document No." := "Sales Invoice Header"."No.";
            "Line No." := HighestLineNo + 10;
            HighestLineNo := "Line No.";
          END;
          IF STRLEN(SerialLot) + 1 <= MAXSTRLEN(TempSalesInvoiceLine.Description) THEN BEGIN
            TempSalesInvoiceLine.Description := SerialLot;
            TempSalesInvoiceLine."Description 2" := '';
          END ELSE BEGIN
            SpacePointer := MAXSTRLEN(TempSalesInvoiceLine.Description) + 1;
            WHILE (SpacePointer > 1) AND (SerialLot[SpacePointer] <> ' ') DO
              SpacePointer := SpacePointer - 1;
            IF SpacePointer = 1 THEN
              SpacePointer := MAXSTRLEN(TempSalesInvoiceLine.Description) + 1;
            TempSalesInvoiceLine.Description := COPYSTR(SerialLot,1,SpacePointer - 1);
            TempSalesInvoiceLine."Description 2" :=
              COPYSTR(COPYSTR(SerialLot,SpacePointer + 1),1,MAXSTRLEN(TempSalesInvoiceLine."Description 2"));
          END;
          TempSalesInvoiceLine.INSERT;
        UNTIL TempItemLedgEntry.NEXT = 0;
      
    END;



The Serial No. and Lot No. information is stored in the item ledger entry table, so the above function is used to retrieve all the item ledger entries associated to a sales invoice line, and then store those into a temporary ledger entry table, by using the temporary ledger entry table we populate TempSalesInvoiceline table, which will take care of displaying the values on the report without modifying/formatting RTC report.

To print the serial no./ lot no. we just call the new function GetSerialLotNo on the OnAfterGetRecord of the Sales Invoice Line DataItem.

Below is an example of the invoice report printing serial no.

image














Download the object from this link Sales Invoice Report

Please leave your comments, feedback or any suggestions you have for me to improve my blog and also if you have any questions, feel free to post.
Share:

Tuesday, November 10, 2015

Object Numbering Conventions In Navision


Microsoft provided object numbering convention to follow for Dynamics NAV and please check the below link for more information, it is important to follow this Conventions while making customizations as it will help to upgrade easily and support.

https://msdn.microsoft.com/en-us/library/ee414238(v=nav.90).aspx

Share:

How to Read/Write Notes in Navision using C/AL

question-686336_1920

In this post I will discuss how to read the notes and how to create the notes programmatically. When you create a note where the data is saved in the Navision ? I have seen this question being asked several times in the community forum. The answer is the notes are saved in binary format in a blob field (Note) in the Record Link table (2000000068) and the way it maps to the record is using Record ID.

Read Notes:

To read notes you need to find the “Record ID” of the record using Record Reference and then use that to filter the Record Link table and then convert value in the blob field (Note) into readable text.

In the below example the ReadNotes function takes SalesHeader as parameters and displays the first note associated with it.

Write Notes:

To create note once we again need to get the “Record ID” of the record which can be retrieved using Record Reference, and we also need to convert the text into bytes to store in the “Note” Blob Field. Since the Record Link primary key Link ID is set to Auto Increment we don’t need to find the next available “Link ID”, as INSERT statement will take care of retrieving it and assigning it.

There are two helper functions below SetText and HtmlEncode, you need these functions to write notes.

PROCEDURE ReadNotes@1240060000(SalesHeader@1240060003 : Record 36);
    VAR
      RecordLink@1240060000 : Record 2000000068;
      NoteText@1240060001 : BigText;
      Stream@1240060002 : InStream;
      RecRef@1240060004 : RecordRef;
    BEGIN
      RecRef.GETTABLE(SalesHeader);
      RecordLink.SETRANGE("Record ID",RecRef.RECORDID);
      IF RecordLink.FINDFIRST THEN BEGIN
        REPEAT
          RecordLink.CALCFIELDS(Note);
          IF RecordLink.Note.HASVALUE THEN BEGIN
            CLEAR(NoteText);
            RecordLink.Note.CREATEINSTREAM(Stream);
            NoteText.READ(Stream);
            NoteText.GETSUBTEXT(NoteText, 2);
            MESSAGE(FORMAT(NoteText));
          END;
       UNTIL RecordLink.NEXT = 0;
      END;
    END;

    PROCEDURE WriteNote@1240060001();
    VAR
      LinkID@1240060000 : Integer;
      Customer@1240060001 : Record 18;
      RecRef@1240060002 : RecordRef;
      RecordLink@1240060003 : Record 2000000068;
    BEGIN
      Customer.GET('10000');
      RecRef.GETTABLE(Customer);
      RecordLink.INIT;
      RecordLink."Link ID" := 0;
      RecordLink."Record ID" := RecRef.RECORDID;
      RecordLink.URL1 := GETURL(CLIENTTYPE::Current, COMPANYNAME, OBJECTTYPE::Page, PAGE::"Customer Card");
      RecordLink.Type := RecordLink.Type::Note;
      RecordLink.Created := CURRENTDATETIME;
      RecordLink."User ID":=USERID;
      RecordLink.Company:=COMPANYNAME;
      RecordLink.Notify := TRUE;
      SetText('Test Note For the Customer 10000',RecordLink);
      RecordLink.INSERT;
    END;

    LOCAL PROCEDURE SetText@4(NoteText@1001 : Text;VAR RecordLink@1000 : Record 2000000068);
    VAR
      SystemUTF8Encoder@1011 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Text.UTF8Encoding";
      SystemByteArray@1010 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
      OStr@1008 : OutStream;
      s@1007 : Text;
      lf@1006 : Text;
      c1@1005 : Char;
      c2@1004 : Char;
      x@1003 : Integer;
      y@1002 : Integer;
      i@1009 : Integer;
    BEGIN
      s := NoteText;
      SystemUTF8Encoder := SystemUTF8Encoder.UTF8Encoding;
      SystemByteArray := SystemUTF8Encoder.GetBytes(s);

      RecordLink.Note.CREATEOUTSTREAM(OStr);
      x := SystemByteArray.Length DIV 128;
      IF x > 1 THEN
        y := SystemByteArray.Length - 128 * (x - 1)
      ELSE
        y := SystemByteArray.Length;
      c1 := y;
      OStr.WRITE(c1);
      IF x > 0 THEN BEGIN
        c2 := x;
        OStr.WRITE(c2);
      END;
      FOR i := 0 TO SystemByteArray.Length - 1 DO BEGIN
        c1 := SystemByteArray.GetValue(i);
        OStr.WRITE(c1);
      END;
    END;

    LOCAL PROCEDURE HtmlEncode@20(InText@1000 : Text[1024]) : Text[1024];
    VAR
      SystemWebHttpUtility@1001 : DotNet "'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.System.Web.HttpUtility";
    BEGIN
      SystemWebHttpUtility := SystemWebHttpUtility.HttpUtility;
      EXIT(SystemWebHttpUtility.HtmlEncode(InText));
    END;






The two above functions SetText and HtmlEncode are copied from the standard Navision Codeunit (454 Job Queue - Send Notification)


Download the object from this link Notes Management


Please leave your comments, feedback or any suggestions you have for me to improve my blog and also if you have any questions, feel free to post.

Share:

Thursday, November 5, 2015

Tuesday, November 3, 2015

Saturday, October 24, 2015

Keyboard Shortcuts in NAV 2016

keyboard-70506_1920
Below you will find all the keyboard shortcuts available in NAV 2016 for windows client, development environment and C/AL Editor

The following table provides an overview of shortcut key combinations in the Microsoft Dynamics NAV Windows client.


Microsoft Dynamics NAV Windows client

Action

Right Arrow Move to the next field or character
Left Arrow Move to the previous field or character
Up Arrow Move to the field above in the same column
Down Arrow Move to the field below in the same column
Delete Clear selected text
Esc Close the window or undo the data entry
End Move to the last field on a line
Home Move to the first field on a line
Tab Move to the next field on non-line FastTabs
Enter Move to the next editable field
F1 Open Help
F2 Edit
F3 Select Type to filter (field filter)
F4 Drop-down or look up to select
F5 Refresh the active window
F6 Go to the next frame
F7 Display statistics
F8 Copy the field above
F9 Post
F10, Alt Select the menu bar and display access keys
F12 Select the navigation pane
Shift+F1 View error message
Shift+F3 Select Show Results (FlowFilter)
Shift+F4 Open a lookup window (from an ellipsis button)
Shift+F6 Go to the previous frame
Shift+F7 Open the related card
Shift+F8 Drop-down or look up to view
Shift+F9 Post and print
Shift+F10 Display a shortcut menu
Shift+F11 Apply entries, get source documents, or get warehouse documents
Shift+F12 Open the Role Center from the navigation pane
Shift+Tab Move to the previous field
Shift+left-click On multiple column headers, the columns will all be sorted in the same ascending or descending direction.
Shift+double-click If sorting on multiple column headers using Shift+left-click, use Shift+double-click on the first column that was clicked on and this will toggle ascending/descending direction of all the columns included in the sorting.
Left-click Left-click on a column header to sort a column ascending, then left-click again to toggle between ascending and descending.
Ctrl+F1 Collapse or expand the ribbon
Ctrl+F2 Create a new document
Ctrl+F3 Select Search pages
Ctrl+F4 Look up to the related list
Ctrl+F7 View entries
Ctrl+F9 Release document
Ctrl+F10 Select the ribbon and display key tips
Ctrl+F11 Reconcile or split lines
Ctrl+F12 Select the address bar
Ctrl+C Copy
Ctrl+G Go to
Ctrl+E Export to Microsoft Office Excel
Ctrl+L Show links
Ctrl+N Create a new record
Ctrl+O Open the company
Ctrl+P Print
Ctrl+T Select sorting
Ctrl+V Paste
Ctrl+W Export to Microsoft Office Word
Ctrl+X Cut
Ctrl+Z Undo
Ctrl+Page Down Display next document or card in a list
Ctrl+Page Up Display previous document or card in a list
Ctrl+Up Arrow Move up while the selected line stays selected
Ctrl+Down Arrow Move down while the selected line stays selected
Ctrl+Left Arrow Move to the first field on a line
Ctrl+Right Arrow Move to the last field on a line
Ctrl+Alt+F1 Open the About this Page/Report window (Zoom)
Ctrl+Delete Delete the selected line
Ctrl+Home Move to the first line in a list
Ctrl+End Move to the last line in a list
Ctrl+Enter Save and close window (equivalent to choosing the OK button)
Ctrl+Insert Insert new line
Ctrl+Shift+F3 Select Limit totals to (table filter)
Ctrl+Shift+A Clear all filters
Ctrl+Shift+C Copy Rows
Ctrl+Shift+D Show dimensions
Ctrl+Shift+E Edit list
Ctrl+Shift+R View list
Ctrl+Shift+Q Collapse/expand a line in a hierarchy
Ctrl+Shift+V Paste rows
Ctrl+Shift+W Open a list place in a separate window
Ctrl+Shift+Enter Save and close the window and open a new window
Alt Display access keys in the ribbon
Alt+F2 Toggle to display/hide FactBoxes
Alt+F3 Filter to the value in the field
Alt+F4 Close window or close program
Alt+F6 Collapse or expand the active frame
Alt+F12 Optimize space for the current page
Alt+Left Arrow Go to the previous window in the navigation history
Alt+Right Arrow Go to the next window in the navigation history
Alt+Enter Move to the field below without opening the drop-down menu
Alt+Tab Toggle between open windows


The following table provides an overview of shortcut key combinations in the Microsoft Dynamics NAV Development Environment
.

Keyboard Shortcut

Action

Right Arrow Move to the next field or character
Left Arrow Move to the previous field or character
Up Arrow Move to the field above in the same column
Down Arrow Move to the field below in the same column
Delete Clear selected text
Esc Close the window or undo the data entry
End Move to the last field on a line
Home Move to the first field on a line
Arrow, Tab, Enter Move to the next field on non-line FastTabs
F1 Open Help
F10, Alt Select the menu bar and display access keys
F6 Drop-down or look up to view/select
Arrow, Shift+Tab Move to the previous field
Ctrl+C Copy
Ctrl+O or F12 Open the company
Ctrl+V Paste
Ctrl+X Cut
Ctrl+Z Undo
Ctrl+Home Move to the first line in a list
Ctrl+End Move to the last line in a list
Esc Save and close window (equivalent to choosing the OK button)
F3 Insert new line
Alt+F4 Close window or close program


The following table describes the keyboard shortcuts that are available from the C/AL Editor window

Keystroke

Action

Ctrl+G Open the C/AL Globals window to view and declare the global variables, text constants, and functions in the object.
Ctrl+L Open C/AL Locals window to view and declare local variables and text constants for the current function or trigger.
Ctrl+Z Undo the last action.
Ctrl+Y Redo the last action.
Ctrl+J List all members (as IntelliSense) for the local scope.
Ctrl+Space List the members (as IntelliSense) for global scope.
Ctrl+W Select the word.
Ctrl+U Make the selected code lowercase.
Ctrl+Shift+U Make the selected code uppercase.
Ctrl+Shift+N Expand all collapsed code.
Ctrl+K+I Display information about C/AL symbol (such as variable, functions, and parameter) in which the cursor is positioned. This displays the same text as when you point to the element.
Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post..

Share:

How to resolve “Divide By Zero” Error in Navision RTC Report

question-686336_1920

One of the common error in RTC reports is #Error and there are many reasons for #Error in RTC report, but in this post I want to discuss one of the scenarios i.e. "Divide By Zero" exception.

If you have an expression which results Divide By Zero Exception then the result you see is #Error, and normally to avoid this you can write an IIF statement in expression.

For Example:

To display the value as zero if the denominator is zero then I would write something like this

=IIF(Sum(Fields!LineAmt.Value)=0,0,Sum(Fields!NetGP.Value)/Sum(Fields!LineAmt.Value))

 

In the above expression, I am doing a quick check to see if Sum(Fields!LineAmt.Value) is zero, then I want to display Zero otherwise I evaluate my expression, it is pretty simple. But here is the problem the reporting processing engine will still evaluate FALSE part, in this case Sum(Fields!NetGP.Value)/Sum(Fields!LineAmt.Value) and the result will be #Error even if Sum(Fields!LineAmt.Value) is Zero.

There are two possible solutions in this case to resolve the error:

1st Option:

 

=IIF(Sum(Fields!LineAmt.Value)=0, 0,Sum(Fields!NetGP.Value)/iif(Sum(Fields!LineAmt.Value)=0,1,Sum(Fields!LineAmt.Value)))

 

2nd Option:

VB Function

Public Shared Function DivideByZero(ByVal Numerator As Decimal, ByVal Denominator As Decimal) As Decimal

   If Denominator = 0 Then
     Return 0
   End If
   Return (Numerator / Denominator)
End Function

 

and use the following expression

Code.DivideByZero(Sum(Fields!NetGP.Value),Sum(Fields!LineAmt.Value)

 

Personally I prefer the VB function because it is neat and can be used in multiple places.

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post.

Share:

Thursday, October 22, 2015

Milestone10,000 page views reached

I am very happy to see this number on my blog today, I started blogging couple of years ago but did not post more than 5 or 6 blogs, but in the last couple of months I started blogging actively and in just few months the visitors to my blog increased and today it crossed 10,000. Thanks to all for your support and this really encourages me to publish good content and helpful tricks.

image

 

 

 

Thank you All.

Next milestone is 20,000….

Share:

Monday, October 19, 2015

Naming Conventions in Navision

One of the best programming skills every programmer needs to have is to follow the proper naming conventions of the application and also do proper documentation of the changes they are making.  Microsoft provided some naming conventions to follow for Dynamics NAV and please check the below link for more information
Share:

Empty Rows/Columns on the Navision Report when printed to Excel

info-553639_1920

I was asked recently to create a  custom sales report which salespersons wants to output the data into excel, since RTC reports have the option to save as excel, I created a simple report with row header and values, so that users can run this report and use Print Save as Excel option, but when the report is saved as excel it created empty rows and columns.

Below is the example of the excel when the report is saved as excel

image

In my report design the tablix does not have any hidden rows or any other header rows, so I was started checking how those empty rows are created and can be avoided

This is the screenshot of my report design, as you can see there are only two rows, one is the header and other is the detail.

image

After spending an hour or so, I realized that space is not because of the tablix, but rather the space above the tablix and on the sides, once I removed all the space and made sure there is no space as shown below, the issue was fixed.

image

Below is the screenshot of the excel after the change

image

If you encounter these kind of issue, just don’t concentrate on the tablix on the report, do check the space around it . When the report is exported to excel it takes the whole report into consideration, so try to avoid blank spaces if you know the report will be used as excel.

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post..

Share:

Tuesday, October 13, 2015

Simple Tool to clear the data from the field in Navision

tool-78016_1280

It is quite often during the implementation or re-implementation projects that there is a need to change a field data type or delete the field after it was added. The issue is if that field has the data, then in order to change the data type or to delete the field we have to clear the field value otherwise you will get the error. Below is an example of such error

image

If you have multiple companies in the database then you to have to clear the data in all the companies before you make the change, normally I create a process report to clear the data and run that in each company but it is a tedious task if you have many companies. I have come across this situation many times, so I have created a simple tool called Clear Fields, this is a process report with option to filter on the table and the field you want to clear, it also has the option to clear the data in all the companies, so you don’t have to change company and run this in each company.

image

As of now this is only designed to clear one field at a time and it handles any field type, in the future I plan to extend this and add more options to it. I have uploaded the .fob and .txt objects in the below location, it also has a word document which will explain the above the request page options

Download Objects: http://1drv.ms/1Mwrzum

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post..

Share:

Monday, October 12, 2015

Saturday, October 10, 2015

NAV 2016 supports SQL Server Authentication

news-426895_1280

There are lot of exciting features added to Navision 2016, and one of the feature  they added is support to the SQL Server Authentication, in previous versions we can access Navision using the below four credential types

  • Windows
  • UserName
  • NavUserPassword
  • AccessControlService

To learn more about these credential types please refer to the following link

https://msdn.microsoft.com/en-us/library/hh169371%28v=nav.80%29.aspx?f=255&MSPPError=-2147217396

Microsoft Dynamics NAV 2016 supports SQL Server authentication between the Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV database in SQL Server. Previously only Windows authentication was supported, but to configure this we need to do the following procedures

  1. Set Up an Encryption Key
  2. Configure SQL Authentication on the Database
  3. Configure SQL Server Authentication on Microsoft NAV Server Instance (Non-Multitenant) or Configure SQL Server Authentication on Microsoft NAV Server Instance in a Multitenant Deployment

All the details about the above procedures can be found at the below link

https://msdn.microsoft.com/en-us/library/dn951464(v=nav.90).aspx

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post..

Share:

Tuesday, October 6, 2015

Timestamp field available in tables in NAV 2016

If you open a Navision table from SQL server you might have noticed that a timestamp field is available which is a hidden field and was not accessible from Navision in the earlier versions, but with the release of NAV 2016 they made this available to NAV users and you can access that from the Navision, add filters and write code against it.

How to add a timestamp field

In order to access the timestamp field we need to add a custom field to the table with datatype biginteger and we can name it anything but in the properties of the field and we need to set the new property SQL Timestamp to yes.

Whenever you add a record to the table a row version number is generated for that record and stored in that field.

 

image

One important note you can only read the timestamp field we added and use if for filtering purpose or other purpose, but cannot write the data to the field.

The typical usage of this field according to Microsoft help is

A typical use of the timestamp field is for synchronizing data changes in tables, when you want to identify records that have changed since the last synchronization. For example, you can read all the records in a table, and then store the highest timestamp value. Later, you can query and retrieve records that have a higher timestamp value than the stored value.

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post..

Share:

New MetaData Virtual Tables in NAV 2016

In NAV 2016 there are four new metadata virtual tables were introduced and those are

  • Table MetaData ((2000000136)
  • Codeunit MetaData ((2000000137)
  • Page MetaData (2000000138)
  • Report MetaData ((2000000139)

All these tables are read-only and has all the metadata information about the respective objects. The information which you can access in previous version by looking at the properties (Shift + F4) of the object, now you can access all that information from these tables.

For ex: If you select any table and select properties of the table (Shift +F4) you will see the below information

image

Since virtual tables cannot  be accessed directly from the object designer I created a new page with source table as Table MetaData , and when I ran the page and checked the information in that table about Payment Terms Object, I can see all the above information from the table except the CaptionML and Description, which I believe is very useful.

If you ever want to access properties of an object, you can just create a variable of that subtype and access all the information. Ex: If you ever want to know what is the CardPageID of a page or if InsertAllowed on the page, then you can access it using code.

image

Same is true for codeunits, pages and Reports.

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post..

Microsoft Thanks for adding these tables.

Share:

Monday, October 5, 2015

New DataType FILTERPAGEBUILDER in NAV 2016

There is a new datatype introduced in NAV 2016 and it is called FilterPageBuilder, this datatype can be used to create a filter page that enables users to set filters on multiple tables. The page can have multiple controls i.e. multiple records and you can use that to capture filters on the records.
The following are the functions that are available for this datatype.

Share:

Code Coverage in NAV 2016

If you remember in previous versions there is a tool called code coverage which was very useful tool to trace the code / debug the error for the developers, but in NAV 2013 and higher version this was not available but an alternative application profiler was available. If you want to learn more about that please visit my blog about this

http://www.archerpoint.com/blog/Posts/using-application-profiler-run-code-coverage-nav-2013-r2

Good news is Code coverage is back again in NAV 2016.

You can access the code coverage from Departments –>Administration –> Application Tools –> Code Coverage

image

It is easy to start, whenever you want trace the code for a specific action, just click the start button before the action and stop after the action. The code coverage page will list all the objects it got hit and will show the functions/triggers and coverage %. You can even filter the page for specific objects or line type.

I hope this tool will really help the developers/consultants to debug the code easily.

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post..

Share:

Finally a new C/AL editor in NAV 2016 with Intellisense

 

Today NAV 2016 was released and one of the technical improvement/feature in this version is the new C/AL editor with intellisense, this is really a exciting feature. Though this editor is not an advanced version like visual studio editor but it is definitely a great improvement and start over the old C/AL editor.

Intellisense really makes easy to access all the fields, system functions, local functions of the variable/record. It will save so much time and typos.

image

If you declare a variable of record, for ex: I declared a customer variable of subtype Customer table. When I click on the customer variable it will show a small window with all the fields in the customer table with name type and Length. This is very useful when you want to know the type of the field and length of the field in a table.

image

NAV 2016 has so many exciting features and I am pretty sure you will see lot of blogs about those feature in next coming days.

Share:

Monday, August 17, 2015

How to find out who created/modified sales order/purchase order in Navision

question-686336_1280In standard Navision if you want to find out who created/modified a document i.e. sales order, purchase order, transfer order… you cannot unless you do have a customization or you have to enable a change log setup

 

In this blog I will discuss how to make the customization to achieve this, but if you want to know how to achieve this using change log setup please refer to the following links

http://www.archerpoint.com/blog/Posts/setting-change-logs-microsoft-dynamics-nav-2013
https://msdn.microsoft.com/en-us/library/hh169208%28v=nav.71%29.aspx?f=255&MSPPError=-2147217396

 

Please note if you are using change log to trace for insertion and modification, then don’t include all the fields in your change log setup include only some fields.

How to achieve with customization: You need to have development license to make the below modifications

This example is for Sales order but you can do the same for other documents.

Open development environment and from the Object Designer select table tab on the left hand side and choose table 36 – Sales Header

Select Design and add the following four fields,

1. Created By (Code 50)

2. Created On ( DateTime)

3. Last Modified By (Code 50)

4. Last Modified On (DateTime) 

image

The field number’s can be different. Save the change by choosing File –> Save and Then File –> Exit.

Open the table 36 again in design mode, then from Menu choose View –> View C/AL Code or Press F9

As show in the below Figure 1, you need add the custom code in the OnInsert Trigger and OnModifiy Trigger of the object

image

Figure 1: Showing Table 36 Sales Header in Design Mode C/AL Code – OnInsert and OnModify Triggers

OnInsert()
// SK0001 >>
"Created By" := USERID;
"Created On":= CURRENTDATETIME;
// SK0001 <<

OnModify()
// SK0001 >>
"Modified By" := USERID;
"Modified On" := CURRENTDATETIME;
// SK0001 <<



Save the changes by choosing File –> Save and Then File –> Exit.


Now you can go to the Sales Order Page (42) or  Sales List Page and add those fields, if you want to track this in purchase or transfer then you can repeat the same steps as above but for the purchase use  38 – Purchase Header  table and for Transfer Order – 5740 Transfer Header Table.


With these simple modifications now you can track who created/modified or when they created/modified.


Please leave your comments and suggestions.

Share:

Friday, August 14, 2015

Change color of non-editable field on Navision page

Whenever you make a field non-editable on the page and if that field does not have any table relation set on the table then the background of the field shows in grey color as show in the below figure

For this example I made the salesperson code field non-editable on the customer card page, as you can see it shows value in the blue but where as the Last Date Modified field textbox background is gray it is little dull and sometimes hard to read, it is not a big deal but how can you make that field look like salesperson code field without any table relation ?

image

Figure 1: Customer Card showing Salesperson Code and Last Date Modified fields in non-editable mode.

There is a field property called “LookUp”, which provides a lookup window for the textbox, you normally don’t see this property being used other than on Journal Pages or other. Ex: Item Journal, General Journal…. . This property is set there because the batch name field source is a variable so you have to explicitly set to LookUp.

Even though we don’t want to lookup in this scenario, but if you set this property to true on the above field “Last Date Modified” then the field will look as shown in the below figure

image

Figure 2: Customer Card showing Salesperson Code and Last Date Modified fields in non-editable mode after setting LookUp

Please let me know your comments and suggestions.

Share:

Thursday, August 13, 2015

Cumulative Update 10 for Microsoft Dynamics NAV 2015 has been released (Build 42222)

This Cumulative Update 10includes hotfixes and regulatory features released for Microsoft Dynamics NAV 2015, including hotfixes and regulatory features released in previous Cumulative Updates.

Note You must convert the database if you are upgrading to this cumulative update from a cumulative update earlier than cumulative update 9 (build 41779). For more information, see Converting a Database in Help for Microsoft Dynamics NAV.

The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

  • AU - Australia
  • AT - Austria
  • BE - Belgium
  • CH - Switzerland
  • DE - Germany
  • DK - Denmark
  • ES - Spain
  • FI - Finland
  • FR - France
  • IS - Iceland
  • IT - Italy
  • NA - North America
  • NL - Netherlands
  • NO - Norway
  • NZ - New Zealand
  • SE - Sweden
  • UK - United Kingdom
Where to download this cumulative update

You can download the cumulative update from KB 3086434 – Cumulative Update 10 for Microsoft Dynamics NAV 2015

Problems that are resolved in this cumulative update

The following problems are resolved in this cumulative update:

Platform hotfixes

ID

Title

375205

The SaveValues property stores variable lengths that lead to strange side effects.

375140

The Windows client is running slowly and consumes a lot of memory.

375134

Multiple lines are selected from a list after clicking on a single line.

375118

"Server page is not open" error message when closing a page.

375196

Different error messages appear if you setup the UI Elements Removal property to LicenseFile and to None when you start the client.

375338

Exception is thrown by Sync-NAVTenant after deleting a table.

375292

It is possible to create a dimension value with a blank value in the Dimension Code field.

374750

Data is changed after you run the Export to Excel function.

375308

It should be possible to ignore diagrams in Excel sheets when doing an import.

375213

The Windows client crashes when a modal page is opened in the background.

375311

"The metadata object Page XXXX was not found" error message

375327

The client crashes when you remove the Edit action from a sales order list in the Order Processor profile via personalization.

375275

Streaming of dotnet assemblies does not support processor architecture.

375337

The Windows client crashes if invalid characters are added to a text screen.

375332

Enable ADFS to allow e-mail logging in combination with Exchange Online.

Application hotfixes

ID

Title

Functional area

Changed objects

375121

When using the Send-to Excel function with StyleSheets, the DateTime fields are converted to strings.

Administration

N/A

375241

The upgrade process is stuck.

Administration

COD 104055

375298

Issues with the Profile Translation feature in Microsoft Dynamics NAV 2015 CU 9.

Administration

COD9170
Demotool\COD101994

375312

The upgrade hangs for Dimensions.

Administration

COD 104055

375087

The Negative-Sign Identifier field is not used correctly when importing files of type Fixed and the sign is placed before the amount.

Cash Management

COD 1201 COD 1241 COD 1262

375088

If the Data Line Tag field is empty, all lines are skipped when importing files of type Fixed.

Cash Management

COD 1201 COD 1241 COD 1262

375280

A note sent over on a purchase invoice from another user is posted and then creates duplicate notes in My Notifications.

Client

COD 1305 COD 5063 COD 5407 COD 5704 COD 5705 COD 5923 COD 5988 COD 86 COD 87 COD 900 COD 96 COD 97 TAB 904 COD 12469 COD 17368 COD 17387

375392

"The IC Partner does not exist. Identification fields and values: Code='XXX'" error message when you try to import an intercompany file to the inbox.

Finance

COD 427 COD 435

375190

If you apply entries with a payment tolerance, and a payment discount and a rounding precision are set up, then you get a wrong remaining amount in the payment and not in the invoice as expected on the Customer Ledger Entries page.

Finance

COD 12

375144

The Recurring Journal page allows the posting of lines that have already expired.

Finance

COD 13

375163

The Quote report prints an extra page in Word.

Finance

REP 1304

375346

An inbound item ledger entry applied to a previous outbound item ledger entry is not cost-adjusted when the outbound entry is related to a job.

Inventory

COD 22

375397

The line amount in job ledger entries is incorrect when you partially post receipt of a purchase order linked to a job and then create an invoice by using the Get Receipt Lines function and post.

Jobs

COD 90

375191

A job planning line is created when you post a job journal line with no value in the Line Type column.

Jobs

COD 1026

375249

"Due date must have a value in Planning Component: It cannot be zero or empty" error message when you run the Calculate Plan function on the Order Planning page.

Manufacturing

TAB 246

375315

If you create a new contact from the Related Contacts page, the Company No. field is not filled automatically.

Marketing

PAG 5050

375017

"Amount must be negative in Gen. Journal Line Journal Template Name=" error message when you post a prepayment credit memo in a sales order where a payment method code with a balance account is used.

Prepayments

COD 11 COD 444 COD 367

375283

The Amount Incl. VAT field and the Outstanding Amount field should be calculated in the same way on sales order lines.

Sales

TAB 36 TAB 37 TAB 38 TAB 39

375296

The Shipment No. and Shipment Line No. fields should not get filled in if you use the Copy Document functionality from a sales document.

Sales

COD 6620

375156

A reservation entry with a tracking entry type and with an order-to-order binding is created when you cancel the existing reservation and the Order Tracking & Action Msg. option is enabled.

Sales

COD 99000831

375336

The Restore functionality does not provide the accurate line amount when you restore a sales return order with the Line Discount option.

Sales

COD 5063

375185

"An attempt was made to change an old version of a Sales Header record. The record should first be reread from the database. This is a programming error" error message when you create a sales invoice through the Get Shipment Lines function.

Sales

COD 60 COD 70 TAB 111 TAB 121

375310

If you have a customer or a vendor with an empty country code, you can not check the validity of the VAT registration number.

VAT/Sales Tax/Intrastat

COD 249

375206

The Calc. Regenerative Plan function replans an existing sales order when you already picked for the sale.

Warehouse

COD 7307

375248

The Whse. Item Tracking line is not removed when you assign it to a production order component that is fully picked when the production order is of type Make to Order.

Warehouse

TAB 5405

375189

"Quantity (Base) must be 0 or 1 when Serial no. is stated" error message when setting a serial number in a picking document.

Warehouse

COD 7307

How to install a Microsoft Dynamics NAV 2015 cumulative update
See How to install a Microsoft Dynamics NAV 2015 Cumulative Update .
Share:

Cumulative Update 29 for Microsoft Dynamics NAV 2013 has been released (Build 42219)

This Cumulative Update 29 includes hotfixes and regulatory features released for Microsoft Dynamics NAV 2013, including hotfixes and regulatory features released in previous Cumulative Updates.

The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

  • AU - Australia
  • AT - Austria
  • BE - Belgium
  • CH - Switzerland
  • DE - Germany
  • DK - Denmark
  • ES - Spain
  • FI - Finland
  • FR - France
  • IS - Iceland
  • IT - Italy
  • NA - North America
  • NL - Netherlands
  • NO - Norway
  • NZ - New Zealand
  • SE - Sweden
  • UK - United Kingdom

Where to download this Cumulative Update

You can download the cumulative update from KB 3086433 - Cumulative Update 29 for Microsoft Dynamics NAV 2013 (Build 42219).

Problems that are resolved in this cumulative update

 

Platform hotfixes

Note You may have to compile the objects in your database after you apply this hotfix.

ID

Title

375110

"Server page is not open" error message when closing a page.

375137

Suppress .NET warning when running the NAV server and the client on the same machine.

375309

The Windows client disconnects when renaming a record.

375351

"The company <name> does not exist" error message when you try to change a language if the company name has a leading space character.

Application hotfixes

ID

Title

KB Functional Area

Changed Objects

375108

The expiration date on a recurring journal should not be based on the work date.

Finance

COD 13

375287

If you use the Insert G/L Accounts function on the Account Schedule page, the lines are inserted in the top if you place the cursor on a new line at the end.

Finance

PAG 104

375218

The Adjust Cost Item Entries batch job recognizes wrong costs on adjustment value entries when an inbound item ledger entry is reversed on a later date with a fixed cost application.

Inventory

TAB 339 TAB 5802

375305

Wrong average cost of the in-transit location after upgrading from a previous version to version 2013 with a new design.

Inventory

N/A

375142

The VAT Amount field is wrongly calculated if the Pmt. Disc. Excl. VAT and the VAT Tolerance % fields are enabled in the setup.

VAT/Sales Tax/Intrastat

COD 90

 

How to install a Microsoft Dynamics NAV 2013 cumulative update

See how to install a Microsoft Dynamics NAV 2013 cumulative update (https://support.microsoft.com/kb/2834770/ ) .
Share:

Cumulative Update 22 for Microsoft Dynamics NAV 2013 R2 has been released (Build 42221)

This Cumulative Update 22 includes hotfixes and regulatory features released for Microsoft Dynamics NAV 2013 R2, including hotfixes and regulatory features released in previous Cumulative Updates.

The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

  • AU - Australia
  • AT - Austria
  • BE - Belgium
  • CH - Switzerland
  • DE - Germany
  • DK - Denmark
  • ES - Spain
  • FI - Finland
  • FR - France
  • IS - Iceland
  • IT - Italy
  • NA - North America
  • NL - Netherlands
  • NO - Norway
  • NZ - New Zealand
  • SE - Sweden
  • UK - United Kingdom

Where to download this cumulative update

You can download the cumulative update from KB 3084775 – Cumulative Update 22 for Microsoft Dynamics NAV 2013 R2 (Build 42221).

Problems that are resolved in this cumulative update

 

Platform hotfixes

Note You may have to compile the objects in your database after you apply this hotfix.

ID

Title

375136

The Windows client is running slowly and consuming a lot of memory.

375204

The SaveValues property stores variable lengths that lead to strange side effects.

375135

Suppress .NET warning when running the NAV server and the client on the same machine.

375117

"Server page is not open" error message when closing a page.

374727

Data is changed after you run the Export to Excel function.

375307

It should be possible to ignore diagrams in Excel sheets when doing an import.

375347

"The company <name> does not exist" error message when you try to change language if the company name has a leading or trailing space.

375356

"The Element <element name> is unexpected" error message when you import a document with an XMLport if the first element is self-closing.

375331

The Windows client crashes if invalid characters are added to a text screen.

Application hotfixes

ID

Title

Functional area

Changed objects

375138

It is possible to create a dimension value with a blank value in the Dimension Code field.

Administration

PAG 537 TAB 349

375026

A note sent over on a purchase invoice from another user is posted and then creates duplicate notes in My Notifications.

Client

COD 447 COD 5063 COD 5407 COD 5704 COD 5705 COD 5923 COD 5988 COD 80 COD 86 COD 87 COD 90 COD 900 COD 96 COD 97 TAB 904

375225

"The IC Partner does not exist. Identification fields and values: Code='XXX'" error message when you try to import an intercompany file to the inbox.

Finance

COD 427 COD 435

375254

The expiration date on a recurring journal should not be based on the work date.

Finance

COD 13

375119

An inbound item ledger entry applied to a previous outbound item ledger entry is not cost-adjusted when the outbound entry is related to a job.

Inventory

COD 22

375304

Wrong average cost of the in-transit location after upgrading from a previous version to version 2013 with a new design.

Inventory

N/A

375290

The line amount in job ledger entries is incorrect when you partially post receipt of a purchase order linked to a job and then create an invoice by using the Get Receipt Lines function and post.

Jobs

COD 90

375214

The filter provided in the Outstanding Orders and Amt. Rcd. Not Invoiced fields on a job task line is wrong when you drill down and the job task type is End-Total.

Jobs

PAG 1002

375390

The available capacity is calculated as an integer instead of a decimal.

Manufacturing

COD 99000774

375125

The Amount Incl. VAT field and the Outstanding Amount field should be calculated in the same way on sales order lines.

Sales

TAB 36 TAB 37 TAB 38 TAB 39

375318

A purchase order with a special order option is reserved when you create a sales order with the Reserve Always option on the item card.

Sales

COD 99000845

375187

The Service Document - Test report does not show invoice discounts correctly if one of the service lines has the Allow Invoice Disc. option set to No.

Service

COD 228 REP 5915

How to install a Microsoft Dynamics NAV 2013 R2 cumulative update

See
how to install a Microsoft Dynamics NAV 2013 cumulative update
(https://support.microsoft.com/kb/2834770/ ) .
Share: