How to void partially applied invoices in Dynamics GP

I should have put this in my top technical support issues blog as this is about as common of an issue as it gets. I can't tell you how many times I've heard ^$%#@$! Great Plains. I can't void out a payment because it's applied to an invoice that still has amount outstanding. I also can't void out the invoice as it has a payment applied to it.

This is one of the most rinky dink processes but this is the resolution for both receivables or payables:

1. You have a payables or recievables invoice that has a payment or credit memo applied to it. The credit document is fully applied so is in history
2. You cannot void the credit document or invoice
3. You must create a dummy credit document for the amount outstanding on the invoice
4. Post the credit document
5. Apply the credit document under the apply window
6. You can then void out the payment, dummy credit document and invoice if you desire

The same would be true if the invoice is fully applied and there is still an outstanding amount on the payment. Create a dummy invoice and apply it to the payment. Then you can void the payment and the dummy invoice.

As Mariano says, "Don't shoot the messanger".

Should you backup your database before processing payroll?

A client called yesterday and were in a bit of a tizzy. They processed payroll last month and in the middle of things GP crashed. They said a few cuss words, logged back into GP then continued processing. When they went to do their period end reports they were a little taken back because gross wages was off by $13,000,000. I had to write that out just to see how many zero's that is.
A few questions came out of the CFO's mouth such as:

• How could this be?
• Did they really send a check out to an employee for that amount?
• Is our bank reconciliation off?
• Is our GL amounts off?

We discovered an employee with the below information:

My first question was - "Are you sure this employee is still in the country because if I received a check like that, I'm heading straight to Samoa for an indefinite period of time."

They chuckled a bit but I don't think they found my comments to helpful.

The payrun that caused this was of course two weeks ago so restoring from a backup was not really an option. This made me pull out my SQL skills and go to work. I used tech doc 948268 to resolve the issue but here are the basic steps:

1. Backup Company databases
2. Make sure you backed up the correct company database
3. Delete the payrun in question from the following tables using these scripts. Replace XX with the correct Audit trail code. delete UPR30100 where AUCTRLCD='XX' delete UPR30200 where AUCTRLCD='XX' delete UPR30300 where AUCTRLCD='XX' delete UPR30400 where AUCTRLCD='XX' delete UPR30401 where AUCTRLCD='XX'
4. Delete the employee's summary information by using this script, replacing YY with the employee ID and ZZZZ with the year in question. delete UPR30301 where EMPLOYID = 'YY' and YEAR1 = 'ZZZZ'
5. Run Reconcile in payroll - Point to Tools on the Microsoft Dynamics GP menu, point to Utilities, point to Payroll, and then click Reconcile.
6. Deal with bank reconciliation module and GL as necessary

I ended up deleting one table that had 450,000 rows duplicated for one transaction. Thus the 13 million dollar employee card.

Now the real question is should a backup be done before processing a payroll batch? The answer is a resounding YYYYYYYEEEEEEESSSSSSS!!!!! Does that mean everytime? Let me be clear. Everytime you process payroll you are at risk of having your payroll module blow up. The above was a fairly easy fix compared to what can happen. I wish I could be more upbeat about how great GP is and how solid payroll is as a module.

Another thought. We have a couple of clients that all they do is payroll. They process thousands of transactions a week and in any given day they have several people posting payroll batches. To back up their database takes 10 to 20 mins. To do a backup each time is not really feasible. So after explaining the situation clearly they have decided to risk a days worth of work for irregular event of having the payroll module messed up with a posting interruption.

Talking with Mike from our office about this event and he told me how he fired someone when he was a controller when they processed payroll without doing a backup. The one and only time they didn't have a backup resulted in heartache, gnashing of teeth, Mike flying off the top ropes with a 10 key....you get the drift.

Here is a link to backing up your GP system.

Please consider making a backup before processing your payroll. One time in a thousand you'll actually need it but you'll thank your lucky stars when you do.

How long....before applying new SP's?

As MBS has just released SP 4 for Dynamics GP 10.0 I've had a Elevation of calls come in about updating. I'm more than happy to point clients in the direction of the new SP but I usually pose the question How Long do you usually Walk On before applying a new SP from MBS (Lots of U2 references, what more can you want in a blog).

I've heard anything from "as soon as it's released" to "we Stay a full SP release behind."

I usually recommend waiting for a month before applying a new SP for a couple of obvious reasons. Number One and two:

• SP releases fix issues but occasionally cause problems as well


• Installation issues Mysteriously occur. See Mariano's from the trenches post

So why would you Desire to update as soon as the next Sweetest, Magnificant SP is released? I would suggest only after fully testing on a test system and only if you need a fix that is resolved in the SP release. 10.0's fix list is here in the installation guide. Other than that why not wait a short while to let everyone else have a Bloody Sunday.

So How Long (older version of song) do you wait before installing a new SP?

Still haven't found what you're looking for? Hope you don't have Vertigo.

Have a Beautiful Day. I know I will....With or without you.

To upgrade or not to upgrade Dynamics GP

