Monday, 29 December 2008

NAV 2009 Book - more details on PACKT site

If you are interested in our book Implementing Microsoft Dynamics NAV 2009, you can find a couple of things of interest on the PACKT web site.

First of all, there is the table of contents for the book.

Then there is an article based upon some of the examples in Chapter 7 called Extending the Application using Microsoft Dynamics NAV 2009.

It's been a long year and as you can see from the table of contents, Vjeko and I have been very busy. 2009 promises to be a pretty exciting year as we start to see more and more people getting into the exciting world of Web services in NAV 2009.

For now, I would just like to wish you all a very Happy New Year!

Wednesday, 24 December 2008

A New Home for Vjeko's Blog

Way back in February of this year, I made a post about a blog that appeared on my Google Alerts for Navision that really caught my eye.

Now Vjeko's blog has moved to a new home: http://www.NavigateIntoSuccess.com. It has a new sleek look, new features, but the same fantastic content that has kept many readers returning again and again.

If the number of comments a blog receives is a testament to it's success then Vjeko has done extremely well over this last year. His articles regularly receive many comments from readers and he always takes the time to answer the comments.

Vjeko often writes in-depth, well researched, and informative posts on Dynamics NAV and Sure Step. I am a little biased, since we have just finished co-writing a book; however, if you are not already a subscriber to his blog, go and check it out. Now that the book is finished, we will both have more time for blogging and forum posting.

Tuesday, 23 December 2008

Where did my Web service go?

I was recently playing around with Codeunit Web services in NAV 2009 and after making a change which involved adding a function to my Codeunit, the Web service was no longer available for me to call.

