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,
Christina

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.

http://www.precipioservices.com

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.

http://www.precipioservices.com

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!

HAPPY HOLIDAYS!

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!

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.

http://www.precipioservices.com

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 tomorrow...so 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?

Check eConnect 2010 Service Status Programmatically

The number one eConnect 2010 issue that I've been asked to resolve has been the following error that I discussed back in September:

There was no endpoint listening at net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/EntityOperations that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details.

The problem with this eConnect error is that it is useless for the average GP user, presumably because it is just a lower level WCF error being communicated up through eConnect.  Not only is it too technical, but it is misleading at best.  If a user runs your eConnect 2010 integration and sees this error, they will e-mail you or pick up the phone.  I've also found that it's useless for many GP consultants, as I've received several inquiries from consultants as well, repeatedly asking about this error.

What is frustrating is that the issue is so simple:  The error is almost always caused because the eConnect 2010 Integration Service is not running.

Unfortunately, I don't know why the service is stopping on the client servers, but since I can't directly control all of my clients' servers, I can only try and deal with the error more gracefully and give them enough information to resolve it without having to call me.

And so I wondered, can I just put some code in my integrations that can detect the status of the eConnect 2010 service before I even try and call it?  I've never tried checking the status of a Windows service, and although I assumed it was possible, I didn't know how difficult it would be.  Well, after receiving yet another e-mail about this issue tonight, I finally took a few seconds and read how to detect the status of a Windows service using .NET.

This great tutorial article told me all that I needed to know to check the service status in less than 60 seconds.

It's wonderfully simple--in just a few lines of code, you can tell if the eConnect 2010 service is running. Here is a basic example:

ServiceController byConstructor = new ServiceController("DynGP11eConnect");

if (byConstructor.Status != ServiceControllerStatus.Running)
{
    MessageBox.Show("The eConnect 2010 Integration service is NOT running.");
}
else
{
    MessageBox.Show("The eConnect 2010 Integration service IS running.");
}

Other than those few lines, just make sure to add a reference to System.ServiceProcess, and then add using System.ServiceProcess; to your class.

It's that easy!

I would personally recommend adding this service status check to any eConnect 2010 integration.  I'll be adding it to my standard Functions library that I use with all of my integrations.


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.

http://www.precipioservices.com

See the Forest for the Weeds

Everyone has heard the trite saying, "See the forest for the trees".  We often get caught up in details and miss the bigger picture or the context of those details.  Another interpretation might be that it means focusing too much on tactics without focusing enough on strategy.  Well, sometimes I'd be happy if it were just forests and trees, as I've found on some of my projects, there are forests and trees, but also weeds.

If we are in the middle of a complex Dynamics GP implementation, we may be so focused on complex and highly detailed configuration or technical issues that we neglect or avoid other important higher-level project management tasks, such as communication, original requirements, priorities, deadlines, risks, and perhaps even customer satisfaction.

After doing consulting work for almost 15 years, I've observed something about myself and how my brain seems to work in different roles on projects.  I observed that I can write code, research technical issues, reverse engineer complex processes, and work on excruciatingly detailed tasks.  I also observed that I can manage projects, manage resources, manage budgets, manage teams, manage client communications, and coordinate dozens of tasks simultaneously.  I'm pretty comfortable that I can handle both of those roles.

But several times, to my surprise, interest, and frustration, I have found that I can't seem to do both of those simultaneously on the same project.  Maybe to some extent I can manage a project and handle a small subset of detailed tasks, or help jump into a complex issue to help resolve it.  But, for instance, I found that it is very difficult for me to handle 120 hours of development on a complex integration while simultaneously managing multiple tasks, resources, or deadlines that are completely separate from those development tasks.

I recently had a project management conference call for a project where I had been working evenings and weekends to develop an application, make dozens of modifications as requirements rapidly changed, and perform hours of testing on dozens of test case scenarios.  I have been deep in the weeds for several weeks.  When we started discussing other tasks on the project, I literally didn't remember some items that were discussed in previous conference calls.  I had been so focused on one application and its code that I seem to have forgotten or ignored the subsequent tasks, which were no less critical, but were things that I had not yet started working on.

