Automated preparation of Test Companies in Dynamics NAV

Posted March 1st, 2015 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

We all know the situation: A customer needs a copy of the live environment to run a number of tests, or it could be that a new test/development environment must be made periodically. Often it is necessary to update the test company with the latest data. Therefore, it is necessary to create a copy of the live database and restore it with a different name. Having done that, it must be prepared so that data from the test company cannot be confused with data from the live company.

The preparation can involve quite a number of things:

Change Company

The company must be renamed in Companies.

Rename the Company Name

The company name must be renamed in Company Information so that all sender addresses on external documents clearly reflects that is origins from a test company.

Change the System Indicator

I also change the System Indicator to tell the user that they are in a test environment, both by changing the text shown in the corner to show the word TEST clearly and to show the date the test company was based on, this will help the users later running tests in the system.

Giving this signature in the right corner of each page in the Windows client:

Redirect printers

It is also necessary to redirect the printers so picking lists from the test system are not suddenly printed in the warehouse or production orders in the production department. Even worse would be that external documents like purchase orders, sales quotes or orders accidently are printed and sent by mistake.

Redirect Freight integration

If integration with the shipping company is automated, then it is advisable to make sure that it is disabled in the test company. Otherwise, you will face some very angry truck drivers.

Stop Job Queues

There is no need to keep all jobs queue entries in the Job Queues running, like automatic posing of invoices or printing of invoices. So stop all the jobs that will give problems in a test environment.

Stop EDI flow

If the company communicates with external partners with EDI or the like, it is necessary to redirect all documents to a test folder.

Prevent Electronic Invoices

Many companies send invoices electronically, the paths that are uses for sending and receiving invoices and credit memos must be changed so nothing is sent or received by mistake.

Stop Electronic Payments

Payments sent and received are usually initiated manually, so the chances for mistakes are few. However, it is best practice to change all paths so the payments are not imported or exported to the bank by mistake.

Redirect Captured Documents

Capturing purchase invoices are commonly used in companies, and the paths used for the captured documents must be changed.

Stop or redirect Inter-Company Flow

If the company a part of a larger organization with an automated IC-flow, it is very often desirable to make sure that there are two IC-flows, one for the live transactions and one for the test environment, otherwise it can be difficult to test the Inter-Company functionality. In any case, the functionality must be redirected so the flow either stops or runs on the test environment instead.

Stop or redirect Master Data Replication

If the company a part of a larger organization with an automated Master Data Replication flow, it is just like the IC Flow desirable to make sure that there are two replication flows, one for the live replications and one for the test environment. The functionality must be redirected so the flow either stops or runs on the test environment instead.

Automate the tasks

These are only some of the changes that needs to be done. Others could be integration to production facilities, machines, BI databases or CRM systems. The list can be endless.

Now the customers will not be happy if they have to call a consultant to perform all these tasks, not to mention the actual backup/restore functionality every time they need a new test environment and even worse, to remember all the different tasks that needs to be done after the restore. Secondly, the number of databases that must be copied in a large organization can make the task so complex that errors will eventually happen.

Therefore, I have automated the creation of the test environment for my customers. It comes in two or three parts depending on which Dynamics NAV version the customer run:

  1. The backup of the live database and restore to the test environment

    This is not covered here in detail, but it can be performed in many ways:

  • Manually from SSMS
  • Automatically with a SQL job
  • Automatically with PowerShell (SQL Server 2012 & SQL Server 2014)

     

  1. The Company must be renamed in the test database. If the customer run Dynamics NAV 2009 or 2013, it must be done manually, but in Dynamics NAV 2013 R2 or 2015 there is a PowerShell CmdLet for exactly that

     

  2. All the rest must be performed inside the company in NAV. Build a codeunit to handle all the changes. If the customer run Dynamics NAV 2009 or 2013 it must be run manually, but in Dynamics NAV 2013 R2 or 2015 there is also a PowerShell CmdLet for to execute the codeunit

If the test company is in the same database as the live company, a simple PowerShell script can be run to insure that everything is done properly. This will work in Dynamics NAV 2013 R2 and 2015.

This is the script for real Men; it removes the company NO QUESTIONS ASKED. A gentler version could be without the –Force parameter on the Remove-NavCompany command. This will politely ask before removing the company. Of course, this could be done manually, but if we face 30 or 50 companies then the script is THE solution.

