Great Plains Guy

Free Visual Studio 2010 Training from AppDev!

2010-04-09T07:05:00.001-07:00

Cannot change the XXX account at the Summary Level

2010-03-25T08:40:00.001-07:00

A user of mine got this error message today:

“You cannot change the PURCH account at the summary level. Change the inventory account for the line item instead.”

I’d never seen it and neither had she. The first thing I thought was, OK, change it at the line item, but they left out one piece of critical information, i.e. where is the line item!

So, to figure this out, of course, I asked her what she was doing, good start, eh?

She was trying to match invoices against purchase orders (Transactions->Purchasing->Enter/Match Invoices) and more specifically, she was trying to modify the distribution account number by clicking the Distributions button.

Clicking the Distributions buttons brought up this screen.

When she tried to change the Account number here, she got the error message pop up window.

The issue is that the error message isn’t quite what you would call user friendly. Change the “line item”? That’s what she thought she was doing. She didn’t realize that this was the summary window, why would she, where does it say that?? She went back to the invoice and tried to search for a way to change the line item on the invoice, but no go.

She clicked the Item link, which brings up a pop up for the Item Maintenance window. Nope.

She clicked the Information Icon, which brings up the Purchasing Quantity Status window. Not.

She clicked the magnifying glass icon, which brings up the items from the purchase order,

so that you attempt change the line item entered on the invoice, which wont work if you saved the row already, you’ll get another nasty error message:

Why doesn’t GP just do the delete for you? I guess its a “safety” thing.

She tried clicking the Note icon, which allows for entering a note tied to this invoice line item entry. And she tried clicking the Arrow icon, which actually is the correct icon, but not on this screen. On this screen, the Arrow icon brings up the Purchasing Invoice Item Tax Detail Entry window. Blah.

Realizing that the “line item” must be from the PO that she’s trying to match against, I had her go to Transactions->Purchasing->Purchase Order Entry, and bring up the PO in question.

NOTE: Make sure to exit the Enter/Match Invoices window first, or else this:

Tee hee! We are that other user. GP is so forgiving. Not!

And if you do have both windows open and you go to close the Enter/Match Invoices window, you get this!

Man! What fun!

So, make sure you exit the Purchase Order Edit window before you go back to the Enter/Match Invoices window. The moral of the story? Don’t try to open the same document in two different windows!

So, we close out of the Purchase Order Edit window. Then we close out of the Enter/Match Invoices window. Now we reopen the Purchase Order Edit window and edit the PO.

Once there, she clicked on the Arrow icon on the PO line item (the one the original error message was referring to!) which brings up the Purchasing Item Detail Entry window (make sure you click on the line item you want first, otherwise this window will open, but it will be blank).

Now she changed the Inventory Account number to the one she wanted, saved the PO, went back into the Enter/Match Invoices, and the account number change was reflected in the “summary” window, as she wanted.

Whew! What a round about way to get an account number changed! Anyone know of a quicker link? ;-)

Microsoft Certifications : Register for Visual Studio 2010 Beta Exams

2010-03-22T05:26:00.001-07:00

Six free beta exams on Microsoft’s .NET 4! Sign up now!

Microsoft Certifications : Register for Visual Studio 2010 Beta Exams

Dynamics GP 2010 Sneak Peek

2010-03-16T17:28:00.001-07:00

Very cool sneak peeks at Great Plains 10!

Dynamics GP 2010 Sneak Peek

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

2010-01-07T13:51:00.001-08:00

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

Sales Order error - Not all required fields have been entered

2010-01-07T06:03:00.001-08:00

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…)

The Dynamics Products - Who Uses Them and Why?

2009-11-24T18:12:00.000-08:00

Interesting article on all the Dynamics Products, who uses what and why!

http://www.softwareadvice.com/articles/manufacturing/understanding-the-difference-between-gp-nav-sl-ax-1111709/

Macros in GP

2009-09-02T06:02:00.001-07:00

Nice article on creating Macros in Dynamics GP!

http://www.dynamicscare.com/blog/index.php/macros-in-dynamics-gp/

Great Plains 10 SDK - Free Download!

2009-08-31T10:21:00.000-07:00

