Why it pays to read old quality reports

So, every once in a while, you stumble across an old quality report referenced in a knowledge base article. Usually I look at it, see it was resolved with a service pack to version 6, or 5.5, and I think "oh, well, that's not the problem". But today taught me that sometimes old quality reports will shed some light on a current situation.

So, here is the scoop. A client emails me asking why they see duplicate records in the RM30501 (Commission History table). They are paying commissions when invoice is posted (not when invoice paid). They see something like this in the table:

STDINV2599 with commission amount $100
STDINV2599 with commission amount $100
STDINV2599 with commission amount ($100)

Each entry has its own sequence number. So I do some testing. I enter an invoice, post it, void it, and see if I can come up with the same entries in the RM30501. No luck. So I told the client I needed to take a look on their system. We poke around for a bit, chasing one wild hair after another with no luck.

And then I start searching randomly in the knowledge base and I find an old article referencing an issue on version 6 that was resolved with a service pack. In the scenario they gave, if a payment was voided that was greater than the invoice amount, it would lead to negative commissions appearing on the commission report even if commissions where supposed to be paid when the invoice was posted (not when invoice was paid). Not quite the issue that started this, but promising enough. So here is what I tested:

1. Enter invoice in SOP and post
2. Pay invoice with payment greater than invoice amount (so a portion of the payment is unapplied) and post
3. Move invoice to history using Paid Transaction Removal (this step will move the commision record from the RM10501 to the RM30501)
4. Transfer commissions to mark the commission as paid
5. Void the payment recorded (this step will move the commission record back from the RM30501 to the RM10501, but more importantly this is where two additional records are created for the commission in the RM10501- one positive, one negative, so the net is correct but the detail is not)
6. Transfer commissions again, this time a negative commission will appear (even though we are paying commissions when posted not paid, so the voiding of an invoice should not matter)
7. Print Reports>>Sales>>Commission>>Commission Dist by Salesperson and you will see all three transactions for commissions: the original plus the two erroneous entries created by the void

Strange, huh? The commission distribution report is not a huge issue since it nets correctly, although it is troublesome that the detail is incorrect. A bigger issue is the transfer commissions journal, since that incorrectly shows a negative commission transferred and is often used as an entry list for paying commissions from payroll or payables.

Anyway, Microsoft is able to reproduce the issue, so we'll see what resolution they provide. I am a bit curious if the quality report came back after being resolved, which makes me wish I had a version 8 install to test on as well. I will definitely update this blog with any additional news. In the meantime, you may want to monitor closely if you use the transfer commissions journal to pay commissions. And definitely share any stories you have of long lost quality reports rearing their ugly heads :) I will share any posts I get.

Have a great holiday weekend!

SQL 2005: Errors due to SQL Server Browser not running

With SQL 2000 I was fairly proficient with troubleshooting connection issues, adjusting the client library network settings, and even dealing with dynamic ports and different SQL Server instances. But with SQL 2005, a few features were introduced that change how I troubleshoot connection issues. The features probably help to reduce the number of connection issues by eliminating most client configuration, but when issues do come up, my impression is that they can be more difficult to diagnose with SQL 2005. Fortunately, the resolution is typically simple, but diagnosing the problem can be very frustrating.

Two times in the last several months, I've run into a problem where I simply cannot connect to SQL Server 2005 from another computer. I know the SQL service is running, and I can login to SQL from the server itself, but client workstations and other servers are unable to connect. This has prevented me from logging into Dynamics GP, or it has prevented an eConnect integration from working.

The error message I receive looks something like:

SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [0xFFFFFFFF].

[SQL Native Client]Login timeout expired