The CodeUnit

Creating the codeunit, usually involves a lot of “hard code”, here is an example:


 

 

Http://b-a.dk

Opening for charges purchased directly to the production

Posted February 26th, 2015 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

“Why can’t I post charges directly to a production order without making special operations and create the vendor as a subcontractor on a work center” is a question I often meet.

The answer: You can do just that, it just takes a little preparation and design access to the tables.

On a purchase order, it is possible to see which production order number and production order line has generated the purchase order. This is normal way when it is used for subcontracting. The information is set automatically when the purchase order has been created from the subcontractor worksheet.

However, sometimes charges for, e.g., freight are billed on a separate invoice from a vendor different from the actual subcontractor. In that case, it is convenient to be able to post a purchase order that is charged directly to the production because Item Charges cannot be used with purchase orders from subcontractors.

The three fields on the purchase order line defining the subcontractor are the Production Order No., Production Order Line No. and Work Center No.

By default, the three fields are not editable in the page.

To enable it, perform the following simple change:

  1. Go to the development environment
  2. Open the database, click the Table menu item
  3. Navigate to table number 39
  4. Locate the three fields numbered 5401, 99000752, and 99000754
  5. On each field go to the menu item View > Properties
  6. Find the property Editable and change it to “Yes”
  7. Save and exit.
  • Other fields are also available on the purchase order line and can be included, but the three mentioned are the only ones necessary.

     

     

Now the three fields are editable on the production order. Then I need to create one new Work Center for charges. The vendor chosen for the work center can be any random vendor the field just must be filled. An existing work center can also be used as long as it is a subcontractor.

Now I am ready to post charges directly to production orders.

The item number used on the purchase order must be the produced item from the production order.

So I create a purchase order with the vendor number from my freight vendor, the item number from the produced item and the new work center.

Amazing how versatile London Postmaster is.

Then I fill these three fields:

Post the purchase order and Navigate tells the story:

No Item Ledger Entries only Value entries. No items will be received. The cost will be treated as a service charged directly to the production Work in Progress (WIP).

From the production order, it looks like this:

 

In Finance it looks like this after the Inventory batch has run:

Here the cost ends up on account: 2140, which is the WIP account.

Keine Hexerei, Nur behendigkeit


 

 

Http://b-a.dk

Checking your Dynamics NAV License and object versions

Posted February 23rd, 2015 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

When I first started in Columbus NSC, I faced the usual license struggle, sent out on my first assignment:

  • What was included in the license?
  • Which extra object number series was included in the license?
  • Who is in charge of assigning object numbers?
  • How do we control the builds?

So I called my highly skilled colleague Martin Jordt Hansen who has been around in Columbus NSC for many years. His answer was to send me a small tool: The Check License objects.

Three small objects that made a big difference:

Running the main page: Check License will calculate all the information needed temporarily.

Why not just make a page showing the 2000000043 License Permissions table, you might ask?

Because the Check License objects shows a lot more information:

All the objects included in the license are shown here, but more important it is also shown which objects are already used. Here we can see that the next available object for tables are number 50012.

The real goodies are in the bottom of the page:

Here it will show all the latest version numbers in the database. This is one of the good ones, so we can read this from it:

  • It is a Dynamics NAV 2013
  • It is based on a W1 version with a Build 36005 (Rollup 10)
  • It is a Localized Danish database
  • It has IEM and BIS from To-Increase
  • It has Payment Management
  • Our own Build Number is NSC1.30
  • And a lot of other add-ons

Martin has graciously decided to share these objects and they are therefore available on Mibuso.com/downloads.

So go get it!


 

 

Http://b-a.dk

Posting freight lines automatically from the WMS Shipment in Dynamics NAV

Posted February 17th, 2015 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

Some of my customers using the Dynamics NAV Warehouse Management (WMS) module face the same issues with this WMS setup:

  1. The Sales Orders are created by the sales people and a freight line is applied, either as a G/L line or as a resource line.
  2. When the sales order is released for delivery, shipment lines are made based on the sales order, either by the sales person, by the warehouse manager or by the warehouse employee himself.
  3. Depending on the setup, a pick is created and posted to “transport” the item from the picking zone to the shipment zone.
  4. Lastly, the warehouse shipment is posted.
  5. At nighttime, all sales orders with posted shipments are automatically invoiced and invoices are printed/sent by email.

