Marking Records - Gotcha!

Here’s a simple little modification that requires a developer’s license (you need to write some code.) In version 5.0 I have a Job No. field in my G/L Journal and I want to be able to filter my G/L Journal so that only records that have a Job No. that is blocked are displayed. My journal gets created through an interface (in case you were wondering how these lines got entered.)

I knew I could to this by Marking the records where the job was blocked and then showing only Marked records. This is the Gotcha: the default parameter for the Mark and MarkedOnly functions is false meaning it will effectively not do anything. The online help tells you this but still it’s a bit of a puzzler if you don’t do these things very often.

My code for Only Records with Blocked Jobs is as follows:

CLEARMARKS;

IF Rec.FINDSET THEN

REPEAT

IF "Job No." <> '' THEN BEGIN

lJob.GET("Job No.");

IF lJob.Blocked <> lJob.Blocked::" " THEN BEGIN

lBlockedJobsFound := TRUE;

Rec.MARK(TRUE);

END;

END;

UNTIL Rec.NEXT = 0;

IF lBlockedJobsFound THEN

MARKEDONLY(TRUE)

ELSE BEGIN

MARKEDONLY(FALSE);

MESSAGE('No blocked jobs found in this journal batch.');

END;

My code for showing all records is as follows:

CLEARMARKS;

MARKEDONLY(FALSE);

Still, it would be nice if the end-user could have applied this filter as they would be able to in Microsoft CRM. Maybe one day future versions of NAV will allow the user to define filters using related tables too.

Happy New Year to you all!

Well it’s nearly the end of 2007 and I can’t let the year finish without wishing you all a Happy New Year! Thank you to everyone that has taken the time to subscribe to or read my blog over the last few months. I started this blog in April to help me learn to write. It’s been challenging but fun.

Since starting the blog, I’ve had over a 1000 visits from over 60 countries (see map). Incredibly I have not had a single comment posted. I guess I should not be surprised since I regularly read blog posts but rarely make a comment. If you read this, make a comment – even if it’s just to say “Hi”.

The most popular content by far has been my write up of Dynamics NAV 5.1 Reporting and Web Services. I took the time to review the video preview of the upcoming release and wrote what I saw and gave my opinion. This goes to show how much interest there is in the new version of Dynamics NAV (now codenamed as “6.0” but more than likely to be called “Dynamics NAV 2009.”)

I spent a lot of time researching and writing a series of postings on the new send-to-application feature in NAV 5.0. This exercise was time consuming and ultimately fruitless. It’s hard to know what to write about. I wanted this blog to be original and tried to avoid re-posting news from other sites or forums. I am still perplexed as to how you get your blog postings included in the Google Alerts for Dynamics NAV.

Probably the most satisfying moment was when the good people at PACKT publishing agreed to send me a free copy of David Studebaker’s book on programming Microsoft Dynamics NAV in return for a review posted on my blog and Amazon. I was really stoked to get something free from my blog. I am pleased with my book review but I don’t think David or Packt were that impressed.

2007 has been a tough year but I am looking forward to 2008. IT is a funny business, we’re always looking for the next big thing. I’m sure as soon as I get my hands on the new Dynanics NAV I’ll be wanting the next realease. One of the real highlights of my working year has been working with one of the industries real stars. Chommy Cash is a NAV and .NET programmer extrordinaire who is sadly leaving us to start an Alpaca farm in the Himalayas. Chommy, if you’re reading this (which I’m sure you won’t since you never read anything I write), I’ll miss you and wish you all the best for the future.

Enjoy your celebrations, and remember to add to your resolutions: “I must leave comments on the blogs I read.”

Test Drive Microsoft Dynamics NAV 5.0

This link came up on my Google Blog Search for Dynamics NAV:

http://www.dynamicsnavtestdrive.com/

By logging onto the test drive environment using your web browser, you will be able to experience Microsoft Dynamics NAV first hand without the need of installing it on your computer. You can explore the product on your own, or follow along guided exercises and demos.

Book Review: Programming Microsoft Dynamics NAV

Author: David Studebaker

Publisher: Packt Publishing

ISBN: 1904811744

Published: October 2007

This is currently the only book that covers programming Microsoft Dynamics NAV and that for most people will be a good-enough reason to buy it.