I brought up the list of the services on my computer (http://localhost:7047/DynamicsNAV/ws/Services) and it was still showing in the list, but when I entered the URL for the actual Web service WSDL, I got an error.

It turns out, the reason was because one of the functions I had added to my Codeunit took an Automation object as a parameter. If you get this problem, the thing to do is bring up the properties for the function and set the Local property to Yes. Once that is done, the offending Function is hidden from the Web service and normality is restored.

Thursday, 11 December 2008

Implementing Microsoft Dynamics NAV 2009


What a great way to wake up! You can now pre-order our book. I woke up this morning to see that the link to pre-order the book with a discount was up on the PACKT site. Wow!

I have co-authored this book with my good friend Vjekoslav Babić, and I can see that he's been busy blogging about the book and letting everyone know. One of the reasons I have been so quiet in the blogoshpere and on my favourite forum over the last few months is because I've been working so hard to get this book finished.

We've really tried to make this book as informative as possible, while keeping the fun elements from our blogs. There have been some fantastic people involved in producing this book, with feedback from members of the product group and technical reviewing from seasoned NAV experts and bloggers (like Eric 'Waldo' Wauters). Thanks to everyone that has read this blog over the years and to those that have left the occasional comment.

The book is aimed at consultants and developers of NAV and gives an insight into the new features of NAV 2009, and also a series of tips and tricks and practical examples for configuring, modifying, and extending the application. I can honestly say that this is the best book on NAV 2009 I have ever written.

So what are you waiting for? Go and pre-order your copy now.

Sunday, 23 November 2008

Dan Brown Loves NAV 2009 (and so do I)

Well done to the product team for getting NAV 2009 released to market. It's been a long haul for them and what they've acheived is truly remarkable. Dan Brown has made a blog post on the NAV Team Blog (read it here) in which he talks about the emotion that people attach to the new user experience and you should really go and read it if you have not done so already.

I've been playing around with NAV 2009 since the first release was made available to partners in May of this year. I'd seen some pretty cool videos previously and I was surpised to find the product looked just like the video and was fast and stable. The new Role Center approach is going to take some thinking from partners and initially introduces an extra step in the configuration process, but over time I can see this dominating the entire implementation process with the focus being more on the people in the organzation, the roles they play and the processes they need to perform and less on the data and the functions that are required to work on the data.

2009 is going to be a great year for NAV and I think it's going to be a whole heap of fun putting the theory into practice. Have you had chance to use it yet? If so, leave me a comment and let me know what you think of it.

Wednesday, 19 November 2008

Watch out for the Demo Install

I'm amazed at how simple the demonstration install of NAV 2009 is. Click one button and it goes ahead and installs everything you need. A word of warning though, if you use this option and you have an existing NAV version installed (such as NAV 5.0 SP1) then the Microsoft Dynamics NAV folder will get overwritten without prompting. You may find it worthwhile copying your Microsoft Dynamics NAV folder to another folder (such as Microsoft Dynamics NAV 5.0 SP1) before doing the NAV 2009 install.

Monday, 17 November 2008

He's Been!

Santa came early this year and he's left me a shiny new download of Microsoft Dynamics NAV 2009. Yes that's right! It's finally here.

If you have access to PartnerSource, you can download it from the NAV 2009 Download Page.

I installed it on my home machine which is running Vista Home Premium with no SQL Server installed and I didn't really expect it to install. I ran the demo install and it just worked - it installed SQL 2005 Express, all the bits and created the demo database. I started the Microsoft Dynamics NAV Business Web Services service and the web services just worked!

Coolest thing for me? The date picker in the RoleTailored client on Vista is just so nice with animated zooming and swooshing and stuff. Nice!

Well done Microsoft NAV Product Group! This is a truly awesome version. I'm sure you feel like a well deserved rest after such a long haul.

Tuesday, 4 November 2008

CodeCamp 2008 - NAV Resources

Presenting on NAV to CodeCamp was great fun. Here are some follow up links.

I was asked if there was a way to get hold of the product to play with. The answer is that the you can download NAV 5.0 from MSDN if you have a MSDN subscription and you can also download a license file. You’ll find it under business solutions. The product DVD contains a load of documents as well that includes the application designers guide which is a really good introduction to programming in Dynamics NAV.

If you do not have a MSDN subscription, you can download a restricted version with some development capabilities from one of the online forums at http://www.mibuso.com/dlinfo.asp?FileID=950 although you will probably need to register on the forum before being able to download the file.

Here are some useful URLs for anyone that is interested in NAV...

I also wrote a blog post that shows how to use the Web Services enablement of NAV 2009 with InfoPath which I didn't get time to demonstrate. You can read that on this blog here.

Thanks to everyone that attended my session and for the interesting questions that were raised.

Monday, 3 November 2008

Freddy's Blog Delivers the Goods on NAV 2009

It's great to see more and more MS guys blogging their stuff on Dynamics NAV 2009.

Freddys Blog from Freddy Kristiansen is the latest find for me and his first posts are out of this world. This is the sort of stuff that is only available from the product team and a big thanks to Freddy for taking the time to share these useful tips.

Other recent goodies can be found on Lars Lohndorf-Larsen's Blog and Claus Lundstrøm's Blog.

Sunday, 2 November 2008

NAV 2009 Web Services and InfoPath

I gave an introductory talk at Christchurch CodeCamp 2008 on Dynamics NAV from a Developer's Perspective yesterday but unfortunately ran out of time before being able to show this example of consuming a page web service in an InfoPath form, so I figured I'd make a blog post.

Lars Lohndorf-Larsen has already made a couple of posts on how to expose codeunits and pages as web services using the upcoming NAV 2009 three-tiered architecture and has provided a sample c# program for consuming them.

I'm using the Marketing Beta Release for this so any partner will be able to download the VPC image and repeat this exercise.

First of all, you'll need to expose the Customer Page (Object ID=21) as a web service with a service name of Customer. This is as simple as running form 810 Web Services and inserting a record with Object Type=Page, Object ID =21, Service Name=Customer and ticking the Published field.

If you enter http://localhost:7047/DynamicsNAV/ws/CRONUS_International_Ltd/Page/Customer in your web browser, you should see the WSDL for the page web service. It still amazes me how easy this is.

Now it's time to start up InfoPath. On the Getting Started page, click the Design a Form Template... option on the left hand pane.
In the Design a Form Template dialog, click the Web Service option and click OK.

Select the option to Receive and submit data and click the Next button. Paste the URL for the Customer Web Service into the field that is asking for the location of the data connection web service. If you used the right URL (I just copied it from my browser address bar after I had checked the WSDL for my web service) you should see the following screen when you click Next.

I found this screen a little confusing because I've never used InfoPath before I didn't realise it is wanting me to tell it which method can be used for reading data from the web service. Select the ReadMultiple option and click Next. On the next screen, you get an opportunity to name the data connection. I left mine as the default Main query and clicked Next. The next screen askes for the URL to the web service that will be used when users submit their changes. It defaults to the previous URL so just click Next. On the following screen you are asked to select the operation that will be used for submitting the data. I selected UpdateMultiple and clicked Next.
On this page we need to specify where the Customer comes from so I clicked the Modify button next to the Field or group text box and drilled down until I could select the Customer Node in the XML structure.
You can leave the name for the data connection as Main submit and click the Finish button. If all went to plan, you should now have a new template to drag your fields on to.
Drag the filter group on to the Query fields area of the design surface and select the option to add the groups as a repeating table. This way users will be able to add any filter fields to the read command. You can select the properties of these fields and set a default value - for example, Field defaults to No. and Criteria defaults to *. This will ensure you get some records back. You will also need the setSize parameter since this is mandatory for the ReadMultiple operation. Drag this on to the control surface and give it a default of 100. You should have a query section that looks something like this.

Now expand the dataFields node and drag the Customer node on to the fields area of the design surface and once again select Repeating Table as the option. This puts in every field which is way too much so just right click on the table after you have created it and selec the properties, then click the Change Binding button. You will see a tree that is expanded to show the customer node, that's fine, just click Next and you'll get on to a dialog where you can remove fields. For a quick way to remove all fields, click in the Columns in table list box and hold down Alt+R until there are no fields left - now you can click on the fields you want and add them. I used No, Name, Address and City. You should re-size the fields so they fit the available space and have a larger Name and Address field. Now it's time to run the form and see what happens. Click the Preview button. When prompted, select the option to connect rather than work offline. Hit the Run Query button and accept the dialog box and you should see something similar to the following.
Nice! OK now you can try editing the name of one of the customers and hitting the submit button. If you see a dialog saying "The form was submitted successfully, you've just updated the NAV data!

Friday, 24 October 2008

New Zealand Box-biting Championships 2008


Our New Zealand Microsoft Dynamics NAV User Group had a fantastic two-day conference in Hanmer Springs this week.

Mason Robinson did a great job of organizing this event - which normally consists of a morning meeting with lunch, but this time comprised a coach journey to Waipara Springs Winery where we had a winery tour, tasting and lunch. We then journeyed on to Hanmer Springs where we checked in to our rooms and I gave a presentation on the upcoming Dynamics NAV 2009 release, focusing on the new RoleTailored client and the Web Service Enablement features.

The presentation was well received and I think a lot of the users are very interested in the new release. Rob from Fujitsu then did a demonstration of Demand Planner and Microsoft Forecaster and I finished the presentations with a short demonstration of the Reporting Services report viewer control in NAV 2009. The hard work sessions concluded with general discussions and information sharing from the many users that had attended this event and there was some very useful suggestions from many users and warnings about the differences in the NAV 5.0 Jobs module.

Our sessions finished and we had drinks, dinner and a quiz session in which our team came second (pah!) We finished the evening with a ridiculous box-biting competition that required us to pick up a cardboard box with our teeth without touching the floor with hands or knees. On each round the box was reduced in size until the final round had the last three contestants picking up a flat piece of cardboard from the floor with their teeth.



Here I am trying to pick up the box. This was as low as I could go and for some strange reason I thought that taking my shoes off would help me pick up the box by being lower to the ground.

After a few hours sleep we enjoyed a nice breakfast, a few recap sessions on NAV 2009 and a follow up on what we had learnt the day before and a demo from Sue from Microsoft which included the Edit in Excel feature. We had an hour or so in which we chatted and discussed issues on Reporting Services, NAV, etc. (and enjoyed a coffee next to the hot spas) followed by a coach journey back to Christchurch.

Thanks to everyone for such a fantastic time. I think we set the bar pretty high for this conference and I would be interested to know if anyone has managed to have a NAV user group meeting that tops this one.

NAV 2009 Edit in Excel

At our recent NAV User Group conference, I had the pleasure of seeing some new NAV 2009 functionality demoed by our local Microsoft ERP Expert.

In the demo, Sue set a filter on a list of records (I think it was a vendor list) and then from the Actions command menu selected a new option called "Edit in Excel".

This looked similar to the Send to Excel feature with one difference. There was a new button on a Dynamics NAV tab of the Ribbon that allowed the data to be updated.

In the demo, Sue deliberately set the Responsibility Centre in the spreadsheet to an invalid value and the update returned an error message that the Responsibility Centre was not valid. She then corrected the data in the spreadsheet, hit the update button and the data was updated in NAV.

This was a fantastic demonstration of the new NAV 2009 web services in action. I'm pretty sure this option is not available in the Marketing Beta Release that is available to partners and the image Sue had was based on Windows Server 2008.

It looks like there's definitely more cool stuff coming in the NAV 2009 release and this ability to edit data in Excel is going to prove extremely useful to many users.

Monday, 6 October 2008

Careful with those Captions!

On occasions I have needed a table that is similar to an existing table but with maybe some changes that I didn't want to have to make to the standard system (such as adding a field to the primary key).

One easy way to do this is to design the table you want to copy and use the File, Save As... menu option to save the table as a new table name and ID.

If you do this, make sure you change the Caption for the table to match the table name. Today I spent far too long investigating a problem with an error message telling me a record could not be inserted into table X since it already existed.

For some bizarre reason, the error was only being triggered when a commit was being called so there was some guesswork as to where the error was actually taking place. No matter what I tried it appeared that something really weird was going on. Eventually in a fit of frustration, I deleted the table in question and yet the error message still appeared.

The problem? Well the table had been created by copying another table and the caption had not been changed. As a result the error message was giving the wrong table name - causing great confusion.

Now here's a question for seasoned veterans. Does anyone know why the error was only appearing at the point of a commit or when all commits were removed after all other code in the transaction had executed? When you put a lock on a table, does NAV use SQL BEGIN TRANSACTION and then use COMMIT or ABORT if an error is thrown, or does it cache all transactions to the locked table and only send them to the database when the COMMIT is executed? I think I might do a little experiment and log the SQL commands to see what is going on. If this is the case then removing the LOCKTABLE command would have helped the debugger pinpoint my error.

Saturday, 4 October 2008

New MVP Award


Last week I found out that I have been awarded an MVP for Microsoft Dynamics NAV. This is a great honour and I am looking forward to the coming year with great enthusiasm. If you are at all interested in Dynamics NAV, you can't help but know that the new and exciting Dynamics NAV 2009 will be launched this year and with it there will be a heap of new challenges and possibilities. The great thing about Dynamics NAV is that there is always lots to learn. I will try to help others where I can by passing on what I discover.

I work with some amazing people and have had the pleasure of meeting many great programmers and analysts in my years of working with NAV. Big thanks to everyone that has supported me over the past few years. There are a number of cool things coming up from me and my good friend Vjeko Babic but I'll post more about that as the year progresses. Once again, thanks for supporting this blog.

Tuesday, 30 September 2008

Calling Web Services in the Marketing Beta of NAV 2009

My first experience of using web services in the Marketing Release (CTP4) for Dynamics NAV 2009 resulted in a couple of errors. The first one I experienced was a bit of confusion over the way Visual Studio 2008 handles web services.

If I had read through Lars Lohndorf-Larsen’s blog post on how to consume a web service properly this wouldn’t have been a problem, but basically if you want to add a web service in VS 2008, it’s a little different.

When I went to add my service, there was no add web reference option.



I thought, hey that’s cool they’ve changed the name and updated the user interface, so I just selected this option and put in my address. But when I came to use the web service I got the shock of my life when none of my methods were there. Now I had to go back and read Lars’ post and this time I was determined to follow it step by step to see what I was doing wrong.

Sure enough Lars does say:

2) This step depends a bit on whether you use VS2005 or 2008. In VS2005, just rightclick on "References" in the Solution Explorer, and select "Add Web Reference". In VS2008, to get to the same place, rightclick on "References", then select "Add Service Reference", then click the Advanced button, and then click the "Add Web Reference" button.

So when you see this screen:



Click the Advanced button and then click the Add Web Reference button.



Now we’re back in familiar territory and everything works as it did previously.

Except – my console app didn’t work! I kept getting this error: "Path property must be set before calling the Send method."

I think this problem only happens when you are adding multiple web references to a single project (not uncommon) and I’m really not sure why it happens – maybe someone who knows a bit more about .NET and visual studio 2008 could add a comment as to why this happens.

Even though the URL property is set on the web reference, for some reason the service doesn’t know the URL, so I needed to explicitly set it within the code.

Here’s an example.

SSRef.SystemService SysServ = new SSRef.SystemService();
SysServ.Url = "http://localhost:7047/DynamicsNAV/ws/SystemService";
SysServ.UseDefaultCredentials = true;

I guess this is my first blog post on NAV 2009 although, like many people, I’ve been working with it a lot since the CTP3 release. I have to say that this is an amazing product and I really can’t wait to start using this for real. Nice one Microsoft!

Sunday, 28 September 2008

Everybody Lies

The Dr. House approach to requirements analysis
This posting was first published on the Intergen Blog Site on the 26th September.

Gregory House M.D. is a maverick medical genius who, in each TV episode, heads a team of diagnosticians in their attempts to diagnose an unfortunate patient’s mystery illness. House’s signature phrase is “Everybody lies” although, for his character, it’s more than just a saying, it’s a philosophy. In this article, I’ll demonstrate how the “everybody lies” approach can be applied to requirements analysis in order to reduce costs and improve overall project quality.

