Thursday, December 30, 2010

Tax Free Holiday!

So, I had a client email last week because they had a number of employees with state tax but it was not calculating for that specific state.  The strange part was that all of the setup seemed right....
  • Cards>>payroll>>state tax, assigned to the employee
  • Cards>>payroll.>>tax information, default state code correct
  • Cards>>payroll>>pay code, pay codes marked as subject to state tax
  • Transactions>>payroll>>transaction entry, the state code was on the transaction
Bizarre.  So I asked for a copy of the build report, and saw that the state tax code was on the build report correctly.  But on the calculate report, it was not calculating an amount.  Aha!! I looked at the set up for the state tax code (setup>>system>>payroll tax) and saw that the first filing status for the state was a 0% status.  And the employees had been set up with that, since it was the default. 

Some quick scripts if you find yourself in the same boat.  The first selects the records to update, and the second updates the filing status.  Our situation was limited to employees in a specific department that worked in another state, but you could adapt these to limit by any other factor as well.

--identifies the records to be changed

select UPR00100.DEPRTMNT, UPR00700.employid, UPR00700.STATECD, UPR00700.TXFLGSTS from UPR00700 inner join UPR00100 on UPR00700.EMPLOYID=UPR00100.EMPLOYID where UPR00700.STATECD='insert state tax code here' and UPR00100.DEPRTMNT='insert department code here'

--executes the change
UPDATE UPR00700 set UPR00700.TXFLGSTS='insert filing status here' from UPR00700 inner join UPR00100 on UPR00700.EMPLOYID=UPR00100.EMPLOYID where UPR00700.STATECD='insert state tax code here' and UPR00100.DEPRTMNT='insert department code here'

For the filing status variable, you will have to set up one employee correctly and then look at the UPR00700 to see the proper value in TXFLGSTS (select * from UPR00700 where EMPLOYID='insert employee ID here' and STATECD='insert state tax code here') for the state code.

Hope everyone had a great holiday! And here's to a great new year!
Take care,

Tuesday, December 28, 2010

GP 9 Year End Update Causes Blank Report Writer Sections when using VBA

Last month a client asked me to make a few modifications to their Dynamics GP 9 Purchase Order report.  Because Report Writer is such a pathetic tool (I don't care what Mr. Musgrave says ;-), I had to use VBA to add some fields to the report.

And because the client is still using GP 9, I had to use RetrieveGlobals9.dll to query the GP company database directly.  Not a problem, I finished the modifications and everything worked fine.

Then last week I get a call from the client saying that their PO report isn't printing properly.  They explain that edges of the report are cut off, and the bottom 2 inches of the report are also being cut off.  Based on that explanation, I suspected there was a printer driver issue.  But the problem occurred on all workstations with multiple printers.

To see first hand what this looked like, I setup a GoToMeeting session with the client and we printed to a PDF file.   Sure enough, the right edge was cut off, and the bottom of the PO was blank.

Here is some additional info:

1) When printed to screen, the report will display properly and look fine
2) When printed to screen, and then sent to the printer, the report will print properly
3) When printed directly to a printer, the PO will have the right and bottom edges cut off
4) This problem seems to have occurred after installing the GP 9 2010 YE Payroll Update (KB2435598, version 9.00.0386)
5) The problem happens consistently on multiple workstations and multiple printers

I performed tests on my development server, but was unable to reproduce the issue.  I then installed the GP 9 2010 YE update.  Sure enough, I was then able to reproduce the problem.

At first glance, it appears to be a printer driver issue, since the report formatting gets messed up.  After moving fields around to fix the margins and prevent fields from being cut off, I then performed dozens of tests to figure out why the bottom of the report was still being cut off.

After a few tests, I realized that the sections weren't being cut off, they were actually being suppressed completely.  And it appeared that the sections being suppressed were the two sections where I had VBA code.

Once I discovered the VBA connection, I added some breakpoints in my code to see if there was an error that was causing the VBA to fail and somehow cause the section from printing.  Even though the report printed to screen fine, perhaps there was a VBA error when printing directly to the printer.  But after stepping through every line of code without an error, yet the sections still did not appear, it didn't seem to be a VBA error.

So then I tried removing all of my VBA code from the report.  After I did that, the missing report sections printed properly, albeit with blank custom fields.  So then I decided to see exactly which line of code was causing the section to fail.  After several tests, I narrowed it down to this line:

Set objUserInfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")

It appears that the 2010 Year End update introduced some type of bug or issue that will prevent a report section from printing if it involves a VBA reference to Retrieve Globals 9.

Here is an example of what the PO lines should look like:

But here is what it looks like after the year end update is installed and the VBA code calls RetrieveGlobals9:

So if you happen to be lucky enough to have a client on GP 9 that has modified reports with VBA and needs the 2010 year end update, be on the lookout.

I have a support case open to see if Microsoft has any thoughts on this issue.

UPDATE:  Microsoft Support responded very quickly, indicating that this is a known bug, discussed in KB Article  884601.  Apparently this issue has been around for a long time (it seems to have existed with GP 7.5 and 8), but based on my experience with GP 9, it doesn't always occur--it just happened to start after the customer installed a year end update.  The support rep indicated that because the issue is relatively easy to work around, there are no plans to fix the problem, at least with GP 9.  I don't know if the issue exists with GP 10 or GP 2010.

KB884601 offers two workarounds.  First, you can switch the report from a graphical format to a text format.  Obviously, this is not a viable option for the vast majority of reports, and text reports are horrifically ugly.  Scratch that one off the list.

The second option is to restructure your VBA to change the scope of RetrieveGlobals and ADO objects.  But, the support rep indicated that this option may not resolve the problems for all reports.  First, you need to move the RetrieveGlobals and ADO variable declarations to the General Declarations section of the report.  You then only instantiate RetrieveGlobals and your ADO objects in the Report Start event.  Finally, your report section event code will make use of the shared objects and variables.

These code changes appear to have resolved the issue on my development server--I am now able to print the report directly to printer without any issues.  I am waiting to schedule the installation of the new report package on the client's server, but I am hopeful that this will resolve the problem for them.

Of course, if that still didn't work, the final workaround would be to print the report to screen first, then print to printer.  This is apparently considered an "easy" workaround, hence the lack of interest in fixing the problem.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

Tuesday, December 21, 2010

A Dynamics GP Partner Tragedy

This morning I fielded a question on Experts-Exchange about third party products for Dynamics GP 2010.  The customer was asking about inventory barcoding and warehouse management using Dynamics GP.

One of my recommendations to the GP customer was to consult with their GP partner to help them analyze their requirements and then get their help with reviewing the third party solutions.

To my horror, the customer replied:

"The GP partner in our area doesn't have any experience with GP 2010."

Aaaahhhhhh!!!!!!!!!!  Wake me up, I'm having a nightmare!!!!!

I don't know any of the details, but that statement alone tells me that something is very wrong.  If I take the statement at face value, it means that there is a GP partner out there potentially selling software that they can't support.  Interpreting further, could it mean that the partner is comfortable with prior versions of GP, but for some inexplicable reason thinks GP 2010 is somehow different or more complex?  That just doesn't make sense.

So without knowing more, I can say one thing with confidence:  Regardless of the partner's competence, the customer thinks that the partner is incompetent, which is all that matters in terms of the customer's perception of, and experience with Dynamics GP.  And so the customer is now turning to Experts-Exchange to get help (one question at a time!) on selecting a warehouse management solution? 

That, folks, is a tragedy!

There are soooo many well staffed, well trained, experienced, certified, competent, helpful, and friendly partners that there should be zero reason why a Dynamics GP customer can't be working with a partner that they are satisfied with, and who helps them successfully implement Microsoft Dynamics GP, which they have paid thousands of dollars to purchase.

We (competent consultants) take classes, we study, we take exams, we get certifications, we do research, and we work very hard to make sure that we know as much as possible to help our clients and produce customer satisfaction. 

Sure, there are situations where a partner isn't an expert in an industry or in a particular module.  And yes, there are situations where customers in some cities or remote locations may not be able to find the perfect partner in their back yard, but as a GP consultant that supports customers remotely across the US and in other countries, I can say with confidence that geography shouldn't stop any customer from finding a partner or even an independent consultant that they can work with.

So PLEASE, I beg you, stop the tragedy.

Customers:  You have the right to work with a certified, competent, professional Dynamics GP partner.  Demand one!  Find one!  There are plenty of partners looking for you to be their customer.

