Items with Multiple Item Unit of Measures in Navision

Items in Microsoft Dynamics NAV can have multiple units of measures, you can for example sell in one unit of measure and purchase in another unit of measure. This is great, but there are some things to be aware of when setting an item up with different unit of measures.
An item must have a base unit of measure. The base unit of measure is how the inventory quantities are displayed and how the inventory is valued. It is also how it is produced (you can have a Production BOM in a different unit of measure, but the Routing and the Production Order will always be in the base unit of measure).

It is considered best practice to have the base unit of measure to be the smallest one, this to avoid having fractions of units leftover due to rounding. Sometimes this is not possible and then it is good to know what is behind the ‘best practice’ to avoid getting into troubles.

Here is an example of how to recreate an issue with different unit of measures:

Let’s say we have an item that we purchase in feet and sell in inch and we setup the base unit of measure to be feet. The items unit of measures will then be setup like this.

 The quantity per base unit for an inch is set to 0.08333 (the base unit must always be 1), this field has a maximum of five decimals so it gets rounded like this (which is part of the problem). One feet equals 12 inch (for all the metric people 🙂).

We now purchase 10 feet which will create the following transaction.

 After this we sell 120 inch (which is the same as 10 feet), this creates the following transaction.

As you can see the 120 inch translates to 9.9996 feet due to the rounding in the items unit of measures. This creates a scenario where we have 0.0004 feet left in inventory which we shouldn’t have.

Setting the items base unit of measure to inch would have prevented this situation (and you can still purchase it in feet). This is why it is always recommended to set the smallest unit of measure as the base unit of measure.

Although, this only happens when there is a rounding involved, knowing this you can say that it is quite safe to setup items with a pair as the base unit of measure and sometimes sell it in pieces or setup an item with meters as the base and sometimes sell it in cm, etc.

The situations that I have seen where it was more or less impossible to set the base unit of measure to the smallest unit was for produced items. In those cases all routings, work instructions, costs, etc. was related to a specific unit of measure and it was not feasible to rearrange the entire production setup just to have the smallest unit of measure be the base.

If a situation like the above already exists then having procedures to adjust the fractions out each month might be appropriate (something like a stock count).

Editing a non-editable field in a table

When a field on a table is set to Editable = No, the field is not editable in the RTC.
So if you want the field editable, you would need to change the table and the field becomes editable.

It seems there is a workaround for this. Other than using a CodeUnit to update there is an alternative method as well.

If you look at a page field, you will see that the field has an editable property defaulting to <False>.
When you change the property to True explicitly, you can actually edit the field on the page, even though the table has the editable property as No.

Here is one of the blog posts that describes this in detail.

http://mibuso.com/blogs/kriki/2011/05/09/edit-a-non-editable-field/

Import Excel File in Navision using Excel Buffer

 The post is to import the data in a table From an Excel File.

Most of us know this feature is there in standard objects of Microsoft Dynamics NAV from a long time.
If you are one of those who don't know about it please go through the article.