I've become a bit spoiled. Always finding great nuggets of data in people's blogs (like MVP Victoria Yudin's - see my previous post), I tend to forget to RTFM.

I just revisited an old friend, the Great Plains SDK, which is chock full of good info that I didn't even have to ~~google~~ bing for!

Download the GP SDK yourself and get tons of Dynamics GP technical info for the low, low price of FREE!

http://www.microsoft.com/downloads/thankyou.aspx?familyId=16d5bcea-c965-4f9a-a965-f4a81b0dd564&displayLang=en

Great Blog Article on the Sales Order Processing Tables!

2009-08-31T10:06:00.000-07:00

Check out this great blog article by fellow MVP Victoria Yudin on the Sales Order Processing Tables. (I've used it myself multiple times, I guess it was about time I shared the link!)

http://victoriayudin.com/gp-reports/sop-tables/

ApexSQL Code is free!

2009-08-23T08:19:00.001-07:00

ApexSQl, maker of a number of fine SQL tools, is giving away their SQL Code tool for free!

http://www.apexsql.com/blog/2009/03/free-code-generator-apex-sql-code.htm

Don't know how long this will last!

Dynamics GP Database Maintenance Utility

2009-07-16T11:57:00.001-07:00

I did not know about this. Dang!

https://community.dynamics.com/blogs/gpmarianogomez/archive/2009/07/07/microsoft-dynamics-gp-database-maintenance-utility.aspx

Great utility from MS to recreate GP objects.

Thanks for the detailed write up, Mariano!

Virtual MS Partner Conference

2009-07-15T05:44:00.000-07:00

Couldn't make it to WPC? Check it out!

http://hub.digitalwpc.com/

Restoring Deleted SmartLists

2009-07-14T13:40:00.000-07:00

I was looking for SmartList tables in SQL so that I could restore some SmartLists that someone accidentally deleted. Here's what I came up with:

ASITAB20 -- empty for me
ASIEXP51 -- seems to hold SmartList exports, empty for me
ASIEXP81
ASIEXP83
ASIEXP86

After restoring a copy of my company db to my UA box, I copied missing rows from my UA server from these 5 tables back into my production servers tables and that did the trick!

I did a simple insert for each one (even though the first two tables didn't have any missing rows), like so:


SET IDENTITY_INSERT ASIEXP81 ON
INSERT INTO ASIEXP81
(ASI_Favorite_Dict_ID, ..., DEX_ROW_ID)  
SELECT *
FROM [UA].DYNAMICS.dbo.ASIEXP81
WHERE DEX_ROW_ID NOT IN
(
  SELECT DEX_ROW_ID
  FROM DYNAMICS.dbo.ASIEXP81
)
SET IDENTITY_INSERT ASIEXP81 OFF

FYI, I ran this code on my production server and had my UA server set up as a linked server.

Virtual Convergence!

2009-07-14T13:37:00.000-07:00

Check it out! You can attend the Dynamics GP conference that you thought you missed!

Lots of free content! Pay money if you want more, but I think the 50 Tips in 50 Minutes presentation (which is one of the free samples) is worth the time to click it!

http://vepexp.microsoft.com/convergence09

How to determine, maintain, and report accurate costing in Inventory in Microsoft Dynamics GP 9.0 and 10.0

2009-07-14T08:40:00.000-07:00

Stumbled across this little gem of an article when trouble-shooting my inventory costs. Very enlightening stuff!

http://www.pinncorp.com/LinkClick.aspx?fileticket=Q1hwsoTQRlA%3D&tabid=452&mid=3482

Great "Did You Know" Series Post on Historical Inventory Trial Balance (HITB) Report from Microsoft

2009-06-29T14:21:00.000-07:00

http://blogs.msdn.com/gp/archive/2009/06/27/week-3-did-you-know-series-of-the-week.aspx

My TechEd 2009 MVP Interview

2009-06-17T03:33:00.000-07:00

MVP David Patrick-Dynamics GP

Leaving Feedback for Dynamics Great Plains

2009-05-21T06:51:00.000-07:00

How do I leave feedback for Dynamics GP? Check this article out:

http://blogs.msdn.com/gp/archive/2009/05/19/ever-wonder-where-your-product-feedback-goes.aspx

List Windows Groups Membership from SQL

2009-04-16T07:02:00.000-07:00

Ever wanted to see who was in a windows group?

As a SQL DBA, I used to be able to see this kind of information using SQL 2000, but that part of the GUI got left behind in SQL 2005/2008, however, there is a stored procedure that you can use to see this information:

xp_logininfo 'BUILTIN\Administrators', 'members'

This lists all the users who belong to a given group, in this example, it lists the members of the Administrators group, good stuff to know if you need to know just who can administer the SQL box that your Dynamics GP / Great Plains is running on!

Can't Login After Moving Databases to New Server

2009-04-02T18:59:00.000-07:00

If you ever move your Great Plains databases from one server to another, make sure you do the following:

use master
go
GRANT EXEC ON smDEX_Max_Char TO PUBLIC
GRANT EXEC ON smDEX_Build_Locks TO PUBLIC

I've been burned more than once by forgetting these. Without them, you can't login and you'll pull your hair out because everything looks right (and indeed is right!) security setup-wise.

Having trouble logging in after a server move? Try this and see if it helps!

SharePointSaturday in DC on May 2nd - FREE Event!

2009-04-02T07:32:00.001-07:00

For all you local SharePoint guys and gals out there!

http://www.sharepointsaturday.org/dc/Pages/about.aspx

FREE Microsoft TechDays Live 24 Hour Virtual Event - It's FREE!

2009-03-25T11:53:00.000-07:00

Not exactly a Great Plains specific event, but definitely something for Microsoft Developer's to check out:

https://www.msfttechdays.com/public/home.aspx

TechDays is a free, one day (24 hour), live virtual conference/event. It's on the web, so its in your region! Sign up and build your schedule already. Don't worry, you don't have to attend the whole 24 hours (except you hard core devs, you know who you are). Did I mention that it's free?

Mark's 50 tips in 50 Minutes Presentation!

2009-03-16T10:52:00.001-07:00

http://msdynamicsgp.blogspot.com/2009/03/convergence-2009-50-tips-in-50-minutes.html

New Official Dynamics GP Blog!

2009-03-06T04:03:00.000-08:00

Check out the new OFFICIAL blog of Dynamics Great Plains called Inside Microsoft Dynamics GP!

http://blogs.msdn.com/gp/

Since this is straight from the horse's mouth, this blog should be a great source of official information right from the guys at MS!

Convergence 2009 is coming!

2009-02-13T11:55:00.000-08:00

Convergence 2009 New Orleans is just around the corner!

Don't miss the must attend event for all Great Plains professionals!

· The session catalog (containing 225 sessions) is now live on the Convergence marketing site.
· The Convergence Customer Excellence Awards recognize, honor and celebrate customers who have achieved outstanding success with their Microsoft solutions as well as Microsoft partners' solutions. Customer Excellence Award nominations are now being accepted through January 20, 2009.

Check out these 6 reasons why you should go!

http://www.microsoft.com/dynamics/convergence/neworleans09/about_6reasonstoattend.aspx

Application Connections in Forecaster 7.0 or Where is Forecaster.ini?

2009-01-28T13:32:00.000-08:00

Here's some quick information on how Forecaster stores application connections.

I'm running Forecaster 7.0 under Citrix and we ran into an issue where any new application connections that we're being created weren't being saved. It turns out that these connections are stored thusly:

T:\Documents and Settings\\Local Settings\Application Data\Microsoft\Forecaster\Forecaster.ini

(where T: is our system drive letter under Citrix).

The contents of Forecaster.ini are pretty straight forward, here's an example entry:

[Applications]

0=Demo

[Demo]

FileName=T:\Program Files\Microsoft Forecaster 7.0\Demo70.mdb

We're getting around the fact that Citrix cleans up this file on logoff (thus erasing any newly created application connections) by using a batch file to copy this file to and from the user's home drive during logon and logoff.

I hope this helps with anyone needing make sure these entries get saved or if they need to be moved (or copied or whatever)!

Great Plains Test Drive!

2009-01-27T13:35:00.001-08:00

Take Great Plains for a try out virtually!

https://dynamicsgptestdrive.com/content.aspx

Webcast on Tools to Customize your Dynamics Great Plains Solution

2009-01-22T08:30:00.000-08:00

Found this great video covering various tools available for customizing your Great Plains install!

http://www.microsoft.com/video/en/us/details/6b4c0715-ece7-4ad3-a2ef-043c6ae717a7

Great Great Plains Videos!

2009-01-22T08:19:00.001-08:00

You ever want to know how to do something? You don't want the why or a bunch of "fluff" about the architechure, you just want to know where to click to make things happen. I find that "How To" Videos can really help out in this situation!

I was pleasantly surprised to find a bunch of these "How To" videos posted up on YouTube! Who would have thunk it? I thought YouTube was only for music videos and RickRolling! Well, come to find out, many reputeable companies are using YouTube to post all sorts of educational videos.

For "How To" Video's in the Great Plains world, check out this link:

http://www.youtube.com/user/mdpolino

This is the YouTube homepage (aka channel) of our friend from DynamicAccouting.Net, Mark Polino. It's pure gold when it comes to finding out the how to do a specific thing in GP!

If it turns out that you are after more of the "why" type info (and I guess we do that need, too, eventually), try this YouTube channel:

http://www.youtube.com/user/NALPro

or this one, from Microsoft:

http://www.youtube.com/user/MicrosoftSBC

Happy Video Watching!

Great Plains 10 Table Name Quick Reference

2009-01-09T14:32:00.000-08:00

Found this little gem, a great quick reference for Great Plains 10 Table Names!

http://www.box.net/shared/jc90kdsh2k

And more great downloads here:

http://msdynamicsgp.blogspot.com/2005/09/gp-downloads.html

Thanks to Mark Polino and the guys from from DynamicAccounting.net!

desSPRkmhBBCreh

2009-01-05T06:23:00.000-08:00

I found this bit of SQL while reviewing a trace of Great Plains acitivity:

SELECT desSPRkmhBBCreh FROM

and I was wondering what the heck the column desSPRkmhBBCreh was, as it wasn't in any tables that I could find.

Then I found this gem:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/15/what-is-column-dessprkmhbbcreh.aspx

Ah ha! It's all so clear now.... :-)

Tips & Tricks - Adding to Company Name

2009-01-04T08:32:00.000-08:00

So here's something I didn't know (among many things!), adding the text <TEST> to the end of a company name in Great Plains causes a pop up message to appear when a user first logs into the sysetem. The message warns the user that they are in a test system and not to put in any real data. Here's the actual pop up:

This is especially useful in my QA and Dev environments, since I occasionally have real end users in there testing new features.

Pretty cool trick!

Great Video on Excel Based Budgeting

2008-09-19T13:14:00.000-07:00

Another gem found!

For those who love Excel and Great Plains, here's a quick (5 minutes) video intro on how to do Excel Based Budgeting in Great Plains.

http://www.blip.tv/file/103862?filename=Dynamicsherpa-ExcelBasedBudgeting524.wmv

Thanks Dynamic Shepra and the guys at DynamicAccounting.net!

Here's their website, chock full of other GP video how to goodness!

http://www.blip.tv/users/view/dynamicsherpa#153951

- Dave

FRx Virutal Labs have moved!

2008-08-26T11:13:00.000-07:00

I see how it is. Always keeping us on our toes. But I found you. The FRx labs I "discovered" a while ago disappeared on me, but I found them again. Ha ha!

http://msfrx-vdms.demoservers.com/VDMS

Dynamics Road Show is Coming to a City Near You!

2008-08-25T12:52:00.000-07:00

Check it out, the Dynamics Road Show is Coming to a City Near You!

http://dynamicsroadshow.spaces.live.com/

TechNet Plus Direct Subscription give-a-way!

2008-06-23T07:23:00.000-07:00

Check out my .NET User Group blog for a link to a TechNet Plus Direct Subscription give-a-way that I am running!

http://mapamdug.blogspot.com/2008/06/technet-plus-direct-subscription-give.html

Great Plains 10 Installation Adventure Part 1

2008-05-27T09:38:00.000-07:00

I'm attempting to upgrade to GP 10. I've run into a few snafu's and I thought I'd post them here (with my solutions/work arounds) to help anyone else out that might be encountering similar snafu's.

When attempting to install GP 10 SP1 on a PC with NET 2.0 SP1, I got an error like so:

"Product: Dexterity Shared Components -- .NET Framework 2.0 is not installed. The installation cannot continue"

Well, come to find out, the GP 10 installer checks for .NET 2.0 but has an issue if you have .NET SP 1 installed.

The work around is to uninstall .NET 2.0 SP1 (and in my case, .NET 3.0 as well) and then my install got past this particular error.

If you don't have the luxury of uninstalling .NET 2.0 SP 1 (as most people don't), you can try this fix from MS:

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYVLXUZLWUKKQPOVRVPQLLMRTZOXZMKWURYUMMLPMPXYSSUVNW&wa=wsignin1.0

Good luck and let me know how you make out!

Things I Had To Do To Get Replication Working

2008-04-22T13:56:00.000-07:00

I recently had a fight/set up transactional replication (with updating subscribers!) on my databases.

Here's some of the crazy stuff I had to do:

1) Go into the bowels of my DCOM security setup and open everything up (more details to come).

2) Run msdtc -install / hack the registry / run msdtc -reinstall.

