Sales Tax Simplified

It seems like the Dynamics GP forums are alive with sales tax questions lately. So, I thought I might tackle this one in the same manner that I do in the classroom-- by breaking it down in to steps. I treat sales tax as a three step process.

1. Document Default
2. Item or Setup Default (depending on if you are working with Receivables or Sales Order Processing, for example)
3. Tax schedule comparison

So, for this post, we are going to keep it simple and look at taxes in Receivables Management and Payables Management. I promise another post on Sales Order Processing and Purchase Order Processing next week :) But, for now, lets keep it simple.

Let's start with Step 1. There is one key setup that can impact the tax schedule that appears on a document (whether it be Payables or Receivables) by default. In your company setup, Tools>>Setup>>Company>>Company>>Options, there is a setting to Use Shipping Method to Determine Default Tax Schedule.

• If this option is marked, then the tax schedule that appears on the transaction will be dependent on whether the shipping method is a pickup or delivery method (more on this later).


• If this option is unmarked, then the tax schedule that defaults on the transaction will always be the Vendor Address' schedule (on Payables) or the Customer Address' schedule (for Receivables)

Now, let's look at Step 2. Now, Step 2 assumes that in your setup you have chosen Advanced for your tax calculations (Tools>>Setup>>Purchasing/Sales>>Payables/Receivables>>Options) rather than Single Schedule. Single schedule will always charge according to Vendor or Customer Address' schedule without Step 2 or Step 3.

In Step 2, we need to find the setup schedule (in Payables or Receivables). This is the schedule specified for each amount (Purchases, Sales, Freight, Misc, etc) in the setup options window mentioned above. In most examples in training manuals, these schedules are setup to include ALL DETAILS.

So, in Step 3, the schedule from the document (Step 1) is compared to the schedule from setup (Step 2). Details that the two schedules are compared, and only those that exist in both places are calculated.

Easy right? I know, I know..you are probably saying, huh? So, lets look at an example. Let's say that I have the following tax schedules set up:

• MOTAX: Contains the MOSTATE, KCCITY, MOSPEC, and JCKCTY Details which will be assigned to my Missouri based customers


• KSTAX: Contains the KSSTATE and KCKCITY Details which will be assigned to my Kansas based customers


• VALID: Contains the MOSTATE, KCCITY, JCKCTY, KSSTATE, KCKCITY which contains all of the currently valid tax details. I have purposely left off MOSPEC, it was a special tax that was only charged last year in MO and is no longer valid.

I set up my customer ABC AUTO with the tax schedule MOTAX, and I assign KSTAX as the default tax schedule for sales in Company Setup. VALID is specified in my Receivables Management Setup as the tax schedule for Sales.

Let's assume that we have chosen to have shipping method determine default tax schedule. So if I enter a Receivables invoice for ABC AUTO, the tax schedule will default as follows:

• Shipping Method with a delivery type: MOTAX (from Customer Address)


• Shipping Method with a pickup type: KSTAX (from Company Setup)

So, lets assume I use a Shipping Method with a delivery type, so MOTAX defaults. This would then be compared to the VALID tax schedule specified in setup. What is in common?

• MOSTATE, KCCITY, JCKCTY are the only taxes that would be calculated, since MOSPEC did not exist in the tax schedule in Receivables Management setup for comparison

I think of the tax schedule specified in setup as my "control" schedule, containing all valid tax details to be used in comparison against the documents. In many cases, this would be all details. But in some cases, as outlined above, it may be easier to remove a tax detail from the one control/setup schedule than to remove it from all schedules that contain it. This is also a great approach if a tax is only valid (or invalid) for certain portions of the year (think of sales tax holidays).

Please post back your questions or comments or examples to help with the understanding. I promise more next week on distribution. Have a great weekend!

Update from 9/2: Mike Lupro was kind enough to share his naming convention for tax schedules and details. Mike says, "I recently implemented a pretty simple tax scheme for a client and found myself getting wrapped around the axle with schedule names vs. detail names. I finally used the following naming scheme so the client could tell if they were dealing with a schedule or a detail. And to easily have the items sort into a state-by-state order. It also helped them decide if it was a purchasing schedule/detail vs. a sales schedule/detail. Overall it was a pretty simple setup and the naming convention would get more complicated if the taxes were more granular." Thanks Mike!

Forecaster Users and Dynamics GP Users

Do you ever have an issue come up, and you know you read about it somewhere...but you just can't find the KB article or the blog lnk. But it still keeps eating at you?

That happened to me this morning, when a client had tried to take her Dynamics GP user ID and make it work for Forecaster 7. Needless to say, it didn't work :) Because of the encryption that GP uses when creating a user ID in SQL, that same user ID will not work with Forecaster even if you assign the appropriate database permissions.