Using the WMS module, it means that the warehouse employees do not necessarily see the sales order because they have all necessary information on the Warehouse Shipment. However, only item lines are transferred to the warehouse shipment so all other types have to be handled manually on the sales order. Making an automation like this, will leave all G/L lines and resource lines behind on the sales order to be posted separately later. This also means that the customers will receive separate invoices: one for the items and one for the freight.

There is a fairly simple solution to the problem. It does demand a license to change codeunits though.

In the Warehouse setup table (5769), two fields are created:

  • Post G/L lines with Shipments
  • Post Resource lines with shipments.

Two lines if both G/L Lines and Resource lines should be posted otherwise one field is enough.

And the equivalent Warehouse Mgt. Setup page (5775):

In the Whse.-Post Shipment Codeunit (5763):

Find the function HandleSalesLine, make a local variable…

…and insert the Following lines just before the end of the function:

~~~~

Now if the fields are activated in the setup table, freight lines made as G/L lines or resource lines on the sales order will always be included in the posting of the shipment, and thereby be included on the invoice.

To keep the solution as close to standard as possible, the fields could be created in a customer specific setup table and page. This way the only change will be in the codeunit.


 

 

Http://b-a.dk

Cancelling an active production order in Dynamics NAV

Posted February 14th, 2015 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

A production order can be created in four different statuses. In three of them, it is not a problem if a production order was created by mistake. The “Simulated”, “Planned” and “Firm Planned” production orders can be edited or deleted endlessly. However, the “Released” production orders can be edited and deleted freely until the first posting has been made. Then it is no longer possible to delete.

This is a problem if the components of the production order or the capacity of the routing of the production order has been set up with forward flushing. This will post the components or the time consumption when the production order is released or a released production order is refreshed. This also means that a production order that is accidentally released cannot be returned back to the previous status, and certainly not deleted.

Why?

Because entries exist on the production order. This means that one of the following tasks have been performed:

  1. Consumption has been transferred from inventory to Work in Progress (WIP). The consumption will create an item ledger entry and a value entry.
  2. Consumed time has been registered to the production order. This will create a capacity ledger entry and a value entry.
  3. Output has been registered to the production order. The output will create an item ledger entry and a value entry.

So in order to delete the production order, it is necessary to:

  1. Reverse all consumption transactions. (Watch out for the cost price of the returned item)
  2. Reverse all capacity ledger entries with the time consumptions
  3. Reverse all output transactions
  4. Change the quantity of the finished item to 1 and post the output
  5. Reverse the output.

This can be done in the production journal.

An example:

Production order 101041 has been created, and postings have been made by mistake.

There are all types of entries posted to the production order:

Item ledger entries, both consumption and output:

Capacity ledger entries:

So all transactions are reversed:

A special thing to remember is that the output of the last operation must be applied to the original output item ledger entry to ensure correct reversal. The consumption can be applied from the correct item ledger entry for the same reason.

This will reverse all costs on the production order:

Now all that is left is to change the quantity to 1 and post the output.

Is this really necessary? Yes, otherwise the Change Status on the production order will fail with this error:

So there is no way around.

Now the output has been posted with a value of zero:

This posting must be reversed in the item journal. Remember to apply to the output entry.

Now the production order can be changed to “Finished”.

Should this be an automated function? Possibly - if the components have been set up with forward flushing, then it could be an idea. Otherwise, it is okay that the reversal is a little bit difficult, just to encourage users to get it right the first time.


 

 

Http://b-a.dk

Making the Budget page or the Forecast page remember its settings

Posted February 10th, 2015 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

The Production Forecast page consists of two parts: the filters and the matrix.

Using the Budget page or the Production Forecast page, it soon becomes very apparent that the filters are discarded whenever the page is closed, causing swearing and agony when the filters must be reset on reentry. This is especially irritating if the page is closed accidentally.

A small design change can fix this issue and save the user lots of grief:

  1. Go to the development environment
  2. Open the database, click the Page menu item
  3. Navigate to page number 99000919
  4. Go to the bottom line
  5. Move one line further down. Now an * appears in the left column
  6. Go to the menu item View/Properties
  7. Find the property SaveValues and change it to “Yes”
  8. Save and exit.

 

  • This applies to all other pages built like the Production Forecast page. All budget pages suffer from the same problem. The described fix can be applied to all the different budget pages.

 


 

 

