Archive for March, 2010

SmartList Builder

There are many ways to use SmartList Builder and many reasons why you should check it out.  SmartList Builder allows you to create your own SmartLists and to enhance existing SmartList views.

You can easily create a new SmartList to compliment the ones that are provided with GP.  And they are very easy to build.

Here’s an example of a simple SmartList I built to look at the MRP results from Manufacturing:

Here’s a simple example of a SmartList with two tables.

Dynamics GP 2010

We held our Dynamics GP 2010 webinar yesterday.  The participation level was about what we expected.

We covered a few different areas:

Demo new GP functionality:

  • Word Forms
  • Home Page Metrics Creation & Customization
  • Report Drill-back to GP from SSRS and Excel
  • “The new way to do business” demo
  • “Management Reporter” demo

Pricing and Licensing:

  • Changes in DCO
  • Opportunities for Business Portal Users


The materials from the event are available here:

Check it out when you get a chance.

GP 2010 – Adding Extender Data to the GP Home Page

Dynamics GP Extender has always been a handy tool for collecting additional data in GP.  The reporting options are improving.

With the release of GP 2010, it will be easy for you to add data collected from an Extender Form, to the GP Home Page.

The process is really pretty easy:

  • Create the Extender Form
  • Create an Extender View
  • Use the Report Builder in SQL Reporting Services to create a chart
  • Customize the Home Page to include the new chart

O.K.  There’s a lot there.  But look at this video that steps you through a simple example.

This is another compelling reason to upgrade to Dynamics GP 2010.

Is There a GP BOM Import Tool?

When doing a Great Plains manufacturing implementation, I’m often asked to import manufacturing bills of material from another system. Clients are sometimes very surprised to hear that GP does not have a built in BOM import tool. We do BOM imports using SQL inserts from Excel spreadsheets for clients during an implementation so some clients think that they should have a tool to import BOMs too.

I think there is a need for such a tool and we could develop one but there are some big issues for both the implementation team and the client. Let me try to explain.

There are 3 GP tables that hold BOM related data.

  • BOM Header table
  • BOM Detail table
  • Reference designator table

The data in each of these tables has to be formatted and organized correctly. If not formatted and organized correctly, the import may not work or bad data may be imported. This leaves quite the liability for the implementation team and if users are not getting the results they expect, they do not have a useful tool.

The BOM Header table is fairly straight forward as the import would have to list only the finished good or subassemly items. Just think of the upper left quadrant of the GP Bill of Material Entry window.

The BOM Detail table is much more complex. Think of the right half of the Bill of Material Entry window. In order to get all the data imported correctly the source document would have to list each finished good and it’s components (including subassemblies) and each subassembly and it’s components. Each of these rows would have to include the correct position number and relationship to the appropriate subassembly and final assembly. This takes a long time to do. Try taking an indented BOM from another system, export it to Excel, and then put all the rows in the proper sequence and keep all the columns straight. It takes time to do this.

The formatting issue is worse for the reference designators. Most systems will export the reference desigators in one long string. GP requires that the string be broken into 21 character segments (including spaces and punctuation). I have had to format these strings into as many as 10, 21 character segments (a column for each one in Excel) and it takes a lot of time.

Users also have to know what the field values mean in these tables. For example what is a BOM Type = 1? What is a BOM Category of 2? SQL requires the use of numerics to identify these rather than ‘Phantom or Regular as you see on the front end. Did you also know that these two fields are flipped in the SQL tables? What the GP Bill of Material Entry window calls BOM Category is BOM Type in SQL.

Users also have to know the field formats that SQL uses and the field lengths. If you exceed the field length in your data source, you will not get the desired results in the front end.

In order to have an effective tool, you should validate the import with the Great Plains business logic. Do all the items exist in the Item Master, are the items setup in Item Engineering Data, are all the sites and work centers setup, does the BOM already exist, etc., etc., etc. The fact that Microsoft has not exposed a BOM API via eConnect would require complete reverse engineering or specialist Dexterity knowledge (to invoke manufacturing calls in the correct order). Business logic would also need to include handling of BOM revisions and revision levels.

