Ramblings of a Dynamic Mind

Uploading Large Files to RTC

2012-01-19T15:56:00.001+13:00

Recently I have had a client attempting to load large files to NAV through RTC.

They are in the process of migrating a company to NAV and have a fairly large number of G/L Entries to load.

We had already developed a generic G/L Journal upload tool so the data had been mapped into a file that could be loaded though that.

When the routine was run we almost immediately got a ‘File is too large error’

After having a dig around I found a setting in the CustomSettings.Config file that limits the size of upload files.

The setting is

<add key="MaxUploadSize" value="5"></add>

The default is 5mb. So by extending this and restarting the service tier larger files can be loaded.

Dynamics CRM – NAV Connector(NAV Customisations)

2011-07-05T13:05:00.001+12:00

Once the connector is installed you need to configure NAV to publish the web services for it.

Step 1 - Setup the connector.

This is fairly straight forward. Go to Marketing Setup and on the Synchronisation tab check the box ‘Enable Connector’ This will then prompt you to confirm that you want to setup the connector and when you OK it will build all the correct Web Service settings and Integration Page records.

When the connector is setup you will see a set of web services inside Dynamics NAV. To check them go to Administration –> IT Administration –> General Setup –> Web Services

In the Connector itself

Inside the connector you can see the entities that are coming from NAV by going into the Settings and then selecting the NAV Configuration Utility.

Once you have connected to the database and selected the companies from it you which to connect with you will get a list of the published entities from it

This will give you all the standard tables and fields, but often you will want to add new fields and tables that are specific to the customer

Step 2- Add Custom Fields on tables already mapped

To add a new field to a table already mapped you only need to modify the Page.

Here I have added Name 2 to the Contact Page

As all the fields on a Page are exposed as the WSDL this will automatically update the list of available fields when you next refresh the Connector

Here we can see Name 2 has been added to the source fields list in the connector.

Step 3 - Add Custom Tables

Again this is fairly straight forward.

In order to get the system to automatically add the entries to the Web Services and the Integration Page tables you need to modify Coudeunit 5150. There are 3 functions you need to modify.

EnableConnector

In teh EnableConnector Function you need to add a new line for each new table. This allows the routine to determine what the correct Form and table ID’s are for your new table. If you want to force a specific form you can specify it here.

In this example I am adding the Vendor to the connector

      
    EnableConnector()

    IF NOT IntegrationPage.FINDFIRST THEN BEGIN

      SetupWebServicePages(FORM::"Resource List",DATABASE::Resource);

      SetupWebServicePages(FORM::"Payment Terms",DATABASE::"Payment Terms");

      SetupWebServicePages(FORM::"Shipment Methods",DATABASE::"Shipment Method");

      SetupWebServicePages(FORM::"Shipping Agents",DATABASE::"Shipping Agent");

      SetupWebServicePages(FORM::Currencies,DATABASE::Currency);

      SetupWebServicePages(FORM::"Salespeople/Purchasers",DATABASE::"Salesperson/Purchaser");

      SetupWebServicePages(FORM::"Customer Card",DATABASE::Customer);

      SetupWebServicePages(FORM::"Item Card",DATABASE::Item);

      SetupWebServicePages(FORM::"Sales Order",DATABASE::"Sales Header");

      SetupWebServicePages(FORM::"Sales Invoice",DATABASE::"Sales Header");

      SetupWebServicePages(FORM::"Posted Sales Invoice",DATABASE::"Sales Invoice Header");

      SetupWebServicePages(FORM::"Units of Measure",DATABASE::"Unit of Measure");

      SetupWebServicePages(FORM::"Ship-to Address",DATABASE::"Ship-to Address");

      SetupWebServicePages(FORM::"Contact Card",DATABASE::Contact);

      SetupWebServicePages(FORM::"Currency Exchange Rates",DATABASE::"Currency Exchange Rate");

      SetupWebServicePages(FORM::"Customer Disc. Groups",DATABASE::"Customer Discount Group");

      SetupWebServicePages(FORM::"Item Disc. Groups",DATABASE::"Item Discount Group");

      //New Table

      SetupWebServicePages(FORM::"Vendor Card",DATABASE::"Vendor");

      SetupIntegrationService;

    END;

    MarkDeletedIntegrationRecords;

    SetupIntegrationTables;

SetupIntegrationTables

You need to add a new line for each table here

     
    SetupIntegrationTables()

    InitializeIntegrationRecords(DATABASE::Resource);

    InitializeIntegrationRecords(DATABASE::"Payment Terms");

    InitializeIntegrationRecords(DATABASE::"Shipment Method");

    InitializeIntegrationRecords(DATABASE::"Shipping Agent");

    InitializeIntegrationRecords(DATABASE::Currency);

    InitializeIntegrationRecords(DATABASE::"Salesperson/Purchaser");

    InitializeIntegrationRecords(DATABASE::Customer);

    InitializeIntegrationRecords(DATABASE::Item);

    InitializeIntegrationRecords(DATABASE::"Sales Header");

    InitializeIntegrationRecords(DATABASE::"Sales Invoice Header");

    InitializeIntegrationRecords(DATABASE::"Unit of Measure");

    InitializeIntegrationRecords(DATABASE::"Ship-to Address");

    InitializeIntegrationRecords(DATABASE::Contact);

    InitializeIntegrationRecords(DATABASE::"Currency Exchange Rate");

    InitializeIntegrationRecords(DATABASE::"Customer Discount Group");

    InitializeIntegrationRecords(DATABASE::"Item Discount Group");

    //New Table

    InitializeIntegrationRecords(DATABASE::"Vendor");

IsIntegrationRecord