[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Reading this message might have you think, "Ah, I forgot to enable remote connections!". But then you check the server settings, and you find that Remote Connections are in fact enabled. At this point, the error message becomes misdirection.

You can ping the server, and browse the server shares, so the network is okay. And strangely, you may find that every other workstation in the company can login to GP and connect to the SQL Server without any issues. So you are then possibly tempted to think that it is a workstation issue--again, more misdirection.

The next step is to Google the message, which will provide tons of fruitless newsgroup posts by people who have the exact same problem, but also lacked an answer. Until you stumble onto this excellent post by the Microsoft SQL Server Protocols Team (a protocols team!, who knew!).

It provides an exhaustive list of SQL Server 2005 connection errors, along with actual English descriptions of what those cryptic or kurt messages actually mean. For each error, they also provide some recommended steps for troubleshooting and resolving the error message.

In my case, I was running into lovely error Message #6. And their recommendation: You should enable the SQL Server Browser service on the server.

Sure enough, the SQL Server Browser service was not running. Once I configured and started the service, the login errors instantly disappeared and everything worked fine. Neither the error message, nor the symptoms (other workstations were fine) would have naturally led me to think of the SQL Browser service, and even though I have run into this exact issue before, I simply forgot the role that the Browser service plays with SQL 2005.

In my next post, I'll discuss the SQL Server Browser service, what it does, and why we need it.

White Papers Aplenty

Sometimes it feels like I go through phases where white papers just seem really applicable to issues that come up both within my company and with customers. Having just been through one of these phases, I thought I would share some of my favorites...


Microsoft Dynamics GP and SQL Server 2008
Partners: https://mbs.microsoft.com/partnersource/documentation/whitepapers/mdgpsqlwhitepaper.htm?printpage=false
Customers: https://mbs.microsoft.com/customersource/documentation/whitepapers/mdgpsqlwhitepaper.htm?printpage=false
Interesting whitepaper on the benefits of compression with SQL Server 2008, including benchmarking results on performance.


Choosing a Development Tool for Microsoft Dynamics GP 10
Partners: https://mbs.microsoft.com/partnersource/documentation/whitepapers/gp10_toolswhitepaper.htm?printpage=false
Customers: https://mbs.microsoft.com/customersource/documentation/whitepapers/gp10_toolswhitepaper.htm?printpage=false
This is a great overview of the available development tools, for both developers and application consultants to familiarize themselves with the licensing costs as well as the skills needed to successfully use them, and the capabilities of each tool.


Planning for Security in GP 10
Partners: https://mbs.microsoft.com/partnersource/documentation/whitepapers/msdygp10_securityplanwp.htm?printpage=false
Customers: https://mbs.microsoft.com/customersource/documentation/whitepapers/msdygp10_securityplanwp.htm?printpage=false
This serves as a starting point for security discussions, and it looks at security in a holistic way (not just within the Dynamics client specifically). It reminds me that security is more than roles and tasks.

Deploying Microsoft Dynamics GP 10 in a Multinational Environment
Partners: https://mbs.microsoft.com/partnersource/documentation/whitepapers/deployinggpmultinationalenvironment.htm?printpage=false
Customers: I could not find this on CustomerSource, but please feel free to post on this blog if you cannot access.
This is an older white paper, but I always re-read it before embarking on a multinational implementation. It just helps remind me of the installation considerations and pitfalls of multiple countries, localization requirements, and languages.


Strategies for Multi-Entity Environments in Microsoft Dynamics GP 10
Partner: https://mbs.microsoft.com/partnersource/documentation/whitepapers/strategiesmultientityenvironmentsgp10.htm?printpage=false
Customer: https://mbs.microsoft.com/customersource/documentation/whitepapers/strategiesmultientityenvironmentsgp10.htm?printpage=false
This is another older white paper, but it again reminds me of the considerations of one database -vs- many. I have a worksheet I have developed to assist with the assessment, but this document definitely helped me refine it further.

Achieving Compliance Objectives in Microsoft Dynamics GP 10

Partner: https://mbs.microsoft.com/partnersource/documentation/whitepapers/helpachievecomplianceobjectivesgp.htm?printpage=false

Customer: https://mbs.microsoft.com/customersource/support/documentation/whitepapers/msdygp_whitepaper_achievecomplianceobjectives.htm?printpage=false

This white paper presents a general view of compliance, how to begin the process, and some aspects to review. It is a great starting point for those that are new to compliance requirements, and need some assistance in where to begin.

Please share your favorite white papers, and I will update the post. You can find many more white papers through Deployment>>White Papers on PartnerSource, or through Documentation>>White Papers on CustomerSource :)