It seems odd to claim that everybody lies. Just as there’s no good reason for a patient to lie to a physician who is trying to save their life, we don’t expect a business person to lie when we’re gathering requirements. The modern-day philosopher, Homer Simpson, has this to say on the subject of lying: “Marge, it takes two to lie. One to lie and one to listen.” Homer’s insight helps us understand the fundamental problem: the users must tell us the truth but as analysts we are equally responsible for finding it.

Before I try to convince you of my rather tongue-in-cheek methodology, you should first understand that getting requirements right is a serious business. Research from Barry Boehm and Philip Papaccio has shown that defects introduced early in a project, such as in the requirements analysis phase, can cost 50 to 200 times more to fix later in the project than if they had been corrected close to the point at which they were introduced. 50 to 200 times – that’s a staggering difference!

Gathering requirements means capturing business problems, not computer problems. You don’t need to be Sherlock Holmes (or Gregory House for that matter) to understand that the first step in solving a problem is to clearly identify what the actual problem is. Building a solution based on the wrong requirements can be a costly mistake to make, but how exactly can we apply our new “everyone lies” approach to requirements analysis and avoid getting it wrong? One approach could be to shout “liar” every time a user describes a requirement, but some may find this a little disturbing. First we need to understand the nature of the lies and how to avoid them.

The first problem I have discovered is that people like to describe solutions and not requirements. If I had a signature phrase for requirements analysis it would be, “That’s not a requirement, it’s a solution!” I’ll admit it’s not as catchy as “everybody lies” but the sentiment’s the same.

A requirement is the answer to a “what” type of question and should always be expressed in business terms. A solution is more of an answer to a “how” type question. There’s an easy way to tell the difference between requirements and solutions: if you can implement it, it’s a solution.

Writing down a solution instead of a requirement happens frequently, but most of the time we get away with it because it just so happens that the solution we’ve written is correct. No harm, no foul; but what happens when the solution isn’t correct? Remember that equation we had before? 50 to 200 times more expensive to fix. That’s why we get the requirements signed off to make sure they’re correct, because no-one would sign off on requirements when they’re not correct. Or would they?

Having a signed off requirements document means nothing if the requirements are wrong – we’re still going to need to fix the defects and our goal is to reduce the project costs, not just our costs. Let’s assume that no one would sign off requirements that they know to be wrong, so it must be that they’re not understood, but this raises a new mystery: why do people agree to requirements when they don’t understand them?

The problem starts when we use the wrong language to describe requirements. If we’re not using business terms, we’re putting the business users at a disadvantage. It can typically go one of two ways: either the business user says, “Hey I’m sorry but I don’t understand this requirement so I can’t agree to it”, or they say, “I don’t understand this requirement but the consultant seems pretty confident that he’s right, so I’ll just keep quiet. If it’s wrong someone else will pick it up later on.”

So the next time you find yourself writing a requirements document, remember that you’re a detective and not a secretary. It’s your job to find the real problems in business terms and not simply record what you’re told. When people tell you that the captured requirements are correct, ask them to explain them to you, just to check they’re not lying.

In my next post, I’ll explore the “Tourette’s Syndrome” approach to handling support calls.

Thursday, 25 September 2008

SQL Code Beautifier