My only interpretation at this point is that when I am swimming through 3,000 lines of code, my brain is extremely focused on hundreds of details, "loading" many of those details into memory as I scan and jump through the code.  Writing code, I have to be able to trace processes throughout an application, from querying a database, to transforming data, to validating field values, to inserting transactions, along with error handling and configuration files, all of which requires extraordinary attention to detail, since programming languages won't forgive an incorrect character, missing line, or improperly placed parenthesis.  I have to enter a "zone" to navigate the code, which takes time and concentration, but once I'm in that zone, I'm able to efficiently work at that level of detail.

But when I have to manage a project, it seems to be a very different mindset or set of mental skills.  It involves synthesizing more discrete elements, such as resources, tasks, dates, dependencies, or meetings.  It involves a lot more mental "task switching" that seems to require fundamentally different thought processes than coding.  Managing projects and delegating tasks, I'm typically able to stay out of many of the details, which I speculate allows me to perform that task switching without having to enter a "zone".

I've spoken with colleagues about this observation, and they generally seem to agree with the concept, and I've noticed this with consultants that I've managed.  While they are working on a detailed, task, if I ask them about other tasks and how they are going to prioritize them, I see the familiar blank stare.  They were so busy fighting with Integration Manager for the last hour that they haven't been able to step back and look at all of the pending tasks.


I also find that this affects how I transition from one task to another.  After I finish some code, complete the documentation, and make a backup of everything, I find it is sometimes difficult to move on to a completely different task, whether detailed or high level.


I think the lesson here is that if you have similar observations and experiences, try and keep this concept in mind when managing a project.  Your project managers will likely become frustrated with your consultants and developers as the consultants and developers are dealing with mental whiplash trying to transition between tasks.  And your developers and consultants may need to develop some practices to help them manage their assigned tasks and lower the cost of transitioning from one task to another.

As someone who often develops and goes deep into "the zone", I think I need to develop better practices to make sure to allocate some time on a daily, or at least weekly basis, where I focus on the trees and the forests before I dive into the weeds.

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.

http://www.precipioservices.com

What kind of pilot are you?

This past summer I co-trained a Sure Step class with Ross Allen from Salesworks.  I learned a lot from the experience, but there was one analogy that Ross used that has really stuck with me.

Those of us who work as application consultants, project managers, and business analysts often complain of being left out of the pre-sales process (the Diagnostic phase in Sure Step).  We complain about salespeople not communicating requirements, minimizing scope inappropriately, or worse.  But have we ever stopped to wonder why salespeople keep us away from their process?  Maybe, if we are willing to look at ourselves critically, we might find that we contribute to the lack of cooperation.

When I ask students in Sure Step classes why they want to be involved in the pre-sales process, inevitably they respond with a variety of reasons-

• to “inject” reality in the process
• to advise the prospect of the complexity/risks involved
• ensure that the prospect understands the danger

But is this appropriate during pre-sales?  ERP implementations inherently have risks and complexities.  Isn’t the point more that we know how to manage these?  Yes, it is complex and there are dangers, but aren’t we the experts?

So, to use Ross’ metaphor, what kind of pilot do you want to be?  Imagine you are boarding a flight.  The weather is  stormy out, and there is some nervousness amongst the passengers (not unlike the customer as they are trying to choose an ERP solution).  Which of the following scenarios would make you feel better?

1. As the plane taxis to the runway, the pilot gets on the intercom and says “Wow, it looks rough out there.  I have flown in a lot of bad storms, but I think this may just be the worst.  I will try my best, but I really can’t guarantee anything at this point.”
2. As you are boarding the plane, the salesperson who sold the plane to the airline says “This plane can survive anything, you will be fine!”
3. As you settle in to your seat, the pilot comes over the intercom and says “Your copilot and I are well prepared to deal with storms like this.  Control is working with us to route around the storms out there, so we can avoid the worst bumps.  So relax, enjoy the flight, and we will get you to your destination safely.”