Partners:  Please work with your customers to make them happy and let them know that you care!  If you know your stuff, show your customer!  If you don't know your stuff, get your act together, since it makes all GP partners and even Dynamics GP look bad.

And with that, please have a happy holidays, software nightmare and partner tragedy free.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

Wednesday, December 15, 2010

Quick Post- Why I love Management Reporter

Sitting in an airport, delayed for 4 hours trying to get to Denver for our annual year end customer event, I thought I would post a quick couple of thoughts on Management Reporter.  I have been working in it more and more lately, and I really like it.  Of course, there are the missing pieces for now, but I know that they will come in time.  But a couple of things that make life so much easier....
  1. Being able to easily add dimensions, including account category or analytical accounting, for reporting. Just make the change in the company settings (Companies>>Companies).
  2. Create report groups to generate reports together, and specific whether you want to override the report definition settings for date, etc.  (File>>Report Groups)
  3. Being able to organize report building blocks, and reports in report library by folders.  And then in the Report Definition, on the Output and Distribution tab, being able to send reports to a specific folder in the Report Library.
Now, if I could only get to Denver!


Tuesday, December 7, 2010

SmartList and Year End. A love story.

My love for SmartList knows no boundaries lately, especially when you add the capabilities of SmartList Builder in to the mix.  But that is for another day.  Today, I wanted to talk about using basic SmartList during your year end.  I have been working on a presentation for our annual year end event for customers, and thought I would share a few of my top SmartLists for year end.

In no particular order (drumroll, please!):
  • 1099 Transaction Audit- Validate 1099 amounts based on transaction detail
    • Payables Transactions
    • Add Columns
      • Document Amount 
      • 1099 Amount
      • 1099 Type
    • Search
      • 1099 Type is not equal to Not a 1099 Vendor
      • Voided is equal to No
      • Posted is equal to Yes
      • Document Date (or Posting Date, depending on preference/need) is between Beginning of Calendar Year and End of Calendar Year
  • 1099 Vendor Audit- Confirm that you have Tax IDs for all 1099 vendors
    • Vendors
    • Add Columns
      • 1099 Type
      • Tax ID Number
      • Vendor Status
    • Search
      • 1099 Type is not equal to Not a 1099 Vendor
      • Vendor Status is not equal to Inactive
  • Open Accounts Receivable- Document open AR at year end
    • Receivables Transactions
    • Add Columns
      • Current Trx Amount
    • Search
      • Current Trx Amount is greater than 0.00
      • Void Status is equal to Normal
      • Posted is equal to Yes
    • Can use a similar setup for Payables Transactions 
  • Discontinued Items- Review discontinued items to see if you would like to remove the records during year end
    • Item Quantities
    • Add Columns
      • Item Type
    • Search
      • Item Type is equal to Discontinued
      • Record Type is equal to Overall
  • Standard Cost Review- Review item standard cost for periodic valuation methods to determine if you would like to update standard cost as part of year end
    • Items
    • Add Columns
      • Valuation Method
      • Current Cost
      • Standard Cost
    • Search
      • Valuation Method is between FIFO Periodic and LIFO Periodic
  • Employee Status- Review employees to see if any records need to be inactivated
    • Employees
    • Add Columns
      • Inactive
      • Last Day Worked
    • Search (optional)
      • Inactive equals No
      • Last Day Worked is greater than 1/1/1900
  • Account Type Review- Review the account type (e.g., Balance Sheet or Profit and Loss) to ensure that account balances close properly during the financial year end close process
    • Accounts
    • Use defaults, but make sure that maximum records allows all records to be returned (check under Search for maximum records setting)
Please share any SmartLists you rely on for year end as well, and I will update the post! Happy holidays!

Monday, December 6, 2010

VBA6.DLL File Not Found Error with Dynamics GP 2010 and Office 2010

I am helping a client upgrade from GP 9 to GP 2010, and as part of the upgrade, we are reviewing and documenting dozens of GP customizations that they have had in place for years, many of which are no longer used.

While I was reviewing the Modifier & VBA customizations on the test server, I received an error when I tried to open one of the modified windows.

"File not found: VBA6.DLL"

Thankfully, Vaidy Mohan had bumped into this error back in August and posted to the partner forum.  Beth Gardner with Dynamics support then graciously posted an entry on the Developing for Dynamics GP Blog explaining the issue and providing a resolution.

