PO Creation in NAV from SMS

Many a times, we have been asked if we can access NAV from areas which have limited or no internet connection at all. I was recently setting up a demo for an organization that did most of its business in areas where internet connectivity is impossible as these areas are very remote.

However, an SMS need not require internet connection and so I was intrigued by the idea of using SMS to create Orders (be it SO or PO).

In order to do this, you will require the following:

1. A dedicated e-mail id
2. Outlook running continually on a dedicated machine
3. An application that can forward SMS's received on a particular number to the e-mail id.
4. NAV code to read the mails periodically

I used outlook automation tool in the following manner:

1. Fetch the default name space
2. Fetch the default folder (usually 6 for inbox)
3. Read mail based on it being "Un-Read"
4. Read the body and save it into text
5. Read the text and create order
6. Mark mail as Read.

If anyone requires the code do ping me.

Using SQL Views for Reports NAV 2013 +

We had just completed an upgrade of our customer from NAV 4 to NAV 2013 R2. As a part of our first phase, we had gone live with all processes and certain reports which would be needed from the first day itself.

As a part of our second phase we were to complete the remaining reports, many of which would include huge amounts of data.

As we all are aware, a resident problem with report rendering in cases which include huge amounts of data is that quite often you would face one of the below issues:

(1) Time taken to generate output
(2) System out of memory exception

We tried arguing with the client that this is an issue which even Microsoft has not addressed, however, the clients argument was quite simply that if the same report did not have any such issues in the earlier version, why should there be an issue with the latest version (which apparently should be better than the previous ones).

And yes, the client is right, more so in this case as it is a logic counter explanation.

This is when I set about identifying avenues for speeding up the report for starters and then to overcome the memory issue.

I had 2 options (1) To create an SSRS report (2) Use SQL views to create the report in NAV itself.

You could use any option, as both shall give you the same performance, but I opted for the latter as my team comprises of more NAV technical consultants and fewer SQL consultants. This would give us the flexibility of coding more in NAV and less in SQL thereby making issue resolution simpler and faster for my team.

So this is what I did:

(1) I created an SQL view (let me tell you, this is very simple and it also has a visual way of coding which could be used by even those who do not have full knowledge of SQL). My view would join 4 tables (1) Value Entry (b) Item Ledger Entry (c) Item (d) A customized table. Here you need to make sure of one very important point. While naming the query be sure to name it like this: COMPANYNAME$NameOfTheVIEW e.g. Leaping Frog Solutions$ValueEntryMultiJoin

(2) As a second step, I created a table with the same name as my view, i.e.  ValueEntryMultiJoin. This table would have the same fields as that in the SQL View.

(3) In the table created in step 2, I set the LinkedProperty = Yes and LinkedTransaction = Yes

And thats it!. You have a table which will always give you the most updated view based on the View created in SQL. So, in essence, I had let the VIEW make data based on grouping required for me, which otherwise would have been done in NAV. Also, the number of lines for sifting of data had been significantly reduced.

I can now, use that table as a data item in my report and the results are almost bewildering. A report that would take 3 hours to complete would now take only 2 minutes. I had actually created a report that was even faster than the one in the customers older version!

Now, for the second issue of memory. All you need to do here is to convince the customer that the report would not have a preview option. I convinced my customer and created a CU which would transfer any report into either Excel or PDF based on the selection. Doing this I overcame the memory issue too!

Thanks for Reading!

Format Amount in Indian Style Navision