A client of ours uses Encore's Recurring invoicing in conjunction with Nodus's Credit Card Advantage. They are still on Great Plains 8.0 and are using EFT transactions as well. The need has arisen for customers to use multiple EFT bank accounts for different contracts in Recurring Invoicing. Can't be done in 8.0 but in 10.0 you can have EFT accounts attached to different address ID which can then be used as a billing address on the contract.

My answer to them is you need to upgrade to get that functionality. The accountants response was "What the heck. Why haven't we updated yet?" I would have liked to say "I've tried to get you guys to upgrade for a year or more now" but instead I was diplomatic and said "I think your IT staff is in the process of following some of our recommendations to move up to the new system. You may need to check with them."

Coming from the Partners viewpoint I usually say after one or two service packs you can upgrade whenever you like. That's not always necessary/feasible. The real issue comes as upgrading Dynamics GP (or any software for that matter) comes with certain costs. A few are listed below:

• Annual maintenance costs from Software vendor- typically a percentage of you system list price


• New hardware to meet new software recommendations


• Consulting or in house time to do test upgrade - includes time spent verifying data upgraded successfully, 3rd party application testing, interfacing with end user to verify everything looks ok, etc.


• Consulting or in house time to do production upgrade software, install clients, update reports, install 3rd party applications, upgrade 3rd party applications, and fix modified reports that don't update, etc.


• Downtime for current employees during upgrade


• Training users on new features or procedures


• Ineffiencies while using/navigating through a new system


• Increased support costs with Partner after upgrade specifically related to new functions and procedures

So why in the world would anyone volunteer to update their system as it seems like a new version is released every 6 months? I've listed a few reasons to upgrade below:

• Increased effiencies with new hardware, system performance with updated software


• Older software is not supported and you have to find a dinosaur that charges $400 per incident to fix anything. (I know the two last dinosaurs that support GPA, man are they old and ornery)


• New technology only available with new versions of software - Business Portal, eConnect, Sharepoint, SSRS, etc.


• New versions are being fixed, developed, enhanced so in theory you should have less support calls regarding system bugs


• Upgrade costs are minimized when system moves to the next version up instead of making a 2, 3, or more step move. See Mariano's blog for upgrade paths.


• Training usually is less involved as there are not 2 or 3 sets of "What's New" crammed into one training session


• Newer version of software is typically more developed. If you are still using the first version of CRM, RMS, Analytical Accounting etc., I'd suggest you are akin to gnawing your foot off to get out of a bear trap (Montana metaphors, got to love it).

So what do you think? How do you determine when to upgrade?

What are some of your reasons To Upgrade or Not to Upgrade?

Here are a couple of links to formulate my thinking:

• MBS Marketing blog (of course they want you to upgrade)


• David Musgrave better not convince MBS to extend 8.0's life cycle policy again


• Andrew Karasev

Setup New Fiscal Year in Dynamics GP

It's summer time in Montana so that means fly fishing and farmers markets. Oh how I love summertime. All two months of it. (It might end up only one and a half months this year as it's barely starting to warm up now).

Usually this time of year also brings a rash of calls regarding Fiscal Year setup in GP. Fiscal year setup is a very simple thing to do but when you only do it once a year it sometimes intimidates the faint of heart.

Here's how to setup a new fiscal year:

1. Navigate to Dynamics GP>>Tools>>setup>>company>>Fiscal Periods
2. Put your cursor in the year field and type in the new fiscal year (I.E. 2010)
3. Press Tab
4. Verify the dates are correct in the date fields
5. Verify the number of periods you want in the year. Typically 12.
6. Choose calculate
7. You can then check off the periods you don't want anyone to post to yet. You can open these whenever you desire.
8. If you already have these periods setup you may just need to uncheck the modules to allow posting.

Couple of notes:

• Make sure the dates don't overlap any other year
• Make sure you have all dates covered. GP doesn't like any days missing from one year to the next

Backup, Backup, Backup your Dynamics GP System

I've been a lazy blogger the past month or so. Seems like my writers block coincided with our move to our new blogging location on RoseBizInc.com so hopefully this entry will get me back in the spirit. If I fail miserably to excite, inspire, inform or educate anyone who reads these blogs please don't tell me so. I'd rather live in a spirit of happy ignorance than to really hear your true opinion.


Had an interesting happening a few weeks ago. Had a client that has project accounting, purchasing etc. We were seeing some rather interesting results in the POP module so we ran check links on the purchasing transaction table. No problem. We then ran it on the purchasing historical transaction table and let it run during the night. Looked at it in the morning and noticed a check links report that had 4500 pages of removed historical transactions. Yikes. If you don't know GP:


1. Why are you reading this blog

2. That is not a good thing

3. Check links is sometimes refered to as "Break Links". It's a tool used to verify data in the tables and will either say it's all good, add back in info that's missing, or remove data not needed.


So no sweat, lets restore to last nights backup. System guys says, cool, I'll let you know when it's restored. Now in this situation what would be the worst possible thing that you could hear when you pick up a call from the systems guy. In my case it was "uh, we got a problem." Turns out they have been doing backups to the same tape drive for the last month. Each night it wrote over the previous nights backup. And when they went to restore the backup some how they managed to delete their one and only backup for the last month and a half. Turns out the guy responsible for backups was in Costa Rica on vacation for another week so.... It's funny now that I look back on it.


There's more to the story but I'll leave this post with a moral:


When in doubt (or even if you are not), BACKUP, BACKUP, BACKUP your Dynamics GP system. Here is an older post I did about backing up GP in case you want to make sure you are backing up all aspects of your GP system.

MSDE file full error Dynamics GP

Had a client using MSDE and was getting space errors whenever trying to process any transactions. Couldn't post at all, just print reports. We looked at the size of their database and found it was 2 GBs. As you may know there is a 2 GB space limit on MSDE so I suggested updating to SQL Server Express Edition 2005 which has a 4 GB restriction. It is free and would double their storage capacity.

The client thanked me profusely and said they never heard of Microsoft giving anything away for free. I agreed but told them it's not exactly free as I would charge them for my time which ended up being 3 hours when all was said and done.

Here is the process I used to update the client from MSDE to SQL Server Express Edition 2005:

1. Call MBS Sales Ops ((800) 456-0025) and get SQL keys for GP. These should be free but if you don't have them GP will give you nasty error messages. Do this first. I actually did this last and it took a day to get the stupid keys. When you have the keys go to step 2.


2. Download SQL Server Express Management Studio 2005


3. Install the SQL Server Express Management Studio (Do this first if you want to use it to create your backups.)


4. Make a backup of the company and Dynamics databases


5. Run Capture login script on current install of MSDE. See Tech doc 878449. Script is found here.


6. Download SQL Server Express Edition 2005