Http://b-a.dk

Manufacturing with Microsoft Dynamics NAV on Amazon

Posted February 8th, 2015 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

I am happy to announce that my book:

Manufacturing with Microsoft Dynamics NAV

Now is sold through Amazon UK with free delivery in all UK.

You can find the book here.

It is still available for sale to the rest of the world here.

Strange fonts printing from a Terminal Server

Posted February 8th, 2015 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

At a client, I faced a strange situation: In some situations, printing from the clients looked different from printing from Terminal server running on Windows Server 2008 R2.

What happened was:

When printing from a Windows client everything looked ok:

When showing a preview on the Terminal Server it also looked ok:

Even saving it as a pdf worked fine:

However, when printing from the Terminal Server it looked terrible:

After much Googling, I found out that this has something to do with the resolution setup to the user printing on the server. The user must be set up with a resolution compatible with a 4:3 format. The Dynamics Community has described the problem here: https://community.dynamics.com/nav/b/dmsblog/archive/2012/10/02/nav-2013-report-issues.aspx

This also means that if it is a NAS and it is running as the user DOMAIN\NASUSER it is necessary to log on to the Terminal Server as this user and change the resolution to 1280×1024 or 1024×768 or any other 4:3 compatible resolution.

Not pretty but it will work.

But how about any other users logged on to the Terminal Server? They will also have to log on with a resolution compatible with 4:3.

This is not an acceptable solution.

When publishing this solution internally in Columbus NSC, my colleague Anders Larsen pointed out, that there is a hotfix available at Microsoft:

http://support.microsoft.com/kb/2768741/en-us

After installing the Hotfix AND RESTARTING THE Terminal server, printing will look like this regardless of resolution for all users on the terminal server.

Happy Days J

 

Bulk Deletion of Notifications in Dynamics NAV

Posted May 11th, 2014 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

Have you ever experienced this:

For some reason, the NAS has not worked properly and the Notification page on the RoleCenter is flooded with messages. Next thing is to fix the problem with the NAS and delete the notifications.

No problem, just select all and then delete them, right?

NOT

You have to select them individually and Remove the notification:

Ok then, let’s run the table and delete the records from there (everything is easier with a developers license :))

Well that is possible, but that is hardly a user interface, we want to pass on to our users.

The notifications are not stored in a normal Dynamics NAV table, but in the Record Link table. Deleting all records in the Records Link table is not an option, because that would remove ALL real record links and intentional notes as well.

Running the table we can see the record link table.

The Type field will show if the record link points to a link or a note. Within the notes, we can distinguish between user notes and system notes by the User Id. So much more reason for running the NAS using a dedicated domain User ID.

The bottom line is a note on a customer, whereas the rest are the notifications from the NAS user.

Now we have enough information to create a report to do the deletion:

Using the Job Queue to find all NAS’es and combine it with the Record Link table,

I start by changing a report property:

Processing Only, There is no reason to involve the Visual Studio layout, unless you want documentation for the deletion.

Then a property on the Job Queue DataItem:

DataItemTableView, to remove the sorting option on the request page

RequestFilterFields, to enable filtering on the job queue code.

Then a property on the Record Link DataItem:

DataItemTableView, to remove the sorting option on the request page and to filter Type to Notes.

DataItemLink, to filter users with the User Id from the Job Queue.

ReqFilterFields, To enable filtering on Company Name and Creation Date.

Now everything is ready but I need a variable:

…and a text constant:

And a couple lines of code:

Save and exit

Now add the report to the IT Manager RoleCenter (9018):

Now run the report:

Take everything, please

And YES I am sure.

Press F5 and the RoleCenter looks like this:

Niiiice J

It you are lazy, you can download the objects for Dynamics NAV 2013 here.

The objects are shared as is, and no guarantees are given.

Finally a real author

Posted May 8th, 2014 by Peik Bech-Andersen
Categories: Microsoft Dynamics NAV 2013

Now I am officially a writer - I got my book.

Actually I got 1.517 copies.

Help me clear out the basement again :)

Buy one or download a free sample at http://b-a.dk