You also need to add a reference in here so the system knows that the Table is part of the integration.

     
    IsIntegrationRecord(TableID : Integer) : Boolean

    IF TableID IN

       [DATABASE::Resource,

        DATABASE::"Payment Terms",

        DATABASE::"Shipment Method",

        DATABASE::"Shipping Agent",

        DATABASE::Currency,

        DATABASE::"Salesperson/Purchaser",

        DATABASE::Customer,

        DATABASE::Item,

        DATABASE::"Sales Header",

        DATABASE::"Sales Invoice Header",

        DATABASE::"Unit of Measure",

        DATABASE::"Ship-to Address",

        DATABASE::Contact,

        DATABASE::"Currency Exchange Rate",

        DATABASE::"Customer Discount Group",

        DATABASE::"Item Discount Group"

        //New Table

        //DATABASE::"Item Discount Group"]

        DATABASE::"Item Discount Group",

        DATABASE::"Vendor"]

    THEN

      EXIT(TRUE);

    EXIT(FALSE);

Once this is done you can refresh the integration by Un-checking the Enable Connector box in Marketing Setup and then rechecking it again. This will refresh the settings.

When you next refresh the connector you will find the new entity

Now the Vendor Card appears in the list of entities and just needs to be enables to be used in a map.

Dynamics CRM – NAV Connector

2011-07-05T13:02:00.001+12:00

With the release of NAV2009R2 Microsoft came the release of the Dynamics Connector for NAV and CRM.

The connector itself has been around for a while and was in use with Dynamics GP and CRM. Since it came out with NAV I have had a play with it and I have a couple of sites that are at various stages in implementing it.

Personally I think linking Dynamics NAV with Dynamics CRM gives a very powerful solution. The users access by the method that makes most sense, fairly often we see uses needing to deal with customers or vendors and track interactions but with a limited impact on the ERP. They maybe raising orders or making basic enquires but they don’t need all the functions that the ERP provides.

Often sales wants to be out and about working in an offline capacity. The offline integration with Outlook in CRM makes this possible, plus it presents a familiar interface.

The connector provides out of the box integration for these sorts of things.

The connector itself acts as a mapping tool and uses web services on both sides to handle the integration.

While working with it I have found the amount of information on how to add customer fields and tables to the connector to be limited

In the next 3 posts I’ll look at how to setup and customise the connector.

The first will look at Dynamics NAV
The second Dynamics CRM
and finally the third will look in more detail at the connector itself

Windows 7 Gadgets and Disabled UAC

2009-03-09T09:13:00.001+13:00

For some reason Windows Gadgets don’t run in Windows 7 if you disable the UAC. Now I like windows gadgets and I am trailing Windows 7 but some of the software I was using was having a hard time with UAC so we turned it off and all of a sudden all my gadgets disappeared. after a bit of searching around I found the solution of the Windows 7 News blog.

<Quote>
The Windows 7 Sidebar and Gadgets do not work correctly if a user disables the User Account Control in Windows 7. There is however an easy way to make the gadgets and sidebar work again without having to replace system files (that was the way suggested in many forums).

All that needs to be done is to change one value of a parameter in the Windows 7 Registry to make the sidebar and gadgets work even with UAC disabled.

Open the Windows 7 Registry by pressing [Windows R], typing [regedit] and hitting [enter]. Now navigate to the Registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Sidebar\Setting and change the value of AllowElevatedProcess to 1.

Restart the computer or logout and login again to see if the changes work as intended. The Registry setting ensures that the gadgets and the sidebar are working in Windows 7 properly even if UAC is disabled.
</Quote>

On my machine that registry value was missing so I added a new DWORD to the setting. Logged out and back in and all my gadgets reappeared. Life is good again :)

NAV Web Services

2009-02-13T09:00:00.001+13:00

So we have been doing some integration work between a custom piece of development and NAV2009. Our NAV Dev setup some code and exposed it as a series of web services. To test them a simple console app was written and everything was good.

Then they tried running the console app on a different machine to the NAV web service, all of a sudden nothing worked.

We went back over the config settings of the console app and confirmed that it was pointing to the correct URL for the web service, which it was, but for some reason when we ran it it kept trying to look for the service on the localhost. This turned out to be a setting in Visual Studio that by default embeds the URL of the web service when you register it, who knew..... well one of our .NET devs did thankfully. So by changing the GenerateDefaultValueInCode to False your solution will look to the Config file instead.

So after changing that and rebuilding the app we tried again. Still not working. After much testing with little joy we tried some simple tests on a different set of machines, on these we managed to get the following

Turned out that Windows firewall was blocking the port we were using, so after adding an exception to the firewall everything worked fine.

So next time we do some of this stuff we are going to make sure the server has the port unblocked and that we remember to tell Visual Studio not to imbed the web service URL (its default action) so we can control it with a config file.

Another Year Over

2009-01-08T14:57:00.001+13:00

It's been an exciting one for me.

First up Dynamics CRM4.0 was released at the start of the year. We have done a number of installation of it now and the team have done some very interesting things with it.

Next Dynamics NAV5 SP1 was released, while not a major release it did bring some interesting things. Firstly it got rid of the highly confusing SIFT tables and replaced them with views. It also brought access to the Dynamics Mobile client.

Then December brought us Dynamics NAV 2009. This is a major release and brings us lots of fun stuff. The most obvious is the new Role Tailored Client(RTC). It also gives NAV web services out of the box, something we have been after for a long time. It supports SQL2008, also released this year. We have already started working with this latest version and have a number of upgrades and new installations lined up for the first part of next year.

Closer to home the Intergen Dynamics team had some significant growth with around a dozen new team members joining over the year.
Dave Roys in Christchurch was awarded Most Valued Person by Microsoft, this is a great honour as there are only a dozen or so MVP's for NAV worldwide and Dave is the only one in Asia Pacific.
Craig Keenan was made a Subject Matter Expert for NAV.
Intergen was involved in the Dynamics NAV Partner Advisory Board and with 7 attendees out of a total of 11 coming from Intergen I think it shows how involved in the future of NAV we intend to be.