Okay, so #1 is not comforting…agree?  And brings in to doubt the pilot’s qualifications and professionalism.  And #2 is not who we want to assure us, as he is not the expert in flying the plane (like implementing the software).  But #3 hopefully does provide some level of comfort.  Customers (and airline passengers) don’t always recognize the risks of what they are undertaking.  But with us at the helm, we can help reduce those risks by applying our implementation experience as well as product and industry knowledge.

When Ross first used this metaphor it was an “A-ha” moment for me, and really changed my perspective when I am asked to participate in the pre-sales Diagnostic phase.  Keeping this in mind, as well as the goal of pre-sales (to close the sale!), I think I am a much more effective asset to the sales team.

And this news story from the past week drove home for me how a pilot’s demeanor can affect the experience for the passengers.  This is the sort of pilot I want to be!

So, sit down with your sales folks, and figure out how as a team you can increase prospect confidence in your solution while decreasing risk to both you and the prospect by being a confident pilot.  It’s a win/win.

Configuring DTC on Windows Server 2008 for eConnect 9 or 10

This evening I received an e-mail from a client who was getting DTC error messages when they tried to run an eConnect 9 import.

In theory, that shouldn't be too difficult.  But there were a lot of red herrings.

First, the error they were receiving was "An error occurred while enlisting in a distributed transaction".  That is all I had to go on at first.  But then their GP partner told me that these errors seemed to start when they transferred their Dynamics GP databases to a new SQL Server.  Okay, that's good to know.

So I connect to their SQL Server, and I am reminded that they are running a Windows 2008 R2 Enterprise clustered server.  Okay....  I've never had to deploy an eConnect 9 or eConnect 10 integration on a cluster before--only eConnect 2010--so this should be interesting.  After researching DTC on clusters, I found several articles on cluster configuration that were well outside of my comfort zone, so I thought I might have to defer to a Windows cluster expert.

But then I thought I should just try and check the basics to see if there was anything else I can identify that might help to troubleshoot the issue.  So I start with the basics:  Check the MSDTC configuration.  On the SQL Server, I open Component Services.  When I right click on My Computer and view Properties, I'm greeted with an unfamiliar version of the window--there are no MSDTC options.

I forgot that on Server 2008, the DTC configuration is located in a different place in Component Services--it's under a separate folder called "Distributed Transaction Coordinator".  Hiding in plain sight, very sneaky.

When I checked the DTC security options, all seemed well.

Just to verify connectivity, I used osql.exe at the command line on the GP Terminal Server to verify that I could connect to the new SQL Server.  That worked fine, and GP was running fine, so the general network connectivity seemed okay.

Given that the error appeared to be related to DTC, I put a copy of dtcping on both the application server and database server.  When I tried to have the application server ping the SQL Server, dtcping would time out with an error.

Invoking RPC method on APP1
Problem:fail to invoke remote RPC method
Error(0x6D9) at dtcping.cpp @303
-->RPC pinging exception
-->1753(There are no more endpoints available from the endpoint mapper.)
RPC test failed

Based on excellent information from this fantastic Microsoft article on troubleshooting DTCPing errors, this error itself is misleading.  It implies that DTCPing was unable to retrieve a port, but it actually means that a firewall is blocking the DTC traffic.

I then switched directions, using the SQL Server to ping the application server.  When I did that, I received a response!

Received Bind call from SQL1
Trying Reverse Bind to SQL1
Error(0x6D9) at ServerManager.cpp @453
-->RPC reverse BIND failed
But when the application server tried to "reverse bind" back to the SQL Server, it failed.

So this told me that something was blocking the DTC traffic from the application server to the SQL Server, but the SQL Server could send DTC traffic to the application server.

