Archive for November, 2008

Year end close time again

Victoria collects a lot of technical documents and resources in her blog listed below.

Probably many more blogs will come as YEC gets closer but wanted to reference this for the early to close users.

AP Trial Balance by vendor class

Just had someone call and want to know how much they owed for all their merchanising vendors. They did not have the vendors assigned by class. I used SQL to look up the vendors and found each vendor had some default accounts listed on the card. I used 3 queries to get what they needed after backing up the database in case I messed up. Queries are as follow:

  • Select * from GL00100 – this shows what the account index number is needed to interpret the PM00200 table accounts
  • Select * from pm00200 where pmprchix = ‘61‘ or pmprchix = ‘66‘ – 61 and 66 were the account index’s listed in the first query.
  • Update pm00200 set vndclsid = ‘MERCH‘ where pmprchix = ‘61‘ or pmprchix = ‘66‘ – Updated each vendor to be included in the MERCH class I set up in GP under Tools>>setup>>purchasing>>vendor class. In all we updated 400 vendors in a matter of mins.

The bolded red fields would need to be changed to fit your needs.

Once this work was done we could run the AP trial balance report with a class ID restriction for MERCH vendors only.

Relatively easy solution to get the customer what they needed.

Using Excel Report Builder

Excel Report Builder is an easy way to create new reports and connect with GP data. It is licensed as part of the SmartList Builder module.

To access the Excel Report Builder:

Tools >> SmartList Builder >> Excel Report Builder

Some of the particular features I like are:

You can add MS SQL Server tables and views to your reports and join them with GP data
You can add calculated fields, selecting from a wide variety of functions
You can select multiple companies and create a summary page
You can publish to the Reports Library in GP, SharePoint, or a file location
The connection to the data is automatically created and stored in the spreadsheet
It’s easy

Here’s the link to the User Guide.

This is one I created to look at the purchase requirements generated from MRP:

1.) Enter Report ID, Report Name, Report View
2.) Add table: MRP1010 – PR_Short_Items_Work
3.) Select the fields to include
4.) Publish >> Permissions
5.) Publish

If you selected to have the report publish to the Reports Library, it will be available immediately.


Payroll year end for 8.0

Got a funny email from MBS last week. For several months we’ve been complaining to MBS about not supporting Dynamics 8.0 for payroll year end. Seems wrong for Microsoft to say 8.0 is a supported version……..but no payroll year end update will be provided. Had a few clients scrambling to upgrade (they should have done this already but haven’t). Deep down inside I was crossing my fingers that MBS was going to break down and provide the update anyways.

Well I get an email from Terry Heley at MBS (Great payroll resource to be in contact with) saying the payroll update has been released for Dynamics 9.0 and 10.0 and there has been no changes from last year to the payroll forms.

What does that mean? Well those on GP 8.0 can probably squeak by without upgrading (at least untill October 2009 when 8.0 is MBS un-support-able) as no year end file is needed to be run other than for a few scenarios. You can also manually update the tax tables for 2009 with a SQL script.

Moral of the story for customers – Upgrade when recommended by your VAR. Last minute upgrades are often bloody and painful especially if your system has any 3rd parties or customizations. You also run the risk of Microsoft not supporting critical business processes such as payroll if you wait too long.

Moral of the story for Me - Crossing your fingers does work……sometimes.

Payroll ACH file deletion

Had a customer call today that had lost a build for 800 payroll checks in the ACH Generation window. The payroll person had mistakenly deleted the build and the direct deposit ACH file was gone. (See tech doc 865591 that says this can’t be brought back).

They have not been doing a backup before processing payroll which is recommended. I should be more clear. Doing a backup before processing payroll is extremely important. Most of the time processing payroll is completed without errors. On the occasion where there is an issue, the payroll and employee files are completely messed up. Event such as power outages, server restarting with/without explanation, computer crashing while posting, etc. will wreak havoc to the payroll sub module. Often there is no recourse besides restoring to a backup.

There was no backup before payroll processing. A full days work had been done by many departments. Looked like there was no solution…..we did come up with an acceptable work around however. After discussing we decided that they would:

  1. Restore last nights backup to a test company. (The payroll batches had been entered the previous day.)
  2. Re-run the payroll in the test company.
  3. Verify numbers from summary reports in test company versus live company
  4. Create the ACH file from the test company. They had an old ACH file and could look at the header and change information where appropriate.
  5. Recommended working with their payroll processor (Wells Fargo) to make sure the ACH file was correct.

Luckily there was a fairly simple work around instead of voiding out the payroll checks and reentering the information again. Once again, do a backup before processing payroll.

Identify Items That Don’t Sell

I initially came across this gem on the Dynamics GP public newsgroup.

Mohammad Daoud provides a nice SQL query to identify inventory items that have not been selling, i.e. slow moving items. My experience is that this is information that many businesses could use. His post is here.

Backup routine

It’s amazing how many times I get calls from people that have a catastrophe with their GP system but have no backup. Yesterday I finished up a call with a user that had an LDF file (GP log file) magically disappear. The log file itself was a second log file that is used to speed up transaction log performance since their database is about 25 GBS.

“Didn’t seem like that big of an issue just to have a log file deleted” said the user. With the disappearance of this log file users had not been able to get into GP for 3 days. The backups that should have been run nightly had not been done since late September. The drive that the backups were scheduled to was a removable drive that was removed and not replaced in September. No backups were able to be done and the log file had grown to 27 GBS which used up all the space on the data drive and kicked SQL into single user mode. After several calls and attempts at restoring the MDF file from early November we ended up having to restore back to September 28th. They lost an entire months work because no backup were being done.