3) Create some funky looking user in my SQL db so that trusted authentication would work for connecting to the distributor.

4) Enable MSDTC on my servers (since its disabled by default!), which involves doing an install of the MSDTC windows component (makes me wonder why they didn't just call in installing MSDTC versus "enabling" it?)

5) Had to create a linked server specifically named with my Server Name

6) Had to go into Windows Firewall and add port 135 and the MSDTC program as exceptions, even though Windows Firewall wasn't running!

7) Used tool DTCPing to tell me I wasn't crazy. But it reported everything was successfull, even though I couldn't get a distributed transaction to run, so I am crazy! :-)

Whew. OK. This is just a first post to vent my frustrations with setting up replication. I promise to come back and tidy this up with more details, screen shots, and KB links!

FRx Demo Lab

2008-04-15T12:13:00.000-07:00

I stumbled on this little gold nugget by accident.

https://microsoftfrx.demoservers.com/

Great way to check out FRx using a hosted virtual lab! Nothing to install, runs in your browser, and comes with a nice little Hands On Lab type walk through.

Check it out!

(UPDATE: The labs moved! Here they are: http://msfrx-vdms.demoservers.com/VDMS)

"Operation must use an updateable query" error in IM

2008-03-28T10:46:00.000-07:00

MES (from the GP public newsgroups) had an error when attempting to use Integration Manager:

"Operation must use an updateable query"

I love these kind of error messages, because they're just so darn recognizable. (I'm not kidding, really, its quite sad when you can recognize error messages, like, "Hey, there's Larry!")