I then decided to check the SQL Server to confirm that the Windows Firewall was disabled.  Alas, the firewall was enabled.  Aha!  When I turned off the firewall, sure enough, DTC Ping operated fine in both directions between the app and SQL server.  And I also found that the option to "Notify me when Windows Firewall blocks a new program" was unchecked, which is why I didn't receive a dialog when DTCPing was blocked.  When I enabled notifications, I received this message when I launched DTCPing.

So, now the fun hunt for the Windows Firewall Rule that would enable inbound DTC traffic on the SQL Server.

After some searching, I learned that DTC has pre-defined rules on the Windows Server 2008 firewall.  Under the Windows Firewall "Advanced Settings", if you click on Inbound Rules, there are dedicated Distributed Transaction Coordinator rules.  But there are 3 separate rules for different DTC protocols.

Remembering that the DTC properties window listed only TCP/IP, I guessed that the TCP firewall rule would do the trick.

Sure enough, once I enabled that inbound firewall rule, DTCPing worked fine.

I then fired up an eConnect integration, and transactions started flowing.

So, if you are configuring eConnect 9 or eConnect 10 on Windows Server 2008 (or any machine with the newer version of Windows Firewall), if the firewall is enabled, check to see if inbound DTC traffic is allowed.

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.

http://www.precipioservices.com

Book Recommendations: Accounting for IT Professionals

I often get asked for book recommendations by IT folks who need/want to learn the language of accounting to better support their GP users.  I often do focused Dynamics GP trainings for IT professionals, to help them better support and manage their Dynamics GP system.  And I always recommend the same to books, so when I got asked the question again today, I thought I would share my standard recommendations.

McGraw-Hill 36 Hour Accounting Course
A great introduction, very thorough and concise.  And it's great because you can do a snippet at a time, working through it slowly.

Accounting for Dummies
I know this is a bit cliche, but it is a great "light" overview of accounting principles and terms.  A very easy read for those that might get bogged down in the McGraw Hill book.

Please feel free to add your own recommendations!

Formatting Notes Using eConnect 2010 - Challenge!

Just when I thought I was comfortable with Dynamics GP integrations, along comes eConnect 2010.

The change from COM and DTC to a Windows Service took a little while to understand, but I've adjusted and am now pretty comfortable with the service and how to troubleshoot it.

Using eConnect within Visual Studio is very similar, except to a few changes to the final call to eConnect, but that wasn't difficult to accomodate.

But now I've run into a change that has me stumped for the moment.  It's minor, but as with many "minor" things, it's an annoyance that just seems to be challenging me to figure it out and find a solution.

When importing notes into GP using eConnect 9 and 10, I have previously used "\r" or vbCr (carriage return) to produce line breaks in the note text.  Worked like a charm.

When I had to send a note line break using GP 10 Web Services, I figured out that you need to send the UTF-8 escaped version of carriage return, which is & #13. (I've had to add a space to prevent the blog publishing from thinking it is HTML)

Great, problem solved, mission accomplished, non-issue.

But then comes eConnect 2010.  When testing an integration that I upgraded to 2010, I noticed that my notes now had the vertical bar in the note instead of a line break.  Hmmm.

I tried \r, \n, and \r\n, but all produced the same result.  So then I tried & #13, but that just put the literal text of "& #13" in the note!

Just to make sure I wasn't going crazy, I queried the text of a GP note with manually entered line breaks and was able to confirm that "\r" is still the line break being used and stored in SQL.

So now I'm stumped.  I need to do more testing with \r to see why that doesn't seem to work, and how that value is being stored in the database when it is saved by eConnect 2010.  I'm guessing it is escaping the character somehow and that is causing the problem, but I haven't had time to trace the SQL or query the resulting note text to figure out what character is being stored.

If any eConnect 2010 gurus out there know the answer, post a comment and impress us!


UPDATE:  So I have done some more testing, and what I'm seeing with notes created using eConnect 2010 is pretty strange.   I'm still reviewing my import to make sure that I don't have a bug in my code, but here is what I'm seeing.