Away from the Dynamics products. Windows 2008 and SQL 2008 were both released.
Intergen has been doing huge amounts of work with MOSS as well as its more traditional custom builds. We have also been looking at all the new stuff Microsoft announced at the PDC around Cloud Services, Windows 7 etc.

2009 should be a very interesting year, between all the new things that are coming and the current global economic situation there will be many challenges ahead. More and more I think companies will be looking at ways to work smarter and reduce their overheads. Hopefully that will mean that people like me will still be in demand.

NAV 2009 Released

2008-12-02T19:36:00.001+13:00

NAV 2009 has been released in the 'wave 1' counties. This means some things that we have been waiting for a couple of years for are finally available.

There are still some gaps and the new client will take some getting used to but the addition of web-services out of the box and the new architecture is a great leap forwards.

I have posted before about web-services and since then I have been trying a few different things. In one demo from Microsoft they showed an example of them by adding a 'E#dit in Excel' button to a page that launched Excel, pushed the currently filtered data over and then allowed a user to modify it and update back into NAV.

Another interesting example has been posted by Dave 'Gaspode' Roys. In his example he is using an infopath form to get NAV data, edit it and submit it back to the system. Take a look here.

I am also trying a few things using the new client as well. Generally I 'learn' better when I have a project that needs a solution, fortunately I have a pet NAV project to play with. In our office we have a snack club that I current run, while its overkill really I keep track of it all in NAV. This gives me a chance to try to solve the minor problems that come along, and play with the new cool stuff.

This weeks project was a migration from NAV5.0 to NAV2009. As the basis of the system was fairly standard this was a straight forward exercise, adding a few new fields to tables and the doing a NAV backup and restore to a W2k8 server with SQL2k8 and NAV2009. Then I went through and rebuilt the few special bits of code that I needed.

Next up will be the addition of some RS reports and a simple app that the team can use to record what they are taking and set up their bills, a task that is currently done in a book. Once I have it figured out I'll post some examples.

Webservices in NAV2009

2008-10-07T15:50:00.001+13:00

Anyone that has been reading up on NAV2009 cannot have failed to have noticed that NAV can now publish Webservices. I know clever developers have been building Webservices for NAV for a while now, but they have been building them from scratch. NAV2009 gives a simple form where you can expose a Codeunit, or a Page(the new object type used by the .NET client).

One of the examples in the Application Developers Guide publishes a Page built against the Customer Table. The example then goes on to build a simple console app that inserts, Modifies and deletes a customer record, displaying the results between each stage.

Now I know very little C# and the example has a number of bugs in it but even I have managed to get this work and add some very basic error catching in a few hours. One of the devs here told me it would have taken him somewhere between a day and 2 days top achieve the same in the past.

So the example I am talking about.

In NAV open Object Designer and run form 810
Add Page 21 and call it Customer

In Visual Studio 2008 I started a ConsoleApp Project.

I then added a reference to the WebService by Right Clicking Service References and selecting Add Service Reference. I then Clicked the Advanced Button

I then clicked Add Web Reference

I then add the webservice and named it WebService(not very original I know)

Once I had this I could start with the C# Code. Admittedly most of this was copy - pasted from the example. But as I said there are a few bugs that I had to resolve, plus I wanted some simple error trapping

        1: using System;

       2: using System.Collections.Generic;

       3: using System.Text;

       4:  

       5: namespace ConsoleApplication1

       6: {

       7:    // import newly generated Web service proxy

       8:    using WebService;

       9:   

   class Program

   {

       static void Main(string[] args)

       {

           //create instance of service and setting credentials

           Customer_Service service = new Customer_Service();

           service.UseDefaultCredentials = true;

  

           //create instance of customer

           Customer cust = new Customer();

           cust.Name = "Customer Name";

           //Next line will throw an error. Comment out to get successful passing

           cust.Address = "12345678901234567890123456789012345678901234567890123456";

           Msg("Pre Insert");

           PrintCustomer(cust);

          

           //insert customer

           //service.Insert is wrong. Correct and added a Try/Catch for errors

           //service.Insert(ref cust);

           try

           {

               service.Create(ref cust);

           }

           catch (Exception ex)

           {

               Console.WriteLine(ex.Message);

           }

           Msg("Post Insert");

           PrintCustomer(cust);

  

           //create filter for searching for customers

           List&lt;Customer_Filter&gt; filter = new List&lt;Customer_Filter&gt;();

           Customer_Filter nameFilter = new Customer_Filter();

           nameFilter.Field = Customer_Fields.Name;

           nameFilter.Criteria = "C*";

           filter.Add(nameFilter);

  

           Msg("List before modification");

           PrintCustomerList(service, filter);

  

           cust.Name = cust.Name + "Modified";

           //service.Modify is wrong. Correct and added a Try/Catch for errors

           //service.Modify(ref cust);

           try

           {

               service.Update(ref cust);

           }

           catch (Exception ex)

           {

               Console.WriteLine(ex.Message);

           }

  

  

           Msg("Post Modify");

           PrintCustomer(cust);

  

           Msg("List after modification");

           PrintCustomerList(service, filter);

           try

           {

               service.Delete(cust.Key);

           }

           catch (Exception ex)

           {

               Console.WriteLine(ex.Message);

           }

  

  

           Msg("List after deletion");

           PrintCustomerList(service, filter);

  

           Msg("Press [ENTER] to exit program!");

           Console.ReadLine();

       }

  

       static void PrintCustomerList(Customer_Service service, List&lt;Customer_Filter&gt; filter)

       {

           Msg("Printing Customer List");

  

           //conduct the actual search

           //service.Find is wrong. Correct and added a Try/Catch for errors

           //Customer[] list = service.Find(filter.ToArray(), null, 100);

           Customer[] list = service.ReadMultiple(filter.ToArray(), null, 100);

          

           foreach (Customer c in list)

           {

               PrintCustomer(c);

           }

           Msg("End of List");

       }

  

       static void PrintCustomer(Customer c)

       {

           Console.WriteLine("No: {0} Name: {1}", c.No, c.Name);

       }

  

       static void Msg(string msg)

       {

           Console.WriteLine(msg);

       }

   }

}