I instantly recognized this to be an Access database error that occurs usually for one of two reasons (actually its the same reason, the database cannot be written to, but there's two different causes).

The first cause is if the Access .mdb file has been marked with the readonly attribute. This can happen when you copy it from a CD or if you inadvertly had it selected when you selected another file and marked it for read only.

The second cause (and more common, I think) is if you don't have permissions to write to the database, which was the case for MES.

Which Access database file am I referring to? The IM.mdb file that Integration Manager uses to store its integration definitions.

So, if you get this error when trying to run an integration, make sure that the user who you are logged in as when trying to run the integration has permissions to write to the IM.mdb file!

If that aint it, make sure the file hasn't been marked read only.

Happy Integrating!

How to Remove Outlook from GP Dashboard using SQL

2008-03-27T11:58:00.000-07:00

So, a user by the name of 2020 asked on the newsgroups the other day, how can we disable outlook on the Great Plains home page?

I happen to have had this same issue and using my trusty SQL Profiler, I found that when I manually uncheck Outlook from my home page, GP ran a SQL statement like the following :

UPDATE DYNAMICS.dbo.SY08100
SET Visible = 0
FROM DYNAMICS.dbo.SY08100
WHERE COLNUMBR = 1
AND SEQNUMBR = 3
AND Visible = 1
AND USERID = 'dpatrick' -- comment this if you want to do it for all users
AND SectionID = 2

This works great, but it doesn't actually stop any particular user re-checking Outlook on the home page (but it got me what I wanted, which is to default Outlook to being off for everyone).

Dave Musgrave, from Microsoft, later chimed in and gave us this link:

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;918313

which discusses how to actually disable Outlook from the homepage (and uses a script that looks pretty close to the one I use).

Good stuff!

Thanks Dave and thanks 2020 for asking the question!

Great Plains Developer Resource List

2008-03-18T09:48:00.001-07:00

Neil Jordan shot me an email about one of my recent posts and mentioned that it would be nice to have a central place to discuss GP Developer related issues.

I agreed that it would be nice to have a central depot for sharing GP developer information.

The challenge I find is that there already are several "central" places to share info, and the fact that there are several of them kind of takes away from any of one of them being the "central" one!

That said, here's my short list of GP dev resources:

RESOURCE	URL	COMMENTS
Great Plains Microsoft Public Newsgroups	http://www.microsoft.com/dynamics/gp/community.mspx	There's two newsgroups here, Microsoft Dynamics GP and Microsoft Dynamics GP Developer, make sure to check both. Watch out for the occasional SPAM post and make sure to modify your email address before you post, something like davidepatrick@no-spam-hotmail.com, so as not to get SPAM'ed.
CustomerSource	http://www.microsoft.com/dynamics/customersource.mspx	Log in required, but worth signing up if you're a GP customer Lots of good stuff, access to the Knowledge Base is probably what I use most
PartnerSource	http://www.microsoft.com/dynamics/partnersource.mspx	You mean you're not a partner? Why not become one!  Lots of good resources here for partners.
Great Plains User Group	www.gpug.com	Not free, but host web based meetings, so you can join from anywhere
Experts Exchange	www.experts-exchange.com	Free to read, costs money to ask your own questions, but could be well worth it!
DynamicAccounting.net	http://msdynamicsgp.blogspot.com/	One of only a few GP Developer blogs that I know of
GP Dynamics	http://www.gp-dynamics.com/	Lots of free GP SQL scripts
Micosoft Dynamics Forums	http://www.microsoftdynamicsforums.com/forums/default.asp	A non-MS newsgroup dedicated to Dynamics
Tech Round Up	http://www.jasonhartman.net/	A friend of mine's (and GP MVP) GP dev blog. Hasn't been updated in a while, but still lots of good stuff!
Great Plains Guy	GreatPlainsGuy.blogspot.com	This blog! 

If I had to pick one to be the grand daddy central place, it'd have to be the GP newsgroups (but there really are 2 of them that I check, so I guess I still can't pick just one, ack!).