Unlike the Arab styling of numbers which places a comma on each thousand (1000's), the Indian way of styling numbers is different and is used only by India, Nepal, Pakistan, Bangladesh and Sri Lanka.

Currently, NAV does not support the Indian Numbering Style on Reports or must I say that RDLC doesn't have any provision.

The following piece of code enables you to format Amounts in Indian Style of Numbering.

______________________________________________________________________

Variables

Name	DataType	Subtype	Length
AmtInText	Text		50
DecPlaces	Text		3
Stop	Boolean
DummyAmt	Decimal
DummAmtInText	Text		50
i	Integer
DivBy	Integer

_____________________________________________________________________
Code

DecPlaces := '.00';
AmtInText := FORMAT(Amt);
AmtInText := DELCHR(AmtInText,'=',',');
IF STRPOS(AmtInText,'.') <> 0 THEN BEGIN
   AmtInText := COPYSTR(AmtInText,1,STRPOS(AmtInText,'.')-1);
   DecPlaces := COPYSTR(AmtInText,STRPOS(AmtInText,'.'),STRLEN(AmtInText));
END;

RetAmtVal := FORMAT(Amt);
RetAmtVal := DELCHR(RetAmtVal,'=',',');
i := 1;
DivBy := 1000;
REPEAT
   CLEAR(DummAmtInText);
   CLEAR(DummyAmt);
   IF i = 1 THEN BEGIN
      DummyAmt := Amt/DivBy;
       i +=1;
   END ELSE BEGIN
      DivBy := DivBy * 100;
      DummyAmt := Amt/DivBy;
      i +=1;
   END;
   IF DummyAmt < 1 THEN
      Stop := TRUE
   ELSE BEGIN
      DummAmtInText := FORMAT(DummyAmt);
      DummAmtInText := DELCHR(DummAmtInText,'=',',');
      IF STRPOS(DummAmtInText,'.') <> 0 THEN
         DummAmtInText := COPYSTR(DummAmtInText,1,STRPOS(DummAmtInText,'.')-1);
      RetAmtVal := INSSTR(RetAmtVal,',',STRLEN(DummAmtInText) + 1);
   END;
UNTIL Stop;
RetAmtVal := RetAmtVal + DecPlaces;

Barcode Printing with ZPL ZPL II - NAV

Have you had to print barcodes directly into a printer from NAV? Yes, most of us who have clients from the manufacturing or retail domain often have to undergo the cumbersome exercise of developing, formatting and deploying such reports.

However, during a recent visit to one of my clients manufacturing utility I had a chance to work on Zebra printers. Zebra comes with its own set of programming (scripting) language called ZPL and more recently have developed a more robust version of the same language and have baptized it as ZPL II.

There's not much difference between the 2, its just that ZPL II sends commands much faster resulting in less standby when sending commands to print multiple barcodes at once.

And believe me, its much easier to use these commands from NAV and send it to the printer.

So, this is what you need to do.

Create a template using the designer tool which comes along with the driver. Save this file as a text file onto a drive which is accessible to the user.

Share the Barcode printer across the network so that we can send commands to the printer.

Copy the code from the text file and paste it into a section of the report.

The end result looks like below:

So, I saved the contents of the report onto a text file again and send the text file to the printer using the SHELL command.

No formatting hassels!!!

www.lfspl.com

Mail Client Approval System in NAV (Navision)

Recently, one of my clients had a rather unique requirement. The senior management, almost all of whom are a part of a single business family and travel frequently wanted a facility whereby they could approve purchase orders and other such critical documents online.

There are various ways you can do this off course using web services and the simplest one is by making a web page, with approve or reject buttons. And I was pretty much sorted until the client told me that the web page mechanism wouldn't work as they were skeptical of the use of such data over a web page and felt that their data could be at risk from hackers, eavesdroppers and the likes.

So, the next best solution that came to my mind was to build an application however, such applications are not only costly to build but would also require to be enabled for different OS.

So, I thought to myself, why not use an e-mail body for approvals!

It turns out that this is pretty simple. You or the programmer needs to have knowledge about NAV web services and IIS.

In short, what goes into this is the following:

• Create a Codeunit Web service for approvals
• Create a mid-layer IIS web service (This web service is used for communicating between 2 other web services)
• Create a REST web service
• Expose the REST web service on the IIS
• While writing the body content of an e-mail create 2 hyperlinks with the hyperlink as the REST web service with parameters (one link for Approve and the other for Reject)

That's it!!!!

That's all that you need to do.

Pretty simple.

For further information and code you can write to me at vrushankmehta@gmail.com

www.lfspl.com

About the Frog and Navision

The Frog and Navision is an attempt by Leaping Frog Solutions Pvt. Ltd. to share developments made by us with the Navision Family.

For starters, let me introduce my company to you.

Leaping Frog Solutions Pvt. Ltd. (a.k.a. LFSPL) is a privately owned company located in Mumbai, India. We started operations in June 2012 and have since been involved with many NAV customers in various verticals.

www.lfspl.com