So what am I trying to say here? It is my belief that using SQL imports for BOMs does not save users any time, really. By the time you get done with all the formatting, import verification, error correction, user acceptance and sign-off, you could have created these BOMs using the GP Bill of Material Entry functionality. Many BOMs are similar to each other in many organizations and the BOM Copy functionality saves users time and reduces errors.

It is fairly easy to create a SQL BOM import to get the data into GP manufacturing once you understand the table structure and have a basic understanding of SQL statements. The real issue is getting the data in the correct format for the import and then validating the data once it is in.

With the many BOM export formats being used today (single level, multilevel, CSV, XML, etc.) it is time consuming and costly to write interfaces for each. Custom adapters or complex mapping tools may be needed to handle these various formats. So it becomes a costly issue for the client or the partner.

With that said, there are some pretty nice tools available to import items and BOMs from CAD software such as Solid Works and Autocad. If clients are using these tools, we generally recommend they buy one of the integration tools that are available to export items and BOMS from the CAD software and import it to the GP item master and BOM tables. These integration tools act as a middleman and take the data from one system and format for GP.

I know that creating BOMs is no fun, but Great Plains functionality does a pretty good job entering BOMs and using business logic to validate the entry of BOM data. There just really are no shortcuts if you want accurate BOMs and manufacturing functionality. Manufacturing is complex because of all the inter-relationships involved. Everything (BOMs, routers, work centers, MO’s, picklists et. al.) works together and if business logic is not followed, it most likely will not and you will end up with a bad result. So take your time and do it right the first time.

Manufacturing Insights

GP 2010 – Drill-through to GP Screens from Reports

Dynamics GP 2010 includes 23 drill-through connections to GP screens that can be imbedded in reports.  This is a further extension of GP data to other reporting tools that you can use in your organization.

The drill-through connections key on specific fields (e.g. Customer Number) that open specific GP screens (e.g. Customer Maintenance).

Also delivered with GP 2010 will be a Drilldown Builder that will allow you to create your own drilldown functionality.  This will be licensed as part of SmartList Builder, which already includes Excel Report Builder.

I created this short video that demonstrates how to add a drill-through to an existing report.

This is another compelling reason to upgrade to Dynamics GP 2010.

For more information about GP 2010, check out this site and attend our Sneak Peek event.

Why Are My Cycle Count Dates Wrong?

Have you ever found yourself in a situation where you spent hours and hours setting up your cycle counts, religiously recording your counts and then find that all the ‘Next Count Dates’ are wrong? Or maybe you ran a SmartList and saw that all your ‘A’ parts have the same ‘Next Count Date’?

Most users of Great Plains inventory only setup cycle counts once a year, or if you are really good at it, once every few years. So, when the ‘Next Count Dates’ are wrong you find yourself scratching your head for two reasons.

The first is ‘How did this happen’? The second would then be ‘How do I fix it’? The answer to the first is not always easy to figure out, but here are some reasons that the ‘Next Count Dates’ get out of whack so to speak.

  • Full Physical Inventory – When an item has been setup to be cycle counted, most users will spread the ‘Next Count Date’ for the item(s) evenly over the count cycle. For example, say you have 90 items that you want to count every 90 days. GP allows users to count one of the items every day for 90 days so that after 90 days all of the items have been counted. When the item is counted, the ‘Next Count Date’ is calculated to be 90 day from the date it was last counted. So when a full physical inventory count is done, all items now have the same last counted date so all 90 day items will have the same ‘Next Count Date’. 90 days from the full physical inventory count date.
  • Items were counted ‘Out of Cycle’. For instance, if you have a site that has items spread out over time to be counted and your company for some reason decides to count everything at that site on the same date, the ‘Next Count Dates’ will all be the same depending on the cycle count assignment interval assigned to it.

These are the two most common situations I have found. The fortunate part is the ‘Next Count Dates’ are easy to reset so that items are again spread out over time. Open the Stock Cycle Count Assignment window in Inventory Cards.