When I assign text to the eConnect customer NOTETEXT field, I am making sure that the text contains "\r" for line breaks.  When I view the serialized XML just prior to sending it over to eConnect, there are line breaks in the XML.

But after the note is saved and I query the TXTFIELD value from the SY03900 table, I am seeing "\n" characters for line breaks, which is causing the display problem in GP.

Here is the value being passed to eConnect:

City: Las Vegas\rPostal Code: 3658\r\r\rLas Vegas\r3658

And here is what I am querying from SY03900 after it is saved:

City: Las Vegas\nPostal Code: 3658\n\n\nLas Vegas\n3658

So it seems that somehow, for some reason, eConnect is replacing \r with \n in Notes.


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.

http://www.precipioservices.com

Learning Dynamics GP Database Table Names

Today at the MS Dynamics World Decisions Fall 2010 virtual conference, I gave a presentation on the Dynamics GP Developer's Toolkit.  After the presentation, I had about 30 minutes to answer questions from attendees.

One of the attendees asked  if I knew of any good diagram or documentation, other than the SDK, of the Dynamics GP database tables.  The attendee wanted to know how to learn the Dynamics GP database tables so that he could start developing customizations and integrations.

My recommendation was to first understand the history and design behind the GP table naming convention.  Once he had that knowledge, he could then more effectively use something like the Resource Descriptions windows within GP to locate specific tables.

I referred him to David Musgrave's excellent discussion on the topic:

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/05/29/understanding-how-microsoft-dynamics-gp-works-with-microsoft-sql-server-continued.aspx

David's discussion was much broader in scope than just the table names, but in the post above, he does an excellent job of describing the history and design of the table and field naming conventions.  With this information, a developer can have a very good understanding of how most of the company database is structured and how to find specific tables.

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.

http://www.precipioservices.com

Hi, My Name Is Christina And I Actually LIKE Modifier

It is a universal truth that we people are complainers.  We complain about missing functionality here, broken functionality there, and it seems like we are never happy with the tools we have.  Call it perfectionism, call is realism, I am as guilty of it as the next person.

But this week I was reminded of the usefulness of Modifier, a tool that is often lumped in with Report Writer as having limited usefulness and causing frustration.  I think, as is often the case, the devil is in the details.  Proper scoping initially can ensure the proper development tool is selected for the project.  And for this project, the use of Modifier decreased the cost and maintainance of a previous Dexterity based customization.

When I was initially given the Dexterity customization to review, I noticed how basic it was.  Literally 6 modified windows, all based on existing GP windows.  No reports.  No procedures.  Nothing else.  And no source code, meaning that whatever road we took it would most likely involve recreating the customization.  In looking at the modified windows, the modifications we all cosmetic.  Changing the displayed size of fields (not the field itself) and rearranging headers.  I am sure the developer bloggers out there can add to my impression, but it seems to me that someone knew Dexterity so that is what they reached for as a tool.  Not the worst reason to pick a customization tool, but definitely not the only factor to consider. 

So, based on the fact that the modifications were cosmetic, Modifier was my tool of choice.  Easy to use, I modified the 6 windows quickly and easily.  No concerns about source code, additional dictionaries to install, etc.  No Dexterity (not that Dex is bad, but it does mean involving a developer each time)  And when it comes to upgrades, the process is fairly simple.  And, if the window changes and upgrading is not possible, it is easy enough to recreate now that we have documentation of the customization.

I recognize that Modifier may not be the best choice when getting in to heavy customization, but for cosmetic adjustments it works just great.  And with Business Ready Licensing, clients don't even have to own Modifier to run the modifications you create.  They just need the Customization Site License which is part of BRL. 

So the lesson here, I think, is pick the right tool for the job and save the client money while saving time now and in the future.  I know I have cited this white paper before, but Choosing A Development Tool is a worthwhile read for both consultants and customers who want to take ownership of their development projects.

What Implementation Methodology? I Think Mine Might Be Busted.