The nice thing about the newsgroups is that they are read and answered by MS folks and (even better) by MVPs. So, it's not a bad place to voice your concerns, share your issues, and sometimes even help your fellow GP dev guy out.

Do you have any resources to add to my list? Maybe we need a central list of GP dev resources? Maybe a wiki or something?

What do you think?

How to Get Table Info in Great Plains - 2 Ways

2008-03-05T08:18:00.000-08:00

I mentioned yesterday that I had to restore some Item records after making a horrendous change to one of my Unit of Measure Schedules. It occurred to me that not everyone may be familiar with the underlying GP tables, maybe because they are named oh-so-intuitively...NOT!

So, for those of us who can't read minds, here's two ways for finding out what the table IV00106 really is. From the GP Menu, select:

Tools >> Resource Descriptions >> Tables

That pops up the Table Descriptions window. From there you can click the elispes to select a table.

Once you select a table, you get the down and dirty details.

Another way, which is actually the way I use most of the time, simply because it's a quick and dirty look up using SQL in Query Analyzer (or SQL Server Management Studio, as it's now called, but I still like the old name) , which is where I am at most of the day. So, without further ado, here's the script:

SELECT *

FROM DYNAMICS..SY40501

Gives you all the physical table names, what series they belong to, and a little descriptive info on what the table actually contains.

