You, Me and Dynamics GP
The premier information site for Microsoft Dynamics GP
Fellow Packt Publishing author and Dynamics NAV consultant Mark Brummel looks at what it really takes to have a successful ERP implementation. I’ve tackled a similar subject in my MSDynamicsWorld article on why you need a champion.
We both agree, its the people, specifically the end users, that ultimately determine how successful and implementation will be to a business.
Back playing with Management Reporter, Jivtesh gives us a very nice post on Troubleshooting and Errors when working with MR.
Continuing his look at Sure Step, Vjekoslav Babic looks at business process change during an implementation.
Jivtesh thinks that FRx will be around for a while and I tend to agree. Since you’ll still be using it, he give us a fix for Error 3617 The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
David Musgrave reminds us again how easy it is to identify the Security Tasks and Security Roles using the Support Debugging Tool.
Over at GPWindow, Jivtesh looks at another way to send a window location in Dynamics GP to a user.
Vjekoslav continues his look at fit and fit gaps in Sure Step with Sure Step in action: a blurry Degree of Fit?
Beth Gardner tackles an error that can occur because the Office 2010 beta and other releases have updated some system registry keys for VBA 6 incorrectly. Beth points us to a fix and KB for more information in her post File not found: VBA6.dll error occurs or GP crashes and creates a Watson fault bucket 1474386816 .
In his new post Mariano looks at Using T-SQL and recursive CTE to find related sales documents in Dynamics GP.
Dynamics GP 2010 adds a new feature to recurring batches in GL. Checking the Clear Recurring Amounts checkbox removes the batch amounts after posting allowing new amounts to be entered the next time. This is essentially a hybrid allowing Quick Journal style functionality combined with with batch posting, controls and approval.
My new partner focused post is up at The Death of Reason. I don’t think that we do enough post project reviews in the Dynamics world and I’d like your help to figure out how to better learn from both good and bad project elements.
If you only want VBA code to run for some users in Dynamics GP, Michael Johnson shows how to tackle that in his post Using GP Security to Control for which users VBA code runs
Navigate into Success continues their look at the Fit Gap analysis portion of the Sure Step implementation methodology for Microsoft Dynamics with 4 strategies for a favorable Degree of Fit.
Mariano has a great look at the Top 10 Microsoft Dynamics GP freebies.
Lisa Price at I.B.I.S. had a nice tip that I had never thought of. When companies are transitioning to Microsoft Dynamics GP, they often make changes to their chart of accounts to improve reporting or data entry. Lisa uses the account alias functionality to hold the old account number when possible. This eases the transition between systems because it allows users to lookup their old account in the alias and find the new account.
If there's not a one to one relationship, it's not perfect but if you're splitting or combining accounts in the chart, you probably don't want much of a link back to the old account anyway.
Originally Posted by Mark at 3/18/2008 08:09:00 PM
Default Process Holds for Dynamics GP SOP Doc IDs is on of Michael Johnson’s latest blog topics. He’s been on a tear the last few days with great blog posts.
Take a look at Belinda, the GP CSI new post on partial payments in Dynamics GP.
Michael Johnson delivers SQL code to cancel GP contract lines outside of Dynamics GP in his new post.
Christina Phillips takes a look at the difference between setting up journal posting per transaction or per batch in her newest post Posting Settings- Creating a Posting Journal Per What? up now at Dynamics GP Land. I’m a “per transaction” guy myself.
SmartConnect
Lately I've had the opportunity to spend some time creating integrations for Dynamics GP with SmartConnect from eOne. SmartConnect is an integration tool for Dynamics GP built on eConnect. The UI is very friendly for users of Integration Manager and the eConnect back end means that it's very fast.
At first glance the interface looks like an updated IM interface. It's generally easy to figure out though I highly recommend the 1/2 day training that is available. SmartConnect includes the ability to copy and rename files pre and post integration and to schedule integrations. Additionally it can process errors into files to email or for later reintegration. Scripting is done with VB.net or C# and the transition from VBScript to VB.net has been very smooth for me. I'm definitely impressed with the helper tools.
There is so much in here that I'm having trouble even listing all of the features including:
Pricing is similar to integration manager and I'm finding myself wondering why I would ever recommend IM again. SmartConnect just seems like such a nice combination of the simplicity of Integration Manager and the power of Scribe.
There has definitely been a learning curve. Since I'm used to the quirks of IM and now have to figure out the quirks of SmartConnect. Specifically:
The beauty of SmartConnect is that it's more powerful than IM but still easy enough for a power user to master. SmartConnect definitely needs to be added to your list of integration tools.
Search Engine Journal has a new post up on When to Say Goodbye to a SEO Client. SEO is Search Engine Optimization for those of you new to that term. As I was reading the post about when to turn a client loose I was struck by how much of this applies to Dynamics GP clients as well. See if any of these sound familiar:
Notice that none of this has anything to do with ERP in the source article. Bad behavior is bad behavior.
Here is the problem if you are a Dynamics GP user. If you fall into these behaviors you are not getting the most out of your partner or your implementation. It’s that simple. You are doing your company a disservice by being dysfunctional. Don’t handicap yourself by being stubborn, inattentive or cheap. Ultimately you are the one who suffers. Take responsibility for your system.
Read the whole thing to see how much you think applies to Dynamics GP.
Wrapping up our VBA mini theme, Michael Johnson delivers VBA code to Disallow adding Serial Numbers on the fly when fulfilling Orders in Dynamics GP.
Sure its a Dynamics NAV blog but Vjekoslav does a great job of explaining the Fit Gap Analysis in Sure Step.
Jivtesh continues to expand GPWindow. This time he’s added the ability to Search Dynamics GP Communities and Forums. It’s still in alpha testing but this is a great extension to blog and website information about Dynamics GP.
Continuing our mini theme on VBA today, Michael Johnson looks at Filtering Grids in Dynamics GP with Modifier/VBA in a new post up at his website.
Vaidy has great post up with issues he’s having importing VBA Customizations from 64 Bit to 32 Bit for test purposes.
Michael Johnson gets us started playing with SQL Server cursors to get at Dynamics GP data in his new post Row by row analysis and execution with Cursors.
Here at DynamicAccounting.net I’m launching a new initiative, Best Practice Guides for Dynamics GP. The first one is free and it’s an update of my previous Use Tax document for Dynamics GP.
Not all of these documents will be free but this first one should give you a great idea of what to expect. The goal is to provide information about a specific Dynamics GP topic that can’t be adequately covered in a blog post but isn’t long enough or is too specialized to be a book. Use tax is a great example because the document has very useful information for the folks who need it but the audience can be very limited.
I expect these guides to be 20 to 75 pages in length and they will sell for $20-$30. The Use Tax guide will always be free.
In some cases these will be expanded versions of speaking topics. The guides will allow users a shortcut to detailed information about that topic saving a lot of Google digging. I will have more guides up soon and I’m looking forward to tacking some topics that I’ve had on my list for a while.
I hope you like the new Best Practice Guides and that they help improve your Dynamics GP implementation.
Don’t miss Chris Liley’s Dynamics GP Multicurrency Webinar on Tuesday. If you’re not a Multicurrency expert in GP, this free webinar is for you.
Mariano Gomez continues his look at Using SQL Server CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM. This time he focuses on configuring SQL Server and creating table triggers.
Michael Johnson has posted a free customization designed to provide a Password Expiration Notice in Dynamics GP when using the SQL Password rules option. I’ve including a link in the Downloads section as well.
Ok this is the last of my comparison tips for balancing. Next week we’re off on to something else.
For this week, I’ve got SQL code to compare the AR subledger to GL to see if they match. You’ll need to adjust the natural account number and segment for you specific chart.
/*Set AR Type and get AP Balance. Change the Checkbook in Red for your company */
SELECT 'AR' as SubType,SUM(custblnc) AS SubBal, 0 AS GLBAL
into #SubtoGLCompare
FROM RM00103
/*Get the GL total for this Account. Change the account below or optionally add additional
actnumbr parameters to tighten or broaden the account*/
INSERT #SubtoGLCompare (SubType,SubBal,GLBAL)
SELECT 'AR' as SubType, 0 AS SubBal, SUM(Perdblnc) AS GLBAL
FROM GL11110
WHERE actnumbr_2 = '1271' -- Change this
Select SubType, sum(Subbal) as SubBal, Sum(GLBal) as GLBAL, sum(SubBal)-Sum(GLBAL) as Difference,
'Difference is Subledger minus GL.' as Instructions
from #SubtoGLCompare
Group by SubType
Drop Table #SubtoGLCompare
I’m off to Ohio this week and while that’s not exactly globetrotting it is a new destination for me so this week’s blog theme is just that, globetrotting.
I’m still looking for tips to add to the new 50 Tips in 50 Minutes for Microsoft Dynamics GP presentation. If you’ve got tips email them to me at mpolino@gmail.com. You could win a signed copy of the Microsoft Dynamics GP 2010 Cookbook.
See the original post for complete details.
The Twelve early warning signs of IT project failure also apply to ERP projects including Dynamics GP. The first step to recovery is admitting that you have a problem.
Over at Developing for Dynamics GP Patrick Roth looks at a simple solution for the Side-by-side configuration is incorrect error message given when launching Dynamics GP.
Mohammad Daoud looks a couple of Bank Deposit issues that he has had recently in his posts:
Belinda the GP CSI has a brief look at the underutilized Collections module. This is an easy to implement module that quickly pays for itself.
I’ve got a new article up on MSDynamicsWorld.com. My newest article looks at the significant changes coming for 1099’s as a result
of the new healthcare legislation and how to start preparing with Dynamics GP.
Don’t skip this one. Too many folks still don’t know about the avalanche of paper that is coming.
Packt Publishing is running special on the Book/eBook combo of my book, the Microsoft Dynamics GP 2010 Cookbook. Right now you can get both the paper book and the ebook versions for a low price of $61.19. The book itself is $52 on Amazon.com. With this offer you can get the convenience of the ebook version for for only $9 more.
I have no control over the publisher or Amazon’s pricing and I’ve already seen good discounts on the book that didn’t last. Hurry up and grab this combo now at Packt Publishing before it’s gone
Microsoft Dynamics GP 10.0 Service Pack 5 has been released. Developing for Dynamics GP has the announcement. This should let GP 10 companies run SQL Server 2008 R2 for their GP installations. There are a number of important considerations with this service pack including no upgrade path to GP 2010 until GP 2010 SP 1 is released. Be sure to check out David’s post for all the links you’ll need for SP 5.
My new Sure Step article is up at MSDynamicsWorld. If you are thinking about implementing Dynamics GP or just curious about Sure Step in general make sure to check it out.
Michael Johnson has a new SQL procedure for us. This time he helps make Deleting Empty SOP Batches Easy.
Belinda is back looking at that age old question, to Void or Delete sales order transactions in Dynamics GP.
The Dynamics Corporate Performance Management blog has nice look at Using Microsoft Dynamics GP Account Categories with Management Reporter.
David Musgrave’s new post looks at using the RW_DateToString() function in Report Writer to facilitate Displaying Dates in any format on Reports
Mariano Gomez has a look at how he has been Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM
Patrick Roth has been highlighting some of the great new (an sometimes free) tools in the Professional Services Tools Library (Commonly known as PSTL or “Pistol”) for GP 2010. In his new post, Patrick looks at fixing Duplicate Customer PO Numbers in SOP Entry a tool that previously cost some dough but is now free.
Christina Phillips continues her look at the Class functionality in Dynamics GP. This time it’s payroll classes featured in her new post In Defense of Classes (Payroll).
Continuing her FRx Series, Belinda looks at exporting out of the FRx Drill Down Viewer.
David Musgrave took some time to dig into Microsoft Office’s Click To Run and Office Online versions as part of a troubleshooting effort. Bottom line is that you need the full MS Office to really make it work right with Dynamics GP. Of course, once you get the data out of GP, Office Online is a great way to share it.
In a series she is running on FRx, Belinda takes a look at the Report Viewer feature. “Try it, You'll like it!”, she says.
I want to highlight a new feature on GPWindow.com. Jivtesh has provided the ability to search for a window in Dynamics GP and GPWindow will return the path through the system to that window. Check out Jivtesh’s post Where’s that GP Window ? for more details.
Over at Dynamics GP Land, Christina Phillips reminds us of the value of classes in inventory.
Victoria Yudin is back with a SQL view for asset account numbers in Dynamics GP. I love seeing Fixed Asset related posts in blogs and this one is no exception. It will go in the SQL Scripts area as well.
Mariano has questions about the relationship between the Microsoft Dynamics GP 2010 Connect feature and IE popups.
Devon Southall is back with a look at how use the features of the Builders to show Work, Open and History at the same time.
Victoria Yudin has a new post up with a SQL view for Inventory Price Levels in Dynamics GP. I will add a link to this in the SQL downloads area.
David Musgrave follows up this previous post with another look at Currency values returned by VBA to a report are multiplied by 10 or 100 this time with Multicurrency.
DynamicsBlogger overs Restricting User Access to Dynamics GP during Maintenance at the SQL level.
Have you have ever wanted to added Internet Information to a Smartlist Builder report? If you have, the Dynamics GP Builders & Reporting site shows you how in their post Adding Internet Information (Email addresses) to Lists in SmartList Builder, etc;
Michael Johnson looks at the fact that more companies are leasing their equipment in this economy and reminds us that Dynamics GP has lots of options ot help.
Jivtesh describes an error he got with Integration Manager in his latest post. The funny thing is that I got the same error the next day. Don’t worry, it’s an easy fix.
Belinda the GP CSI looks at Customer credit limits in Dynamics GP. This is another underutilized area so it’s great that Belinda is focusing on this.
Mariano Gomez follows up his original post on the Dexterity OLE Container.
Patrick Roth has coverage of new tools added to the Professional Service Tools Library for Dynamics GP 2010. Make sure to check Patrick’ post Free Tools Added To PSTL 2010.
Over at the Dynamics GP Builders & Reporting site, they have a nice look at adding the GL accounts to Builder Lists.
Ian Stewart looks at the new EC Sales List and shows how GP already handles.this. Recently I got a chance to setup the VAT Daybook for a company with a new Scottish subsidiary. Setup was surprisingly easy and the total due was right on the mark.
Belinda, the GP CSI looks at putting an invoice on hold. This is a great option when there is a dispute with only a single invoice.
An important part of maintaining Dynamics GP is balancing the AP subledger to the General Leger. GP has a utility to help you do this if they are out of balance. Additionally, The Close can go beyond the capabilities in Dynamics GP to provide even better balancing options. But what if you are regularly in balance and you want to make sure that you stay that way without having to run the big AR Trial Balance report every day? Well, here is a little bit of SQL that can I’ve used before that can do just that. This compares the AP subledger to the selected AP GL account and shows the difference. It’s a quick way to see if AP balance.
SELECT 'AP' as SubType, (SUM(currblnc))*-1 AS SUBBAL, 0 AS GLBAL
into #SubtoGLCompare
FROM PM00201
/*Get the GL total for this Account. Change the account below or optionally add additional
actnumbr parameters to tighten or broaden the account*/
INSERT #SubtoGLCompare (SubType,SubBal,GLBAL)
SELECT 'AP' as Subtype, 0 AS Subbal, SUM(Perdblnc) AS GLBal
FROM GL11110
WHERE actnumbr_2 = '2100' -- Change this
Select SubType, sum(Subbal) as SubBal, Sum(GLBal) as GLBAL, sum(SubBal)-Sum(GLBAL) as Difference,
'Difference is Subledger minus GL.' as Instructions
from #SubtoGLCompare
Group by SubType
Drop Table #SubtoGLCompare
Also from David Musgrave is a post on window positing with VBA in Dynamics GP and beyond in his new post Where has my Microsoft Dynamics GP window gone?
Dynamics Blogger tossed out a list of desired Requisition Management Enhancements in Dynamics GP and want to know when they will be available.
David Musgrave covers an issue where Currency values returned by VBA to a report are multiplied by 10 or 100 in Dynamics GP.
Belinda, the GP CSI shows how to deal with New Items or Site added to Inventory in mass.
Mohammad Daoud looks one option he used for managing projects within the Project Management Module for Dynamics GP.
Mohammad Daoud has coverage of what happens when your Dynamics GP Upgrade Failed, Company Locked and no Longer Upgradable with small lock image on the left?!?!?
David Musgrave took my MSDynamicsWorld article Seven Ways to NOT Get Answers to Your Microsoft Dynamics GP Questions via Newsgroups and expanded on the concept with a look at Getting the most out of your Microsoft Dynamics GP Community.
Jivtesh took David’s post and added his process for finding answers.
I’m coming full circle and adding one more resource. The Microsoft Dynamics GP 2010 Cookbook is full of real world answers with complete, step by step instructions and screenshots.
Jivtesh has a great look today at Setting up Email Options for Multiple Customers/Vendors in Dynamics GP 2010
Both David Musgrave and Mariano Gomez have coverage of DexSense, a new third party product aiming to bring Intellisense like functionality to Dexterity.
Vaidy latest post looks how Mohammad Daoud’s article on Home Page metrics helped him solve his own metrics issues.
Vaidy new post points us to Craig Buckler’s article on 10 Reasons Why Project Estimates Fail.
ComputerationDynamics takes a look at a common solution for when a Batch Gets Hung in Posting.
MVP Mariano Gomez tackles a newgroup question: What are those GL entries with reference SALESASMxxxx? in his latest post.
I ran into a situation recently where a client needed specific item costing for each product that they sell. This is most common for serialized items where there is a single item number but specific costs need to be applied for each serialized item.
Dynamics GP can handle this but there are a few things that you need to know to make this work right:
GP doesn’t finalize the cost for the item until posting so all of the edit reports show FIFO or LIFO cost but the correct cost is applied at posting. It works, you just can’t tell that it works until you post the transaction.
For this week’s background I’m turning the camera on you. You’ll find out why in a Monday morning post.