This is an area I think will make my life a lot easier as I get involved in integration projects fairly often.

As I get more knowledge I'll post up more examples.

Intergen is the MS NZ Partner of the Year 2008

2008-10-06T12:01:00.001+13:00

Year year Microsoft partners get various awards. Most of these are based on submissions. Partners enter into the various categories and are the submissions are then judged by Microsoft.

Partner of the Year is different. Microsoft employees look at the partners and selects those that they feel are suitable. These are then judged.

For Intergen this is a huge honour and, coupled with being announced as a member of the Presidents Club, firmly establishes us as the leading Microsoft Partner in NZ.

To see all the categories and winners for this years Microsoft awards take a look at the MS Site here.

Vista Gadgets

2008-10-06T10:51:00.001+13:00

I have been using Vista for almost a year now and one of the things I like in it is the Sidebar. When I was on XP I used desktop gadgets from a third party so when I finally got Vista I did some digging around its sidebar. While there are a number of cool gadgets two of the most useful I have found are the App Launcher and the Magic Folder.

App Launcher allows a user to set up shortcut's to what ever they like and they can be grouped into folders. While a number of standard windows folders can be accessed, for example My Computer, one of the things that is missing is links to the user folders such as Documents by default.

The other gadget I have found really useful is the Magic Folder. I often save all sorts of things to my desktop over the course of a day. Magic Folder allows me to drag and drop them onto the folder which then files them away based on the file extension. So any pictures are moved the the My Pictures folder, pdf's move to a folder inside My Documents, etc. You can specify your own file extensions and you can move to anywhere on your system.

If you find you regularly have masses of files on your desktop and you can store them loosely by file extension it is well worth looking into.

Dynamics NAV MVP

2008-10-06T09:32:00.001+13:00

I am pleased to say that the newest MVP for Dynamics NAV is Dave Roys.

Why am I so pleased that Dave has been recognised? well for the past four and a half years I have been working along side him. Dave is based out of Intergen's Christchurch office. Prioir to his move to New Zealand he had been working for a UK partner while I was with, at that time, Navision UK Ltd, and he was a student on one or two of the courses I ran back then. So in a ,very small, way I help contribute to his passion for NAV, well I hope anyway lol.

If you haven't already read his blog I advise you to do so.

RPO Released

2008-09-17T08:11:00.001+12:00

What is the RPO?

The RPO (Runtime Page Optimizer) speeds up ASP.NET and SharePoint websites. Its simple. We automated 8 of the guidelines set out in Yahoo's "Best practices for speeding up your site" and checked these with the creators of YSlow to make sure the RPO gives you the fastest website ever. Try the RPO now from www.getrpo.com.

Technical background

The RPO (Runtime Page Optimizer) is a software component for IIS 6/7 web servers that speeds up ASP.NET and SharePoint websites significantly. The component works at runtime - with no development changes, no extra hardware and performs these optimizations:

Combination. Combines JS files, CSS files reducing HTTP requests. It can optionally combine JPEGs and GIFs into CSS sprites to reduce the number of images. This significantly decreases the roundtrips between browser and server reducing page load time
Compression. Minifies and gzip compresses resources, decreasing page load times and data traffic
Caching. Increases speed through caching - automatically setting far future expires on the browser, caching optimized resources on the server, with full control over when both caches are refreshed
Browser aware. Works with all versions of IE, FF, Opera, Chrome and Safari - optimizations are automatically turned on/off to meet the browser's capability
Framework support. Supports ASP.NET 2.0+, AJAX, SharePoint 2007, DotNetNuke, EPiServer
Fast set up. No extra hardware required, no CDN maintenance, no browser software, zero impact installation - component is a single DLL installed on a web server
You decide how fast your pages load. Out-of-the-box the RPO will speed up websites at level-4 optimization. For even greater speed, you can use the RPO to sprite images and combine all JavaScripts into one, you'll just need to ensure your images are correctly scaled and your JavaScripts behave well when combined. This level of optimization usually takes a "milestone" to complete, the RPO reduces the developer effort to hours and keeps your images and scripts in their original maintainable format

Do you need it?

Speed is everything. Unless you've implemented best practices, the website that seems fast on your local machine is often slooooooow when people use it from around the country or internationally. Users hate slooooooow webpages. Asking your customers to wait is like asking them to leave. Try the RPO now from www.getrpo.com.

Microsoft Dynamics NAV compatibility with Windows Server 2008 and SQL Server 2008

2008-09-02T12:22:00.001+12:00

Microsoft Dynamics NAV 4.00 SP3 and 5.00 SP1 are compatible with Microsoft Windows Server 2008. The only updates to take into account only applies to customers with Business Notification and/or Commerce Gateway functionality. You can find more info in KB article 956161.

Compatibility with SQL Server 2008 is still in test. Just check this site for an update on the test results... . I will (try to) keep you informed as well, off course.

Intergen in top 5% of Microsoft Dynamics Partners

2008-07-24T14:36:00.001+12:00

Posted From Scoop

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.