I thought I would post it here, so I remember it the next time :) The client does get points for trying to be efficient, though.

Integration Manager error with a twist: All windows and palettes must be closed

A few days ago on Experts Exchange, I fielded a question regarding the very familiar Integration Manager 9 error:

"The destination could not be initialized due to the following problem: All Microsoft Business Solutions - Great Plains windows and palettes must be closed before starting an integration."

This is a common error, but is usually easily solved by just closing any open windows. What made this a little unusual were some of the details:

1) IM 9 was being run in a Citrix environment
2) IM worked fine the first time the integration was run, but after that, the error occurred
3) The user was sure that all GP windows were closed

The only thing that seemed to be uncommon was that they were running IM in a Citrix environment. So we tried checking for orphaned Dynamics.exe processes, and even tested running the integration when no other users were on the server. But the problem still occurred.

The user shared that if IM was shut down and restarted, the error still occurred. But if GP was shut down and restarted, the error went away for one more integration (but would still happen again after the first integration completed).

After a few rounds of questions, the user posted a key piece of information: The user was logging into a different GP company after the first integration completed and running the same integration in a second GP company.

So I installed IM 9 in a test environment, then had to create a test company in GP 9 so that I had two companies to switch between. I ran the sample GL Transaction integration into TWO, and it ran fine. I then switched to my Test company, and when I tried to run the same integration, sure enough, the error occurred.

I knew I didn't have any GP windows open, so something else was causing the problem. Staring at GP for a few seconds, it occurred to me that the GP "Home Page" was technically a window. Curious, I clicked on the "X" to close the home page, ran the integration again, and violá, IM ran just fine.

I tested this several times and confirmed that when switching companies between IM integrations, IM considers the GP home page to be an open window.

So, I offered that the user either manually close the Home Page between integrations after logging into the second company, or change their settings so that the Home Page does not open. Thankfully, that solved the problem for them as well.

The Ever Expanding Payroll System

In the past couple months, I have fielded requests from a variety of clients for add-on modules for Payroll. The base payroll module in Dynamics GP has some great functionality; but as companies look for greater efficiencies and ways to streamline operations, they start to look for ways to expand the functionality of the system to better meet their needs. What was once a manual process may need to become an automatic process. What was once handled through printing, folding, and sending may now need to be sent through email or delivered through a portal.



So, I thought I would share some of the options I have put out there to help with the processing of payroll. This is by no means a complete list, so please share additional options in the comments and I will update the blog entry to include them :)



First, don't forget about the Payroll Extensions. Unless customers opted out of these, they get them automatically (for a small bump in their product list cost, and therefore enhancement costs). Functionality to track deductions in arrears, to pass liabilities from payroll to payables (e.g., deductions, taxes, etc), and to blend overtime rates is included in the Payroll Extensions.



Advanced Payroll is an additional module (which means additional cost) from Dynamics GP, that adds some specific functionality to the payroll module. It will not apply to everyone, but for those that have the need, it can be a great fit. Capabilities include posting payroll hours to unit accounts in the general ledger, managing pay policies which include different pay rates based on shift, department and/or position, calculation and reversal of payroll accruals, allocation of employee costs across departments, and advanced labor reporting functionality.

PTO Manager is a relatively new member of the family, and it can replace the standard payroll or human resources vacation and sick accrual functionality while adding functionality like allowing two accrual schedules to be applied to vacation time simultaneously (for example, a base schedule plus a bonus schedule given to select employees) and management of carryover hours.

If we move outside of the Dynamics GP client, in to Business Portal, there is a whole other world that opens up in terms of employee self service, including:

• Benefit Self Service: Online access for employees to enroll in benefits. Refreshingly easy to configure and control, I am repeatedly surprised by what a simple, yet functional, add-on this can be. My husband works for a large corporation that uses a large product that is not nearly as easy to use, or as intuitive.
• Human Resources Online: Online access for employees and managers to access pay stubs, personal information, skills and training, as well as other HR features like recruitment.

Okay, so those are a few of the Microsoft ones that are out there. But how about third party products? Here are a couple that keep coming up in my discussions with clients:

• Greenshades: I used to think of them as primarily dealing with taxes, which they still do...offering a variety of tax solutions for integration with Dynamics GP payroll. However, they also have a variety of other products to address additional PTO management needs, paperless payroll (think sending pay stubs and W-2s electronically, and online access to employee information).
• Integrity Data: Another familiar face that also offers leave management (with online self service capabilities), position and FTE control features, employee allocation capabilities, and emailing of earning statements.
• JAT: Offering carrier interface (for those using HR) for benefit information, as well as electronic delivery of pay stubs and online access to W-2 information.

I am sure there are many more out there that I should mention, so I will also put a plug in for www.isv-central.com and the Microsoft Solution Finder as great tools to assist in your search for that perfect module :)