7. Install SQL Server Express Edition. Mixed mode. (You may need to allow remote connection in SQL setup if it doesn't default). You can install SQL in one of 2 ways. Upgrade the existing instance of MSDE. (I tried this and it failed on the upgrade). Or install a new instance (Default instance is SQLEXPRESS, call it whatever you like). You will then need to restore the Dynamics and Company databases to the new instance.


8. Run the results of step 5 against the new instance of SQL to recreate your users.


9. Put in new reg keys into Dynamics that you got in Step 1. Dyn utilities on any version pre 10.0. In version 10.0 you will need to run this script (delete sy003500) then enter reg keys in application. See tech doc 943965


10. Update ODBC to point to new instance of SQL

This is similar to the procedure of moving SQL to a new server.

Unable to Open Customizations Dictionary

Microsoft Dynamics Great Plains uses a reporting tool called Report Writer. I’ve given it somewhat of a bad rap in the past. I hope I haven’t hurt anyone’s feelings with my disparaging words.

One of the most common errors I see people referring to is the “Unable to open customizations dictionary” error when trying to import a new report into the reports.dic file. The resolution to this error is talked about in tech doc 919440 and 869323. Nine times out of Ten the resolution includes getting everyone out of GP then trying to import the file again. (Tools>>customize>>customization maintenance).

Error message something like this:

I suppose if you don’t have much to do, or have more than a few users and those users don’t have that much to do, it’s not really a problem to kick everyone out of the system. But often I’ll hear “Oh fiddle sticks, I’ll have to wait till tonight to do this” as there’s no way to kick everyone out of the system during the day.

When I have the patience and energy I use the following steps to bring in the new reports so that users can still be in the system and the import is still successful.

1. Make a backup of the Reports.dic file
2. Save your package file where you can browse to it from Dynamics
3. Change the path to the reports.dic file in the dynamics.set file to a new location. (By default the Dynamics.set file is in the GP folder where Dynamics is installed. Open with notepad and the reports.dic file is the 3rd line down where the paths start to all the products.) Change the path to a new location or simply change the reports.dic file path to end with reports2.dic instead of reports.dic
4. Launch Dynamics and open Customization Maintenance (Tools>>customize>>Customization Maintenance)
5. Import the new report from the package file to the new reports2.dic file
6. Exit out of Dynamics
7. Change the reports2.dic path back to what it started with in step 3, in our case ending with reports.dic
8. Launch Dynamics and open report writer (Tools>>Customize>>Report Writer)
9. Choose Import on the right
10. Browse to the Reports2.dic dictionary file, highlight the report and press insert (You may be asked to overwrite the current report if you already have that report in the system), then press “Import” at the bottom to bring in the report.

Not the easiest but works without getting everyone out of the system.

FRx Top Technical Support Issues

FRx has been around forever and is the most often recommended tool to do financial statements for the Dynamic GP and SL. The life span is coming to an end in the distant future but will still be around for many years. Over the past several years I've made a most common technical support list for FRx. FRx buzz has a top 12 tips list you can check out as well.

Here is my top trouble shooting list in no particular order:

1. Can't launch FRx from GP (Reports>>financial>>FRx) - Tech doc 850786 - The path to FRx is stored in the FRXDYN.INI file in the Great Plains directory on the workstation. If the path was entered incorrectly rename the FRXDYN.INI file, launch FRx again from Great Plains, and choose the correct path to the FRX32.exe file on your particular workstation.



2. Missing accounts in BS or P & L (or financials don't tie) - FRx does not automatically update accounts added in GP. Run exception report in FRx then add accounts that are missing or duplicate.



3. How to create a budget variance report- See Tech doc 862445. Also shows you how do do the budget variance for certain rows. See previous post on how to get budget into GP step 6.



4. Locked rows, columns or trees- Compact current spec set and system database - Tools>>compact FRx database. This solves lots of issues and is one of the first things you can do for maintenance purposes.



5. Invalid work drive error - Tech doc 865772 for citrix, 864623 non citrix - For a temporary fix you can delete the Optional Work Drive path found at Admin>>processing options.



6. Can't see any accounts after page break - Use PI for a page break instead of PB. See tech doc 859445.



7. FRx won't take new year date period or Change default Base Period to C when opening a catalog in FRx- Tech doc 862372 - Delete .G32 files in Sysdata folder. I do this almost everytime I start troubleshooting as it solves a lot of issues. Automated solution for 10.0 is found here. 9.0 users can find it here. Heck, since I'm so nice here's 7.5 and 8.0.



8. How do you share FRx reports with multiple users- Tech doc # 854726- Basically export out all reports in Company>>spec set>>highlight all catalogs, rows, columns and trees and save to a TDB file (just in case). Copy Sysdata folder that you want to make shared and paste in a network location. Point all workstations to this folder in Admin>>organization>>sysdata. If you can't change path there see step 14. Make sure this is being backed up with your nightly backup routine.



9. Error 91 - Sucky error to get. As you see it could be one of 16 issues from tech doc 874276. It's often a permission issue or DLL issue. (DLL automated solution here.)
   


10. Error 8900- tech doc 912977 - Also have seen a company ID on a tree not be a valid company in GP cause this error.



11. Where are the FRx service packs? See MBS link


12. Can't launch FRx from GP - The launch file for FRx is FRx32.exe. The default location is C:\Program Files\FRx Software\FRx 6.7\FRx32.exe. You can put that path in the location in Reports>>Finanicial>>FRx. If that launches something other than FRx report designer you can change the path in the FRXDYN.INI file in the GP folder on the workstation.


13. OFSI errors - Tech doc 858323 - Similar to 91 and 8900 errors.


14. Can't change sysdata folder in Company>>organization>>sysdata - Update path manually in the FRx32.CFG file located in C:\Program Files\FRx Software\FRx 6.7\FRx32.CFG. Make sure there is a \ at the end of the path or it will error out.


15. Can't log into FRx. Usually the company>>information setup is incorrect. See tech doc 865836. Sometimes it's an ID-10-T error. FRx uses the same SQL user as GP. Make sure the login ID is the same as GP not the network user ID that defaults in FRx. Must be exactly the same as GP. (eg. User ID - ted can not be TED.)

Any other common issues I'm neglecting?

Can't print report after service pack, hotfix or tax update in Dynamics

I appologize to my faithful blog follower Chris G for not being as prolific this week. Chris emailed to remind me I have at least one person reading these posts. Thanks for the encouragement. This ones for you Chris.

I referenced this issue in a previous blog but let me give a few more details. The screen shot below is a GL trial balance that would not print after a tax CODE update. Dynamics simply crashed without giving any useful error. (this was an MSI file that was installed, went through the "include new code" routine in gp.) I'm assuming that this will be an issue with all updates in the future until the GP community revolts and refuses to use report writer any more.



The thing to note is this was not a modified report but it was still giving an error. I tried updating the modified forms and reports like is says in the documentation for SP's but received the same response (GP crashed after generating the report below). I recreated the reports.dic file (see steps below) and it fixed the issue.


Don't know if you can tell from the screen shot but the account numbers were all messed up with a box in the second segment and all spaced out. Click on screen shot for a better look.


Personally I would just do the below process and recreate the reports.dic file when doing updates from now on.

1. Have all user Exit out of GP
2. Save a copy of the reports.dic file
3. log into gp and export out all reports etc. by going to Microsoft Dynamics GP>>tools>>customize>>customization maintenance
4. Select everything in the window and choose export
5. Save reports - I usually name it something like allreportsdate.package
6. Exit out of GP
7. Do the SP, Hotfix, Tax update
8. Delete reports.dic file
9. Log into GP
10. Go to Microsoft Dynamics GP>>tools>>customize>>customization maintenance and choose import
11. Select the file you exported in step 4 and choose Open, then OK. If you receive an error about "unable to open customization dictionary" you probably had someone sneak into GP and it can't import because of it.

Reports.dic file and updating SP's in 10.0

Got the below email from Terry Heley (MBS payroll lead) in conjunction with the new payroll update coming out today. Sounds like a major pain for any SP updates but does sound like job security for Dynamics support people.

Basically, Make sure you update Reports.dic before you do anything. I'd probably export out all reports etc using customization maintenance. Then update the reports using Utilities (I'd probably say a prayer, cross your fingers, commence any other helpful acts of meditation and serenity etc.)

Haven't needed to do this for a SP yet but my guess is creating a new reports.dic file and importing in your old reports after the SP update would be an easier way to update the reports after a SP.

Notice no error messages will be given if you don't do this step other than some probably errors when you run the report. Nice one MBS.

*****************
A new requirement has been added to the Microsoft Dynamics GP 10.0 service pack, hot fix and compliance release install process. All modified reports and forms must be updated in Microsoft Dynamics GP Utilities following the database update. In the past this was only a requirement for the major release update, such as from GP 9.0 to GP 10.0. Because of data model changes, etc. that are now in the MSP patch files, the modified reports and forms must also be updated. This new process does not apply to Microsoft Dynamic GP 9.0.

The FTE System Team and VMC System Team handles the support for all service packs, hotfixes and compliance update installations on GP 9.0 and GP 10.0. The support for the new modified reports and forms update process on GP 10.0 will also be handled by the FTE and VMC System Teams.

PLEASE NOTE: If a user launches into Microsoft Dynamics GP 10.0 without updating the modified dictionaries, they WILL NOT get prompted to update the files. Therefore, the user might see errors running reports and accessing forms. If you have a case where a user is getting errors running a report or accessing a form following a service pack, hotfix, or compliance update, please ask if they went through the modified dictionary update process following the MSP patch install.

This new process has been added to the U.S. Payroll/Canadian Payroll Documents and the 10.0 Hotfix Install Guide. A new Hot Topic has also been published. The current hot topic indicating that GP 10.0 patches may damage the reports and forms will also be updated with this new process.

Here are the links to the new Hot Topic that is live right now. Once the March hotfix is available, this link will also be available on the hotfix download page.

CustomerSourcehttps://mbs.microsoft.com/customersource/support/selfsupport/hottopics/hot_topic_mdgp10_modifiedreportsformsupdaterequiredforpatchreleases.htm?printpage=false

PartnerSourcehttps://mbs.microsoft.com/partnersource/resources/support/selfsupport/hottopics/greatplains/hot_topic_mdgp10_modifiedreportsformsupdaterequiredforpatchreleases.htm?printpage=false

No beginning balances in FRx balance sheet

I should have included this in my last post about the most common technical support questions. Usually around this time of year I get a rash of calls about how Dynamics is broken. Seems like all the balance sheet accounts don't have beginning balances even though their P and L have correct numbers.

Dynamics has been this way since the beginning of time. You will not have beginning balances until you close out the previous year (P and L accounts start with $0 each year so they are not effected). I know what you're thinking. "I'm not ready to close out the year yet". I usually have 2 responses to that objection.

1. You can still post entries to the GL to the most recent historical year. So even if you close out the year you can still make your audit adjustments etc. If you do choose this option be aware that the entry will freak you out. It will look like it posted the entry twice. The first is the original entry, the second is the system automatically adjusting the beginning balances for the open year. The second entry is always on the last day of the fiscal year (say 12/31/20xx). If you made your entry in the historical year on 12/31/20xx it will look like it's duplicating. No worries. That's what the system is supposed to do.


2. Do some fancy formatting in FRx. I would recommend a column layout similar to the below screen shot (click on picture for a closer look). The first 2 GL columns would be non printing columns. The first GL column give you YTD numbers for the 12th period of the previous year. The second column gives you YTD numbers for the current year. Column D adds both together.

You would probably want to save this as a seperate column layout so you can adjust back the original layout after you close out the year.

Tricky way to allow you to get a BS and procrastinate your year end close.

Dynamics GP Top Technical Support issues

I was asked what issues I deal with the most often. I'm sure there are many others but these are the first ones that come to mind along with the resolutions below.

Here's my Top 13 support issues list in no particular order:
1. Batch stuck in posting, receiving, etc. status
2. Where do I find the table that holds a particular set of data?
3. My sub-module doesn’t tie to the GL
4. How do I set up a new fiscal year?
5. I can’t login to Dynamics – Unknown dictionary error
6. How do I import budgets?
7. Dynamics won’t let me post
8. I have a metrics error on my homepage
9. How do I move Dynamics to a new server?
10. After closing the GL I realized I have incorrectly listed a BS account as a P & L account, now there is not beginning balance. (or vice versa)
11. How do I adjust then print 1099’s?
12. When trying to access sales transaction entry I get the error "Your previous transaction-level posting session has not finished processing".
13. Inventory shows it is allocated when it’s not

Resolutions:

1. Doug Pitcher blog post - http://www.rosebizinc.com/gpblog/2009/01/stuck-batches-in-dynamics-gp.html. Dave Musgrave comment gives script that works well.
2. Blog post by MBS employee Dave Musgrave. http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/05/finding-table-and-field-information-in-microsoft-dynamics-gp.aspx
3. See Steve Chapman post. http://www.rosebizinc.com/gpblog/2008/02/reconcile-to-gl-easily.html
4. Tools>>setup>>system>>company>>fiscal periods. Type new fiscal period in the year box and press tab. Make sure all periods are correct then press calculate.
5. Usually permissions error when network ID doesn’t have access to reports.dic file. Find path in Tools>>setup>>system>>edit launch file>>enter system password>>Highlight dynamics GP, look to see where Reports line is pointing to. Open a browser and try to path to this location. Need
6. Cards>>financial>>budgets. Use import wizard by choosing New>>using budget wizard for Excel.
7. Print edit list of batch (printer icon at top right hand corner of batch window). This will tell you why you can’t post
8. Tech doc: 918313. Install Office Web Components link, question and answer #21.
9. Doug Pitcher blog: http://www.rosebizinc.com/gpblog/2008/08/moving-sql-to-new-server.html
10. Tech doc #864913
11. Doug Pitcher blog: http://www.rosebizinc.com/gpblog/2009/01/dynamics-gp-1099-printing-for-90-and.html
12. Tech doc #852623. Run scripts to release captured user.
13. Run inventory reconcile at Tools>>utilites>>inventory>>reconcile. Everyone needs to be out of GP (well sop, pop, inv or any module that touches inventory)

I'd be interested in seeing your most common lists of frequently tackled issues in GP. Any issues come to your mind other than the ones above?

Dynamics GP 10.0 year end close issue - 'GL_account_SUM_HIST_View'

Ran into this yesterday and thought I'd post it as I didn't see any tech docs anywhere on it.

A client updated from Dynamics GP 8.0 to 10.0 in October. The client was just closing out the 2008 year but ran accross the following error message near the end of the close just before the report should print.

A get/change first operation on table 'GL_account_SUM_HIST_View'. Followed by "Number of results columns doesn't match table definition" after you click OK.

All the rows had moved from GL20000 to GL30000. The fiscal year period setup was not updated to be a historical year. Neither was the last close date adjusted to the new year in the GL Year End Close window under routines.

Here is the recommended approach to fix this issue:

1. Restore backup before year end close was attempted


2. Go to Dynamics GP>>Maintenance>>SQL


3. Choose the company database


4. Highlight Account Summary Historical View


5. Check off all boxes to the left (See screen shot below)


6. Run Tools>>Utilities>>Financial>>Reconcile on all years, starting with oldest first


7. Rerun year end close routine
8. Verify all BS balances have update and all P & L accounts have closed out to RE

If you don't have a backup to restore to you should be ashamed of yourself. Here is an alternative approach I used at first until I decided I'd be more conservative and restore from a backup first.

1. Back up company database
2. Check off historical year in the fiscal period setup window
3. Update LSTCLSDT in GL40000 to be date desired (12/31/2008 in our case). Script would be something like Select * from GL40000. Should only be one row in table. If so then use this script: UPDATE GL40000 SET LSTCLSDT = '12/31/2008'.
4. Go to Dynamics GP>>Maintenance>>SQL
5. Choose the company database
6. Highlight Account Summary Historical View
7. Check off all boxes to the left
8. Run Tools>>Utilities>>Financial>>Reconcile on all years, starting with oldest first
9. Rerun year end close routine
10. Verify all BS balances have update and all P & L accounts have closed out to RE

This second approach doesn't give you the normal report after the year end close since all the rows are already moved to GL30000.

Don't know what caused the issue. Guessing it's because of the upgrade. Maybe the GL10111 table got corrupted somehow. It happened on every company until we did the above process.

2008 Payroll year end close update Dynamics 10.0

Bear with me a moment while I grumble and I'll get to YEC for 10.0.

Great Plains isn't what it used to be. That's both good and bad I suppose.

It is good that all workstations should be on the same SP version to be able to log into a company. This saves many significant issues popping up as most are well aware. Workstations used to always be out of sync and a lot of the time the resolution would be "Apply this patch like all the rest of the workstations. etc."

However it is frustrating when you only need to apply the year end update for payroll. In times past you could put the cnk file only on the payroll persons workstation and they'd be good to go. It's a major effort now to apply any service pack. It almost feels like people are being pushed into a Citrix/Hosted or Terminal Server model. There are major advantages of only having to apply SP's once at a central location. I know you can have the SP automatically apply the next time a user logs into the system but that is mostly just a huge pain none the less.

Ok, 10.0 year end update. Here are a few things I've come across while going through the year end update. Most are pretty user specific but here they are none the less.

1. All people must be out of the system. Sounds like a no brainer but we spent several mins troubleshooting why the system tables wouldn't update then found some people had snuck into the system. (Hopefully Mike's head doesn't hurt too bad from hitting it repeatedly on the desk in front of him.)


2. To install the update make sure the installation of GP is at least the first recommended release of 10.0. Had one client on 10.0.465 which is a beta release of 10.0. Who knows how they got that one installed. Kept getting this erorr when trying to apply the update. Applied 10.0 Feature pack 1 which includes SP3 (1.5 gb download so don't do this with a 56k modem), applied the update and they were good to go.
3. You can run the update from a client workstation. Don't need to be at the server. Kind of counter intuitive but it works. After corrupting GP on the server during resolution of #1 we got the update to go on a client workstation. (Thanks to Jamie Nelson on this one).
4. Plan for several minutes per database. I've timed several updates and it seems like it runs around the 20 min range per database. This is true when the databases are pre SP3. Not sure if it's quicker if SP3 is already applied. (Anyone know this?)
5. You must run the year end wage file before applying 2009 tax tables. Duh.
6. You can't run a 2009 payroll before running the year end wage file. Duh again. ha.
7. User gets kicked out of GP when running payroll reports after applying update. Recreated reports.dic as it was corrupted in the process. So....make sure you backup reports.dic, forms.dic, dynamics and company databases etc. The process looks like it actually exports then imports the reports as part of the update so that could easily cause issues.

Here's a few other Year end update comments from Christina Phillips on her blog.

Victoria Yudin still has a great post on Year end close resources.

2009 payroll tax update for Dynamics GP 8.0

For those slackers still on Dynamics Great Plains 8.0 and are using payroll here is a procedure to apply the 2009 tax tables before you run your first payroll of the year. You probably don't need to worry about the tax update for 2008 as no form changes have been made. You should do the payroll year end procedure (tech doc # 850663) as usual but when it comes to applying the 2009 tax update you will have to do something like this.

Here is the 2009 payroll tax update script. It is for 10.0 but I tested on my 9.0 system and it completed successfully. I would recommend this procedure for applying the tax tables for 8.0:

1. Verify current tax numbers that are currently setup in payroll tax tables. Tools>>setup>>system>>payroll tax. Also note what date is listed for Last Tax Update (use in step 4)
2. Back up the Dynamics and company database
3. Run the script in SQL
4. Look at same tables as step 1 and verify changes have been made. Also verify last tax update is now listed as 12/19/2008
5. Run a payroll in a test company to verify the numbers are correct
6. Run payroll in production company and verify numbers are correct

Hope that helps you remaining 8.0 folks. Need to consider upgrading soon.

How do you install a large number of workstation after an upgrade efficiently?

Recently we have discussed best practices about upgrades in our company. One of the big challenges in an upgrade is installing a large number of Dynamics workstations at the appropriate time with minimal user interruption as possible. Installing Dynamics has always been a pain. MBS has approached fixing this issue with their mass deployment tools but they never work and it usually ends up easier to do the installation manually. Depending on the installation you could spend 15 mins - 40 mins per workstation. Launching the installs simultaniously (3 at a time in the same area for example) may cut time a bit but still a huge pain.


This led to a series of responses by our technical team. Here is a summary from one of our team member Jason Young. Summary of response below:


One of the tricks I have used many times to minimize downtime for large rollouts is to leverage imaging tools like Acronis True Image. This tool lets you take a picture of a server or servers and redeploy them in a virtualized environment or dissimilar physical hardware. In essence, you can take the customers environment back to the office for the test upgrade without risk of downtime or impacting day to day production of the customer.

Imaging gives you some big advantages:

1. You don’t change the state of the customers environment until testing is successful and you are ready for the production rollout. This gives you huge kudos in the eyes of local IT staff.


2. You always have a clean rollback image.


3. You don’t have to bother local IT staff because you are working with an image offsite.


4. You can couple this type of test upgrade with a Terminal Server (which we have and is easy to build) and now you can do your user acceptance testing via web. Again, no customer impact.

To do the production rollout it is always optimal to do a parallel installation, but the obvious drawback is the cost of hardware. For customer without additional server resources, there is another trick that can minimize downtime and customer impact. For customers with a single backend server (single SQL server), you install a name instance of SQL and restore production data to the new named instance.

Here are the advantages;

1. A named instance of SQL means you have to use a different DSN name than the default instance. This means you can pre-install workstations with the new version and point them at the new instance without worry of an accidental upgrade.
2. The current state is not affected so you have a rollback in the event of an upgrade failure. You don’t have to do any type of restore because you never changed the data in the default instance of SQL.
3. You are basically taking a single server and doing a parallel application upgrade. So, it’s an in-place upgrade in terms of hardware but parallel in terms of the application and data.

End of summary.

Discussion: I like the seperate instance of SQL approach. I do this with different versions of Dynamics. I have 9.0 on one instance and 10.0 on another. You'd have to be careful that users do not launch and continue working in the older version instead of the newer version but that can be overcome with training or simply stopping the older instance of SQL when the time is right (probably need to set the older instance to manually start in case of a server reboot).

Anything you do to make this task easier?

Report writer reports to excel

Getting report writer reports to Excel has never been that easy. Usually it entails sending the report out to a CSV file and then cleaning up the mess. One thing that seems to help a bit is to put the following line in the dex.ini file.

ExportOneLineBody=TRUE

If you then save the report to a tab delimited file and open it with Excel it seems to come over a little better. Try doing this.

1. Put ExportOneLineBody=TRUE in your dex.ini file


2. Open Dynamics and run a AR trial balance with options. Make the file destination Tab delimited


3. Open the file with Excel and it should look something like this:

(Click on image to see a better shot of the report)

You may need to add the customer name in the body (data section) of the report then sort by customer number but it's a reasonable mod that could get you a somewhat usuable report in excel.

Instead of like this:

So don't give up hope on report writer reports before going straight to smartlist or crystal.

***Authors note: This trick (ExportOneLineBody=TRUE ) has worked since version 6.0. I only figured this out recently.....OK, well acutally, I didn't figure it out. Truth be told another GP person told me about it after seeing a tech doc on it. We were both astounded that we are on version 10.0 and this is the first time we've seen this trick. Thanks David G.

1099 edit list for 10.0

Was in Los Angeles all last week. Nice to visit several clients that I have not been on site with for over 4 years. Spent time with Frances, Kerry and Brian from our L.A. office. Great people and a joy to be around.

Had someone call about 1099's for the first time this year. They were already preparing for that stressful time of year and noticed their edit list had no balances for any 1099 vendor. Found tech doc #944888 saying this is a know issue for 10.0 and was resolved in service pack 2. Also had someone schedule their payroll year end close with me. First one of the season.

Must mean it's time for Christmas tree's and presents when I start thinking of 1099's.

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.

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.

Dictionary not loaded issues

Technical issue: Worked with someone yesterday that was receiving an error message when trying to access the vendor maintenance window. I've seen the error refer to security issues where the user doesn't have security access to the form (at least in 8.0 and 9.0). This was a 10.0 install.


Troubleshooting steps: Looked at security and verified user had access to vendor maintenance form. Then verified the user could go into the window from a different computer with their log in ID so it had to be a code issue. I looked at the dynamics.set file and saw the number of products were not the same but instead of installing the missing products manually I copied the GP folder from the working workstation over to the workstation that wasn't working.


Additional comments: I've occassionally used the same approach while installing clients with lots of 3rd parties and modules. Install a basic install of GP then copy the GP folder over from a working install (including service packs, 3rd parties, and additional GP modules). Works like a charm and saves lots of time on the installation process.

Automatically log into Great Plains

I've set up my GP system to launch from a shortcut on my keyboard. Saves me from clicking 10 times and entering my password each time. Here's the instructions on how to do this.

Tech doc#: 855677

1. Copy the following macro code into a text editor. This will be the basis for the automatic login.

Logging file 'none.txt'
CheckActiveWin dictionary 'default' form Login window Login
TypeTo field 'User ID' , 'lessonuser1'
MoveTo field Password
TypeTo field Password , 'pwd'
CheckActiveWin dictionary 'default' form Login window Login
MoveTo field 'OK Button'
ClickHit field 'OK Button'
NewActiveWin dictionary 'default' form 'Switch Company' window 'Switch Company'
ClickHit field '(L) Company Names' item 1 # 'The World Online, Inc.'
MoveTo field 'OK Button'
ClickHit field 'OK Button'

2. Make the following changes to the macro code text file:
a. Change lessonuser1 to the user that wants to be automatically logged in.
b. Change pwd to the user's password.
c. Change The World Online, Inc. to the company that you want to be logged into. Note The World Online, Inc. is named Fabrikam, Inc. in Release 8.0.
d. Save the changes to a file named Login.mac. Save the file in the Great Plains client folder. This would be C:\GreatPlains if the defaults were used during the installation.

3. Modify the Great Plains icon on your desktop.
a. Right-click the icon and then select Properties from the drop-down menu.
b. Find the target to Great Plains. (The example that follows assumes that the defaults were used during the client installation. The client will be assumed to be found in C:\GreatPlains).
c. Modify the Target line to look like the following:
C:\GreatPlains\Dynamics.exe C:\GreatPlains\Dynamics.set C:\GreatPlains\login.mac

4. Save the changes to the icon and then close the window.

5. Test the change by double-clicking the icon. You should now be automatically logged into Great Plains and into the company that was set up.

I have a keyboard that has favorite buttons and I've set one of the buttons to launch the GP icon. So I can launch and login to GP with one push of the button.

One caveat is to remember your password is saved in the macro so think about it before setting this up. I work out of my home in NW Montana so security is not that big of an issue for me.

Who Changed that setting?

I frequently have clients call and ask "How can I tell who changed that setting". One answer is to to turn on activity tracking in system settings (Tools>>setup>>system>>activity tracking). You have some simple functions you can track such as login failures, changes to setup files, changes to card maintenance files etc.

After turning on the fuction you can then generate reports to see the activities you are tracking under Reports>>system>>general>>activity detail.

This is not a very robust tool but can give you information such as user who made changes, date and time, workstation ID, etc. A good start and doesn't cost any money to implement.

For more comprehensive tools you would have to purchase the MBS Auditor module or a third party product such as Rocktons Auditor.

So, I would suggest starting with Activity Tracking as a nice and simple first step.

Dynamics Technical Support Help

This is the first of many blogs regarding technical support for the Microsoft Dynamics Great Plains. As a technical support specialist (I think that's my official title) I will hope to make the world a better place by giving tips and tricks, outlining technical trouble shooting methods, and reviewing common GP procedures.

My first tips and tricks shows you how to copy a format in Report Writer so you don't have to start from scratch. Eg. SOP blank invoice form can be copied to SOP Blank Return form. (Hint: you can do this for all similiar forms but need to Find and Replace tables if you want to copy Blank invoice to Historical invoice).

Copying The Layout From a Modified Invoice to an Unmodified Invoice
Article ID:856003

Question:I have modified an invoice in Report Writer. I'd like these same changes to appear for a different invoice. How can I copy the modified layout from one invoice to another?

Answer:
1. Make sure both the reports are in the Modified Reports column in Report Writer.
2. Go back into Dynamics/eEnterprise to Customization Maintenance (Tools - Customize - Customization Maintenance). Export both the modified and unmodified invoice to package files. 3. Once exported, open up the unmodified Invoice using Notepad or Word and remove all the text except for the report name at the top.
4. Open up the modified report and copy all of the text, except for the report name at the top and then paste it into the unmodified invoice.
5. After all the changes have been made and saved, you can then go back to the Customization Maintenance window (Tools - Customize - Customization Maintenance) and import the reports back in.
6. Go to Security Setup (Setup - System - Security) and grant security to the newly modified report.

Hope that saves you consulting dollars or painful hours using the blessed GP Report Writing tools.

Cheers,

Doug Pitcher