The book is intended to help reduce the time needed for non-NAV programmers to become productive with NAV’s unique programming language and development tools. From its coverage of terminology and basic concepts to advanced topics and techniques, this book is not just for the NAV-newbie; there is enough advanced content and good advice to prove useful for the seasoned NAV developer too. According to the preface, the book is also intended to help managers and those considering purchasing or enhancing the product to become familiar with the level of customisation the product can provide.

David Studebaker has been programming NAV since 1996 and, with over 40 years IT experience, he is well-qualified to write this book. He is currently a principal of a company that provides development, consulting, training and upgrade services for NAV resellers and firms using NAV. It is rare to get the opportunity to work with someone with such experience and this book may be the closest you’ll ever get to having a true veteran provide mentoring and advice.

Overall this is a good book, but it could have been a lot better. The publisher claims this is a “Fast-paced and to-the-point... [book with] …clear explanations and practical example code.” However, I found some of the early chapters rambling, slow and tedious with more than a handful of mistakes. The chapter on fields in particular contained a number of basic errors that should have been picked up by the proof reader or reviewers. At times I felt as though I was the first person to actually read this book.

Whilst David is clearly a very experienced NAV developer, he is not a great writer, so do not expect an easy read. Some explanations are long-winded and prone to tangential wanderings. It seems that, subconsciously at least, David is aware of his propensity to ramble when, after a 5-page-explanation of the “Date Formula” data type including a programming example, he writes: “It may seem that we overemphasized this experiment. But you got to see a lot more here than just date calculations.” A good editor could have helped us all at this point.

I really wanted to like this book and essentially I do. The content is undoubtedly good, so do not be put off by my criticisms. In value-for-money terms this book is exceptional: try to see how much consultancy or training time 60 US Dollars will buy you!

David has done a great job in completing this book and has obviously invested a lot of time for what must be a tiny market. If you work with NAV you should buy this book.

3-part Format String

I mentioned in the previous post that you could use a 3-part formatting string in the format property of your numeric field to display blank when the number is 0.

Here is an example of a 3-part format property setting. If you put this in the Format field:

="$#,###;($#,###);#"

Then this says:

• When Positive use $#,### which is “Prefix with Dollar sign, blank when zero, comma thousands separator, no decimal places”
• When Negative use ($#,###) which is “Same as positive but within brackets”
• When 0 use # which is “Blank”.

This works quite nicely. Don't try using special characters like "C" for currency in your 3-part format string because this special character is itself interpreted as a 3-part string and you'll find it doesn't work.

Reporting Services Divide by Zero Error in Report Expression

It’s a real pain – if I have a field that I want to calculate as one field divided by another, I have found that I get a divide by zero error when one of the fields is 0.

Normally, I would do a quick check on the field I am dividing by (the divisor) before using it in a calculation. In a field expression, the only way to include this kind of check is with an inline if statement. So I would have something like this in my report expression:

=IIF(Fields!Budget.Value = 0, "", Fields!Budget.Actual / Fields!Budget.Value)

But there is a problem with this. The False part of the IIF function still gets evaluated and your field shows #Error instead of a blank as expected.

There is a really simple way around this – create your own VB.NET function and call this from within the expression. Creating a VB.NET function is really easy as long as you are playing nicely in the sandbox (i.e. not trying to access any of the machine’s resources.) Here is a sample function that you can paste into the Code property of the report:

Public Shared Function VarPercent(ByVal Actual As Decimal, ByVal Budget As Decimal) As Decimal
If Budget = 0 Then
Return 0
End If
Return (Actual / Budget)
End Function

To use the function, just put the following in your expression:

=code.VarPercent(Fields!Actual.Value,Fields!Budget.Value)

That’s it!

If the divisor is 0, we return 0. We could of course return an empty string – although you can just as easily do this by formatting the field using a formatting expression that shows blank for zeros.

Wasps: Are they really Yellow?

Ashely has written a great blog posting on the Intergen blog site titled Wasps: Are they really Yellow?

This reminded me of why I wanted to start blogging. I wanted to write and I thought that blogging was a good way to practice writing. I think most bloggers find that, however motivated they once were, they run out of time and ideas and posting gets harder and harder.

The Intergen blog is a great place for Intergenites to post ideas and thoughts. If you like technology, you should subscribe. At the very least, you should take the 5 minutes or so to read Ashley's post. Who knows, you could maybe even leave a comment.