Have a great end of your week!

Partner Support Resources for Dynamics

I am sure many of you are already aware of the forums out there, both those sponsored by Microsoft, and those that are independent (e.g., Experts Exchange, Egghead Cafe, etc). But I thought I would send out a reminder of the partner-specific resources available to Dynamics partners. Check out the links on https://mbs.microsoft.com/partnersource/resources/discussions/.

In addition to the public forums, there are private forums for partners only, and (my personal favorite) the partner online technical communities-- which are managed by Microsoft Dynamics support folks. The technical communities can be a great way to build knowledge and leverage your support plan without using a case. I find myself posting there with questions that I would never "waste" a case on, but would love to have a definitive answer on.

These forums (as well as search engines and blogs) can be a great resource when you have issues that either do not warrant or a case, or you just want another opinion. Personally, I also enjoy running across the same folks in different forums, it starts to feel like a community of sorts (which, for those of you who know me, is one of my favorite things to talk about...the value of building relationships in the Dynamics community).

Update from the fabulous David Musgrave (Thanks David!) of a few more that he regularly checks out...

• Google groups
• Tek Tips

Please share some of your favorite independent forums, and I will update this post with them. Happy hunting!

Sending XML Files to eConnect

I have been working on an eConnect integration that needs to import up to 40,000 sales transactions a day into Dynamics GP 9.

The challenge is that the data is coming from an off-site eCommerce web site, so we had to export the data from the web site so that we could transfer the data for import to GP.

After considering the pros and cons of CSV and XML, we chose XML because we thought that it would be easier to use XML to handle the header, lines, taxes, and payments in one file rather than having to deal with multiple CSV files. In this respect, we were right--it is easier to have one XML file to process vs. four CSV files. But in other respects, most of which I didn't anticipate, sending XML files to eConnect has some challenges.

When the client offered to prepare and export the XML files for me in the eConnect format, I thought it was great, since it would save me time--I would only need to read the XML file and just pass it over to eConnect. Simple, right? Famous last words.

It turns out, that for this project, importing XML files was moderately complex and posed some challenges.

My first challenge was trying to validate the structure of the XML file, which the client was creating. Normally the XML would be created by the eConnect .NET Serialization assemblies, in which case you never have to worry about the physical format of the XML file. But when the XML is being created by another system, I thought it would make sense to try and validate the format before import.

I thought this would be pretty straightforward, since eConnect provides XSD files that you can use to verify your XML files. After hours of trying to get the XSD validation to work, and a few posts to the great gurus at Experts Exchange, I realized that the XSD validation process was surprisingly impractical and was useless for my project. The eConnect XSD files utilize a few options that make the validation so rigid that I was unable to use it. And more surprising, because of the way XSD validation is performed, the process only returns one error at a time. In XML files with 2,000 to 4,000 transactions each , validating and fixing the XML file format one error at a time was impossible. I ended up just sending the XML file to eConnect and using those more informative errors to get the XML file format correct.

Next, I had to be absolutely sure that eConnect would accept and process XML files with multiple transactions. Processing 40,000 transactions a day using individual transation files was completely impractical, so we really needed to be able to send at lesat several hundred transactions in each file. I thought it would be possible, but with eConnect, until I see it working, I don't make any assumptions. And I also didn't know how sending large files to eConnect would affect the performance of the integration. Fortunately, once we worked out all of the kinks in the XML format, I was able to send a single XML file with 2,000 transactions to eConnect and all of the invoices were created successfully. I have since been able to send a single SOP invoice file with 4,700 transactions and have it import successfully. (see caveat below)

And of course, there is data validation. When you send a single file with thousands of transactions to eConnect, a single data error in that file will cause the entire file import to fail. So it is critical to validate the file thoroughly before attempting to import it, checking most of the node values to confirm that they will not cause a failure. I am validating customer IDs, item numbers, item types, SOP types, transaction dates, document subtotals vs. line totals, and tax IDs. I also have to make sure that the items have valid price lists, item site records exist, and that the ship method is valid. Fortunately this is easy to do with XML and the validation is fairly fast.