It's funny - I've looked for some kind of "improve my layout" type function in SQL Management Studio loads of times but never found it. Then (and I don't know why I never tried this before) I searched for SQL Code Beautifier in Live Search and came to this site: http://www.ubitsoft.com/products/t-sql-beautifier/index.php.

The resulting code looks great, AND you can get an HTML rendering to post in your blog posts. Nice!

Why did I need to beautify my code - well the first time I needed it was when I had to edit someone else's SQL that believed carriage returns were not necessary. The second time (that prompted me today) was when I wanted to see the result from creating a dynamic SQL command - I printed the command but it came out as a single line of text, so I wanted to see what it looked like to be able to check for errors.

This nice tool is definintely going to save me heaps of time. Maybe someone should write a C/AL code beautifier.

When Ctrl+C, Ctrl+V Fails--Send to Excel Saves the Day!

The other day a very scary thing happened to me-copy and paste from NAV failed! I was on a sales order and I copied the sales lines to the clipboard and pasted them to Excel. Nothing new there, we've been able to do this for years and it's been a pretty cool feature of NAV but when I came to check the totals, I was horrified to see that a few of my sales lines were missing!

I tried again and still they were missing. After some investigation, I found the cause of the problem was that some of my items had a double quote in the description for the items. This is not uncommon, especially when you have descriptions with inch sizing in the description. The problem seems to be when the very start of a field is a double quote and there is no double quote at the end. Excel was taking from the start of the double quote to the next double quote as being a full description field (so one of my fields included 10 or so sales lines in the field.)

I was about to log this with Microsoft as a problem with NAV when I saw that if I typed similar data into a text file and copied and pasted into Excel, exactly the same thing happened.

Thankfully the new (version 5.0 new) send to Excel feature saved the day as the XML approach copes with the double quotes without a problem.

Thursday, 14 August 2008

Where Am I? Now I know!

I wrote some posts a while ago when Waldo's post on his blog inspired me to write a little tool similar to the one he was blogging about. I added some features and wanted it to look nice but I came up short in trying to get the FORM to stay open. I could find no way of stopping the user from clicking on the form and pressing ESC.

Today a person with a screen name of Viktoras on the Dynamics User site left a comment on Waldo's blog post explaining how to solve this problem and he was also kind enough to follow the link to my blog and leave a comment for me. So finally I was able to finish my little utility and I just need to tidy up a couple of things and then I can make it available for download. I realise that putting source code on a blog for people to copy and paste is a bit of a poor way to distribute code and so I will be putting the finished code on to a forum - I will write about it here of course!

Awesome Vista Sidebar Gadget


I've only just started to use Vista's Sidebar gadgets in anger. Sure I had the inevitable clock and the Weather forecast and even a calendar but the few times I had searched for something in the gallery I came up with a real "meh!"
Today a colleague pointed me to this App Launcher gadget and I think it's brilliant. I love the fact I can just drag shortcuts on to the gadget to add them to the launcher. I love the fact that folder shortcuts open in the flyout and that I can open my Internet Explorer favourites too. I'm guessing the fact I have a widescreen monitor and the Gadgets are always on top helps. If you're not using Vista, then poor you. If you are using Vista and you haven't seen this one, you should really give it a go. Click on the link at the top of this post to download.

Sunday, 27 July 2008

What's New in Microsoft Dynamics NAV 2009

There is a new training course available for download from PartnerSource and CustomerSource. I'm not the first to blog about this, but NAV 2009 is definitely a hot topic, and I only came across this today, so if I've missed it, I'm sure there are others out there that will be interested. The upcoming release of Microsoft's ERP solution is much anticipated and accounts for a significant number of hits on this blog from search engines. It's not possible to write much about the product but hopefully that will change when the next release is made in September.

If you have access to PartnerSource, you can view the training course contents and powerpoint at this link. The CustomerSource URL is here.

Intergen in top 5% of Microsoft Dynamics Partners

Intergen has been honoured with membership of the 2008 Microsoft Dynamics President’s Club which consists of the top 5% of Microsoft Dynamics partners worldwide.

Intergen received this top recognition from Microsoft during the Microsoft Worldwide Partner Conference 2008 in Houston, Texas. The honour reflects Intergen’s dedication to meeting their clients’ needs.

It's a great feeling to be part of a team that is recognised at this level. To read more, visit http://www.scoop.co.nz/stories/SC0807/S00056.htm.

Tuesday, 22 July 2008

Where did I put that trigger?

I needed to put some SQL triggers on my NAV tables the other day and when I came to check which triggers existed today, I had forgotten how I did it originally.

Here's a quick SQL script that will show you all table triggers in the current database.

SELECT [Trigger Name]=TRIG.name, [Table Name]=PARENT.name
FROM (select * from sys.all_objects where type = 'TR') TRIG
JOIN (select * from sys.all_objects) PARENT
ON TRIG.parent_object_id = PARENT.object_id

I love TRANSFERFIELDS.

I've been doing a fair bit of coding recently and I must admit I love the concept of transferfields and the simplicity. It's really nice to know that when a customer wants a field to work it's way through to the posted documents from the source documents, you can do it all without writing any code.

Still, it would be nice if there were a way of stopping it from going wrong.

Friday, 27 June 2008

I hate TRANSFERFIELDS - Part II

I hate the TRANSFERFIELDS command. The last time I ranted about it I seemed to stir up some strong feelings, so I figured it’s time to bring this one up again.

Last time I commented on how the unsuspecting developer has no way of knowing what is going to happen when they use this command due to the way it copies fields between tables using the field ID to map the tables. Since then I have been tripped up again by this evil function and instead of simply moaning about it, I thought I would suggest an alternative.

So here I am going to introduce the all-new transferfields functionality. Microsoft, please feel free to take this idea and implement it in NAV.

The TRANSFERFIELDS command is programmed the same as now but in order to use it, a field mapping between the two tables must be defined at the table definition level. Let’s say we want to transfer fields from the Purchase Header table and the Purch. Inv. Header tables. You can find an example of this in the Purch.-Post codeunit as follows:
PurchInvHeader.TRANSFERFIELDS(PurchHeader);

This line takes the fields from the PurchHeader record variable and transfers the fields to the fields with the same field ID in the PurchInvHeader record variable.

If you have not defined a filed mapping between these two tables, the above code will not compile. In order to define the mapping I see it working like this.

Go to edit the Purchase Header table and select View menu and select the Table Mappings option. This will launch a form similar to the following:



On this form, you can insert a new line and enter the To Table ID to identify the table you want to map to. It would probably make sense at this stage for the system to automatically insert mapping between the two tables based upon the matching field IDs of the two tables. In this respect replicating the existing behavior would be pretty easy.

To customize the field mappings for the tables, the user will simply click the assist edit button on the Mapped Fields field and this will show the Field Mapping form.



Here the user can set up the field mapping. There are a couple of great things about this approach, you can ignore fields that you would never want to copy, such as the No. field. You can also map fields with different IDs. The other great thing is that field mappings can be managed by end users without the need to write code. An example would be if the user creates a new custom field on the Purch. Inv. Header table called Original Purchase Order No. The user can then make sure that the purchase order number ends up in this field by just using the mappings.

We could even add some rules on what to do when the fields don’t match in type, i.e. should we truncate the string or throw a run-time error.

Monday, 23 June 2008

Help!

In May I wrote a short piece for MSDynamicsWorld.com on on-line help and offered some thoughts on how it could be improved. In response to this article, I received a question asking how to configure the online help in Dynamics NAV.

In truth I have never done this. I knew I had read a document on how to do it but to be honest, it looked so hard I didn't bother. When I came to reply with the details, I really struggled to find the document I was looking for that described the help creation process. If you're looking for it, it's called NOHG.pdf

One of my points in the article is that we really need an easy-to-use help editor, similar to the one in Dyamics AX. Doh! I can't believe I just admitted something was better in AX!

Error: [Microsoft][ODBC SQL Server Driver]Database is invalid or cannot be accessed State ID: HY024

What a great title for a blog post?

I got this error when a customer had tried updating their NAV 4.0 SP3 to use Standard security as opposed to Enhanced.

I suggested all sorts of fancy SQL to resolve the situation thinking it was a permissions error as I know that changing a database to use Standard causes some problems on permissions with the Session and Database File view.

It turns out the user had left the database in single user mode - a pre-requisite of being able to change the security model.

I used Activity Monitor in SQL 2005 Management Studio to kill the connected process, then logged in to NAV and used File, Database, Alter to remove the single user mode from the database. Problem sorted. But what about those missing permissions?

If you create a NAV database with standard security, the system will allocate permissions to the Database File and Session views to the application server role $ndo$shadow. However, if you change a database from Enhanced to use Standard, these permissions do not get assigned and you therefore hit errors unless you are a db_owner.

To resolve this you can grant the permissions yourself as follows:

GRANT DELETE ON [dbo].[Database File] TO [$ndo$shadow]
GRANT INSERT ON [dbo].[Database File] TO [$ndo$shadow]
GRANT REFERENCES ON [dbo].[Database File] TO [$ndo$shadow]
GRANT SELECT ON [dbo].[Database File] TO [$ndo$shadow]
GRANT UPDATE ON [dbo].[Database File] TO [$ndo$shadow]
GRANT DELETE ON [dbo].[Session] TO [$ndo$shadow]
GRANT INSERT ON [dbo].[Session] TO [$ndo$shadow]
GRANT REFERENCES ON [dbo].[Session] TO [$ndo$shadow]
GRANT SELECT ON [dbo].[Session] TO [$ndo$shadow]
GRANT UPDATE ON [dbo].[Session] TO [$ndo$shadow]

Saturday, 14 June 2008

User Acceptance Testing - the Key to Surviving an ERP Go Live

This posting was first published on the Intergen Blog Site on the 12th June.

When I came to New Zealand, nearly six years ago, I wanted to throw myself off a bridge with some elastic tied to my legs. I had heard that this was something Kiwis did and I wanted to fit in to my newly adopted environment. The world’s first commercial bungy jump started in the mid ‘80s in Queenstown, New Zealand. And it was there, on the Kawarau Bridge, that I took the plunge in 2002.

It was frightening but totally worth it. I knew it was safe but that didn’t stop me from being absolutely terrified. Sometimes in life, no matter how scared you are, you need to take a leap of faith. 5, 4, 3, 2, 1, bungeeeeeeeeey!

There are some similarities between bungy jumping and going live with an ERP solution and it was thinking about those similarities that inspired me to write this piece. Don’t panic, I’m not going to try and contort what I have to say about the ERP go live process to be all about bungy jumping using some clever metaphors. If, however, you do find yourself standing on the edge of an ERP implementation, you should read this article before jumping head first.

First of all, let’s consider the timing of the go live. How do you know when an ERP implementation is ready to go live? Is it when you have run out of time, or money? Often those, or some other seemingly arbitrary factor, are the main reasons for deciding on a go live date, but are you really ready to go live and how do you know?

The Six Ps
Like many things in life, prior preparation prevents pretty poor performance. In the case of an ERP implementation, the preparation comes in the form of user acceptance testing (UAT). User acceptance testing is often used as a project milestone for contractual reasons; completing UAT signifies that the solution has reached an acceptable level of stability and this in turn can be linked with the issue of who is going to pay for fixing defects. UAT is actually far more important than that — it is your key to project success. Imagine being the first person to bungy jump from the Kawarau Bridge. You’re standing 43 metres above the river with a bungy cord around your legs. Are you going to jump because you believe it should work in theory? I think not. Before you jump you’re going to do a little testing first: maybe throw a crash test dummy off the bridge to see if the harness holds, to ensure the rope is the right length. It’s important to iron out the issues in a safe environment first, and for an ERP implementation the safe environment is UAT.

Every issue that is found during UAT is one less issue that will need to be solved after go live, and the thing about go live issues is they can be really dangerous. When an issue occurs in a production system in a go live environment, it needs to be fixed quickly, and there is typically a great deal of stress associated with the issue. Being hurried in a stressful environment does not make for good programming and it certainly doesn't allow for well thought out design.

In order to avoid this stressful and potentially business-damaging situation you must start your preparation early, but what exactly is UAT. And, more specifically, what should you be testing?

Test the Entire Solution
In UAT, you are trying to simulate your go live situation. The closer you can get your UAT environment to your go live environment, the more confident you will be that you’re going to survive. Here’s a list of some of the things you need to be testing:

  • The configuration of the production server hardware and server component installation, such as database, application server, SharePoint server, IIS, report server.

  • The ability to install client software. It may be that you have a dedicated set of machines for the users to perform UAT, but if you are planning on installing client software as part of your go live, you should be testing your installation procedures by installing some software on some client machines that will be used for the testing. Go live day is not the day to hear, “Well it worked in the testing room - what’s different?”

  • The configuration of your production ERP environment. The testing must be performed using the system with configuration settings as they will be in the live environment. It is inevitable that you will need to make some configuration changes during UAT in order to resolve issues; however, you must ensure that the same changes are logged and applied to the live environment. And you should also be aware that any change in configuration could invalidate all testing that has been completed so far.

  • The data conversion process. Your data conversion process needs to be repeatable; you need to be able to extract the data to be converted consistently, hopefully using programs or scripts with a minimum amount of manual manipulation. The import of the data must obviously be done through dataports or other programs and the UAT is where you will test the success of those procedures. If you make changes to your data as a result of issues found during testing, you should ensure you can repeat this when you do go live and document the changes to the data conversion process.

  • The modifications that have been made to the system: the new reports, forms, codeunits and external programs, interfaces, and reports all need to be tested. All too often this is the only thing that is covered in UAT. Once again, corrections may mean that previously accepted tests need to be re-tested. This is known as regression testing—it’s not what you fixed that’s important, it’s the things you broke along the way that you need to be aware of.

  • The configuration of security privileges. This is to ensure that users can perform the tasks they need to perform without getting error messages and also to ensure that users do not have access to the data that they are not privileged to see.

Plan your testing and test your plan
The only way to have a successful UAT is to write down what you plan to test and record the issues you have found when they are tested. When you need to make the decision to go live or not, you will want to see the list of business process with lots of little ticks against them showing that they have been successfully performed. Remember the problem of regression testing: when you make a change to configuration, data conversion, or programming, you may as well erase all the ticks you have so far – it’s not what you fixed that’s important, it’s what you may have broken. If there are issues, you want to know what they are before you go live; it’s OK to go live with issues, it’s just important to know beforehand they are there and to have taken an informed decision.

When it comes to preparing test cases—well that’s another posting in its own right. Maybe one of our QA team can offer some advice in that area.

Get Some Help
If you have done your job properly, the go live should be painless. There may be a few surprises, but by and large you’ll be going live knowing that the critical things are going to work. It’s important to have good people around to help you through the go live process. When you’re standing on the platform with your towel wrapped tightly around your ankles, gazing at the horizon, it’s good to know that if you do freeze up, one of the professionals stood behind you is going to be quite happy to give you a little push.

Sunday, 25 May 2008

Fast SQL Copy for Employee Portal

Recently I wanted to copy the contents of my Employee Portal setup from one company to another and then eventually from my test system to my live system. Now I know I could probably do this with a dataport and, to be honest, I did try that but something didn’t quite work. I probably made a silly mistake, but it did inspire me to create a SQL stored procedure to do the job for me.

This stored procedure uses the INFORMATION_SCHEMA in SQL to find the field names of any matchining tables and creates some SQL for me to execute. For safety, I only generate the SQL and do not execute it, giving you a change to change your mind if you don’t like what it is about to do.

The really neat trick is the fact that it will handle fields with an AutoIncrement property set in NAV.

I adapted this from a similar stored procedure I created a while ago that would copy all tables from one company to another which is considerably faster than the NAV backup/restore. For Employee Portal tables I am just assuming they start with ‘EP’ – another good reason to inspect the generated SQL before you execute it.

If you use this code and destroy anything, you only have yourself to blame and I will take no responsibility. This code is posted here “as is” for the sake of sharing knowledge and I will not be held responsible for any loss of data or hair that may result from running it.

Here’s the code...

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[NAV_EP_Copy]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE dbo.NAV_EP_Copy
END
GO

CREATE PROCEDURE [dbo].[NAV_EP_Copy]
@SourceCompany varchar(31),
@TargetDatabase varchar(31),
@TargetCompany varchar(31)
AS

--Version 2 - added target database and row count checks

DECLARE @SourceTableName varchar(100), @TargetTableName varchar(100)
DECLARE @TmpColumnName varchar(30), @TmpColumnType varchar(100)
DECLARE @SQLStr1 varchar(8000), @SQLStr2 varchar(8000), @SQLStr3 varchar(8000), @Values varchar(8000)
DECLARE @Identity int
DECLARE @ErrorCount int, @Debug int

SET @Debug = 1
SET @ErrorCount = 0

IF @Debug = 0 BEGIN
PRINT 'Copying NAV Employee Portal'
PRINT ''
PRINT ' From ' + @SourceCompany
PRINT ' To ' + @TargetDatabase + ' ' + @TargetCompany
PRINT ''
PRINT ''
END

IF SUBSTRING(@SourceCompany,LEN(@SourceCompany),1) <> '$' BEGIN
RAISERROR('Source Company Name must use SQL name (replace chars with underscore) and end in $.',1,1)
SET @ErrorCount = @ErrorCount + 1
END


IF SUBSTRING(@TargetCompany,LEN(@TargetCompany),1) <> '$' BEGIN
RAISERROR('Target Company Name must use SQL name (replace chars with underscore) and end in $.',1,1)
SET @ErrorCount = @ErrorCount + 1
END

-- Debug mode does not do anything other than generate messages so who cares if there is data there alredy.
EXEC('set nocount on select top 1 1 from [' + @TargetDatabase + '].[dbo].[' + @TargetCompany + 'EP WP Request Table Tab'+']')

IF (@@rowcount > 0) BEGIN
IF @Debug <> 1 BEGIN
RAISERROR('Target EP WP Request Table Tab table has data.',1,1)
SET @ErrorCount = @ErrorCount + 1
END ELSE BEGIN
PRINT '***** WARNING *****'
PRINT 'Target EP WP Request Table Tab table has data.'
PRINT 'Executing these commands will delete that data.'
PRINT ' '
PRINT 'Data Loss May Occurr.'
PRINT '***** WARNING *****'
PRINT ' '
END
END

IF @ErrorCount > 0 GOTO ENDHERE

DECLARE srcTable CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE(@SourceCompany+'EP%')

OPEN srcTable

FETCH NEXT FROM srcTable INTO @SourceTableName
WHILE @@FETCH_STATUS = 0 BEGIN

SET @TargetTableName = @TargetCompany+SUBSTRING(@SourceTableName,LEN(@SourceCompany)+1,100)
SET @SQLStr1 = ''
SET @SQLStr2 = ''
SET @SQLStr3 = ''
SET @Values = ''

IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')=1 AND TABLE_NAME = @SourceTableName) > 0 BEGIN
SET @SQLStr1 = 'SET IDENTITY_INSERT [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] ON '
SET @Identity = 1
END

SET @SQLStr1 = @SQLStr1 + 'TRUNCATE TABLE [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] '

DECLARE srcColumn CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @SourceTableName

OPEN srcColumn

SET @SQLStr2 = @SQLStr2 + 'INSERT INTO [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] ('

FETCH NEXT FROM srcColumn INTO @TmpColumnName, @TmpColumnType

WHILE @@FETCH_STATUS = 0 BEGIN

SET @SQLStr2 = @SQLStr2 + ' ['+@TmpColumnName+']'

IF @TmpColumnType = 'timestamp'
SET @Values = @Values + ' NULL'
ELSE
SET @Values = @Values + ' ['+@TmpColumnName+']'

FETCH NEXT FROM srcColumn INTO @TmpColumnName, @TmpColumnType

IF @@FETCH_STATUS = 0 BEGIN
SET @SQLStr2 = @SQLStr2+','
SET @Values = @Values+','
END
END

SET @SQLStr2 = @SQLStr2 + ')'
SET @SQLStr3 = 'SELECT ' + @Values + ' FROM ['+@SourceTableName+'] '

CLOSE srcColumn
DEALLOCATE srcColumn

IF @Identity = 1 BEGIN
SET @SQLStr3 = @SQLStr3 + ' SET IDENTITY_INSERT [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] OFF '
SET @Identity = 0
END

IF @Debug = 1 BEGIN
PRINT @SQLStr1
PRINT @SQLStr2
PRINT @SQLStr3
END ELSE BEGIN
PRINT ''
PRINT '-------------------------------------------------------------'
PRINT @TargetDatabase + ' ' + @TargetTableName
EXECUTE ( @SQLStr1 + @SQLStr2 + @SQLStr3 )
END

FETCH NEXT FROM srcTable INTO @SourceTableName
END

CLOSE srcTable
DEALLOCATE srcTable

ENDHERE:

Monday, 12 May 2008

Where Am I? - Still Wondering.

Well I got the image problem sorted out thanks to a suggested from Vjeko. He said that I should open the image in Paint and re-save it as this has sorted a similar issue out for him in the past.

Well that didn't work, but...



What I did do was capture a screen shot of the icons and save those as a bitmap using a product called HyperSnaps and this imported into NAV fine and looks really nice.

Friday, 9 May 2008

Where Am I?

I was really inspired by Waldo’s blog posting on how to see which database you’re in. Take a look on Waldo's Blog if you haven’t seen it before. This is something that our users have been asking for since version 4.0 first did away with the old form-based menu system (something they could easily modify to show them if they were in the live or the test system.)

I really wanted to make a super-nice version of Waldo’s form but I came up short in a couple of areas. Who knows, maybe someone reading this blog will be able to offer some advice as to how to overcome the problems I found.

First of all, let me give you my requirements.

I want the system to provide immediate visual cues to show the users which database or company they happen to be in. The required information is to provide something eye-catching, to show a large bit of text (like “TEST SYSTEM”) and to provide the database name, company name and finally give me a big area where I can put a support message. Oh and one more thing: when I copy my database from the live system and restore it to my test database, I want the messages and visual cues to stay the same – that’s right, I don’t want to have to edit the data to make my test system say it’s the test system every time I restore it from the live backup.

The solution I came up with is pretty much based on Waldo’s solution so I’m not taking credit for this. I just figured, it would be nice to share this with anyone that’s interested and maybe someone with a bit more time can iron out the imperfections. I should also say that I only ever intended this to work with SQL databases so there’s no need to point out that this will not work with a native database.

Before we get stuck in to the How, let’s take a look at the solution I came up with. Like Waldo’s solution, I have some code in my CompanyOpen() trigger in codeunit 1 ApplicationManagement that will launch a form.

My form will look for details in the setup table matching the current company and database and if it doesn’t find a record, it creates one and displays the following image.



I then have a setup form that allows me to edit the info that’s going to be displayed.



The Database and Company get filled in automatically. The Title field get’s displayed, the title Background Colour is an “unimplemented feature” :-)