Here are some suggestions to creating a successful backup routine to avoid situations like this:

  1. Make checking to see if backups are being done a weekly (at least) job requirement for the IT department in your company. As IT people come in and out of the company, make this a department responsibility so you don’t have the unfortunate/frequent occurrence of new people saying “the person responsible left and no one was checking backups”.
  2. Have the accounting department also check to make sure backups are being done. Surely with two departments checking this will be monitored sufficiently. Place a shortcut on the accounting persons desktop so they can verify they see a BAK file in the backup folder on the server.
  3. Place all modified reports files on the server so these files can be backed up nightly as well.
  4. Make sure all files are being backed up that could cause you loss of data. For example – The actual .BAK file generated with SQL jobs for both the dynamics and company database. Reports.dic , Forms.dic and any other modified dic file for 3rd party apps. FRx Sysdata folder, integration manager IM.MDB file etc, etc. Ask for recommendations from a consultant that knows your system.
  5. Make sure backups are stored at a different location than the actual data. Main idea is to not store backups in a place where you will loose both backup and data if a server goes down. Often suggested to take/store backups off site.
  6. Restore backup to test company periodically to test backups are being created successfully. Also gives you a playground to test things on with recent data.
  7. If you are a user think about saving a backup of the reports.dic file, FRx tdb file (export of FRx reports), im.mdb file etc. on your local workstation after you make changes to reports. This will hopefully be another help in the event of lossed data.
  8. If something happens to your GP system (eg. server crashes) contact help immediately. Don’t touch a thing and don’t wait 3 days before requesting help.

Any other suggestion?

The Sneaky Cost Variance – No Mystery Here!

Have you ever had a GL transaction that just seems to appear out of nowhere? As a consultant I hear about magical transactions from clients all the time. With a little digging we always seem to prove that there was no magic involved at all.

Yesterday I almost had a Proof of Magic Experience … and then … the nature of Cause and Effect reared its (oh yeah…duh) ugly head!

My client has a user who does not have security access to any access to GL related functionality. She does shipping and receiving along with SOP invoicing. She had created a GL series transaction. How?

The controller found a strange transaction which originated in the GL and advised me that “the user who created it” is the same user who has no access rights to do GL transactions. It appears to have mysteriously created itself between 2 receiving transactions. The receiving transactions are correct in that they came from Receiving and they debt accrued purchases and credit inventory. How could a user who doesn’t have access to create GL transactions make that transaction?

I checked it out myself and sure enough, there is a GL series transaction posting to COGS and Inventory. Several head scratching hours and a Microsoft Support Incident later, sure enough, we figured out the “physics of the matter.”


1. There was a negative quantity on hand for an item at 9/30/08.
2. The User shipped out (invoiced via SOP) product that she didn’t have.
3. On 10/1/08 she “received” in the purchase order that was to fulfill this sales order.


1. The Inventory only had the cost from the existing inventory item to debit COGS when it was sold.

2. The current cost for the item (which was significantly higher then prior purchases) came from the purchase order that was received in after the SOP invoice was created. The current cost in the system differed from the purchase order cost.

Ah Ha! Mystery solved. Dynamics GP automatically created this GL series transaction to account for the cost variance – something our Shipping/Receiving/Invoicing User would never have thought of when she overrode her item shortage at invoicing.

Lesson 1: A person with no GL security rights can create a GL transaction (at least get the credit for it!) Don’t let this be a red-herring.

Lesson 2: There are 4 situations in which an automatic cost variance can occur.

Lesson 3: There are no mystery transactions in Dynamics GP.

I have referenced a really good KB Article that Microsoft sent me. It is very clearly written with examples that will help you explain the unexplainable to your client.

KB 869470- Cost Variance for Inventory

Mass Modify and Account Description

The mass modify tool in Dynamics GP is a good way to add a new set of GL accounts for a particular segment of your chart of accounts. For example, you could copy a set of accounts for one location and add a new location with the appropriate account number. (I.E. 01-4100-00 can be copied and 02-4100-00 can be added). Mass modify can be found at Cards>>financial>>mass modify.

However, the accounts that were copied will have the same description as the older location. So if you identify the location in the description (phone expense – San Diego) the new accounts will have the old description. If you want to update the new description you can run a query in SQL to update the description. Below is a sample update statement:

UPDATE GL00100 set actdescr = replace(actdescr, ‘San Diego’, ‘Los Angeles’) where actnumbr_1 = ‘2‘ and actdescr like ‘%San Diego%’

In non technical terms this query says replace San Diego with Los Angeles where ever my new location (2) has the words San Diego in it. You will have to replace the bolded red fields with your information. My location was listed at the beginning of the GL account (actnumbr_1) but you can change that to be whatever segment you have updated.

I would recommend running a select statement on the GL00100 table first and verify you want to replace everything found in the results. Here’s a sample query to do that.

Select * from GL00100 where actnumbr_1 = ‘2‘ and actdescr like ‘%San Diego%’

Once again replace the bolded red fields with your information.

Doing the above procedure saved a client from having to update around 100 accounts manually.

Can you think of any other find and replace uses for SQL?

Macro Mania

I discovered another mis-use of Excel this week. We have a client that receives a transaction download from their bank. They then import this into Dynamics GP to create general ledger transactions.

The problem is that several years ago someone crafted a nifty Excel spreadsheet to format the data in order to facilitate the import into GP. In the spreadsheet there is a hefty reliance on macros to organize, edit, and fill the source data. It may have worked initially, but now it does not; and is not worth the trouble to fix.

We will most likely develop a simple SQL Server / program code application to do the work. It will most likely take less time to deliver, and cost the client less.

Excel is a wonderful tool for analysis, and reporting; and for modeling business processes. It is not a good tool for reliable, well controlled business transaction processing.

Subscription Options:
Subscribe via RSS
Articles Categories