MBS Guru: Reconciling SOP Batches from WITHIN Dynamics GP

From Michael Johnson comes an update to his code for Reconciling SOP Batches from WITHIN Dynamics GP

Updated SQL Scripts and Downloads

I’ve been remiss in updating the big list of Dynamics GP SQL Scripts and Downloads due primarily to vacation but everything is caught up now.

The SQL Scripts list has over 180 items and the Downloads list is well over 50 now. If you’ve missed this part of DynamicAccounting.net now is the time to check it out.

Stop Unexpected Transaction Growth in SQL

Mohammad Daoud points us to a great Microsoft Support article on Stopping Unexpected Transaction Log Growth in SQL Server. I still see an awful lot of this in IT departments that should know better.

Dynamics GP Integration Manager Pain

The power of Integration Manager is it's flexibility. With that flexibility comes the power to turn Integration Manager into Integration Mangler. Steve Endow recounts a case where what appeared to be a simple upgrade of integration manager turned into a monster. As a result, he includes an Integration Review Template to help others avoid this mess.

Convergence 2009 50 Tips in 50 Minutes

For everyone looking for the PowerPoint and Notes from the GP03 50 Tips in 50 Minutes session from Convergence 2009, here you go!

Thank You to everyone who attended!

Rapid Implementation Tools for GP 10 Now Available

Parts of the Microsoft Dynamics GP 10 feature pack continue to trickle out. Now the Rapid Implementation Tools are available for download here.
The Rapid Configuration tools is designed to quickly setup the app and the Rapid Migration Tools is designed to quickly convert Quickbooks data.
More info is available at the download site. I think that just leaves the Power Point connectors to come out next.

For the Dynamics GP/SQL Junkie

I know there are some SQL junkies out there always looking for the next SQL script for Dynamics GP. You know who you are. Well, take a look at the SQL Scripts area on GP-Dynamics.com.

I just stumbled on this today so I haven't tested any of these. As always, use SQL statements at your own risk.

Oh, try not to overdue it!

Code to Force Inventory Adj. Posting to GL

The MBS Guru did us all a favor and dropped the VBA code to check the "Post to GL" box for inventory adjustments in GP on his website. As he readily admits, this code is common knowledge, I think most of us have written it or something similar. Now though, you can use your brain for something else and just download this code.

There is also supposed to be a chunk (CNK) file available for free from GP that does this as well (ask your partner).

The bigger question of course is "Why isn't this included in GP to begin with?". This issue has been there forever. If you have Dex code and VBA code, you know how to fix it, just make it a check box in the app and be done with it. Don't add it to the next survey, just fix it in the next service pack. Heck make it 10 only for all I care. Everybody who needs it pre v 10 has one of these workarounds available but fix it for good.

I'll add a permanent link to the code in the Dev Downloads on the right.

Dynamics GP 10 Security and Roles

It's almost Thanksgiving and Jivtesh is talking roles in GP 10. No, not dinner rolls, quit thinking of your stomach! I mean security roles. With all the security improvements in GP 10 you'll want his handy Excel based reference covering the task permissions for each of the built in roles.

Keyboard Shortcuts...for 10.0!

Jivtesh why do you torture me with these good Dynamics GP posts that I HAVE to link to? People will start to think that I don't have original content here!

Jivtesh has keyboard shortcuts for version 10.0 with screenshots. It's nicely laid out too! Go visit him. Again.

There's some sarcasm for those of you unable to follow the toneless nature of the internet. I'm really thrilled at what Jivtesh is doing even if I'm a bit jealous today ;)

Weekly Dynamic: Rename Report Files for Archiving

A question came up in the GP Newsgroup today. MarcB wanted reports printed to a file to be automatically named by GP. This would ease the naming and archiving burden.

Well, there is a way to accomplish the end result, just not with the same method. In Tools->Setup->Posting->Posting you can specify that you want a report to always print to a file and name the file. This will give you a report with the same file name every time. This gets you part of the way there.

Next you'll need a little VBScript (or something else but for this we've got a VBScript app) that will run regularly and rename the file. For this I've built a little VBScript app that simply takes the filename and appends the date and time to the end. So TrialBalance.txt becomes TrialBalance-2007-10-02-12-31-01.txt (October 2, 2007, 12:31 and 1 second). The file simply renames the original and puts it in the same location, so a network location is preferred.

Setup the VBScript file to run regularly using Windows Scheduler. This could be every 5 minutes, hourly or once a day. Whatever matches a reasonable frequency for your organization. You can combine multiple files in one VBScript app with just a little copying an pasting.

Basic error handling is included so if there's no file to rename, you won't see any error messages. Instructions on where to change the file name and path are included in the file along with notes on how to rename more than one report.

The script file can be found here or in the Download section on the right. It's free and you're welcome to make changes for your organizations. Yes, it works in Vista and not it's not limited to GP related files. You could use it with anything.

Enjoy!

SQL Server 2005 Performance Dashboard

For those of you looking to tune the SQL performance of your GP installation, take a look at the new Performance Dashboard from Microsoft. This is a free add on to SQL Server 2005 but you have to be running SP2.
So what is SQL Server 2005 Performance Dashboard? I'll let the MS folks tell you:
The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio.
The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.
For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer
(missing indexes)
- Blocking
- Latch contention

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.
Reporting Services is not required to be installed to use the Performance Dashboard Reports.
Sounds pretty cool doesn't it! It looks good too!

The folks at SQL-Server-Performance.com have all the details on installing and using Performance Dashboard with lots of screen shots.
You can get the Performance Dashboard from Microsoft as well as SQL Server 2005 SP2.

Policy Manual and Calculator Downloads

I've added two new items to the Download section at DynamicAccounting.net. The first is a process and report that allows you to create and print an accounting policy manual from within DynamicsGP (Great Plains). The process uses the chart of accounts to create policies (non-financial accounts so they won't affect statements) and a custom Great Plains report (built with Report Writer, no Crystal required) to print and distribute the manual. We found when trying to setup our policies that we really just needed the basics but maintaining a Word doc was a pain and setting up a database to manage just that was overkill. So this was the solution. The download includes full instructions and the custom report package. I also have the SQL available if you want to do this in Crystal or with a SQL query on an intranet.

I've also added Moffsoft's great, free calculator, FreeCalc. This is a great replacement for the Windows Calculator and includes a transaction tape. With FreeCalc, I've been able to get rid of the adding machine on my desk.

I hope you like 'em, enjoy!

DynamicsGP Table Reference is Now Available

My DynamicsGP Table Reference document is now available on www.dynamicaccounting.net. Simply register and go to the Downloads section to get the table reference.

This table reference is very useful for developers or report creators, especially when using Crystal Reports or SQL Server Reporting Services with DynamicsGP or Great Plains. The table reference is an Excel file with worksheets containing core GP tables and tables for other GP addons.

As a reminder, registering gets you access to the scripts and download areas, along with our archive of Weekly Dynamic articles so you don't have to sort through old blog entries. We will never sell, rent or transfer your information to anyone else and we won't spam you with email. We have yet to send a single email to our registered users. We prefer RSS for communication so that you can be in control.