SQL – Autogenarating Customer IDs

February 28, 2010

I have been swamped lately working on 3 different projects, and it’s been difficult to get back to blogging as often as I am used to. However, this week I found myself with an interesting situation where my client needed to import a customer master list into GP, but was migrating from a system that autoassigned an internal key to each customer record. My client wanted to move away from this “unique internal number” to an alphanumeric nomenclation similar to that of Fabrikam, for example, Aaron Fitz would have a customer ID of AARONFIT0001. They thought it was a very intuitive way of identifying customers and that it would serve the AR staff well.

The customer data was staged in a SQL database and the goal was to create a query that will assign these customer IDs based on the customer name. So, here is the solution:

AutoCustomerID.sql

-- Created by Mariano Gomez, MVP
SELECT
 -- evaluates the 9 first characters of the customer name and removes any blanks
 -- other characters can be removed) in between those first 9 characters for a
 -- total of 8, adds an extra zero if needed to complete 9 characters
 CASE LEN(UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', ''))) 
   WHEN 8 THEN UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', '')) + '0'
   ELSE UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', '')) END +

 -- accounts for the rest of the string, uses the rank function to do the numbering,
 -- partitioning by customer name. Just in case there is more than one customer
 -- with the same starting 9 characters, rank() will number them sequentially
 SUBSTRING('000', 1, 3 - LEN(CONVERT(CHAR(3), RANK() OVER(PARTITION BY REPLACE
   (SUBSTRING(CUSTNAME, 1, 9), ' ', '') ORDER BY CUSTNAME))))
   + CONVERT(CHAR(3), RANK() OVER(PARTITION BY REPLACE(SUBSTRING(CUSTNAME, 1,
   9), ' ', '') ORDER BY CUSTNAME))
FROM RM00101
ORDER BY CUSTNAME

Run this against the TWO database to see the results.

If you ever have the need to create customer IDs based on the customer name this should give you a starting point.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Microsoft Dynamics Convergence 2010 Sessions

February 23, 2010

As mentioned in my earlier post, See you at Microsoft Dynamics Convergence 2010, I would provide details of the sessions that David and I will be presenting. So here they, please make sure you attend these sessions as we have some great material to show you.



CSGP28 – Microsoft Dynamics GP: Customization Tools Unravelled

Session Type: Concurrent Session
Track: Microsoft Dynamics GP
Skill Level: 200 – Intermediate

Session Description: If you have plans for a Microsoft Dynamics GP customization and don’t know where to start, or are wary of upgrade issues, then this session is for you. Gain some introductory knowledge of Dexterity, Visual Basic for Applications, Visual Studio Tools, and Extender from two of the leading Community experts. We’ll compare and contrast all of the tools and help you sort through the complicated terminology, leaving you with a clear picture of the choices available for your next project.

This session will be repeated as CSGPR28.

CSGP29 Tools for Administering Microsoft Dynamics GP like the Pros

Session Type: Concurrent Session
Track: Microsoft Dynamics GP
Skill Level: 200 – Intermediate

Session Description: Microsoft Dynamics GP administration can be a very challenging task even for a seasoned systems administrator. Learn how to minimize and simplify administrative activities, such as security issues and problems with customizations, by discovering some of the tools and tricks used by the Microsoft Support Organization and its partners. Presenters will demonstrate real life scenarios and work through their resolutions.

This session will be repeated as CSGPR29.

For a full list of sessions, check out the Session Catalog page. David and I will be around at other times, so please join us.

Hope to see you there.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Microsoft Dynamics GP 2010 – General Availability Release

February 18, 2010

Just got off a conference call with some of the Microsoft Dynamics GP executives who announced general availability of Microsoft Dynamics GP 2010, the highly anticipated new release of Microsoft’s ERP solution for the SMB market, on May 1, 2010 — just five days after Convergence Atlanta event.

In preparation for the launch event, Microsoft has scheduled a number of partner roadshow events around 19 cities in the United States, giving partners the opportunity to come in contact with the product and learn the key closing strategies. If you are a Dynamics GP customer, expect a number of partner events that will give you a chance to experience the GP 2010 buzz.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


New Article on MSDynamicsWorld: MVP Frank Hamelly on "Keeping your Axe Sharp"

February 18, 2010

“If I had six hours to chop down a tree, I’d spend the first four hours sharpening the axe.’ – Abraham Lincoln”

MVP Frank Hamelly quotes Abraham Lincoln in his new MSDynamicsWorld article and presents a tale of sorts to illustrate the importance of keeping your skills “axe” very sharp through training. Frank also discusses the different training options available and how employers and employees can take advantage of these to improve productivity during these difficult times.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com


Support Debugging Tool for Microsoft Dynamics GP Build 12 now available

February 17, 2010

For those of you who had the opportunity to be at last year’s Microsoft Dynamics GP Technical Conference and attended the Troubleshooting session presented by David Musgrave and I, you had a preview of the administrative and troubleshooting capabilities available in Build 12 of the Support Debugging Tool. After the tremendous feedback received from YOU at this event and a round of revisions, David Musgrave has released the final version of Support Debugging Tool Build 12.

Please be sure to check out the improved Security Information window, which can now be used as a security explorer if no resource is selected.


Security Information window

Downloads

Support Debugging Tool for Microsoft Great Plains 8.0
Support Debugging Tool for Microsoft Dynamics GP 9.0
Support Debugging Tool for Microsoft Dynamics GP 10.0

NOTE: The tool is posted on PartnerSource and so can be downloaded by partners. If you are a customer, you will need to obtain the tool through your partner. Any feedback or questions about the tool will be handled via the GP developer newsgroup.

Make sure you check out the Installing the Support Debugging Tool for Microsoft Dynamics GP FAQ available at Developing for Dynamics GP.

For other related articles and posts have a look at the Support Debugging Tool tag on this site.

Please go over to Developing for Dynamics GP to let David know what you think about this new build.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Microsoft Dynamics GP 2010 – Service Call Management Enhancements, Part 2

February 17, 2010

You had a chance to review the first set of enhancements in my Part 1 article. Now we will review a second set of features to conclude the Microsoft Dynamics GP 2010 Service Call Management enhancements series.

Generate payables vouchers for subcontractor charges

You can automatically generate vouchers in Payables Management for subcontractor labor, expenses, or additional charges. The vouchers are created during service call billing. To enable this feature, you must specify a Vendor ID for each subcontractor technician. You also can designate subcontractor service types.

Post labor information to U.S. Payroll

You can now post labor information from Field Service to U.S. Payroll. New setup options, Payroll Integration and Post to Payroll, are added to the Service Setup window. When these options are activated, Field Service integrates to U.S. Payroll for labor or indirect labor. Payroll transactions can be based on the information that is entered in the Service Call Entry – Labor, Labor Information, and Indirect Labor Entry/Update windows, and in a new window, Field Service Labor – Payroll. This feature also integrates the cost from the employee’s pay code to the labor record in Field Service.


Service Setup window


Service Setup – Service window


Service Call Entry – Labor & Labor Information Windows

Use kit items in the Field Service Series

You can add kit items to parts lines, and you can make changes to the component lines, including quantity ordered. You can create a purchase order from a kit item or from one or more component items. If you do so, the kit item cannot be transferred, but the individual inventory components can. You can specify a kit as a part for an engineered change order. The resulting service calls that are generated include the kit item and the components that are specified in a new window, Service Parts – Kit Components.


Service Call Entry – Parts window

One thing I found interesting when entering a kit on the Service Call Entry – Parts window is, when a kit is selected you have one and only one chance at entering the quantity being ordered before the code breaks down the kit into its components on the Parts window. I also think that a validation is missing where users are warned when a component belonging to a kit is removed from the Service Call Entry – Parts window.

Use workflow to manage service call escalation steps

You can now create a standard workflow to manage the approval process for service call escalation steps. A new workflow type, Escalation Override, applies to service call documents in the Service Call Entry/Update window. You can activate the workflow by using the Service Type – Escalation window.

Hope you enjoyed this 2-part series and have had a chance to explore some of the new Service Call Management enhancements. Huge, huge are the integrations to payroll and the ability to post payables vouchers for subcontractors. This certainly reduce the amount of steps users have to engage in when closing a service call. I won’t be surprised you will see more integration with Manufacturing in a future revision of this module.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


"You have too many note windows open. Close a note window" Error Message

February 16, 2010

Recently, a system administrator reported a user getting this error and was asking what could be the possible cause for this.

Replicating the error is very simple, and for this, we need a Microsoft Dynamics GP window where many note windows can be entered/opened at once, take for example the Sales Transaction Entry window, which has access to 7 potential notes: the document type, the document number record note, the customer ID record note, the batch ID record note, the site ID record note, the currency ID record note, and the item number record note.

If you attempt to access all these notes at a time will prompt the system to error out when attempting to open a 6th note window. Taking advantage of the Windows 7 Aero interface, one could see why an end user will not realize they have 5 note windows opened at once.

But now to the technical issue…

To display notes — record level notes — the Dynamics GP development team has created five forms, righfully called Form_Note_1, Form_Note_2, Form_Note_3, Form_Note_4, and Form_Note_5. Each time a note is added, the code will check for the availability of one of these 5 forms to be able to open the corresponding note window so the user can either type a new note or read an existing one. If all note forms are opened at the same time, then an attempt to open a sixth note will fail with the error described at the beginning. The solution? Close an unused note window.

Why not create more note forms? 25 years and 11 major releases later, this is an expensive proposition. There is a lot of code in older Microsoft Dynamics GP forms that handle their own calls to validate which of the 5 note forms is open. In newer form objects, these call checks have been replaced by a single call to methods added to the NoteObj form, but unfortunately, “retrofitting” the entire Microsoft Dynamics GP application to work with the NoteObj form is a project in itself that would require tons of testing to make sure nothing gets broken in the process. Certainly, if all the application could be retrofitted to handle calls to the NoteObj form, then adding an extra note form is not so much of an issue anymore… but there lays the problem. Adding this extra form would also cause all third party developers and ISVs to retrofit their code to manage this extra form too. Then comes the philosophical question… what if we want to open a 7th note, or an 8th… do we keep adding more forms? How many note forms would be enough?

More information on Notes, Note Index and OLE Attachments can be found at:

Developing for Dynamics GP – Click here
The Dynamics GP Blogster (this site) – Click here

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.com/