Sunday, February 28, 2010

SQL - Autogenarating Customer IDs

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/

Monday, February 22, 2010

Microsoft Dynamics Convergence 2010 Sessions




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/

Thursday, February 18, 2010

Microsoft Dynamics GP 2010 - General Availability Release

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/

Wednesday, February 17, 2010

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

"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

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/

Tuesday, February 16, 2010

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

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

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/

Monday, February 15, 2010

Microsoft Dynamics GP 2010 - Service Call Management Enhancements, Part 1

As I prepare to do more work with the Field Service Series modules, I wanted to blog about a few of the enhancements to the Service Call Management module in the upcoming Microsoft Dynamics GP 2010. So let's get started.

1. Users now have the ability to automatically move service calls to history when the billing process is complete. I don't know about you, but this one is huge! I did not like the fact that this process had to be done as a routine in Service Call Management, but I guess now is a choice. To enable this feature, the Service Setup - Service window has been provided with a checkmark.


*Click image to enlarge

2. Field Service Series has been provided with a Check Links routine! Fi-na-lly! However, a bit of a caution: you should only run check links on the Field Service Series if the reconciliation routines have been run, but the original problem persists. In other words: BACKUP YOUR DATA before your try this.


*Click image to enlarge

You can choose to run Check Links on Contract Cards, Contract Transactions, Equipment Cards, Service Cards, Service Transactions, and the Service Setup records. Very good start.

