Great Plains Guy

I’ve mentioned this site before, but it’s so good, it deserves another reminder!

Collection of MICROSOFT GREAT PLAINS DYNAMICS (GP) Sql Scripts, Tips and Tricks

One of my users got this error recently when trying to put an item on a Quote / Order. I tracked the problem down to an issue with the item’s Purchasing Options not being set up correctly. In fact, for this particular item, there was no record at all in the IV00106 table, which is the Purchasing Options table. Here’s a screen shot showing the issue:

As you can see, there are no Purchasing Options entered, which is really weird, since you normally can’t get out of this screen without entering one. So, I don’t know how the data got deleted, but I do know that to fix it, all I have to do is insert some basic data. Here’s the SQL statements I ran to both identify and verify the issue and then to fix it.

1) SQL to Verify that the row is indeed missing from IV00106

SELECT *
FROM IV00106
WHERE ITEMNMBR = '15746-AZ'

(0 row(s) affected)

2) SQL to insert a new row

INSERT INTO IV00106
(ITEMNMBR, UOFM, QTYBSUOM, UMPUROPT)
VALUES ('15746-AZ', 'EACH', 1, 2)

IV00106 is not a complicated table. There are four columns you need to fill in, the Item Number (ITEMNMBR), the Unit of Measure (UOFM), the Quantity Base Unit of Measure (QTYBSUOM), and the Unit of Measure Purchasing Option (UMPUROPT), which is a row number for which look up value to use from this list of Purchasing Options:

1 – Not Available

2 – Whole

3 – Whole and Fractional

In my case, I used 2 for Whole.

There’s one more column in this table, the DEX_ROW_ID, but GP fills that one in for you automatically (since it’s an IDENTITY type).

After running the above INSERT, the GP Purchasing Options screen now looks like:

and the user can now put the item on an Order or Quote with no errors!

That’s it. Another quick fix to a problem that pops up once in a while. Now I just need to figure out why this happens!

(And maybe do a little data clean up…)