Add Fields to Visual Basic does not work in GP 9

I've run into this several times, but I never bothered to figure out the cause or solution.

In GP 9, sometimes when you attempt to add new fields to Visual Basic, the fields will not appear in the VBA editor. The window will appear in the VBA editor, but the fields will not be available in the object drop down list in the code window.

Thankfully David Musgrave solved this long ago in this eggheadcafe.com post.

It seems to be caused by a bad Dynamics.vba file. When I deleted my Dynamics.vba file, the fields added fine.

Nifty Freebies

Sometimes I get questions that make me ask myself, "Why hasn't someone asked me that before?". Of course, in these cases, the resulting answer is never as simple as "there is a setting for that". But I have learned over the years that although something is not an option directly in Dynamics GP that I may be lucky enough to find out that there is a free Professional Services Tool to provide the functionality I need.

For those of you that are not familiar with them, the Professonal Services team is a fabulous group of folks who offer a wide range of services and tools to customers and partners.

For partners, check them out at... https://mbs.microsoft.com/partnersource/resources/support/supportinformation/msdy_winwithexpressconsulting.htm

For customers, you can get similar info at...
https://mbs.microsoft.com/customersource/serviceplans/professionalservices

As you might be able to tell already, I am a big fan of this team. They are great folks, and also crazy smart when it comes to finding creative solutions to issues.

But, back to the freebie tools. Some great examples of these follow, paraphrased/borrowed from the PSTL documentation:

Default Add Item for POP/SOP

• Changes the Add Item option to default as marked in Sales Transaction Entry, Purchase Order Entry, POP Receivings Entry, and Purchasing Invoice Entry. A must have for companies that do not want to allow non inventory items, or want to "opt in" to non-inventory items as opposed to assuming that anything keyed incorrectly is non-inventory.

IV Post To GL

• Changes Inventory batch functionality so it looks at the Posting Setup to determine if the batch should update the General Ledger. I love this one, because it prevents the confusion that inevitably comes from having to select/deselect this option every time.

SOP Default Site per Line

• Affects how a site defaults on sales order line items. Instead of using the default site from the SOP window, it uses the default site assigned to the item under Cards>>Inventory>>Quantities/Sites. I love this one, because it addresses the needs of companies with multiple warehouses the stock different items.

Payables Select Checks Combiner

• Consolidates checks to the same vendor when adding to an existing check batch. This is great tool when users build batches by different ranges, and repeatedly add to the same batch.

And just one more...

Toolkit

• Rebuild the GL00105 without a SQL query tool, turn on/off the dexsql.log, rebuild indexes, and recreate dex procs and tables. This is a great resource for internal IT support to perform many common support tasks.

A reminder from fellow blogger David Musgrave, to not forget about the Support Debugging Tool, another fabulous freebie that partners can use to increase their efficiency in solving issues. Here is a link to David's blog post on it:

http://blogs.msdn.com/developingfordynamicsgp/archive/2009/06/05/support-debugging-tool-build-11-released.aspx

Please feel free to share your favorite Professional Services Toolkit items, free or otherwise. I will update the post with additional suggestions :) Have a great week!

Did you know? Project Macros

How many of you have been caught off guard after installing project accounting on existing Dynamics GP installation, when it begins asking users if they want to add project info for vendors every time they try to enter purchase orders?

Many of you may already realize that GP support has a set of macros, one for Customers and one for Vendors, that will run through and save the default settings for project. A little thing that saves users from having to either populate the records manually, or respond to the message every time.

Feel free to email me directly for more info, christinap@theknastergroup.com.

Land of the Landed Costs- Part 2

So, here I am with part two of the landed cost saga finally. Last time, we went through the process of setting up landed costs. In our example, we were setting up insurance costs so that the invoice from the insurance vendor could be matched back to the shipments received from the product vendor. But, before we get down the road that far, let's overview the three key ways that landed costs can be used.

Landed Costs by line item

• Specify a landed cost group on a line item when recieved (can default from the item setup, from the purchase order, or manually entered)
• Calculated amounts can be adjusted by line item on receipt