I find that the easiest way is to assign an ABC code to every item if you haven’t already done so. To assign ABC codes use the Item ABC Analysis wizard in Inventory Routines.

When in the Stock Count Cycle Assignment window, users are asked to enter the Stock Count Interval Days (How often you want to count the items in this group). Enter a Date and Time to Start Counting. This date will be the first date GP will use to assign the items and spread the ‘Next Count Date’ for each item in the group. If you set the interval to 90 days and have 90 parts in the group, the first item will have a ‘Next Count Date’ of 4/12/2017 (as in the example) and the next item ‘Next Count Date’ will be 4/13/2017 and so on until all items are assigned a new ‘Next Count Date’. Keep in mind that inventory calendar down days are skipped, in other words an item will not have a ‘Next Count Date’ that is an inventory calendar down day.

Select the Stock Count Range (range of items to assign to this interval and start date) and insert into the box. In this example, I have chosen to count all my A items once every 90 days starting on 4/12/2017. If you click on the Details button you will see a list of the items selected and their current ‘Next Count Date’. Click Process and you have just reset all your ‘Next Count Dates’.

Not that hard was it. Happy counting.

GP 2010 – User Defined KPI’s and Graphs on the Home Page Using SQL Server Reporting Services

Dynamics GP 2010 includes the functionality to allow users to design their own Home Page by creating their own reports in SQL Server Reporting Services, and displaying those reports to the GP Home Page.

To try this out you can create a simple report using Report Builder in SQL Server Reporting Services.  Once the report is created it’s easy to add to the GP 2010 Home Page.

I created this short video that demonstrates the whole process.

This is another compelling reason to upgrade to Dynamics GP 2010.

For more information about GP 2010, check out this site and attend our Sneak Peek event.

Using Routings to Calculate Mfg Lead Times

In a previous post, we discussed how to enter manufacturing lead times in Item Resource Planning manually. Great Plains Manufacturing will calculate the manufacturing lead times for you from the routings you create to manufacture your items. GP Mfg provides a utility that will calculate the manufacturing lead times based on entries you make in your routings.

As we discussed earlier, there are two types of manufacturing lead times, Mfg Fixed Lead Time and Mfg Variable Lead Time. The entries made in Routing Sequence Entry dictate how these lead times are calculated. The Mfg Fixed Lead Time in Item Resource Planning is determined by the entries made in the Setup Time and the Queue Time on the right hand side of the Routing Sequence Entry window. The Mfg Variable Lead Time in Item Resource Planning is determined by the entry made in the Move Time and the Cycle Time in the Routing Sequence Entry window (see the example below).

The entries made in Routing Sequence Entry are made in hours or fractions of hours. Don’t get confused with the entries made in Item Resource Planning that are made in hours. When you run the Calculate Mfg Lead Times utility, it will convert the hours entered in Routing Sequence Entry to days and display them in Item Resource Planning.

Repeat entering times for each sequence in your routing and the Calculate Mfg Lead Times utility will include entries made in all of your sequences.

Once you have made your entries in Routing Sequence Entry, you will need to run the Calculate Manufacturing Lead Times utility to convert the hours to days and display them in Item Resource Planning. You can run the utility for all items in your item master or just for one item. Click Calculate All for run for all items. To run for one item, enter the item number and click Calculate One.

After running the utility, go to Item Resource Planning. You will see that the Default Values have not been updated, but the sites assigned to the item now reflect the new values calculated by the utility. Also, be aware that running the utility will overwrite and values manually entered in the Item Resource Planning item-site combinations.

You will be able to see all the lead times for an item by doing an inquiry in manufacturing under routings as in the example below.
I would suggest trying this for one item at a time until you get familiar with how Great Plains calculates lead times and you get the results you are expecting.

GP 2010 Word Forms

The new Word Forms functionality in Dynamics GP 2010 is going to really be popular.  It allows you to modify the format of sales orders, quotes, invoices, and purchase orders in Microsoft Word.

The two things that I really like about it are:

  • You can insert complicated graphics into your forms and they will look good.  That is just not possible with Report Writer.
  • You can have as many different form templates as you wish so that you can assign a specific template to a specific customer or groups of customers.

