Print Send To Excel do not export all columns

Few weeks ago, I ran into a strange issue and in this blog I will discuss the issue and the solution which worked for me.

I got a requirement to add the salesperson code and sell-to customer name to the salesline page, since those fields are not part of the salesline table, I have added the code on the salesline list page to retrieve the salesheader record and display those values on the page.

Everything worked well, but when we try to use the Print –> Send to Excel option those custom fields are not available on the excel.  Couple of users were able to get those fields on the excel but not all the users.

Initially I thought it could be a permission issue on the sales header table but that was not the case, even user with SUPER permission has the same issue. We have checked if the excel version is different between those users but that’s not the case, everybody is running the same version.  I have searched the Cumulative updates if there is bug or fix for that kind of error but unfortunately I have not found anything related.

I have found a standard page which uses the similar kind of logic, the page is “Customer Order Lines Status” with ID 10010 which uses the same kind logic which it displays the status from the salesheader table and I have ran into same issue with that page, all fields were exported except the salesheader fields.

Finally, I saw one difference between the users who are able to export all the fields and other, and that is the DynamicsNAV menu on the Excel, The users who have the DynamicsNAV menu are not able to export all the fields, so I went ahead and disabled the DynamicsNAV Add-on.

Voila, that resolved the issue.  I have not uninstalled just disabled it, if you uninstall nothing will export.  I am not sure what is the reason but that resolved my issue.

Here are the steps to disable the add-on

1. Open Excel, Go To File –> Options

2. Select Add-Ins and in the bottom select the Go beside Mange Add-ons

3. Uncheck the box for the Dynamics NAV and Click Ok

I hope this will help others who run into the same issue.

Please leave your comments/suggestions.

Share:

Read more..

Validate Email Address in NAV using RegEx

Last week I was working on a shipment notification project where I need to send Email Notification of the shipment and one thing we need to check is the email address is valid or not. In Standard NAV the SMTP mail codeunit or Mail Management codeunit has a function to checkValidEmailAddress but it does a very basic validation and it did not meet our needs so I have written a new function to validate the email address.

I have used  RegEx( Regular Expression) and According to Wikipedia

“A regular expression, regex or regexp^[1] (sometimes called a rational expression)^[2][3] is, in theoretical computer science and formal language theory, a sequence of characters that define a search pattern. Usually this pattern is then used by string searching algorithms for "find" or "find and replace" operations on strings.”

Since we have the access the functions of RegEx function using DotNet, I went a ahead and wrote the following function to validate the email address using RegEx.

In our case we could store multiple email addresses in a field, so I have used String Array to parse and validate the email address.

PROCEDURE ValidateEmailAddresses@1000000008(EmailAddresses@1000000000 : Text);
     VAR
       RegEx@1000000004 : DotNet "'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Text.RegularExpressions.Regex";
       DotNetString@1000000003 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";
       EmailAddrArray@1000000002 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
       Convert@1000000001 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
       I@1000000005 : Integer;
       EmailAddress@1000000006 : Text;
     BEGIN
       EmailAddresses := CONVERTSTR(EmailAddresses,',',';');
       EmailAddresses := DELCHR(EmailAddresses,'<>');
       EmailAddrArray := RegEx.Split(EmailAddresses,';');
       FOR I := 1 TO EmailAddrArray.GetLength(0) DO BEGIN
         EmailAddress := EmailAddrArray.GetValue(I-1);
         IF NOT RegEx.IsMatch
               (EmailAddress,'^[\w!#$%&*+\-/=?\^_`{|}~]+(\.[\w!#$%&*+\-/=?\^_`{|}~]+)*@((([\-\w]+\.)+[a-zA-Z]{2,4})|(([0-9]{1,3}\.){3}[0-9]{1,3}))$') THEN
           ERROR(Text106);
       END;
     END;

I got the above email address regular expression pattern from the below link, so please visit the below to know what validation it does.
https://www.rhyous.com/2010/06/15/regular-expressions-in-cincluding-a-new-comprehensive-email-pattern/

If you have any other tips or suggestions , please do share them in the comments below.

Share:

Read more..

MAPI is not working properly while sending email in NAV