Christina:  Steve and I don't do a lot of collaborative posts; it just takes a bit too much coordination :) But here is one that came out of many discussions we have had over the years. We all have war stories, things that have gone bad fast. Sometimes it's our fault, sometimes it's not. But statistics support that MOST of the time it is the system's (methodology’s) fault. A broken methodology will repeatedly put even the best consultants in frustrating situations. Here are just a handful of examples where Sure Step, or any formal implementation methodology, could avoid frustration and disappointment.


Steve:  A few times I've received a call from a partner asking for assistance--but with a twist. It starts out with a seemingly normal request for assistance with a Dynamics GP integration or customization. Then I'm asked if I can work on the project "right away". And then I'm asked if I can discount my billing rate.
That's when the alarm bells go off. On subsequent calls, I start to learn the project has some serious issues, and I'm being brought in at the last minute to try and salvage an integration or customization that someone didn't complete or is broken.
Based on two of these experiences, I thought I would give Christina, a SureStep Guru, a challenge. I'll document some observations of a few projects-in-crisis that I've worked on, and she will map the symptoms to elements of the Sure Step methodology that might have helped avoid the breakdowns.

Scenario 1:  "Steve, can you help us with this integration ASAP? We have some code that a developer at an offshore firm wrote, but it doesn't seem to work and they haven't returned any calls or e-mails in 3 weeks, when the development firm said they were transitioning to a new developer. Oh, and can you do us a favor and drop your rates 40%? We're already way over budget, are weeks behind schedule, our go live is in two weeks, and the client is very upset that the integration doesn't work and can't be tested. We're relying on you to help us out."
True story! With friends like these, who needs enemies?
So Christina, how could Sure Step have helped this partner and customer? (and me!)

Solution 1:  First, if we were to open Sure Step, and look under Project Types and Implementation Phases for an Enterprise Project Type, we find activities that could address the scenario above. Selecting an activity will display guidance, templates, and other resources in the right hand pane.

First, under 1.1.2, we see the project management discipline of Risk Management. Active risk management should have identified the potential risks of using offshore resources. And while we are talking about PM disciplines, how about Communications Management (1.1.3), which would include Project Performance and Status Reporting that should have identified potential budget issues before they actually occurred.

Now, if we skip down further in Sure Step, under Additional Resources, we find guidance on an Onsite/Offshore Conceptual Work Split. Was this discussed with the offshore developers? Were standards discussed and then managed proactively following the disciplines we discussed above?

Now, back under Project Types and Implementation Phases, in the Design Phase, there are also a number of activities related to customization design.

Where is the functional design in the scenario above? Was it signed off on by the customer? Did the developer create a technical design? Where is it? How about testing? Who did it and when? All of these set expectations of what the design includes, and make it easier to transition work when a resource is no longer available. These activities may be the responsibility of the developer and other consultants, but with active project management we can easily identify when these tasks don’t happen and manage the outcome.

Scenario 2:  "Hey Steve, I hope you are available right away, because we really need your help. We were working with a third party company to help our client perform a multi-year detailed historical data conversion from their legacy accounting system to Dynamics GP, including project accounting transactions that will be migrated to Analytical Accounting transactions in GP. The vendor was supposed to deliver all of the converted data last Friday, but they called us this weekend to say that they can't read the source data and won't be able to deliver the GP data. We're hoping you can help us figure out how to extract the data from their old system and then import it into GP in the next 4 days since we're behind schedule. The client is not happy and we have already reduced our rates, so can you reduce your rate? Oh, and I had an expectation that this could all be done in less than 10 hours, because the client won't be happy having to pay much more for this conversion since we're already far over budget--will 10 hours be enough?"

Seriously, I can't make this stuff up. I've always wondered how people can keep a straight face while saying both "We really really need you to rescue us from a disaster" and "Can you dramatically lower your billing rate because we're over budget" in the same breath.

Christina, the stage is yours...