Let`s say i have a table with following fields as shown below -

And the Excel file that i want to import as shown below -

The Target is to import the data from excel in the Navision Table.

I have Created a Page to view imported entries and on the same page there is an action of Import From Excel.
For Importing data from Excel we will be using Standard ReadSheet Function in Excel Buffer Table in a Report. The Report will be Processing Only and will have options to Add the Entries or Replace the Entries.

Below is the code that have been used to achieve desired result -

First the Report Checks the option that user selected in Request Page of Replacing Entries or Adding Entries.
Then using Standard Function of Excel Buffer the Excel File Selected by User is Opened.
Then Using ReadSheet Function Excel Buffer Reads the Data in Selected Excel File.
Then System using a function try to get No. of Rows and Column in Selected Excel Sheet.
As First Row in Our Case Contain Caption, so it start inserting data in table from Row No 2 To Last Row.

What system does in Function GetLastRowandColumns -

What system Does in Function Insert Data -

System read the data in a row and assign it to actual table fields.

To Open Excel File and Sheet Selection Code on Request Page is -

Now let's try to execute the process from the Page -

Select Import From Excel and Select Option to Add / Replace Entries. Once Clicked Ok System will ask for the Excel File to Import, as shown below -

Once File is selected and Excel File have multiple sheets, System will prompt to select the sheet also.

If everything goes well a confirmation message will appear and the data will be updated as shown below.

Hope you find above post helpful.

For Objects used in this post please download same from my Google Drive.
Click Here to Download - Prateek.ExcelImport#NAV

OLE or Automation Error: "F9043C85-F6F2-101A-A3C9-08002B2F49FB" not found

How to Resolve OLE or Automation Error

How many times do you got an error when we are trying to do a Data Migration here's the message below:

Could not create an instance of the OLE control or Automation server identified by 

GUID-{F9043C85-F6F2-101A-A3C9-08002B2F49FB}. 

Check that the OLE control or Automation server is correctly installed and registered.

What this message is trying to tell you is that something has not been installed correctly on this client

That something is a file which is not Register with the Client i.e. "comdlg32.ocx".

The error you have {F9043C85-F6F2-101A-A3C9-08002B2F49FB} refers to the GUID for 

Microsoft Common Dialog Control, v6.0. It is typically placed here in the below given folders : 

'C:\WINDOWS\System32\comdlg32.ocx' and C:\Windows\SysWOW64\comdlg32.ocx'


Solution :

Step 1 :- Download the OCX file from the given link below 

https://drive.google.com/file/d/0B39tCHSFl7BLZFUtbHIzVnJPN0U/view?usp=sharing

Step 2 :- Copy File to both Folders :-
               C:\Windows\System32
               C:\Windows\SysWOW64 

Step 3 :- Go to Start and type in CMD in the search space.
               [IMPORTANT] Right click CMD and click "Run as administrator".

Step 4 :- Copy and Paste the below given command on CMD & press Enter to Run it
               (the following depending on sysWOW 64 or System32).

First Command :
               regsvr32 c:\Windows\system32\comdlg32.ocx

After pressing Enter you will get below Confirmation Message.  
        



Now Second Command :
                regsvr32 c:\Windows\SysWOW64\comdlg32.ocx



After pressing Enter you will get below Confirmation Message.  




Hopefully Error will be Resolved after Registering above OCX file.

Note :- Usually you will also get an error if you try to compile Codeunit 412...

Hope it helps :-)

How to Convert Every First letter of a Field into Uppercase by Default

If a Name is entered  into Name Field in Customer Card like for example "prateek gupta" it should automatically convert into "Prateek Gupta"

Global Variables :

Name              Data Type          Length

Indx                 Integer                            

NewValue         Text                     20

Cap                  Boolean

##Code on Trigger : OnValidate (on a Table Field that you wish to apply this code on)

How to Convert First Letter Capital in NAV Field

This will capitalize your first letter and lowercase the rest.

If varName is your variable

## Code :

newval := UPPERCASE(COPYSTR(varName,1,1)) + LOWERCASE(COPYSTR(varName,2));

##Output :

If the Input is "prateek Gupta"

then Output will be "Prateek gupta"

Method 'PageXXXX.a60Action1102601028a62_a45_OnAction' not found

How many of you have faced this issue when 

you click on statistics or Release button ?

Method 'Page9304.a60Action1102601028a62_a45_OnAction' not found

##On sales return order page when you click on Statistics button system will give you below error.

STEPS TO ORIGINATE ISSUE :

Steps to Follow are given below

•    <Step #1: > Open Navision RTC 2013 R2

•    <Step #2: > Open Sales Return Order

•    <Step #3: > Generate New Sales Return Order by filling all the required Fields in header and line.

•    <Step #4: > Click on  Statistics Action Button, you get the error

RESOLUTION :

This happens because action button and method is not linked properly.

To fix this issue design the “Page ID – 6630” and then go to page actions.

Change Old Action Name “Statistics” to “<Action1102601028>”.

Important Note: - Now to know in which Page ID What Action Name is to be given to a particular Action Button.

Read the Error Message Carefully : - (See the Yellow color highlighted first  is Page ID & Second is Action Name)

[Method '    Page9304     .a60     Action1102601028    a62_a45_OnAction' not found.]

Well rename the Name to the action with <Action> and the issue is solved.

How to Calculate Week No. Month Wise from a Date NAV RDLC (Starting Weekday "SUNDAY")

In Navision RDLC if we want Week No. Month Wise i.e. if a invoice is posted on 15/04/2016 then Week no. should come 3.

Note : Initially week starts from Monday but what if we want week no. of a month where week starts from Sunday.

When you are developing a Report in NAV with a Requirement that with Posting Date you also have to provide Week No. in which that particular Posting Date comes. (Month Wise with starting week day "Sunday").


As per the above requirement given below are the required Variables & Code for the same.


##C/AL Globals (Variables) :

Name               DataType         Subtype
DateRec          Record                   Date
WeekNo          Integer
StartDate         Date
EndDate          Date
Ctr                   Integer
PDStartDate    Date
PDEndDate     Date
PDStartBLN   Boolean
DStartDate      Date
DEndDate       Date


##Code on Trigger (On After Get Record) :

Redesigned C/AL Editor – NAV 2016

The C/AL Editor in the Microsoft Dynamics NAV Development Environment has been redesigned to give you more coding capabilities.

Coding in the new C/AL editor is like before except you benefit from new features such as IntelliSense, name completion, change tracking, improved syntax highlighting and colorization.

The new design has a look-and-feel that resembles the Debugger regarding breakpoints.

And here it is:-

 

And if you start typing then it will start IntelliSense and you can press tab/enter to name completion.

  

 

1).Name Completion:  

 

Now Editor suggests the Variable Names which are available.

2).IntelliSense:  

Now Editor Suggest available options for the variables.

3).Change Tracking:

  

4).Improved syntax highlighting and colorization:

NOTE: - Can we Use Old Editor in NAV 2016?

YES, If you still want to use old version editor, you can use the old version of the C/AL Editor by running the Microsoft Dynamics NAV Development Environment from a command prompt and setting the useoldeditor parameter.

Steps: - Run the command prompt (RUN) from the START Option. And add below string and Enter

"C:\Program Files (x86)\Microsoft Dynamics NAV\90\RoleTailored Client\finsql.exe" useoldeditor=Yes

  

 Finsql.exe useoldeditor=yes

useoldeditor

Specifies whether to use the C/AL Editor that was available in Microsoft Dynamics NAV 2015 and earlier versions. The C/AL Editor was redesigned in Microsoft Dynamics NAV 2016. To use the old editor, specify the parameter as useoldeditor=yes or useoldeditor. To use the new editor, omit the parameter or specify it as useoldeditor=no.

With the Release of NAV 2016 Timestamp Field is Available in Tables

Timestamp Field Available in Tables

   1.         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.

2.         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.

    3.         In order to access the timestamp field we need to add a custom field to the table with data type            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.

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.

Note: We cannot add the field as a key in the table, but that does not prevent us from sorting on it. This method will find not only new records, but also existing records that were modified since last check. To locate deleted records however would require some additional logic.

**But this field provide integer no not date and time

Autofill Local/Global Variable Name for forms, records, reports, xmlports, etc. in NAV 2015 & above

By accident I discovered something quite interesting last week and I thought it was worth sharing it through a blog post.

When you create an object type variable in Microsoft Dynamics NAV 2015 & above and you leave out the name then NAV will automatically suggest the name based on the subtype. So, when creating variables that are forms, records, reports, xmlports, etc. then there is no need to enter a variable name anymore. Nice! 🙂

Here is how it works;


Create a new variable by leaving the name blank and enter the datatype and subtype.

Now, when leaving the subtype field Dynamics NAV automatically populates the name as the subtype where the spaces, etc. been removed. Cool!

I will start using this all the time now, saves a lot of key strokes in my mind. It does assumes you are ok with the suggested name that is.

This new feature made my day when I discovered it, I bit nerdy I know. 🙂

If you create a second variable for the same object it gets the same name, would have been nice if it had added a ‘2’ to the end or something, but I guess you can’t get everything.

History of Navision / Microsoft Dynamics NAV

Brief history of Microsoft Dynamics Nav / Navision software versions: 

Dynamics NAV 2017
Oct. 2016 - Dynamics NAV 2017 was released

Dynamics NAV 2016
Oct. 2015 - Dynamics NAV 2016 was released

Dynamics NAV 2015
Oct. 2014 - Dynamics NAV 2015 was released

Dynamics NAV 2013 R2
Oct. 2013 - Dynamics NAV 2013 R2 was released

Dynamics NAV 2013
Sep. 2012 - Dynamics NAV 2013 was released

Dynamics NAV 2009
Dec. 2010 - Dynamics NAV 2009 R2 was released
Sep. 2009 - Service Pack 1 for Dynamics NAV 2009 released
Nov. 2008 - Dynamics NAV 2009 released (pre-relase name was Dynamics NAV 6.0)

Dynamics NAV 5.0
Dynamics NAV 5.0 Service Pack 1 (also called Dynamics NAV 5.1) released 
Dynamics NAV 5.0 released

Microsoft Business Solutions Navision 4.0
Navision 4.0 Service Pack 3 (also called Navision 4.3)
Navision 4.0 Service Pack 2 (also called Navision 4.2)
Navision 4.0 Service Pack 1 (also called Navision 4.1)
Navision 4.0 released

Microsoft Business Solutions Navision 3.7
Navision 3.70B (also called 3.7 Service Pack B)
Navision 3.70A (also called 3.7 Service Pack A)
Navision 3.70

Jul. 2002 - Microsoft Acquired Navision and creates Microsoft Business Solutions division
Navision Attain 3.x
Navision Attain 3.6
Navision Attain 3.1
Navision Attain 3.0

Navision Financials 
Navision Financials 2.x
1996 - Navision Financials 1.x
1995 - Navision Financials for Windows released (for Windows 95)


1985 - First version of proto-Navision released, "PC Plus" for DOS
1984 - Company founded in Denmark