Recently I have worked on a issue related to E-mail, and would like to share the solution which helped me to resolve the issue.

One user is are getting the following error “MAPI is not working properly. One way to get MAPI working is to install Microsoft Exchange” when they try to send the email. This is version NAV 2009 R2, and in this version one way to send a report by email is using, File –> Send –> Report by Email. as show in Fig 1.

When I have searched the web I have found articles/forums where it was suggested to unregister MAPI OCX Dll File and Register it again, I have performed those steps but still it did not help to resolve the issue.

Fig 1.

One thing I did notice on that workstation was even though Outlook 2013 was installed it was never setup/initialized, once I have setup an account on outlook and then try to send the report by Email it opened the Outlook Client with Report body.

So, please make sure there is a default MAPI email client installed and setup when you receive the above error and that resolved the issue in my case.

If you have any other tips or suggestions to resolve this error, please do share them in the comments below.

Share:

Read more..

How to print a remote file from NAV

I have seen couple of times the requirement where we need to print a document from NAV which is not a report, it could be a marketing campaign letter, sales sheets or any other document.

Most of the time we upload these kind of documents on a website/remote site, so if we need print those documents with every invoice or any other statement, below is the function you can use from NAV.

In the below example I just took a random PDF URL from the web and used it for testing.

The key in this function is to download the document from the web using WebClient locally and then use the Process to print the document to the printer.

LOCAL PROCEDURE PrintRemoteFile@1240060002();
     VAR
       FileURL@1240060000 : Text;
       ProcessStartInfo@1240060001 : DotNet "'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Diagnostics.ProcessStartInfo";
       Process@1240060002 : DotNet "'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Diagnostics.Process";
       WebClient@1240060003 : DotNet "'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.WebClient";
       LocalFileName@1240060004 : Text;
     BEGIN
       FileURL := 'http://che.org.il/wp-content/uploads/2016/12/pdf-sample.pdf';
       ProcessStartInfo := ProcessStartInfo.ProcessStartInfo();
       WebClient := WebClient.WebClient();
       LocalFileName := 'C:\Temp\TempFile.pdf';
       WebClient.DownloadFile(FileURL,LocalFileName);
       ProcessStartInfo.FileName := LocalFileName;
       ProcessStartInfo.Verb := 'Print';
       ProcessStartInfo.CreateNoWindow := FALSE;
       Process := Process.Process;
       Process := Process.Start(ProcessStartInfo);
       MESSAGE('Document Printed');
     END;

If you have any other tips or suggestions to resolve this error, please do share them in the comments below.

Share:

Read more..

How to skip unlicensed objects when exporting as text in NAV