And then came the dreaded eConnect "System Error" messages. I haven't run into it this error yet with eConnect 10, but it is definitely an issue in GP 9. A System Error is returned by eConnect for various reasons, but unfortunately eConnect doesn't provide any additional information, so you have to determine the cause through guessing and trial and error. The most common reason for the eConnect System Error is that an XML node is missing. In some cases, nodes are only required under certain circumstances, so although the eConnect help file says that the node is optional, it may actually be required for your particular transaction scenario. So after hours of testing and troubleshooting, we were finally able to identify the cause of the System Errors. In some cases, a node was missing, and in other cases, it was because some of the nodes were not in the exact order that eConnect exptected (XSD theoretically checks for this, but as I described, it just isn't practical).

So all is well and I'm able to process the large XML files on my development server. But when we deployed to the client's server, we received System Errors yet again. We are able to process small files (with 250 transactions) on the client server, but the file with 4,700 transactions fails. I guessed it was because of some slight configuration difference between the client's GP environment and mine, and that one or more transactions were causing the error.

So I wrote a new import routine that would take the single XML file and import each transaction individually. This way we could find the individual transactions that were causing the failure. Once we installed the new integration and tested it, to our surprise, all 4,700 individual transactions imported fine. Our only conclusion is that there must be something on the client's server that caused the large files to fail--a problem that I don't have on my development server. We don't yet know how many transactions we can include in a file, but we will be trying different file sizes to see what will work consistently.

Finally, while doing live imports, I discovered a new quirk of eConnect 9 that surprised me. After importing SOP invoices, we were importing SOP Payments (taCreateSopPaymentInsertRecord) from a separate file. My validation routine detected that there was one error in a file, but somehow the entire file imported successfully. A payment for an invoice that did not exist in GP was not rejected by eConnect. I then created a test payment XML file with a fake customer and fake invoice number. Sure enough, eConnect procesed it without returning any errors--but of course the payment record did not appear in GP. Clearly eConnect 9 has a hole here.

So that's a summary of my adventures. Now that I've been through it, if I had a choice, I would generally not recommend trying to create XML files outside of GP and send them to eConnect. It's tedious, time consuming, and error prone to reverse engineer the eConnect XML format. It is so much easier to use the .NET eConnect Serialization assemblies than to debug and validate XML files. But as this project demonstrated, sometimes the choices are limited, and you just have to go off-roading and figure out new solutions.

Getting to Know and Love the SQL Server Browser Service

A while ago I wrote about some problems I had logging into GP that were caused because the SQL Server browser service was not running.

I'd like to (belatedly) follow up with an explanation of the history behind the SQL Server Browser service, and why you should make sure it is running on a Dynamics GP SQL 2005 server.

Back in the days of SQL Server 7.5, you could only have one "instance" of SQL Server on a machine. That single SQL server listened for connections on port 1433. Life was simple, and all was peaceful. (except for the single instance limitation)

With the release of SQL Server 2000, you could install multiple SQL instances on the same machine, effectively allowing you to host multiple SQL Servers on one physical box. This was great, but there was one problem. Two or more SQL instances couldn't all listen on port 1433. So a new protocol and dynamic port design was developed that would listen on port 1434, and serve as a directory of sorts, telling clients the names and port numbers of SQL instances installed on the machine.

If you've ever had to troubleshoot obscure connectivity issues with SQL 2000, you probably know that it can be frustrating to troubleshoot down to the port level, since you could never assume what port a given SQL instance might be using. And there were also some challenges related to configuring the client communication protocols, causing headaches determining if named pipes or TCP/IP wasn't working properly.

Well, with SQL Server 2005, a new solution was introduced to help make life easier with regard to SQL Server ports and client connectivity. One element of that solution is the SQL Browser service.

The SQL Browser service helps clients identify and connect to SQL instances, and SQL Server Configuration Manager allows you to specify client connection and protocol settings on the SQL Server, rather than having to configure each client PC individually. When used with the SQL Native Client Driver on workstations, this is a nice improvement, reducing installation and administration effort.

Microsoft MSDN has a nice page that briefly covers the background I just described, and provides some additional details on the mechanics and operation of the SQL Browser service.

But, alas, the Achilles Heel of all of this is that the SQL Browser service may not be configured to start automatically when SQL Server is installed. So if you run into obscure problems and spend an hour or two scratching your head wondering why GP won't connect, make sure to add the SQL Browser service to your troubleshooting checklist.

Dynamics GP Window Is Not Visible or Is Hidden

Quick post to share a quirk that I ran into recently. Nothing complex, but it was annoying.

I had been using GP 10 on a remote Terminal Server, and had minimized the main GP 10 window before I disconnected my session. When I logged back into the server, GP would not maximize or resize--it just stayed in the task bar but would not display.

I thought it might be temporary, so I tried closing it, and then relaunching it, with no luck. Even logging out, and logging back in didn't work.

I knew there was a config file storing the window location, but it has been several years since I last had this issue.

The setting is in the Dex.ini file, which for GP 10 is now located in the Data subdirectory for GP.

If you search for the WindowPosX and WindowPosY values, you will want to set those to a reasonable positive values, like 100:

WindowPosX=100
WindowPosY=100

In my case, the values had both been changed to -32000 for some reason, which explains why I couldn't see GP!

Once I set them both to 100 and saved the Dex.ini, GP launched fine.