“Microsoft is proud to congratulate Intergen on being named to this year’s Microsoft Dynamics President’s Club,” said Doug Kennedy, vice president, Microsoft Dynamics Partners. “Intergen not only has demonstrated a high level of product expertise, but also has provided a superior level of service and commitment to our Microsoft Dynamics customers, ultimately contributing to the overall success of Microsoft Dynamics and companies worldwide.”

Intergen is New Zealand's most experienced provider of Microsoft based business solutions. The company works closely with Microsoft to ensure it has the best possible understanding of their technologies so it can provide the best solution for clients.

Tony Stewart, Intergen’s Managing Director, said the award was a wonderful recognition. “It’s a fantastic result and one that is due to the efforts of the entire Intergen team. We’re always asking ourselves how we can work better for our clients and we’re delighted to be rewarded for our efforts.”

The team atmosphere is a key reason for Intergen’s success. In the Unlimited/JRA Best Places to Work in New Zealand Survey for 2007 Intergen was listed as the sixth best place to work in the ‘Medium-Large Workplaces’ category.

Intergen is also a Microsoft Gold Certified Partner which recognises the company as an ‘elite business partner’ in working with Microsoft products.

Dynamics Entrepreneur Solution

2008-03-13T15:30:00.002+13:00

Microsoft recently released a 'new' product in the Dynamics space. Dynamics Entrepreneur Solution. I say 'new' as in reality it is Dynamics NAV with a rebuilt GUI.

They have taken away all but the basics that a small business would need. Pretty much all that's left is simple G/L, Sales and Marketing, Purchasing, Inventory and Resources.

All the other functionality is still hidden away behind the scenes. The intention being that when a customer is of a sufficient size to need those extra bits 'migrating' them to a full blown NAV is just a case of modifying the forms and adding to the menus.

The new forms are very nice. They present the information is a user friendly manor and make it easy for a user to see related information. Where in NAV you would be going to menus to get at related information e.g. Cross References for a customer. as you can see in the screen shots in NAV you would open the customer and find the one you wanted. Then click the Customer Menu. Then Open the Cross References

In Entrepreneur Solution you open the Customers screen. This gives you a list of all customers with some easy filter controls.

By clicking the blue arrow next to the customer you want you get that customers card and in the tabs at the bottom of the card you can select the cross references.

I kind of like this layout. When I was working for MBS I had a fair amount of exposure to Axapta, now Dynamics AX, and some of the screens in the new Entrepreneur Solution have a similar initial look and feel without the dozens of menus to actually make things happen. I can see some of the new screens being popular with larger NAV sites as well as the customers that Entrepreneur is aimed at.

I think it will be very interesting to see how Entrepreneur takes off in the market place.

Dynamics CRM integration to Dynamics NAV

2008-02-25T20:55:00.000+13:00

One of the projects I am currently working on involves managing appointment bookings, tracking activities and making sales to a large customer base.

All of these things are easily achieved in any decent CRM product and being a Microsoft Gold Partner we naturally are using MS CRM. Fortunately for this project MS CRM 4.0 had just been released as we went into the development phase so we took all our CRM 3.0 knowledge and started applying it to CRM 4.0, as well as picking up on all the new cool stuff that we can now do.

There is also a big requirement on replacing the financial systems for this client as their current system no longer does what is needed. For us this means using Dynamics NAV 5.0 deployed at a head office level.

The tricky part of the solution is allowing the devolved users that will live predominately in CRM to record their information and have it feed the NAV system. To do this we needed fairly extensive integration between the two products, something that isn't available 'out of the box'. In the past we have done this sort of project and manually built the integration points, but they were fairly minor, simple things. This solution requires much more complex integration points.

After search around we settled on the Celenia Connector. The latest version of this allows us to wire together all the parts of the two systems and it lets us build configurable workflows in the connections.

This has allowed us to deploy portions of the financial system to users that will never actually have to access the NAV Client. As CRM is web deployable this also minimises the software footprint on individual machines, something that is desirable as all 'managed' desktops at this client are deployed via Citrix.

Microsoft CRM4.0 "Titan" Released

2007-12-20T08:15:00.001+13:00

REDMOND, Wash. — Dec. 17, 2007 —The new version of Microsoft Dynamics CRM, formerly code-named “Titan,” has been completed and released to manufacturing, Microsoft Corp. announced today. The new version is offered under two product names: Microsoft Dynamics CRM 4.0 for on-premise and partner-hosted deployments and Microsoft Dynamics CRM Live for Microsoft-hosted deployment. Designed with a single unified-code base for both on-premise and on-demand deployments, Microsoft Dynamics CRM enables customers to choose the right deployment model for their specific business and IT needs, with the flexibility to change deployment models over time if their needs or preferences change.

This is good news for me as I am leading a project that is using CRM4.0 as a major component.

Read the full release here.

NAV goes WSS3.0

2007-10-24T17:08:00.001+13:00

Finally Microsoft have released an update for NAV that allows it to use WSS3.0 for Employee Portal instead of WSS2.0. This has been much requested and looked forward too, especially in our office were we are doing more and more work involving WSS3 and MOSS.

If you are a MBS partner you can download the update here https://mbs.microsoft.com/partnersource/downloads/releases/EmployeePortalNAV50.htm

Reporting Services, Hierarchy's and cross Browser Problems

2007-09-11T21:43:00.001+12:00

This week I have been doing some Report development for a custom application we have been developing at work. Given everything is SQL based we opted to build the reports using Reporting Services.

On the whole the reports are pretty straight forward. However there were 2 challenges.

The first is around hierarchical reporting. With in the application people can belong to different Occupational Units's(OU). One of the business rules for the reporting is 'A user can only report on their own OU and any OU that is below them.