While the first way to obtain table info (i.e. through the GP GUI) certainly gives a more complete picture, the second way (i.e. through SQL) usually gives me what I am looking for.

Have fun!

Beware the change to Unit of Measure Schedule!

2008-03-04T14:14:00.000-08:00

So....

I made a change to one of my Unit Of Measure Schedule's yesterday, trying to figure out why one of my items didn't have a unit of measure showing in one of our custom web pages that query from the Great Plains database. Turns out that the item's default purchasing unit of measure was left blank and that was the culprit, because that's the field that our custom web app displays.

But, in my debugging efforts (and yes, I *know* I shouldn't have been messing around in the production environment, yes - we have test and QA and DEV environments, any one of which would have been a better choice to experiment in, BUT I didn't think my change was going have to be saved (where are you UNDO?!?!) and I didn't think the change to my UoM Schedule would result in the deletion of records from three different tables! Yes, the change I made, after saving, caused about 16000 records to be deleted from each of 3 IV tables. Eeeks!

Yes, Great Plains did warn me, something along the lines of "your price list and options will be lost", but who pays attention to that stuff. :-)

Ah well, lessons learned. I had to do a restore from the night before (thank God for my back ups, which I test almost too much) and copy all my deleted records from the following three tables:

IV00106 - Item Purchasing Options

IV00107 - Item Price List Options

IV00108 - Item Price List

So, just a little drama and no permanent damage done (I hope!).

