Tuesday, February 26, 2008
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???


Tuesday, February 19, 2008
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...
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...
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
Friday, February 15, 2008
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!