3. Now you have the ability to enter non-inventoried items for Service Call transactions (and throghout the Field Service series. This is going to be huge for a number of customers out there! As a good sign of thoughtfulness, this feature has been provided as a setup option to the Service Series.


*Click image to enlarge

4. In addition to the previous ability to escalate service calls to a technician’s manager, users can now send escalation notices to the manager’s manager, and to the manager of the manager’s manager. You can specify the additional manager levels in the Service Type Escalation window.


*Click image to enlarge

The Field Service Series has a tremendous amount of improvements, too many to cover in one article, so I will be following up this post with a 2nd part on the enhancements introduced in Service Call Management.

Until next post!

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

Friday, February 12, 2010

VST - Modal Dialogs handling make their way out of VBA into VST

Over at Developing for Dynamics GP, Patrick Roth has a new article describing the new Visual Studio Tools 2010 shiny features, in particular Modal Dialog handling, passing table buffers as parameters, and function and procedure notifications. Patrick also provides sample code showing how to respond to Modal Dialog events as well as code to display notifications.

For the most part, VST now behaves likes Dex so, don't complain and enjoy the new enhancements.

PS. Wait until you see the new Menus for Visual Studio Tools... sure not to dissapoint.

Until next post!

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

Wednesday, February 10, 2010

New Article on MSDynamicsWorld: Michael Johnson on "Filtering Salesperson-specific data with SSRS"

"It didn't take long to figure out that the problem wasn't with Business Portal, but rather the way they were trying to use it and NOT using SQL Server Reporting Services (SSRS). Both tools have a place in their environment, but one is not a substitute for another"

My friend Michael Johnson, a.k.a. "The MBS Guru" has finally come out of hiding and has decided to publish a really clever solution for filtering salesperson-specific data in GP using SSRS over at MSDynamicsWorld. The good thing about Michael's solution is, you can apply it to pretty much any report that requires such type of access and filtering capabilities. They don't call this guy the MBS Guru for nothing!

Until next post!

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

Tuesday, February 9, 2010

From the Newsgroups: Multicurrency POP Receipts

Welcome to another weekly edition of the new From the Newsgroups blog entry. This week's topic revolves around Multicurrency POP Receipts and a feature available in GP. As usual, no names will be given out, just the question and the answer.


Q: Functional currency is CAN [Ed: Canadian Dollars]. Item has a receipt that was for qty of 6000 each at .62 each USD currency. Vendor is setup as a US vendor. In the fifo layers it shows a receipt for 5999 at .62 each then another line for a qty of 1 at $12.57. Why is the system showing two lines for this receipt and breaking out the exchange all in one line. The exchange should spread over the qty of 6000 not qty 1.

We know there is an issue with split receipts when the item has more than 2 decimal points or there is landed cost involved. That is not the case here. This appears to be because of the exchange rate.

The response is as follows:


A: Good Day.

Thank you for using Forums.

With your exchange rate, it would appear that you have 6000 qty at an extended cost of $3731.95. Is this correct?

If so, the way GP handles a situation where the qty is not evenly divided into the extended cost is to produce a split receipt. The 'logic' behind it takes as many qty at the 'unit cost' as possible, and then puts the left over amount on the last single qty. This is the way this has worked as long as we've been using split receipts.

This is not so much a matter of putting the 'exchange amount' on one qty, but rather that we can't spread the extended cost over the 6000 qty evenly. $3731.95 divided by 6000 is .6219916666666666666 repeating. We have to use some mechanism to have 6000 qty in our system at total cost of $3731.95. The method we use is a split reciept of 5999 @ .62 and 1 @ 12.57. This is standard GP functionality and has been for many versions.

If you have some suggestions on doing this in a different manner, please feel free to enter a product suggestion so that it can be reviewed for possible future releases.

Thanks!
Some of the features in GP are simply hidden to the naked eye unless you are dealing with a number of situations day in and day out. Multicurrency is one such situation.

Until next post!

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

IM - How to group Integration Manager transactions based on transaction date

Just recently, I fielded a question where the user wanted to group a number of customer invoice transactions in a file based on the invoice date. The file happened to contain invoices downloaded from the billing system in a date range, for example 02/01/2010 - 02/05/2010. They wanted the resulting transaction batches in GP to be something like,

AR02012010
AR02022010
AR02032010
AR02042010
AR02052010

This would facilitate analysis by day and to make sure control totals matched those created on a daily basis.






The solution involves adding a field script to the batch ID field which in turn reads the transaction date field from the source query and forms the new batch ID with that information:




Very simple, but useful script. Keep in mind that you must enable the option to Add Missing batches for the integration as shown below:



When the integration is executed, it will create as many AR batches in Microsoft Dynamics GP as unique transaction dates there are in the file.

Until next post!

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

Monday, February 8, 2010

"Advanced Excel for Microsoft Dynamics GP" Training

My friend, MVP Mark Polino is hosting a 2-day class from March 1 - 2, 2010 at the IBIS, Inc., offices in Norcross, Georgia (20 minutes north of downtown Atlanta). Mark promises some blood after you leave this class as nothing will hold you back anymore from connecting Microsoft Dynamics GP and Microsoft Office Excel -- not my words!

Take advantage of this unique opportunity to get some training from one of the best in the business, so click here for more information.

Until next post!

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

Saturday, February 6, 2010

See you at Microsoft Dynamics Convergence 2010

Great News! I am going to my first Microsoft Convergence conference as a presenter and will be presenting two concurrent sessions (more on this soon) with my partner in crime, David Musgrave. David and I presented together back in November at the Microsoft Dynamics GP Technical Conference 2009 and it was a great success. So now, at the Microsoft Dynamics Convergence 2010, we get a chance to do it again.

Here are some Microsoft Dynamics Convergence resources to start gearing up for April:

  • Microsoft Convergence 2010 Atlanta Registration Site - Click here

  • David Musgrave's Convergence 2010 announcement article - Click here for more info.

  • If you did not get a chance to see MVP Mark Polino at Convergence 2009 in New Orleans, then no worries. He is back with his "50 Tips in 50 Minutes" session this year in Atlanta - Click here for more info.

  • If you are interested in volunteering opportunities at Convergence you need to watch the following video for more information.


  • http://www.youtube.com/watch?v=9gWgaw_CXZM

See you in my backyard.

Until next post!

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

Friday, February 5, 2010

Microsoft Dynamics GP 2010 - International Enhancements

After spending a week in Costa Rica enjoying the spectacular beaches and the fabulous weather in San Jose (although my purpose in San Jose was to deliver Microsoft Dexterity training), I thought it was just appropriate to blog about the international features sported by Microsoft Dynamics GP 2010. The bottom line, there are quite a bit of enhancements for the international community, so let's get right to them:

1. VAT Daybook. You will be pleased to know that the VAT daybook has become a feature available for installation to all countries. Since value added taxes are very common across the globe with most countries requiring pretty much the same tracking and reporting capabilities, the good folks at MS have decided to make it available to everyone.

For you folks up in Canada, the VAT Daybook can be used to generate the Canadian GST/HST return in electronic format. You can set up the reporting periods for the return as specified by the tax Canada Revenue Agency. You can assign the tax detail IDs to consider for each line of the return. You can set up the default claim percentage for input tax credit for each tax detail ID. You can change this percentage for each transaction before you generate the return. You can resubmit the return after making changes, if required.

Vous serez heureux de savoir que le fil de la journée de la TVA est devenue une caractéristique disponible pour l'installation de tous les pays. Depuis la valeur ajoutée les impôts sont très courants dans le monde entier avec la plupart des pays ayant besoin d'à peu près le même suivi et de reporting, les braves gens qui habitaient les États membres ont décidé de le rendre accessible à tous.

Pour vous, les amis au Canada, le fil de la journée la TVA peut être utilisé pour générer la TPS canadienne / TVH en format électronique. Vous pouvez configurer les périodes de déclaration pour le retour tel que spécifié par l'impôt du revenu du Canada Agence. Vous pouvez attribuer le détail impôt identifiants à considérer pour chaque ligne de la déclaration. Vous pouvez configurer le pourcentage par défaut de réclamation de crédit d'impôt d'entrée de chaque détail d'identification fiscale. Vous pouvez changer ce pourcentage pour chaque transaction avant que vous produisez la déclaration. Vous pouvez renvoyer le retour en cas de modifications, si nécessaire.

2. Fixed Assets Enhancements. For you folks in Australia and New Zeland you will be pleased to know that Fixed Assets Enhancements is a tool that allows you to create a depreciation method to calculate depreciation for low-cost and low-value assets. This product is automatically installed when you choose to install the Fixed Assets Management product for Australia or New Zealand installations.

3. Export Financial Data. Export Financial Data is a tool that allows you to export the ACCON account balance report for the users in Belgium and the ETAFI account balance report for the users in France. These reports are exported to a format that can be imported by the respective legal authorities. This product is available for selection only for Belgian or French installations of Microsoft Dynamics GP.

Données Financières de L'Exportation est un outil qui vous permet d'exporter les soldes des comptes rapport Accone en Belgique, et le rapport solde du compte ETAFI pour les utilisateurs en France. Ces rapports sont exportés vers un format qui peut être importé par les autorités judiciaires.. Ce produit est disponible pour la sélection uniquement pour les installations belge ou française de Microsoft Dynamics GP.

4. "Principles of Data Access and Verifiability of Digital Documents". GDPDU is a tool that allows you to extract tax related data from Microsoft Dynamics GP based German legal requirements. You can send this data to the auditor for analysis before submitting the final report to the tax authorities.

You can choose to install this feature from the ...\DvdImage\Interntl\Products\ folder located within your Microsoft Dynamics GP installation package.

GDPDU - Grundsätze zum Datenzugriff und zur Prüfbarkeit digitaler Unterlagen -ist ein werkzeug, dass sie die steuerrelevanten daten aus Microsoft Dynamics GP lebenden deutschen gesetzlichen anforderungen zu extrahieren. Sie können diese daten an den abschlussprüfer für die Analyse zu schicken, bevor die vorlage des abschlussberichts an den fiskus.

Sie können wählen, um diese funktion aus dem ... \DvdImage\Interntl\Products\ in ihrem Microsoft Dynamics GP installationspaket befindet.

Now, you will want to excuse my French and German, but I did the best I could... now, go enjoy your new features!

Until next post!

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

Tuesday, February 2, 2010

New article on MSDynamicsWorld: MVP Victoria Yudin on "Taking charge of your own support"


"When I first started using Microsoft Dynamics GP almost twenty years ago... there were limited options for getting help"

MVP Victoria Yudin gives an account of her 20-year career history with Microsoft Dynamics GP and how the support landscape has changed over those years. She also gives out some important pointers on how to request help on newsgroups, forums, and even from Microsoft Dynamics GP Support. Be sure to check out her article over at MSDynamicsWorld.

Until next post!

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