The moral for today is, be careful what you tinker with in Great Plains, sometimes it wont let you out without saving (see my earlier post/rant) and sometimes what seems like a small change can result in big changes!

Be the first to know about VS2008 and SQL2008 exams

2008-03-04T12:56:00.000-08:00

Sign up to get 40% off new exams on Visual Studio 2008 and SQL Server 2008!

http://www.microsoft.com/learning/mcp/sqlvs/offer/default.mspx

Why can't I Cancel a Change to a PO?

2008-02-26T08:26:00.001-08:00

Ever make a change to a PO that you didn't want to?

You know, before it's received, like to an item's price or something, and then decide, oh crap, I didn't want to do that!

So, you click the little X in the upper right corner to get out of the window and what pops up? This:

So, I click Cancel, thinking, yes, I want to cancel, get out, go away, stop the edit! But, guess what, as I'm sure we all know, clicking Cancel doesn't get out of the PO Entry window, oh no, it just plops you back into it. And there's no undo, no quit, no exiting, nothing!

I have short term memory issues and I can't remember what the price just was that I changed! So, I have to go look it up and put it back the way it was. Ugh! Too much effort.

Why can't I just close the window WITHOUT saving???

Where is my company ID?

2008-02-19T13:08:00.000-08:00

So, I am attempting to install FRxForecaster and as part of the oh-so-easy (not!) install instructions, it asks me for my company ID. Huh?

I don't know my company ID! I know the name, and I guess I can guess that my company ID might be 1, since the default company record that the FRxForecaster install has listed has a company ID that is zero (and 1 comes after zero, as my son might tell me).

But I need to know for sure...so I am off to try out my google fu....

Minutes later (because that's all I have the patience for)...NOTHING.

Ah well, so, I remember from one of my many GP upgrades that I had some script somewhere that told me what companies I had installed and what was their various state of upgradiness. I also remember distinctly that the script showed me what my company ID was (I also remember seeing one company ID listed as somthing like -32676, which was weird. I think it might have been TWO).

Anyhoo...

So, I go digging through my scripts archive ('cause you know I keep them all, hey, drive space is cheap!) and voila!, here it is:

SELECT b.fileOSName, a.Status, a.errornum, c.INTERID
FROM DYNAMICS.dbo.DU000030 a
JOIN DYNAMICS.dbo.DU000010 b ON a.fileNumber = b.fileNumber
JOIN DYNAMICS.dbo.SY01500 c ON a.companyID = c.CMPANYID
WHERE a.Status <> 0 or a.errornum <> 0

From this script I gather that the SY01500 table is the one holding my company info (and ID!). Sure enough, the following gives me my answer:

SELECT *
FROM DYNAMICS.dbo.SY01500

which is, in my case, (ready for it?):

1

I know, I know, kind of anti-climatic, but whatcha gonna do.

Until next time...

Kick Everyone Out SQL Script

2008-02-19T12:58:00.001-08:00

I've been asked this a few times, and so, I thought a post might be in order.

If you ever get "locked" out of the system, i.e. something crashes and then you cannot log back in, this little piece of SQL will kick everyone off ( or,more specifically, it will delete everyone's login from the activity table):

DELETE FROM DYNAMICS.dbo.ACTIVITY

In Great Plains 7.5 and below, this was a life saver. You could also do individual users like so:

DELETE FROM DYNAMICS.dbo.ACTIVITY WHERE userid='username'

In Great Plains 9 and above, they now have a nice window that pops up that lets you delete yourself, if you happen to already be logged in, so the theory is that this script is no longer necessary.

But wait!

That assumes that that little window will still pop up after a crash. And sometimes, after a crash, all bets are off, so I still keep this little guy handy, just in case...

Some Great Great Plains SQL Scripts

2008-02-19T12:54:00.001-08:00

Here's a site full of handy SQL scripts for us Great Plains guys:

http://www.gp-dynamics.com/dynamics-gp-tips-and-tricks.asp

Welcome to the Junk Yard!

2008-02-15T09:48:00.000-08:00

I've started this Great Plains Blog to try to help people avoid the mistakes I've made when developing for, customizing and working with Dynamics Great Plains. I've been using since version 6 and I'm currently on version 9 at the moment, and will soon be making the leap to 10.

I hope to post various tips and tricks, interesting bugs, cool work arounds, and maybe even a few good SQL scripts that have made my life easier.

Good looking out!