One of the tools I often use to review the objects or for debugging is Prism (https://www.stati-cal.com/). It is a great productivity tool for NAV Developers and Architects which helps quickly and effortlessly to get an overview of C/AL code, modules and dependencies. There are a couple of options in the tool to import the objects, one of the option is to have the objects in a .txt format which I normally use.  One issue I often encounter when I export all the objects in .txt format is, our license does not have permission to export all the objects into text format this is because of some restrictions placed by the vendors on their add-on objects.

In this blog, I will discuss different ways to export the objects into text format and by skipping the objects which the license does not have permission.

Option 1:

Use Powershell command Export-NAVApplicationObject with an ExportTxtSkipUnlicensed option. My colleague William Warnke explained this cmdlet and other in his blog. Please read it.

The CmdLet uses the license is which is uploaded into the database, but if you change the license temporarily with change function it will not work, so the license should be uploaded to use this option.

Option 2:

Use the Edit –> Toggle Mark function on all the objects and then unmark the objects which you don’t have permission, but for this you need to know which objects you don’t have permission to export; otherwise you have to try to export all the objects and when you get an error for an object exclude that object in the next iteration and repeat this process, this is very time consuming and not efficient.

I think this is the only option for the older databases before NAV 2009.

Option 3:

This option works only for newer versions 2009 and above. Select all the objects and use the Lock Function to Lock all the objects, it will lock only those objects which you have permission to export into text format. Once you lock the objects filter the objects which are locked and export them into .txt.

(Thanks again to William Warnke for the tip)

Do you have any other tips or suggestions? Please let me know in the comments below.

Share:

Read more..

How to move a file from one folder to another on FTP Server

To move a file from one folder to another we normally using System.IO.File functions Move or Copy but in this scenario these functions will not work on FTP server. To achieve this on FTP we need to use FTP web request methods and below is the code i have used for that.

For your reference please check my previous post for downloading files from the FTP Server How to download files from FTP server.

The function is very simple. The tricky part over here is what method to use on FTP WebRequest to move the file, the method we have to use is ‘RENAME’ and using RenameTo  property to specify the file path.

[TryFunction]
LOCAL PROCEDURE MoveFileFromConcurFTPToArchive@1240060038(FileToDownload@1240060010 : Text);
VAR
  FTPWebRequest@1240060000 : DotNet "'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.FtpWebRequest";
  FTPWebResponse@1240060001 : DotNet "'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.FtpWebResponse";
  NetworkCredential@1240060002 : DotNet "'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.NetworkCredential";
  WebRequestMethods@1240060003 : DotNet "'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.WebRequestMethods+File";
  ResponseStream@1240060005 : InStream;
  FileStream@1240060006 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.FileStream";
  FileName@1240060007 : Text;
BEGIN
  GetFTPSetup;
  FTPWebRequest := FTPWebRequest.Create(FTPSetup."FTP Download FilePath" + FileToDownload);
  FTPWebRequest.Credentials := NetworkCredential.NetworkCredential(FTPSetup."FTP UserName",FTPSetup."FTP Password");
  FTPWebRequest.UseBinary := TRUE;
  FTPWebRequest.UsePassive := TRUE;
  FTPWebRequest.KeepAlive := TRUE;
  FTPWebRequest.Method := 'RENAME';
  FTPWebRequest.RenameTo := 'archive/' + FileToDownload;
  FTPWebResponse := FTPWebRequest.GetResponse();
END;

I hope this will save time for others who are looking for a solution.

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:

Read more..

Move the pointer to the first record on NAV form

Recently I was working on a modification where I have to display an item related information in a form, the information is always changing so we need to get the new data every time they open the form. For this scenario I have created a new table and new form . We have  set the form SourceTableTemporary  property to yes , since we need to get the data and insert into the table every time they open the form.

ItemInfoINIT;
ItemInfo."Item No." := ItemNo;
ItemInfo."Line No." := LineNo;
ItemInfo.Attribute := Attribute1;
ItemInfo.INSERT;

I have added a new function to execute on the open form trigger which will retrieve the data from another SQL Server and insert the data. Everything worked fine but when i open the form. the cursor was on the last record. I want the pointer to be on the first record, when i searched on the form if there is any property that i can use to set the pointer i have not found any default property which can be set.

The solution is very simple, you just need to add the following statement in OnOpenForm triggger after inserting all the records.

IF FINDFIRST THEN;

I hope this will save time for others and especially for those who are new to the NAV development.

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:

Read more..

Print blank when the value is zero in RTC Reports

Sometimes when we are printing decimals/integer fields in the RTC report we don’t want to display zero if the value of the expression is zero, but rather display it as blank,  most of the standard Navision Reports has this logic built in.

For example check this below order confirmation report, the second line unit price is set to zero on the order and it did not print zero but rather printed as blank

How did they do this ?

On the RTC Report they created a new function called BlankZero and the function looks like this

Public Function BlankZero(ByVal Value As Decimal)
     if Value = 0 then
         Return ""
     end if
     Return Value

End Function

The function takes a decimal and if the value is zero it returns blank text string.

So for the above example to display unit price it uses the following expression

=Code.BlankZero(Fields!UnitPriceToPrint.Value)

<

If you need to display blank please use the above function in your textbox expression

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:

Read more..

How to Enable/Disable Inventory Warning Message

Most of us must have seen the below message box when entering quantity for an item on the sales order, the system shows this Check Availability box if the item does not have enough quantity entered on the order, it is good information but sometimes it is annoying, so how can we disable this

You can disable this per item or for all items by using the following two ways

1. You can disable this per item by setting the field “Stockout Warning” on the Item card to No

2. You can disable this for all items using the field “Stockout Warning” on the Sales & Receivable Setup, you need to set it to false to disable.

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:

Read more..

Paste Rows option is missing on RTC Pages

One of the feature on the Navision RTC page is ability to copy rows and paste to excel and also copy rows from excel and paste into the page, mostly this option is heavily used on the worksheet pages i.e. Journal pages.

But recently i have noticed this option is missing  on most of the pages, that is due to a bug/ or change made by Microsoft in the recent Cumulative Updates. The reason why this option does not appear on the page is because of the property PasteIsValid  on the associated source table is set to NO. The default value of this property is yes  but for some tables in the recent builds it is set to No.

So in order to have the option Paste Rows you need to set the PasteIsValid property on the table to yes.

For example if the option is missing on the General Journal Page, then you need to set that property on the Gen. Journal Line (Table 81).

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:

Read more..

Ctrl + Alt + F1 Shortcut Key is not working in RTC to Zoom

As I explained in my last post , the shortcut key (Ctrl + Alt + F1) is used in RTC for About this Page function, which will allow us to view all fields of a record.  This Shortcut Key is very important to view the fields on the subpage as it does not have the Menu option to see Help –> About this Page.

On some computers, this shortcut key does not work and it even happened on my laptop, the reason is because of the conflict of having the same shortcut key on another application. These days most of the computers come with an application called Intel HD Graphics Control Panel or other Intel application to control graphics. This application uses the same shortcut key to open the Display Panel and because of this NAV, RTC shortcut key does not work.

To resolve this, please open the Intel HD Control Panel using Programs and Click Options as shown in Fig 1, it will show you all the Hot Keys for the application

Fig 1: Intel HD Graphics Control Panel

As you can see in Fig 2. to open Display Panel it uses <Ctrl> <Alt> <F1> shortcut and which conflicts with NAV application and causes the shortcut not to function on NAV. On my laptop to resolve this I have disabled the Hot Keys by turning it off and restarted the computer.

In certain cases turning it off did not work so I have to change the Hot Key for Open Display Panel to something else and restart the computer. If disabling does not work try to change the hotkey and restart the computer.

In all the cases where I  have seen this issue the application which caused was Intel HD Graphics, but I believe it depends on upon your computer. If you run this kind of issue please make sure to check with other running application hot keys.

I hope this helps to resolve some of you who are running into the same issue.

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:

Read more..

How to view all fields of a record (Zoom on Record)

In the older version i.e. in classic client version of NAV, to view all the fields for the a record you use the function zoom (Ctrl + F8) which will allow us to see all the fields of the record, it is very useful function to see other field values which are not on the form and it is easy to find a particular field  by selecting the first letter of the field.

For example in below fig. 1 by using zoom i can see all the field values of the selected customer record.

Fig 1 : Customer Card Form

You can use the same function even on the subform, for example if you want to view the fields of the sales line on the sales order form then you just need to select the salesline record and use the zoom function. Example Fig. 2

Fig 2 : Sales Order

In the newer version i.e in RTC to achieve the same results we use About this Page function which will allow us to view all the fields, filters and source expressions of a record. It can be accessed from the page as shown in the fig: 3

Help –> About this Page

Fig 3: Customer Card

The shortcut key for About this Page function is (Ctrl + Alt+ F1)

When you are on the document page to view the fields of a subpage you don’t have the option to use Help –>  About this page, so to view all the fields of a subpage record you need to select the line and use the shortcut (Ctrl + Alt + F1).

Example to view fields of a sale line on the Sales Order  Fig 4.

Fig 4: Sales Order Page

I have seen instances where the shortcut Key Ctrl + Alt + F1 does not work, i will explain it in my next blog post how to resolve that issue

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:

Read more..

Auto Load Dynamics NAV Modules On PowerShell Startup

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

 

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:

Read more..

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

 

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

 

 

 

 

 

 

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:

Read more..

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

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.
















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:

Read more..

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:

Read more..

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

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:

Read more..

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

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:

Read more..

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

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

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.

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.

Below is the screenshot of the excel after the change

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:

Read more..

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

In 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) 

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

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:

Read more..