Author: admin

As we roll-out new builds for GP2018, we are adding new Help functionality to all of our products across all currently supported GP versions  (at this time we are supporting GP2013 through GP2018).   There were four goals for the new Help.  The new Help should…

• Be easy to access
• Make it easy to locate relevant information
• Be available to both desktop and web clients
• Provide the most current information about the product

The new Help (shown above) is available via a black “Help Button” on every WilloWare window.  It provides access to:

• The PDF Manual
• Local Help.  This is a context sensitive CHM Help File that will open to a section of the Help File containing information about the current window.
• Web Manual.  This is a web-based product manual.  Since the PDF Manual and the Local Help file can only be updated by installing a new build of the software, they may not contain the most current information.  The Web Manual is updated as needed to provide the most current and complete documentation.
• Web About This Window.  Web-based context-sensitive Help that retrieves a section from the web-manual that pertains to the current window.

 

One of our core objectives has always been to listen to you.  Sometimes we receive your suggestions through an email or a support case, or on a phone call. We also monitor blogs and Microsoft’s Connect site.

We keep our ears open for things you would like our products to do, or do differently, and also for general Dynamics GP Wish List type items.

To a large extent, GP PowerPack and MFG PowerPack are the result of collecting your requests and turning them into software.

Our database of ideas has always been internal, which makes it difficult to assess how important any given request is.  We just moved it to an external site where you can directly contribute your own Wish List items, read requests from other users, and vote on the ideas.  Most of the “feature requests” we were tracking internally are now visible on the site.

Please join the WilloWare Community site.  Feel free to share the link and invite others to join.  We will use your suggestions to improve our products, and add new features.

If you have the Suite License for GP PowerPack or MFG PowerPack, you have access to everything that is in the Suite, as well as any new modules we add to the Suite.  We have always sought to regularly add new features to both of these Suites, and now the WilloWare Community has the opportunity to directly influence what gets added next!

 

We just released (3-OCT-2017) a new build of MOGenerator’s MO Receipt Integration (MORI) that now has support for WIP Inventory.  This new functionality provides the ability to record usage of raw materials as it happens.  There are two components of the new functionality: (1) adjusting Picklists for WIP transfers, and (2) forcing consumption of components from the WIP Site.

WIP Transfers

The process is tailored for an external application integrating into GP, such as a Warehouse Management package, or Shop Floor Control software.  We designed this approach in consultation with Without Wire Inventory Sciences.

After creating a Manufacturing Order in GP, the external software retrieves the Picklist from GP and uses it to create one or more Inventory Transfers to move inventory from stock to WIP.

The Inventory Transfer is a “picking transaction” for a Manufacturing Order.  The external software tells MORI about the transfer by adding a record to the MOIVTrx table.

When the MORI processor runs, the first step is now to check the MOIVTrx table for new transfers.  As shown above, there are two transfers for MO0017.

It processes transfers IN to the WIP Site first, then transfers OUT of WIP.  The items transferred are matched to lines on the MO’s Picklist.

When inventory is transferred into WIP, the original Picklist line is decreased by the transfer quantity.  A new Picklist line is added for the same item where the Issue From Site is the WIP Site.  An example is shown above.

In the example, 0.75 FEET of WIRE100 have been transferred into WIP from WAREHOUSE. The original Picklist line with Issue From of WAREHOUSE, was decreased from 1.00 to 0.25, and a new Picklist line was added for 0.75 with an Issue From of WIP.

The original line will draw down until it reaches zero, but the WIP line will increase to show the entire quantity transferred into WIP.  Likewise, if a transfer is posted OUT of WIP back into WAREHOUSE, the WIP line will be decreased until it reaches zero, and the original line will be increased until it reaches the original quantity.

There are several effects to note about this process.

First, inventory used by manufacturing can be put into WIP at the moment it is picked, and the MO does not need to be Released.  This ensures the GP inventory quantities are current and accurate.

Second, by automatically adjusting the Picklist to reflect the transfer, Materials Requirements Planning (MRP) can detect the change in demand (i.e. decreasing need for WIRE100), and still see remaining demand.

Third, the Picklist becomes a live record providing a quick reference to inventory issued to an MO, and the inventory still required.

Lastly, by moving it into a WIP Site, rather than adjusting it out and tracking the dollar value, it is possible to perform a physical inventory on WIP.

WIP Consumption

Setup in MOGenerator creates a relationship between a main inventory site and a WIP Site.  This is shown below:

When an MO Receipt is processed, MORI creates a “split-MO” off the parent, which it runs through as a QuickMO.  When MORI builds the Picklist it forces the Issue From Site to be the WIP Site.

As shown above, if the original MO has a Draw Inventory From of WAREHOUSE, MORI will force the components to be consumed out of WIP.

The MOPick table can still be used to direct MORI to consume specific quantities of specific materials.