Solution 2:  Well, this seems like a project with no analysis phase, no design phase, no development phase, just a deployment and operation phase. Sure Step prescribes 5 phases to an implementation project (the first phase, Diagnostic, is a pre-sales phase), and all projects do have all phases (although the length of each phase can vary based on the project).

Activities in Sure Step are laid out by phase, as shown in the screenshot below. There are also a number of cross phase processes, which are activities (like Quality and Testing) which span multiple phases. By following this structure, we can ensure that tasks are completed in a logic order and that activities do not occur without the proper planning.

The Analysis phase is the information-gathering phase, and would include tasks like gathering data migration requirements and developing testing standards. While the Design phase includes activities for the functional and technical design of data migration include the tasks outlined in the screenshot below.

By completing Analysis and Design BEFORE actual development is needed, we can avoid the surprises regarding how to extract the data and the required format for import in to the system. Sure Step uses this measured approach specifically to discourage skipping right to development and deployment. And in the case of the scenario above, it seems that development was combined with deployment, leaving out critical Development phase tasks like data acceptance testing.

Scenario 3:  "Steve, I need you to modify these 6 Dynamics GP and ISV windows to selectively disable certain buttons and certain fields for certain users. I don't want certain users accidentally changing or deleting data on these windows, so let's disable the Delete and Save buttons, and then make certain fields read only just in case. Hmmm, can you password protect a few fields as well? And Dynamics GP doesn't work the same way as their 20 year old custom AS400 system that is being replaced, so we need you to create a few custom windows to replicate their AS400 screens, and then modify the transaction workflow to match their old system. After you are done with that, we can talk about how we can get the Dynamics GP reports to look exactly like the AS400 reports they are currently using. I know that none of these changes are included in the statement of work and that we had 10 people participating in 2 weeks of system design meetings agree that we wouldn't need any customizations or modifications, but I think we really need Dynamics GP to work just like the AS400 system. Let's call these change request number 97 and 98 for now. I'll reserve numbers 99 through 120 for the reporting."

Christina, I just deliver them on a silver platter, and rely on you to work your Sure Step magic.

Solution 3:  Oooookay. Well, although this has an element of lack of functional design to it, I think the real underlying issue is Detailed Business Process Analysis. If we look at the Analysis phase activity for Conduct Detailed Business Process Analysis (1.3.1), we see three key tasks.

Sure Step advocates for this three step process that begins with "Define Target Process (To Be)" and then moves to "Map Existing Processes (As Is)" and then analyzing any gaps. And preceding this, we can also include (per Sure Step) a solutions overview to acquaint keys users with how the systems works "out of the box". Why do we do this? Why do we start with Defining Target Processes? Because we are changing systems which means we will be changing our processes. I know that may seem like an obvious statement, but it is not unusual for organizations and individuals to react negatively to the potential for change. That is why Sure Step even has guidance in the Project Management Library (and throughout the activities as well) regarding Organization Change Management (OCM).

The guidance in Sure Step related to OCM will assist in the project team in helping the client navigate and manage the necessary organizational change that comes with implementing a new software system. Stories like the one above suggest to me that Organization Change Management was not considered as an important activity, and that the client is scared in to wanting to recreate their existing system. Personally, I take the word "consultant" seriously. We are there to consult, and advise, on the best practice. And Sure Step helps you do that in a way that can improve results, minimize risk, and increase project success.

Well, hope you found this scenario/solution format helpful. As always, we love to hear from any of our readers out there. And I love to hear about how you are using Sure Step (even in bits and pieces) to better your processes.

eConnect 2010 Integration Service Won't Start Due To Port 80 Conflict

By Steve Endow

Last week I deployed an eConnect 2010 integration at a client.  We first installed it on the SQL Server for testing, and everything worked fine.

We then deployed it on one of the two load-balanced Citrix servers that the Dynamics GP users utilize, and that went smoothly.  But when we installed and tested it on the second Citrix server,we received the now familiar "there was no endpoint listening at net.pipe" error.  Easy enough to resolve, no problem!

So I open the Services applet, and sure enough, the eConnect 2010 service was not running.  But when I tried to start the service, I received an error that it failed to start.  Hmmm.