To achieve this I ended up building a recursive function that returns a Scalar Table that works out all the OU's a specific user can see. I then use that in my where clause.

The Table Function looks like this

CREATE FUNCTION [Generate_OUTree] 
(    
    -- Add the parameters for the function here
    @OUID [uniqueidentifier]
)
RETURNS @OUTree TABLE
(
OUID [uniqueidentifier],
ParentID [uniqueidentifier],
OUName Varchar(500),
Level Int
) 
AS
begin

    -- Add the SELECT statement with parameter references here
    With OUTree (OUID, ParentID, OUName, Level)
    As
    (
    -- Anchor Member defination to get top level OU
        Select OUID, ParentOUID, OUName, 0 as Level
        From CustomerOUView
        Where OUID = @OUID
        UNION ALL
    --Recursive member defination to get child companies
        Select CCV.OUID, CCV.ParentOUID, CCV.OUName, Level + 1
        From CustomerOUView CCV
        Inner Join OUTree  
            On CCV.ParentOUID = OUTree.OUID    
    )

    insert into @OUTree
    Select * From OUTree
RETURN

As you can see I pass in a parameter that is the ID of the users OU.

To actually use this to achive what I want I am calling he function like this

    Select *
    From dbo.staffView
    Where OUID IN(Select OUID From dbo.Generate_OUTree(@OUID))

This effectively returns all the rows from my view where the OUID in the view is in the list generated by my function.

The follow on to this was we had to build a full OU tree report. The report shows each OU under its parent and indents teh list so it is wasy to see which child OU belongs to Which parent.

The hard part was to be able to sort the report so that the OU structure was returned in a format that made it easy to group the OU's together.

CREATE PROCEDURE [Report_GenerateOUList]
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    With OUTree (OUID, ParentID, OUName, Level, Sort)
    As
    (
    -- Anchor Member defination to get top level OU
        Select OUID, ParentOUID, OUName, 0 as Level ,Cast(OUName as Varchar(4000))
            ,OperatorCategoryName,MembershipCategoryName
        From CustomerOUView
        Where ParentOUID = '00000000-0000-0000-0000-000000000000' --top level OU has no parent

        UNION ALL
    --Recursive member defination to get child OU's        Select CCV.OUID, CCV.ParentOUID, CCV.OUName, 
            Level + 1, Cast(Sort + '|' + CCV.OUName as Varchar(4000))

        From CustomerOUView CCV
        Inner Join OUTree  
            On CCV.ParentOUID = OUTree.OUID    
    )
    Select * From OUTree 
    Order by Sort

In the database the OU ID's are all GUID's and as I wanted all OU's displayed in this report I have hard-coded for the top level OU.

Inside Reporting Services I am using the following to get indentation

=(Space(Fields!Level * 5) + Fields!OUName.Value)

This then causes 5 spaces for each level down the tree to be inserted in front of the OU's Name.

Once I had all this going and deployed to my Report Server everything appeared to be working fine when I viewed the reports with IE7. The only remaining thing to do was do a second check on the reports using Firefox.

Firefox and Reporting Services have a few issues. Due to he way RS renders the reports in IFRAME's Firefox causes them to get squashed up and not display correctly. After some frantic searching, Google is your friend , and a bit of testing on a couple of report servers found the following.

First-up Firefox only shows the top 2 to 3 cm's of your report. The rendering puts it a small IFRAME instead of expanding out down your 'page' the way IE does. This is fairly easy to resolve, thanks to Jon Galloway's blog for this answer. You need to edit the ReportingServices.css. On my machine it lives here "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles\"

In the css file you need to add the following entry.

/* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
  min-height: 860px;
}

This forces Firefox to use a taller IFRAME.

I also found that the width of the report was getting squashed up on my deployment server, although on my laptop's report server it wasn't happening. On investigating the two machines the only thing I could find was a difference in the build versions of the 2 SQL servers. The Deployment server is using SQL2005 SP1 while my laptop is SQL2005 SP2.

As a work around I found that by putting an empty text box in the header of my reports that was the full width of the report Firefox no longer squashes up the report. While ideally I will update the server with the latest SP for SQL this is acting as a work around for the time being.

Dynamics Book

2007-09-04T10:46:00.001+12:00

Maybe I have been a bit slow. Or maybe I just wasn't paying attention to the right things but I just found a useful site that is being put together by the Dynamics community. So far the vast majority of the work appears to have been done by David Singleton, one of the MVP's for Dynamics NAV.

It's called the Dynamics Book and its a wiki for all the Dynamics Products. Understandably at this stage the majority of the information is around Dynamics NAV but hopefully with the wider community getting in on the act that will grow and it will become a good source for all Dynamics products.

This in conjunction with the forums, MiBuSo and Dynamics User that many Dynamics Users and Consultants regularly contribute too are an invaluable resource for anyone working in this space, be you an end user, new consultant or developer, or experienced consultant or developer. There's always a new trick to learn. Check them out.

I'll be adding permanent links to my tool box for all of them.

Document Approval in Employee Portal

2007-07-28T11:15:00.001+12:00

In NAV 5.0 there is now standard document approval functionality.

While it is fairly basic in some respects, it is more than adequate for a smallish company to handle purchase and sales approvals.

A request I had with one of my clients was for the document approvers to check and approve the documents on a website as these approvers would have very little reason to access NAV for any other reason.

I opted to deploy the approvals via SharePoint using Employee Portal.

Initially I figured this would be fairly straight forward. All I needed to do was set up a couple of web parts in NAV that displayed the Document approvals data, pre filter it so that only approvals for the user that had logged in would be displayed. Provide a link that would display the associated Purchase document. I'd need an option box adding that allowed the user to select Approve, Reject or Delegate.

All pretty simple stuff really. So off I went, I added the Option field to a table, set up a list web part for the Document Approvals, I also set up a Card part for this table, the logic being I'd display a list of all approvals with minimal information and then the user would access the card part to get more information and to select the approve, reject, delegate options. In the card part I added a save button.

Now came the tricky bit. I needed to update NAV when the user made a selection.

In standard NAV there the only tables that can be updated by Employee Portal are the ones related to the demo web parts, i.e. Customers, Sales Headers and Lines, BOMs and Items. If you want to get anything else back in you have to go and make changes to at least one codeunit. Depending on what you are trying to do you need to work on codeunit's 6822 through 6827. For what I wanted to achieve I had to add extra functions to 6824 - Modify Head Data mgt.

What I had to do was add a new function that deciphered the incoming XML, extract the fields I was interested in and then execute the standard Approve, Reject, Delegate Functions.

This took a while because I used a principle I had used many times before when building new code. That is I build it up in small steps checking I am getting the result I want at each step. With the Employee Portal bits what I failed to realise was that I had to initialise a whole heap of variables that get passed out of the code I was working on so that the webpart updates after my code executes. As I hadn't added this code I kept getting error after error in the portal site. Given that every time you make a change you have to stop and start the NAS so that it gets the new code and you can't access the standard Debugger to see where it is failing this became a fairly long process of insert an error command in the code. Restart the NAS, reload the approval document, save it and see what happened, i.e. did I hit my deliberate error before hitting the piece of code that was failing. Once I found the point where I had the line of code that was giving a problem it was straight forward to resolve, finding that line took me about 4 hours.

Once I understood where in my 'normal' style of development I had gone wrong it took me another couple of hours to get the functions I wanted to work. I did run into 2 other problems with it all.

The first was that when i update the record that the Portal has gotten I am getting a 'Another user has modified this record since it was retrieved from the database' error. I can almost live with that, I am going to attempt to set it up so that the portal resets and doesn't attempt to display the updated record anyway.

The second proved a lot more problematic. After getting the web base approvals set for one company I am in the position where I need to roll it out over several companies for the same client. What I don't want to do is have multiple NAS's running as the client is going to be creating new companies and removing old ones from time to time, so I want to run the Approval functionality cross multi companies. after much thought we decided to make a new approval table that is updated when approvals are created and modified, This table also captures the company name of the NAV company that the approval was raised in. We then use this to drive the Approval Portal.

This gave us a lot of headaches as when something is approved in the Portal it needs to update the correct company in NAV, but some clever coding has gotten round this. There is a fair amount of other multi company functionality required by this customer so I'll do a separate post about some of those challenges once I have them figured out.

Reporting Services on Dynamics NAV

2007-06-25T17:22:00.000+12:00

When using Reporting Services with Dynamics NAV there are a number of things to take into consideration.

One of the main things to consider is the way the SQL Tables are structured with Dynamics NAV. For each company in the database there will be a set of company specific tables. These are prefixed Company Name$. So for example the Customer table for a company called CRONUS International Ltd. is CRONUS International Ltd_$Customer. Note the '.' at the end of the company name has been replaced with an '_'

This is based off of a setting in NAV that defines which special characters are replaced, and is done to make life simpler in SQL.

This means that when writing reports you need to know the company name of the NAV Company you want to report on. Where there is only 1 Company in the database this isn't to painful, however as a developer it means every time you want re-use a report into a different company you need to go back in and change the Company name part of each table. It also means that, assuming you are going to deploy your reports to a test environment prior to a live deployment' that your NAV Company in the test system has to be the same as in your live one, a practice that can cause other confusions when using the NAV Client.

To get round this problem you can do one of 2 things.

You can generate a SQL View to present the data you want to report on and leave the company name out. This at least makes the reports cross company compatible as you only have to build the views once for each company.
If you are basing your Reports on data prepared by stored procedures you also have the option of using dynamic SQL and passing the Company Name as a Parameter at run time.

Both of these options also are fine where you have multiple companies in the database as you can add an identifier to the SQL View and use a Parameter in the report to filter for the correct data.

However, as with a project I am currently working on, when you have multiple companies and the prospect of more being added and some being closed out on a regular basis the prospect of keeping the SQL Views up to date can force you to use the second option only, i.e. use dynamic SQL and pass the company name as a parameter.

Another thing to consider is that not every field you can see in the NAV client is actually held on the underlying SQL Tables. NAV flowfields are one such example. Form experience this can make what appears to be a simple report very complicated.

Reporting Services Overview

2007-06-25T17:21:00.000+12:00

Reporting Services is a part of Microsoft SQL Services. It first appeared as an optional download for SQL2000 and is a standard option for SQL2005.

As its name implies it is a reporting tool. More specifically its a web based reporting tool built on SQL but that allows reporting on any data source that you can create an ODBC connection too.

Predominantly I use SQL databases as the data source, although I have also built reports using Analysis services as the data source.

The principle is very straight forward. Using Visual Studio 2005 you create a new report. Link it to a data source, either defined specifically in the report or by using an external shares link.

You can then write SQL queries against the data in a series of data sets. You can also set a data set to execute stored procedures on the SQL database. This allows for very complex queries to be written.

Within the report you have access to a number of different controls, including graphing and pivot table style controls.

You can specify that some controls are dependant on other controls and only appear when the parent is selected.

Another useful tool is the principle of 'drill through' reports. These are effectively extra reports that are called when part of a report is selected. All appropriate parameters are passed down to the new report. This allows you to write summary reports with the drill through allowing you to have details when needed.

Another feature of Reporting services is the ability to subscribe to reports. This allows a user to pick a report from the report server and specify when they would like to automatically get it. They can also specify the format the report should be delivered in, e.g. pdf, Excel Spreadsheet, tiff etc. The can set what parameters the report should use and where it should be delivered to. This allows a sales manager, for example, to have the previous days sales results delivered at 8am to his email as an excel spreadsheet.

One final tool that comes with some of the versions of Reporting Services is Report Builder. This is a web deployed report designer. It requires that a data model has been built in advance but once that has been done then an end user can use the Report Builder to build ad hoc reports on the data source. These could be one off reports or if the user decides that the report will be useful to other users they can deploy the report back to the report server where it becomes available to all users.

The Report Library itself is either accessed via its own web site or it can be linked to Microsoft SharePoint. Where it is linked the reports appear in their own library inside the SharePoint portal and can be used in the same was as from the web site.

Jobs

2007-06-23T15:59:00.000+12:00

Under NAV 5.0 the Jobs modules has been fairly heavily rebuilt.

I'll summarise here and try to expand in the future.

For a long time it has been felt that the jobs module in NAV needed to be overhauled. In NAV 5.0 Microsoft has done just that.

Gone are the old concepts of Phases, Tasks and Steps.

Gone are Job budgets.

Now we have Job Task Lines and Job Planning Lines.

Each job has Job Task Lines. These are built up in a similar way to G/L Accounts. Lines are set to be heading, posting, begin and end totals or totalling in the same way as G/L accounts.

The structure also mirrors the structure of MS Project and while there is no integration out of the box right now, its something I think could be done fairly easily, when I get a bit of time I'll give it a go.

You can also specify different Job Posting Groups against each Task Line which allows for better division of WIP and Job Recognition.

Each Task Line has associated Planning Lines. Planning lines are similar to the old job budgets. However the do slightly more than the old budgets. Each Planning Line can be defined as Schedule or Contract or both. At the simplest level Schedule is the expected costs and profits, just like the old budgets. Contract defines the amount you are allowed to on charge to the customer of the Job.

Whenever you post an entry to a Job you have to select if it is Contact, Schedule, Both or neither. You also have to select the Job Task that the entry is against.

Once you start posting Job entries you can create sales invoices. Again this is much easier than under Version 4 and earlier. Now all you have to do is go to the Job Planning lines, or Job task lines, and use a function 'Create Sales Invoice'.

Other enhancements to Jobs include more control around how WIP is calculated and posted, including a new Tab on the Job card that gives a breakdown of the current WIP and Recognition.

Navision Rapid Implementation Methodology

2007-06-22T09:16:00.000+12:00

So the RIM tool kit for NAV has been around for a while now, but I have just started a project that will use this with Version 5.0

With NAV 5.0 when you create a new company you get prompted to say what type of company it is. The list of 'out of the box' configurations is fairly limited with options for Food, Furniture, High Tech, Machinery and Wholesale.

Selecting one of these automatically set's up master data, which is all configured in XML files hidden away in the Client install folder.

That said its fairly straight forward to create new configurations.

Create a new XML file for the configuration you want

Modify the Configuration.xml file to reference the new file.

Once you have your nice new company all set up and the configurations from the XML file have all been loaded its time to move onto the specific data for this company.

Again the RIM tool makes this easy, and if you build your XML configuration file correctly you will even have the RIM Templates setup and ready to go, if you don't have the templates setup for you then you will need to create them but this is also straight forward.

The setup templates fall into 2 categories, Setup Configuration(handled by the Setup Questionnaire) and Data Migration(handled by the Data Migration).

The Setup questionnaires take a little bit of thought initially. You can have multiple setup questionnaires that each have multiple Setup tables associated with them. Looking at it I think you should always try to only use 1 Questionnaire, or ensure that each Setup table is only referred too once or you could end up with some confusion.

Once you have your Questionnaire you need to go to the actual questions. These belong to 'Question Areas'. These are the Setup tables. Give your Question area a Code and description and then associate it with a table. You can then use the function Update Questions. This will populate the question lines with all the fields from the table. You can then go through modifying the questions for each line so that it makes a bit of sense to a user. You can add comments or define initial answers. After you have your Question Area completed move onto the next one.

Once all your Question Areas are designed you go back to the main Questionnaire screen and user either Export to XML or Export to Excel. So far I have only used the Excel option as I intend to give the file to the user to complete. When the excel file is filled in you can re-import it and the answers will be loaded to the Question Area Screens where you can review them and then Apply them, this will update the Setup tables... Easy huh?

For the Data Migration the process is similar. This time there is no Questionnaire to worry about. You just define the table that you want to migrate data to and then select Migration Fields from the Migration button menu. In here you define the fields that will be imported. When you open the screen you will get a list of all the fields and you just need to flag those that will be included.

Once you have this setup you can select the various pieces of master data to create files for and use the functions to Export to XML or Export to Excel. Again I am using the Excel option. If you select multiple tables you get a separate tab in the resultant file for each table. If any of the tables already have data on them this is included in the exported file, useful for that data that was setup in the config XML file earlier where you want the customer to review the defaults and amend them.

This is given to the users and once filled in you can import the data to NAV.

The final part of the Toolkit allows for Templates to be built. These again refer to specific tables.
You can define multiple Templates for each table if required. In the Template you can define which fields are Mandatory and what default values they should have. The Templates can be used as part of the Data Migration to complete data the user may not know about, but they can also be used after the migration.

As an example we have a number of templates for the customer Table. Each defines slightly different posting groups and payment terms. From the Template you can Create an Instance. This will create a new record on the Customer table and pre-fill all the information form the template. alternatively from the customer you can run a function Apply Template. This give yo a list of templates for that table and when you select one it updates the current record with the defaults from the Template.

With a little bit of work the Template's could be made more useful still, especially in environments where new master data, e.g. Customers and Items, are regularly setup as it would reduce the amount of keying of default data while still allowing flexibility.