Landed Costs by apportionment

• Specify an individual landed cost for the entire receipt document
• Apportion the amount across the items on the receipt by qty, extended cost, or weight
• Landed cost must use flat amount cost method

Landed Costs by invoice match

• Landed cost group specified by line item on receipt (default from item setup, or from the purchase order, or manually entered)
• Does not have to calculate an estimate amount on receipt, but it can.
• Individual landed cost must be set up for Invoice Match, and for Revalue for Cost Variance if applicable
• Use Enter/Match Invoices to enter an invoice from the landed cost vendor, and match it to the shipment from the product vendor

So, let's take a look at each of these options. In Landed Costs by line item, we can specify a Landed Cost Group on the Purchase Order line item.

Transactions>>Purchasing>>Purchase Order Entry, click on a line item, and then click the expansion arrow to the right of the Item header.

Remember, this value defaults from the item/site combination in Cards>>Inventory>>Quantities/Sites. However, it can be changed here to provide a different default to the receipt.

So, when we get to Transactions>>Purchasing>>Receivings Transaction Entry, the landed cost group will appear on the line item once again. You can view it in the same way as you did on the purchase order, by clicking on the line item and then clicking on the Item header expansion arrow. However, you may find it more helpful to view the actual landed costs being calculated for the line item, based on the landed cost group assigned. This can be done by clicking on the line item, and then clicking on the expansion arrow to the right of the Unit Cost header.

You can override the amounts specified here. The percentage or amount fields will be available based on the calculation method you defined for the landed cost.

Also, on the receipt, we can do Landed Costs by Apportionment. To do this, we simply click on the Landed Costs button at the bottom of the Receivings Transaction Entry window to open the Receivings Landed Cost Apportionment window.

In this window, you can select Landed Costs to apportion across the entire receipt. You must select a landed cost that is setup with a flat amount calculation method in order to select quantity, value, or weight in the "apportion by" field.

The system calculates each method as follows:

• Quantity: (Line item's quantity shipped - the quantity rejected)/(Sum of all line items' quantity shipped- the quantity rejected)
• Value: [(Line item's quantity shipped -the quantity rejected)*Originating Unit Cost]/Sum of all line items [(quantity shipped -the quantity rejected)*Originating Unit Cost]
• Weight: (Line item's extended shipping weight)/(Sum of all line item's extended shipping weight)

In either case, Landed Cost by Apportionment or Landed Cost by Item, the distributions that result are the same:

• Debit to Inventory
• Credit to Accrued Purchases for Landed Cost (per Landed Cost Maintenance)

So, that leaves us with the last method of Landed Cost by Invoice Match. In this example, let's assume that we posted the estimated landed cost of 10% of extended for the INSCARRIER landed cost on the shipment receipt. Now, we have received an invoice from the actual insurance carrier, Associated Insurance, and find that the costs were actually much greater. Since we set up the INSCARRIER landed cost for invoice match, we can now record the invoice from Associated Insurance and match it back to the shipment from Advanced Office Systems.

To do this, we go to Transactions>>Purchasing>>Enter/Match Invoice and enter an invoice for Associated Insurance (NOT Advanced Office Systems):

There are just a few key differences in how you enter the landed cost invoice:

• Vendor ID is the landed cost vendor
• Mark the "LC" checkbox for the line item to identify it as a landed cost
• Select the landed cost to match for the item (rather than an actual item)
• Match the landed cost to the original shipment from the product vendor using the Matched to Shipment expansion button

The distributions that result depend on whether you have selected to Revalue Inventory for Cost Variance:

If you are revaluing:

• Debit to Inventory (if cost is greater than receipt)
• Debit to Accrued Purchases for Landed Cost
• Credit to Accounts Payable

If you are not revaluing:

• Debit to Purchase Price Variance for Landed Cost (if cost is greater than receipt)
• Debit to Accrued Purchases for Landed Cost
• Credit to Accounts Payable

So, I hope this helps clarify the different ways to approach landed cost in Dynamics GP. I find that some clients will use all three methods, but many also settle on one or two ways that work best for their goals. Please share your experiences, questions, etc.