I checked the Windows Event Log and found two errors, one of which was helpful.

Input parameters:
Exception type:
System.ServiceModel.AddressAlreadyInUseException
Exception message:
HTTP could not register URL http://+:80/Microsoft/Dynamics/GP/eConnect/mex/ because TCP port 80 is being used by another application.

Exception type:
System.Net.HttpListenerException
Exception message:
The process cannot access the file because it is being used by another process


So this seemed to be telling me that the eConnect 2010 service was trying to setup a listener on port 80, but something else was already using port 80.  We initially thought that it might be IIS, but after stopping the IIS web site and IIS services, I used the netstat command to confirm that something was still using port 80.

After re-reading the eConnect 2010 documentation and not finding any references to http or port 80, we submitted a support case.  Support responded promptly and offered two workarounds.

We chose the simpler of the two solutions, which was to change a parameter in the eConnect service config file.  To do this, locate the eConnect web services configuration file, which is usually installed at:

C:\Program Files\Microsoft Dynamics\eConnect 11.0\Service\ Microsoft.Dynamics.GP.eConnect.Service.exe.config

Open this file using a text or XML file editor and change the httpGetEnabled parameter to false.

So what in the world does this mean?


Changing the httpGetEnabled parameter to false disables the HTTP publishing of the Web Services Description Language (WSDL) metadata.  Developers should understand what this means--for anyone else, don't worry about it if it doesn't mean anything.

We learned that by default, the eConnect 2010 service publishes its WSDL using both HTTP and named pipes (net.pipe).  The HTTP method can be viewed using Internet Explorer, while the net.pipe method uses the WCF 'metadata exchange' (mex) endpoint, which can be browsed using the WCF metadata tool, svcutil.exe.  And apparently Visual Studio can access the WSDL using either of these methods.

The workaround above, setting httpGetEnabled =false, turns off the HTTP WSDL publishing, thereby eliminating the need for eConnect to use port 80.  The eConnect 2010 service will still provide the net.pipe WSDL in case it is necessary.  I don't yet know if the WSDL is even used for a compiled / deployed eConnect integration, or if is only necessary to setup the service reference in Visual Studio--that will have to be a separate homework assignment.


I suspect that the httpGetEnabled=false option should work just fine for most customers.  In case it isn't a viable option, the second workaround that support provided was to change the port that the eConnect service uses to publish the HTTP WSDL.  That was slightly more involved, and wasn't necessary for us, so we chose not to use that approach.  If anyone is interested in knowing that method, i.e. you have a development server that is already using port 80, let me know and I'll write a separate post on that method.


2/21/11 Update:  Here are the instructions provided by MS Support to change the port number for the HTTP WSDL.

Step 1:  Edit the Microsoft.Dynamics.GP.eConnect.Service.exe.config file located under the following typical location: C:\Program Files\Microsoft Dynamics\eConnect 11.0\Service.exe.   Change the httpGetUrl to have a different port, such as port 83:

< serviceBehaviors >

  < behavior name="eConnectServiceBehavior" >

    < !--Leaves this to true in order to receive Exeption information in eConnect-- >

   

    < !--This settings turns on Metadata Generation. Metadata allows for consumers to add service references in Visual Studio-- >

    < serviceMetadata httpGetEnabled="true" httpGetUrl="http://localhost:83/Microsoft/Dynamics/GP/eConnect/mex"/  >

  < /behavior >

< /serviceBehaviors >

Step 2:  Use "netsh http add urlacl url=http://+:port_number/ user=DOMAIN\UserName" at a Command Prompt to assign the HTTP namespace to the required user account that the "eConnect 2010 service" runs under.  The below syntax is what I used on my computer to configure it for port 83.   Note the ADOMAIN\GPService account is the Windows account running the "eConnect 2010 service" under Administrative Tools - Services.

netsh http add urlacl url=http://+:83/ user=ADOMAIN\gpservice

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com