I created a short video to demonstrate some of this new functionality.

Here’s an image of an invoice:


For more information on GP 2010 attend our Sneak Peek event on March 25.

Unsure About How Dates Are Calculated for MRP Planned Orders?

I find that many Great Plains manufacturing users do not use MRP effectively, or at all, because the release and due dates never seem to make sense. I realize that managing dates and then trusting the release and due dates from an MRP run can be a daunting task. That should not be a good excuse for not using a very powerful and effective tool.

Many users just use the default dates when creating documents that affect inventory and the manufacturing process because it is “too much work” to enter and manage the dates in Purchase Order Processing, Sales Order Processing and Manufacturing. I would argue that it costs users more in time and costs to not manage the dates up front and throughout the purchasing, sales and manufacturing.

How many times have you, as a user, had to track down a shortage, reallocate items to more urgent orders and correct errors? It is my belief as a former Controller and a current GP implementation consultant, that it cost you and your organization much more on the back end to deal with incorrect release and due dates than it does to enter and maintain meaningful dates in Dynamics GP manufacturing. In fact, I feel so strongly about it, that I believe the savings would more than pay for an additional head or heads that may be required in your organization to maintain accurate dates.

When dates are used and managed effectively, issues that affect manufacturing and eventually shipping are identified quickly during an MRP run and MRP will even make suggestions on how to deal with the issues. MRP will include suggestions on moving orders in or out and cancelling orders that are oversupplied. Why should you study inventory levels via GP inquiries, reports and SmartLists when MRP will do it for you? Seems like a great time saver to me.

So where does MRP get the data to suggest release and due dates?

The due date is the date an order is due in house to meet the demand that created it. Due dates come from many sources including MO’s, MRP suggested MO’s, Sales Documents, Sales Forecasts, etc. The release dates suggested by MRP start with the due date of the document or MRP suggestion the item fulfills and works backwards, based on lead times and calendar down days.

MRP-Planned Manufacturing Orders

Let’s look at MRP-Planned manufacturing orders first. MRP looks at the lead times in the Item Resource Planning windows. The lead times can be entered here manually or generated from routers using the Calculate Mfg Lead Times utility. MRP will look to the item-site combination in Item Resource Planning. Since MRP suggests manufacturing orders for make item only, enter the lead times in days or fractions of days in either the Mfg Fixed Lead Time field or the Mfg Variable Lead Time field or both. MRP uses these fields differently as you might suspect.

  • Mfg Fixed Lead Time – does just that, adds a fixed number of days to the MO when calculating the release date. The formula is Due Date – Mfg Fixed Lead Time. For example, if your forecast shows a suggested MO with a due date of 8/1/2010, the release date would be 5 days earlier or 7/27/2010 as long as 7/27/2010 is not a down day on your work center calendar.
  • Mfg Variable Lead Time – is variable based on the quantity suggested. The formula is Mfg Variable Lead Time * quantity, rounded up to the next whole day. For example, if I have a Mfg Variable Lead Time = .01 and a quantity of 500, my lead time is 5 days.
  • Total Manufacturing Lead Time for a make item is Mfg Fixed Lead Time + Mfg Variable Lead Time, rounded up to the next whole day. Mfg Fixed Lead Time & Mfg Variable Lead Time in Item Resource Planning.

MRP-Planned Purchase Orders
If the item does not have a default site set in Item Quantities Maintenance, MRP will use the Purchasing Lead Time from the Item-Site combination in Item Resource Planning.

If the item has a default site set in Item Quantities Maintenance, MRP has two choices to select from.

  • No Primary vendor selected for the default site – Purchasing Lead Time is from the specified default site in Item Resource Planning Maintenance window.
  • Primary vendor has been selected for the default site – Purchasing Lead Time is selected from the item and vendor in the Item Vendors Maintenance window (Planning Lead Time).

I hope that this takes some of the mystery out of the MRP release dates and due dates.

Subscription Options:
Subscribe via RSS
Articles Categories