You may have looked into a GP performance issue and dug around a bit and discovered that you have high fragmentation in a large number of table indexes.  After rebuilding indexes, a large number of indexes still have high fragmentation, and they all have an Index Type of HEAP.  What is this and why does GP have messy HEAP tables?

First, let’s start by looking at your index fragmentation, and talking about indexes.

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind 
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Run the query above against your company database.  Note the “avg_fragmentation_in_percent > 30” line.  This restricts the query to only show results if the fragmentation is above 30%.

ItemMasterIndexes

The link above is to an XLSX of IV00101 (the Item Master) which I’ll reference below.  There are several tabs along the bottom of the workbook:

 

 

IV00101 shows the complete table, with the data as it could be arranged physically on the hard-drive.  Note that it is not in order by Item Number.  When the Primary Key is “non-clustered”, data is simply added to the table using the next empty row at the bottom of the SQL table.  Think of “SQL Table” as a single Excel worksheet.

For IV00101 the Primary Key (Primary Index) is ITEMNMBR (Item Number).  An Index on a table is like an additional tab in an excel workbook.  For the Primary Key of ITEMNMBR, SQL creates a page of data that contains only ITEMNMBR, and the data on that page is ordered by ITEMNMBR.  Click on the PrimaryKey tab in the workbook.

A Non-Clustered index is a HEAP.  The data is physically recorded on the disk in the order it was added to the table, in other words, it’s sort of a “heap” of data with no organization.

When a new record is added to IV00101, it is added to the bottom of the IV00101 page.  SQL then inserts it into the correct position in the ordered list of the PrimaryKey tab.

The Key2, Key3 and Key4 tabs are the actual Alternate Keys (Indexes) on IV00101.  Key2 is ITEMDESC (Item Description) and ITEMNMBR (Item Number).  The data on that page is ordered by ITEMDESC then ITEMNMBR.

Likewise for Key3, the data is ordered by ITMCLSCD (Item Class Code) and ITEMNMBR (Item Number).

Each time you add a record to the IV00101 table, SQL also adds records to the page for each Index.

Now imagine that the index page is actually written on a yellow notepad.  If you hit the end of the page you need a new page, and you need to place the paper on an open space on your desk.  Now your index is fragmented.  SQL Database Maintenance can reorder the indexes so that the data pages are next to each other rather than scattered around your desk.

If you were able to run the query above on your own database, the %-Fragmentation is a measure of how scattered your yellow notepad pages are on your desk.  If it’s above 90%, that index is on a bunch of separate pages all over your desk.

I’m simplifying a lot.  If you want a more technical description of index and fragmentation, click here.

So, why does GP have HEAP tables?  Wouldn’t it make more sense to arrange the data on the disk in order by the Primary Key?

Because it mostly doesn’t matter, and probably not.

A clustered index (aka not a HEAP) arranges the data on the drive in the physical order dictated by the Primary Key.  To write a new record it needs to rearrange the data on the page so it can insert the new Item Number into the correct row in the table.  This is time consuming.  So there is an argument in favor of HEAPs–they offer better performance writing data to the disk.

The HEAP doesn’t make any difference for most tasks.  If GP needs a list of Items between A and D, it can use the Primary Key page, which is in the correct order.

Ultimately the real performance question is: Does the table have Indexes that provide (1) the columns needed by a GP function, and (2) in the correct order?  For example, if I want a list of all Items in Item Class ATT CORD, SQL Server can use Key3.  If that key didn’t exist, SQL would either have to read through the table by the PrimaryKey, pulling out the needed records, or simply read from the disorganized physical table.  Either way is not efficient.

For a much more technical discussion of HEAPS, click here.

SQL Server has several tools to help you optimize database performance.  In particular, the Database Engine Tuning Advisor can suggest additional table indexes to help performance based on your usage.  Read more here.

 

If you feel too constrained by the standard software spec, which describes only the functionality that will be present in the proposed software, we are proud to now offer our exclusive Anti-Specification Service. Our ASS will identify everything that will NOT be in the software—the features, functionality, and performance characteristics that will not be included. By employing proprietary Broadly Intelligent Guesswork, we are able to anticipate everything you might have wanted, and document its absence in the BIG-ASS spec.

The scope of these projects is nearly unlimited, except for the small amount of time required to refrain from documenting what the software will actually do. The deliverable is one BIG-ASS document.

As the great Sherlock Holmes said, “When you have excluded the impossible, whatever remains, however improbable, must be the truth.”

In this manner, by enumerating all the unlikely possibilities that we intend to exclude during the software construction process, when you closely examine our BIG-ASS spec, you will find the only thing missing is your custom code. If you would like to engage us for a BIG-ASS project, we are offering a special discount to first-time clients in an even percentage amount greater than 14% and less than 16%.