The Image Type allows you to select from Live, Test and Unknown options which will show one of the following icons.



I downloaded these icons from http://tpdkcasimir.deviantart.com/ and made some adjustments to set the background colour to be the same as NAV’s background colour and resize them (when I say I did it, I mean someone with far more talent than me did it for me.)

Now this is where I hit the first weird NAVism – or maybe I was just doing something wrong. Whenever I put the icons on the form, they got stretched. Anyone know what I’m doing wrong? I thought at first that NAV had some kind of minimum size so I resized the Icons to fit the size of my NAV graphic and these got stretched again to look like this.



But at least you can see the idea – and the way the other data fields are being updated from the database.

The Support message appears at the bottom of the form, the Show Info tick allows you to suppress the message in certain company/database combinations – like maybe your live system. The custom background colour is another unimplemented feature.
I made a list form first and then decided a card form is a little easier to set up the data.

OK now let’s look at how I did this.

To start with, how am I going to solve the problem of my data not getting lost when I restore my live system? Well, there’s some other data that doesn’t get lost when you restore over your database in SQL and that’s the user license. The way the system keeps that is by storing it in a table called $ndo$srvproperty that lives in the master database. So I’m going to create a new table in the master database and use that to store details on my NAV databases/companies. Here’s the SQL to create the table and grant permission to public:

USE [master]
GO
/****** Object: Table [dbo].[IntergenNAVWhereAmI] Script Date: 05/09/2008 20:06:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IntergenNAVWhereAmI](
[timestamp] [timestamp] NOT NULL,
[Database] [varchar](30) NOT NULL,
[Company] [varchar](30) NOT NULL,
[Title] [varchar](50) NOT NULL,
[Title Background Colour] [int] NOT NULL,
[Image Type] [int] NOT NULL,
[Support Message] [varchar](250) NOT NULL,
[Title Background Custom Colour] [int] NOT NULL,
[Show Info] [tinyint] NOT NULL,
CONSTRAINT [IntergenNAVWhereAmI$0] PRIMARY KEY CLUSTERED
(
[Database] ASC,
[Company] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT, DELETE, INSERT, UPDATE ON IntergenNAVWhereAmI TO PUBLIC
GO
SET ANSI_PADDING OFF


OK. So now we have a table we can work with. The next thing is that in each database I’m going to use this with, I’m going to need a view that references this table so NAV can access it via a linked table (you know that NAV can have linked tables that are based upon views which point to tables in other databases right?)

Here’s my code to create the view:

USE [Demo Database NAV (5-0)]
GO
/****** Object: View [dbo].[Intergen NAV Where Am I] Script Date: 05/09/2008 20:04:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Intergen NAV Where Am I] AS
SELECT [timestamp]
,[Database]
,[Company]
,[Title]
,[Title Background Colour]
,[Image Type]
,[Support Message]
,[Title Background Custom Colour]
,[Show Info]
FROM [master].[dbo].[IntergenNAVWhereAmI]

If you don’t create this view you won’t be able to compile the table in NAV since the table is a linked table.

Now you can import my objects into the database. Sorry for the massive amount of code but at least you can paste this into a text file and import it (I decided to stick the code at the end of the post rather than in the middle of this text.)

So we’re nearly there. The next thing is to add this code to your codeunit 1 CompanyOpen() function:

IF GUIALLOWED THEN
FORM.RUN(FORM::"Where Am I");

You’ll probably need to hack the code to change the path for the bitmaps and create the bitmap files in order to get the form to compile and obviously you must have the view created in the database before you can compile the table.

That’s it. Obviously there’s a whole heap of other things to do like to make the setup form accessible from somewhere (menu option.)

Sadly I couldn’t address the problem of being able to click on the Form and press ESC. I really want the form to stay in the background and never come to the front but I don’t think I can do this either.

So if anyone knows how to solve the these problems or feels like finishing it off, feel free to have a go – just post a comment and a link to wherever you post your stuff.

This still isn’t a great solution and I think Microsoft really need to add this as a standard feature – and make it so we can change something really useful like the window colour, etc.

Oh yer, this code is posted without warranty or guarantees. If you decide to try and implement this code and you break something – don’t call me.

Here's the code...


OBJECT Table 50500 Intergen NAV Where Am I
{
OBJECT-PROPERTIES
{
Date=08/05/08;
Time=[ 2:37:12 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
DataPerCompany=No;
LinkedObject=Yes;
}
FIELDS
{
{ 1 ; ;Database ;Text30 }
{ 2 ; ;Company ;Text30 }
{ 3 ; ;Title ;Text50 }
{ 4 ; ;Title Background Colour;Option ;OptionString=[ ,Green,Red,Yellow,Custom] }
{ 5 ; ;Image Type ;Option ;OptionString=[ ,Live,Test,Unknown] }
{ 6 ; ;Support Message ;Text250 }
{ 7 ; ;Title Background Custom Colour;Integer }
{ 8 ; ;Show Info ;Boolean }
}
KEYS
{
{ ;Database,Company ;Clustered=Yes }
}
CODE
{

BEGIN
END.
}
}

OBJECT Form 50500 Where Am I Setup List
{
OBJECT-PROPERTIES
{
Date=08/05/08;
Time=[ 2:38:09 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
Width=16500;
Height=6710;
TableBoxID=1000000000;
SourceTable=Table50500;
}
CONTROLS
{
{ 1000000000;TableBox;220 ;220 ;16060;5500 ;HorzGlue=Both;
VertGlue=Both }
{ 1000000001;TextBox;0 ;0 ;4400 ;0 ;HorzGlue=Both;
ParentControl=1000000000;
InColumn=Yes;
SourceExpr=Database }
{ 1000000002;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000001;
InColumnHeading=Yes }
{ 1000000003;TextBox;0 ;0 ;4400 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr=Company }
{ 1000000004;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000003;
InColumnHeading=Yes }
{ 1000000018;CheckBox;8202;770 ;1700 ;440 ;ParentControl=1000000000;
InColumn=Yes;
ShowCaption=No;
SourceExpr="Show Info" }
{ 1000000019;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000018;
InColumnHeading=Yes }
{ 1000000005;TextBox;0 ;0 ;4400 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr=Title }
{ 1000000006;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000005;
InColumnHeading=Yes }
{ 1000000007;TextBox;0 ;0 ;3157 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr="Title Background Colour" }
{ 1000000008;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000007;
InColumnHeading=Yes }
{ 1000000013;TextBox;16333;0 ;4400 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr="Title Background Custom Colour" }
{ 1000000014;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000013;
InColumnHeading=Yes }
{ 1000000009;TextBox;0 ;0 ;1980 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr="Image Type" }
{ 1000000010;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000009;
InColumnHeading=Yes }
{ 1000000011;TextBox;0 ;0 ;4400 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr="Support Message" }
{ 1000000012;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000011;
InColumnHeading=Yes }
{ 1000000015;CommandButton;9240;5940;2200;550;
HorzGlue=Right;
VertGlue=Bottom;
Default=Yes;
PushAction=LookupOK;
InvalidActionAppearance=Hide }
{ 1000000016;CommandButton;11660;5940;2200;550;
HorzGlue=Right;
VertGlue=Bottom;
Cancel=Yes;
PushAction=LookupCancel;
InvalidActionAppearance=Hide }
{ 1000000017;CommandButton;14080;5940;2200;550;
HorzGlue=Right;
VertGlue=Bottom;
PushAction=FormHelp }
}
CODE
{

BEGIN
END.
}
}

OBJECT Form 50501 Where Am I
{
OBJECT-PROPERTIES
{
Date=09/05/08;
Time=[ 7:52:40 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
XPos=0;
YPos=0;
Width=17270;
Height=5060;
Editable=No;
BackColor=11250603;
BorderStyle=None;
CaptionBar=None;
Minimizable=No;
Maximizable=No;
Sizeable=No;
SaveControlInfo=No;
SavePosAndSize=No;
SaveColumnWidths=No;
InsertAllowed=No;
DeleteAllowed=No;
ModifyAllowed=No;
SaveTableView=No;
OnOpenForm=BEGIN
SetDetails();
END;

OnQueryCloseForm=VAR
l_ApplicationManagement@1000000000 : Codeunit 1;
BEGIN
//MESSAGE(FORMAT(l_ApplicationManagement.CanInfoFormClose));
END;

}
CONTROLS
{
{ 1000000009;Frame ;0 ;0 ;17270;5060 ;Focusable=No;
ShowCaption=No;
Border=No }
{ 1000000001;Image ;0 ;0 ;2310 ;2310 ;Name=Tick;
ParentControl=1000000009;
InFrame=Yes;
Bitmap=C:\Users\davidr\Desktop\tips.bmp }
{ 1000000000;Image ;0 ;0 ;2310 ;2310 ;Name=Warning;
ParentControl=1000000009;
InFrame=Yes;
Bitmap=C:\Users\davidr\Desktop\Warning.bmp }
{ 1000000002;Image ;0 ;0 ;2310 ;2310 ;Name=Question;
ParentControl=1000000009;
InFrame=Yes;
Bitmap=C:\Users\davidr\Desktop\Help.bmp;
OnPush=VAR
l_IntergenNAVWhereAmI@1000000001 : Record 50500;
BEGIN
END;
}
{ 1000000005;TextBox;3520 ;2530 ;7260 ;660 ;Editable=No;
Focusable=No;
ParentControl=1000000009;
InFrame=Yes;
Border=No;
FontSize=12;
CaptionML=ENZ=Database;
SourceExpr=g_DatabaseName }
{ 1000000006;Label ;110 ;2530 ;3300 ;660 ;ParentControl=1000000005;
FontSize=12 }
{ 1000000003;TextBox;2310 ;0 ;14850;2310 ;Name=Title;
Editable=No;
Focusable=No;
ParentControl=1000000009;
InFrame=Yes;
BackTransparent=Yes;
Border=No;
FontSize=24;
SourceExpr=g_Title }
{ 1000000004;TextBox;3520 ;3300 ;7260 ;660 ;Editable=No;
Focusable=No;
ParentControl=1000000009;
InFrame=Yes;
Border=No;
FontSize=12;
CaptionML=ENZ=Company;
SourceExpr=g_CompanyName }
{ 1000000007;Label ;110 ;3300 ;3300 ;660 ;ParentControl=1000000004;
FontSize=12 }
{ 1000000008;TextBox;110 ;4290 ;17050;660 ;Editable=No;
Focusable=No;
ParentControl=1000000009;
InFrame=Yes;
Border=No;
FontSize=12;
CaptionML=ENZ=Database;
SourceExpr=g_SupportMessage }
}
CODE
{
VAR
g_Title@1000000000 : Text[50];
g_DatabaseName@1000000001 : Text[50];
g_CompanyName@1000000002 : Text[50];
g_SupportMessage@1000000003 : Text[250];

PROCEDURE SetDetails@1000000001();
VAR
l_IntergenNAVWhereAmI@1000000001 : Record 50500;
l_Session@1000000000 : Record 2000000009;
BEGIN
CurrForm.Tick.VISIBLE(FALSE);
CurrForm.Warning.VISIBLE(FALSE);
CurrForm.Question.VISIBLE(FALSE);

l_Session.SETRANGE("My Session", TRUE);

IF l_Session.FINDFIRST THEN
g_DatabaseName := l_Session."Database Name"
ELSE
g_DatabaseName := 'Database Not Found!';

g_CompanyName := COMPANYNAME;

IF NOT l_IntergenNAVWhereAmI.GET(g_DatabaseName, g_CompanyName) THEN BEGIN
l_IntergenNAVWhereAmI.Database := g_DatabaseName;
l_IntergenNAVWhereAmI.Company := g_CompanyName;
l_IntergenNAVWhereAmI."Image Type" := l_IntergenNAVWhereAmI."Image Type"::Unknown;
l_IntergenNAVWhereAmI.Title := g_CompanyName;
l_IntergenNAVWhereAmI."Title Background Colour" := l_IntergenNAVWhereAmI."Title Background Colour"::" ";
l_IntergenNAVWhereAmI."Image Type" := l_IntergenNAVWhereAmI."Image Type"::Unknown;
l_IntergenNAVWhereAmI."Support Message" := 'This database was automatically added to the ''Where Am I'' register.';
l_IntergenNAVWhereAmI."Show Info" := TRUE;
l_IntergenNAVWhereAmI.INSERT;
END ELSE
IF NOT l_IntergenNAVWhereAmI."Show Info" THEN
CurrForm.CLOSE;

CASE l_IntergenNAVWhereAmI."Image Type" OF
l_IntergenNAVWhereAmI."Image Type"::Live :
BEGIN
CurrForm.Tick.VISIBLE(TRUE);
END;
l_IntergenNAVWhereAmI."Image Type"::Test :
BEGIN
CurrForm.Warning.VISIBLE(TRUE);
END;
l_IntergenNAVWhereAmI."Image Type"::Unknown :
BEGIN
CurrForm.Question.VISIBLE(TRUE);
END;
END;

g_Title := l_IntergenNAVWhereAmI.Title;
g_SupportMessage := l_IntergenNAVWhereAmI."Support Message";
END;

BEGIN
END.
}
}

OBJECT Form 50502 Where Am I Setup Card
{
OBJECT-PROPERTIES
{
Date=08/05/08;
Time=[ 5:15:14 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
Width=9790;
Height=6160;
InsertAllowed=No;
DeleteAllowed=No;
SourceTable=Table50500;
}
CONTROLS
{
{ 1 ;Frame ;220 ;220 ;9350 ;4950 ;HorzGlue=Both;
VertGlue=Both;
ShowCaption=No }
{ 2 ;TextBox ;3850 ;440 ;5500 ;440 ;Editable=No;
ParentControl=1;
InFrame=Yes;
SourceExpr=Database }
{ 3 ;Label ;440 ;440 ;3300 ;440 ;ParentControl=2 }
{ 4 ;TextBox ;3850 ;990 ;5500 ;440 ;Editable=No;
ParentControl=1;
InFrame=Yes;
SourceExpr=Company }
{ 5 ;Label ;440 ;990 ;3300 ;440 ;ParentControl=4 }
{ 6 ;TextBox ;3850 ;1540 ;5500 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr=Title }
{ 7 ;Label ;440 ;1540 ;3300 ;440 ;ParentControl=6 }
{ 8 ;TextBox ;3850 ;2090 ;2750 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr="Title Background Colour" }
{ 9 ;Label ;440 ;2090 ;3300 ;440 ;ParentControl=8 }
{ 10 ;TextBox ;3850 ;2640 ;2750 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr="Image Type" }
{ 11 ;Label ;440 ;2640 ;3300 ;440 ;ParentControl=10 }
{ 12 ;TextBox ;3850 ;3190 ;5500 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr="Support Message" }
{ 13 ;Label ;440 ;3190 ;3300 ;440 ;ParentControl=12 }
{ 14 ;TextBox ;3850 ;3740 ;1700 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr="Title Background Custom Colour" }
{ 15 ;Label ;440 ;3740 ;3300 ;440 ;ParentControl=14 }
{ 16 ;CheckBox ;3850 ;4290 ;440 ;440 ;ParentControl=1;
InFrame=Yes;
ShowCaption=No;
SourceExpr="Show Info" }
{ 17 ;Label ;440 ;4290 ;3300 ;440 ;ParentControl=16 }
{ 18 ;CommandButton;7370 ;5390 ;2200 ;550 ;HorzGlue=Right;
VertGlue=Bottom;
PushAction=FormHelp }
}
CODE
{

BEGIN
END.
}
}

Thursday, 1 May 2008

Does anyone use NAV in Wyoming, West Virginia, New Mexico, or Alaska?



My blog has just reached 1000+ visitors from the USA. Cool! But there are four states missing. It's sad, but I had to look on another web site to find out what the missing states were - Google Analytics doesn't show the state names of those that have no visitors and I'm not good at the geography of the USA.

Now excuse me for being a complete dunce, but are there any people actually living in Alaska? Do they have internet connections?

Before I moved to New Zealand I wasn't even sure if they had cinemas, so as far as I know Wyoming doesn't even have a state lottery. Maybe West Virginia is three-quarters forest. Who knows, maybe New Mexico was the testing ground for the first atomic bomb. Perhaps Alaska is the United State's largest state and is over twice the size of Texas; measuring from north to south the state is approximately 1,400 miles long and measuring from east to west it is 2,700 miles wide.

The internet's an amazing thing isn't it? Anyway, I'm going to look for some blogs in Alaska and leave a comment or a track back.

On a completely unrelated topic, I'm now a Dynamics NAV expert (see the current featured article on MSDynamicsWorld.com.) Now when I started my first job many years ago, an old and wise programmer told me what he thought of experts. He said, "So you're the new expert are you? Well X is an unknown quantity and spurt is a drip under pressure. X-spurt. Geddit?"

Ahhh. English humour.

Wednesday, 30 April 2008

Partner Sauce

PartnerSource seems to be going through something of a revolution. If you use the Global English site, you have probably been using the new saucy version for a few days now. If, like me, you sign in to a localized version, you may be unaware of the changes that are coming.

So what is so good about the changes that warrant a blog post? I’ll tell you what, three little letters: R S S.

It is now possible to subscribe to an RSS feed for News, Sales & Marketing, Support & Deployment and Training & Certification, meaning you read the news in a Vista sidebar gadget, from within Outlook or on your mobile phone. But for me the ultimate feeds are the Most Recent KB Articles and Most Viewed KB Articles. Now you may think that getting excited about this is a sure sign that I need to get out more or at least get a hobby, but believe me this is going to make my life so much easier.

It seems that I am not the only one that has struggled to find out what is going on in the NAV world without a lot of digging around and checking PartnerSource every day, just in case there is something new. For more details on the changes to PartnerSource and a warm feeling of love from Microsoft, check out the link
Get Ready for Exciting, New Changes to PartnerSource

For some strange reason whenever I clicked on the RSS Feed links on the NAV product site, my Internet Explorer crashed – this was because of Skype and as soon as I uninstalled Skype on my machine it all worked fine. I will report this to the team to see if they can fix it up. The non-product site RSS feeds worked fine.

You can find the NAV-related product feeds on this page: https://mbs.microsoft.com/partnersource/solutions/nav/

Wednesday, 23 April 2008

Runtime Errors Suck!

I hate runtime errors. I think that functions in C/AL that can generate runtime errors should be deprecated where possible or altered in their function. Let me explain with an example.

Let's say you have two fields both called Description on two different tables. One of your tables is the Item table and the other is a new table called "New Item" (OK so I'm not going to win any prizes for imaginative table names in this example.) You want to make an assignment from one field value to another so you would do something like this:

l_NewItem.Description := l_Item.Description;


When your code runs the Description gets copied across and everyone's a happy bunny. But, let's say your customer says they want their item description to be made 20 characters bigger. What happens then?

Well let's assume that you increase the size of the Item Description field by 20 characters. Everything still works. Or so you think.

When NAV executes the line of code that previously worked fine on a record that has more characters in the new larger string than will fit in the other string, you (or more likely the first user that comes across that bit of functionality in your production system) will get a Runtime Error. Blurgghhh!

I have programmed in a few languages and C/AL is the only language I have ever come across that does this. What would other languages do? They would truncate the value and continue. They would assume, "hey, the programmer wants me to put a 50 character string in a 30 character string, he must know what he's doing, so I'll just give him as much as I can."

I like that, it's friendly, it makes me feel warm and fuzzy. Why can't C/AL do the same?

But there is a far worse function that can throw runtime errors and this should be banned altogether! TRANSFERFIELDS.

TRANSFERFIELDS is evil. It has to go.

What does it do? Well it, er, transfers, erm, fields (duh?) It is used to transfer fields from one table to another. Sounds cool doesn't it? How does it decide which fields should be copied? It uses the field ID.

What? The totally arbitrary field ID? Surely not, that would be crazy. Yup you heard it the field ID. NAV will attempt to make an assignment between two fields with the same ID, but different names and different types. And what do you think happens if the field types are incompatible?

Run Time Error.

The reason this function is evil is it lures the unsuspecting programmer into its little trap. It looks innocent. It looks like it may save you some time. Don't be lazy. Assign the fields one by one. Think about it, if you have 10 lines of code that assign each field from one table to another, anyone reading your code knows exactly what is happening. If you really want to be good, create a function on the table called CopyFromItem() or something similar. Then do the field assignments in the function so your code would look something like this:


l_NewItem.CopyFromItem(l_Item);


Now isn't that better?

So what prompted me to have this little rant? Well I am doing an upgrade from v3.70 to v5.00 at the moment and the damn thing just failed with a run time error. Some of the code in the standard upgrade toolkit gave me this error message:


The two fields below must have the same type:
Field: JobTaskNo <-- Table
Table: Temp Job Task Phase Step Comb. <-- Temp Phase Step Task Doc Line
Type: Code20 <-- Integer


Can you guess what caused this error?

TRANSFERFIELDS!

So if the expert coders at Microsoft get caught out, what chance do we have?

Monday, 14 April 2008

Field Captions and the CaptionClass Property

I recently needed to have a field that had a changing field caption based upon some conditions. I knew I had seen this in the standard system for sales orders where the Unit Price field changes between “Unit Price Incl. VAT” and “Unit Price Excl. VAT” depending on how you tick the “Prices Including VAT” field so that is where I started.

If you look on the Unit Price field properties on the Sales Line table you will see that the CaptionClass property is set to GetCaptionClass(FIELDNO("Unit Price")).

That looks like a function call so I take a look in the functions on the table.

GetCaptionClass(FieldNumber : Integer) : Text[80]
IF NOT SalesHeader.GET("Document Type","Document No.") THEN BEGIN
SalesHeader."No." := '';
SalesHeader.INIT;
END;
IF SalesHeader."Prices Including VAT" THEN
SalesPricesIncVar := 1
ELSE
SalesPricesIncVar := 0;
CLEAR(SalesHeader);
EXIT('2,' + FORMAT(SalesPricesIncVar) + ',' + GetFieldCaption(FieldNumber));


Well this seems to be returning either ‘2,1,Unit Price’ or ‘2,0,Unit Price’ depending on whether the Sales Header has the “Prices Including VAT” field set to TRUE or FALSE. How weird is that? Clearly that is not what is displaying on the form.

If you take a look in the C/SIDE Reference Guide (select the option from the Help menu of the application,) there is an interesting line that says “The expression is then interpreted by Trigger 15 in CodeUnit 1.”

Let’s take a look at that codeunit trigger.

CaptionClassTranslate(Language : Integer;CaptionExpr : Text[80]) : Text[80]
CommaPosition := STRPOS(CaptionExpr,',');
IF (CommaPosition > 0) THEN BEGIN
CaptionArea := COPYSTR(CaptionExpr,1,CommaPosition - 1);
CaptionRef := COPYSTR(CaptionExpr,CommaPosition + 1);
CASE CaptionArea OF
'1' : EXIT(DimCaptionClassTranslate(Language,CaptionRef));
'2' : EXIT(VATCaptionClassTranslate(Language,CaptionRef));
'3' : EXIT(CaptionRef);
END;
END;
EXIT('');


This is one of those funny functions that gets called by the system whether you like it or not – you don’t pass the parameters to it but you can guess that what the values contain. I am guessing that in my example the CaptionExpr will contain either ‘2,1,Unit Price’ or ‘2,0,Unit Price’.

On examining the code, I can see that we are pulling out a number from the start of the string into a variable called CaptionArea (which in our case is 2) and using that to either run a new function or return the part of the string that appeared after the number. In our example we are calling VATCaptionClassTranslate(Language,CaptionRef).

So, let’s take a look at what this function does:

VATCaptionClassTranslate(Language : Integer;CaptionExpr : Text[80]) : Text[30]
CommaPosition := STRPOS(CaptionExpr,',');
IF (CommaPosition > 0) THEN BEGIN
VATCaptionType := COPYSTR(CaptionExpr,1,CommaPosition - 1);
VATCaptionRef := COPYSTR(CaptionExpr,CommaPosition + 1);
CASE VATCaptionType OF
'0' : EXIT(COPYSTR(STRSUBSTNO('%1 %2',VATCaptionRef,Text016),1,30));
'1' : EXIT(COPYSTR(STRSUBSTNO('%1 %2',VATCaptionRef,Text017),1,30));
END;
END;
EXIT('');


This function starts by stripping out another parameter into a variable called VATCaptionType and the remainder of the string goes into CaptionRef. Then as you can see the VATCaptionType is evaluated and it returns either ‘Unit Price Excl. VAT’ or ‘Unit Price Incl. VAT’. To know this you have to know that Text016 and Text017 contain ‘Excl VAT’ and ‘Incl. VAT’ respectively.

So that’s it. A good example of how to achieve dynamic field captions using the standard application.

But just to round off, what if I wanted to have my own field with a dynamics caption? Well if you go back to the CaptionClassTranslate function in codeunit 1, you’ll see that option 3 will simply return the value that you passed it back to the caption for the field. This is how you would do it.

Let’s say that we are implementing NAV for a client that wants three addition fields on the Customer Card, the currently have them in their old system called “User Field 1”, “User Field 2” and “User Field 3”. Don’t you just hate that sort of thing? Anyway they say that they want to change the caption to something more meaningful but they can’t decide on what to call them (it’s a lame example I know but it’s late so stick with me.) Being a cunning NAV developer you decide to create three setup fields on the Sales & Receivables Setup table called “Field Caption 1”, “Field Caption 2” and “Field Caption 3”. You can then let the users type the caption they want in these fields and use them for the field captions for the new fields you will add on the customer card.

First of all, you create the fields on the Customer table as “User Field 1”, “User Field 2” and “User Field 3”. Then you create a little function on the Customer table that will take an integer parameter (valued as 1, 2 or 3) and will return a Text value that is the right caption. Let’s call our function “UserFieldCaption”. It might look something like this:

UserFieldCaption(p_FieldNo : Integer) : Text[80]
l_SalesReceivSetup.GET('');
CASE p_FieldNo OF
1: EXIT(l_SalesReceivSetup."Field Caption 1");
2: EXIT(l_SalesReceivSetup."Field Caption 2");
3: EXIT(l_SalesReceivSetup."Field Caption 3");
ELSE
ERROR('UserFieldCaption function on Customer table called with invalid field number of %1',p_FieldNo);
END;


Now on our User Field 1 on the Customer table we would set the CaptionClass property to

'3,'+UserFieldCaption(1)


The other two User Field CaptionClass values will be similar – hopefully you can figure this out yourself.

Once you have compiled everything, set the values of the captions on the Sales & Receivables Setup and Open the Customer table from the object designer. There you should see the caption for your new fields using the value you entered on the setup table.