Blog

Our latest posts and rants

Dynamics GP logo

Primary Key Violation in IV10202 When Merging Parts in Dynamics GP

We were merging some item numbers in Microsoft Dynamics GP, using the Professional Services Tools Library, when we encountered a primary key violation in table IV10202.

I couldn’t find a solution in the usual places online.

Warning

I definitely do not recommend doing what follows if you have another alternative. I’ll describe a fix that worked for us, but there is no guarantee that it won’t do something bad to your database.

When I checked the IV10202 table, the primary key which was being violated was defined on just the ITEMNMBR column.

Original primary key is on the ITEMNMBR column

I temporarily modified the primary key to include the RCTSEQNM column.

Temporarily the primary key is now ITEMNMBR plus RCTSEQNM

This time when I ran the item number merge it worked. However, when I checked the IV10202 table, there were two entries for our merged item.

I deleted one of the two entries (I picked the one with the larger RCTSEQNM for no good reason).

I then changed the primary key back to just the ITEMNMBR column, and did an inventory reconcile on the merged item, which did find and fix a problem. I also ran a CHECKLINKS on the database.

This seems to have had a happy ending for us. But follow at your own risk.

Hope this helps someone.

Rodin's sculpture The Thinker

Slight Change in Direction

My colleagues and I share this blog. The following only applies to me.

Up until now, for most of my own blog postings, I have imagined someone faced with the same problem I’ve just had, and tried to post something that could be searched for that might be helpful in such a situation.

According to my reading, blogs are more popular if the author shows his or her personality, or more likely in my case, fakes having a personality.

Image: © Nevit Dilmen found at Wikimedia commons

Image: © Nevit Dilmen found at Wikimedia commons

So while I’ll try to continue posting problem solutions that I think might be useful to others, I’ll also try to include things I’ve explored and found either interesting or overly hyped.

For example, over the weekend I submitted a simple app to the Firefox OS app store. Perhaps in a future post I’ll discuss how that went.

Rockwell Automation logo

Cross Reference Allen-Bradley and SQL Server Data Types

Frequently the controls expert who knows about Rockwell Allen-Bradley tags is not the same person as the database expert who knows about Microsoft SQL Server tables.

Can't we all just get along?

Can’t we all just get along?

But we can still all get along.

Here’s a cross reference for a few basic Allen-Bradley tag types versus data types in Microsoft SQL Server 2008 and 2012 that might be helpful in bridging that gap.

PLC Database
BOOL bit
SINT tinyint or char(1) depending on use
INT smallint
DINT int
REAL real
STRING varchar

Allen-Bradley defines a whole lot of structured data types. A structured data type has members that each have their own data type. But the above basic types should help start the conversation, I hope.

Dynamics GP logo

Programatically Construct a Dynamics GP Macro to Disassociate Items from an Inventory Site

This works with Dynamics GP 2013.

If you’ve ever tried to remove an inventory site from Microsoft Dynamics GP, you know it can be very painful to disassociate the inventory site from all the items with which it is associated. It can take 5 seconds to create associations that will take 5 hours to undo.

Here’s a quicker way.

The SQL script below will create a Dynamics GP macro. The macro disassociates the inventory site from each item with which it is associated, leaving you free to remove the inventory site. Note that all the quantities in the site must be zero, the site must not be on any stock count schedules, etc.

First, replace the inventory site name DRAIN below with the site name you wish to remove. Then execute this script against your company database.

-- The following relies on these substitutions:
--  __ITEMNMBR__
--  __LOCNCODE__

select top 2000000000 * -- Use SELECT TOP in case we want to turn this into a view with a sorted result set
from (

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  0 as SortOrder,
  '# DEXVERSION=12.00.0232.000 2 2' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  1 as SortOrder,
'  CommandExec dictionary ''default''  form ''Command_Inventory'' command ''IV_Item_Qtys_Locs'''
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  2 as SortOrder,
  'NewActiveWin dictionary ''default''  form ''IV_Item_Qtys_Locs'' window ''IV_Item_Qtys_Locs'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  3 as SortOrder,
  replace('  TypeTo field ''Item Number'' , ''__ITEMNMBR__''','__ITEMNMBR__',qty.ITEMNMBR) as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  4 as SortOrder,
  '  MoveTo field ''(L) Display Options'' item 0  # ''All'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  5 as SortOrder,
  '  ClickHit field ''(L) Display Options'' item 1  # ''Site ID:'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  6 as SortOrder,
  replace('  TypeTo field ''Location Code'' , ''__LOCNCODE__''','__LOCNCODE__',qty.LOCNCODE) as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  7 as SortOrder,
  '  MoveTo field ''(L) Location GB'' item 0  # ''All'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  8 as SortOrder,
  '  ClickHit field ''(L) Location GB'' item 1  # ''Assigned'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  9 as SortOrder,
  '  MoveTo field ''Delete Button'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  10 as SortOrder,
  '  ClickHit field ''Delete Button'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  11 as SortOrder,
  '# Are you sure you want to delete this record?' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  12 as SortOrder,
  'NewActiveWin dictionary ''default''  form DiaLog window DiaLog  ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  13 as SortOrder,
  'ClickHit field OK ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  14 as SortOrder,
'CloseWindow dictionary ''default''  form ''IV_Item_Qtys_Locs'' window ''IV_Item_Qtys_Locs'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  15 as SortOrder,
'NewActiveWin dictionary ''default''  form sheLL window sheLL ' as FinalMacroLine
from dbo.IV00102 qty
) qtys

-- The next 2 lines contain the name of an inventory site that we want to disassociate from all items
where qtys.ITEMNMBR in (SELECT ITEMNMBR FROM dbo.IV00102 WHERE LOCNCODE='DRAIN')
and qtys.LOCNCODE='DRAIN'

order by
  qtys.ITEMNMBR,
  qtys.LOCNCODE,
  qtys.ORD,
  qtys.SortOrder
;

You’ll get a result set with 5 columns. The fifth column is a GP macro. Copy and paste it into a file with the extension “.mac”.

Sample result set

Sample result set

Then run that file from GP as a macro, and watch hours of work be completed in minutes 😉 .

If there are any associations that can’t be removed, the macro will stop. You’ll usually get an error message with details of the problem. After you’ve fixed the problem, just remove the macro lines that have already executed, and re-start the macro where it left off.

Hope this helps someone else as much as it helped me!

Dynamics GP logo

Which Stock Count Schedules Contain a Particular Inventory Location?

In Microsoft Dynamics GP, if you want to remove an inventory location from your system, one of the things you’ll need to do is remove any stock count schedule lines that include the inventory site. Otherwise you won’t be able to remove the association between the item number and the inventory site that are on the stock count.

Another piece of the puzzle falls into place

Another piece of the puzzle falls into place

You can find stock count schedules that include the inventory site like this. In the following query we’re looking for the inventory site named DRAIN.

select distinct stckcntid
from dbo.IV30701
where locncode='DRAIN'
order by stckcntid;

And this will find any unposted stock counts that contain the inventory site. Again we’re looking for DRAIN, you need to substitute the name of the inventory site you are seeking.

select distinct stckcntid
from dbo.IV10301
where locncode='DRAIN'
order by stckcntid;

Once you’ve found the stock count schedule(s) you can delete or edit them as you see fit, and one of the obstacles to removing an unwanted inventory site from GP will be gone.

Hope this helps!