It appears that the issue is due to changes that are made by the Office 2010 installation routine that break the Dynamics GP VBA file references in the registry.

It appears that it was originally thought that this might be specific to 64-bit Windows environments, but Beth followed up last month with a new post explaining how it affects 32-bit Windows as well.

The solution looks a bit intimidating, but is just a relatively simple registry update, although Beth reports that some customers had to perform some additional steps to resolve the issue.

I thought I would write a post under the headline of Office 2010, as that seems to be the culprit.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

Wednesday, December 1, 2010

Payroll Audit Summary SmartList POW!

I had a request last week for an audit report for payroll processing that would summarize the amounts and hours being paid by pay code.  This report would be generated after a payroll was built and calculated, to confirm the amounts before checks are printed.

So, I turned to my favorite user-friendly tool: SmartList Builder.  We were able to create the report in under 5 minutes, which I think deserves a POW :)  Here are the steps:
  1. Microsoft Dynamics GP>>Tools>>SmartList Builder>>SmartList Builder
  2. Enter a SmartList ID, Name, and Item Name
  3. Pick Product: Microsoft Dynamics GP, Series: Payroll
  4. Under Tables, click the + and choose to add a Microsoft Dynamics GP table
  5. In the Add Table window, choose Product: Microsoft Dynamics GP, Series: Payroll, and Table: Payroll Work Pay Code
  6. Click Save
  7. If you want to, you can also add the Payroll Master table as well using the same process, related on the Employee ID fields.
  8. In the SmartList Builder window, you can then mark the fields you want to default in the view.  We picked Pay Record, Units to Pay, and Total Pay.
  9. Click Options at the top of the SmartList Builder window to open the Options window
  10. Mark the Summary SmartList checkbox
  11. For the Payroll Work Pay Code table, locate the Pay Record field and double-click.   Change the Summary Method to Group By to group the summary for each pay code.
  12. Locate the Units to Pay and Total Pay fields to confirm that the Summary Methods are set to Sum.
  13. Click Save
  14. Save and close the Smartlist Builder window
  15. Launch SmartList, Microsoft Dynamics GP>>SmartList and include the changes.
  16. View the results! NOTE, this will only display results from a payroll that has been built and calculated but not yet printed and posted.  You should see one line for each pay code in the payroll, with a summary of hours and total amount to be paid.
Extra Credit!  Use the matched table feature in SmartList Builder to add the Payroll Work Deduction and Payroll Work Benefit tables to the SmartList so that the report includes the summary of deductions and benefits as well.  HINT, to access the match tables feature open SmartList Builder, select the Payroll Work Pay Code table, click the Edit Selected Table icon, and choose Matched Tables.

Yay or Nay? The Setup Checklist

Whenever I start a new implementation of Dynamics GP, I am faced with the question of how to best acquaint users with the system during configuration.  I will admit that I was a slow convert to the setup checklist (Microsoft Dynamics GP>>Tools>>Setup>>Setup Checklist).  But on my most recent implementation, I decided to once again give it a whirl. 

For those of you that are not familiar with it, the Setup Checklist allows users to access most of the necessary setup screens to complete configuration of the system.  It is a great tool because it lists the items in the order that they need to be completed, to avoid the flip-flopping between modules.  Even better is the feature that allows users to assign tasks to other users, and track the progress by marking if an item is in progress, completed, or not used.  I know that users often feel buried in "homework" when setting up the system, so the setup checklist helps them organize the work involved.

However, I honestly struggle with the learning aspect of it (the trainer in me rears its head).  When a user accesses the setup from the centralized checklist it is easy and efficient, but it doesn't give users a feel for how GP is organized in terms of setup, cards, and the like or how to navigate to the windows independently (and the inherent "exploratory" learning that goes along with that learning). Of course, we can cover this later in training, but the user(s) involved in the configuration are often the poweruser(s) of I am not sure if putting that learning off until later is a good idea.  And I am hesitant to train on both during configuration and cause confusion.

So, I come back to the fact that I think I prefer to train via menus, and then use the setup checklist as a guide for additional companies (either being implemented simultaneously or later).  What about you?  How do you feel about the setup checklist?  Do you use it? How do you "train" with it?