I've been asked several times to add custom lookups to user defined fields in GP.

Clients want to utilize user defined or custom fields, but they want to have the values validated, and they want to allow the user to select a valid value from a list. Makes sense.

I briefly checked out the drop down and list box controls available in Modifier, but they are pretty limited, and aren't really oriented towards displaying dynamic result sets from a database query.

So I ventured out and created my own custom lookup window in VBA.

As a side note, my biggest gripe with the standard GP lookup windows is their lack of a simple, "real-time" search field. By "real-time", I mean that I want the lookup window to instantly filter the displayed records based on what I type in the lookup search box--as I type it. And I want to have the window simultaneously search multiple fields at the same time--not just one field. For standard GP lookup fields, Rockton SmartFill does an excellent job in this regard, but sadly, in my experience, few clients have been willing to pay for SmartFill.

I'm going to show an example of a lookup that displays Sales Prospect records. In this case, the client called prospects "End Users", so it is called the "End User Lookup" window.

I first created a custom User Form in VBA, and added a Search field at the top. Because VBA is pretty limited in terms of controls, I had to use a ListBox to display my results. Finally, I have a Select and Cancel button.



When the form loads, I utilize UserInfoGet to have it query the GP company database and retrieve all prospects. I store those prospects in a client-side disconnected ADO recordset so that they remain in memory. I then have a simple procedure that displays the recordset results.



When a user types in the Search box, I send the equivalent of a SQL WHERE clause to the ADO recordset Filter property. This dynamically changes the records available in the recordset, allowing me to quickly redisplay only matching records as the user types each character.



As you can see in this example, when I type "wav" in the search box, I am simultaneously searching on the ID, name, and city of the prospect records,…

About a month ago I was asked to review a client's integrations in preparation for their upgrade from GP 8 to GP 10. They run 4 integrations in Integration Manager on a regular basis to import employees, payroll, and invoices from their operational system.

I looked at the integrations and saw that they were relatively simple. They read from a few SQL tables in a staging database on the GP SQL Server, and only had a few simple scripts to call stored procedures to prepare the data prior to import by IM.

Pretty simple, right? I documented my findings and declared that the upgrade should have no impact on the integrations.

A month later, and many projects later, I completely forgot the details of the review that I performed. And in the meantime, the client discovered that their old SQL server was underpowered and that they would need to install GP 10 on a new SQL server machine. I didn't get that particular memo, so I was oblivious to this new detail.

On the scheduled date, the partner performed the upgrade to GP 10 on the new server, and all was swell. Except until the client tried to run their integrations.

Integration Manager complained that the source table could not be found. Hmmm. That's odd. And remember at this point, I didn't realize that a new SQL server had been installed.

When I checked the table list, a bunch of random objects were listed--none of which looked like a staging table. The integrations were using a DSN within IM, so when I checked the DSN, I found the problem. By default, DSNs created in IM do not have the database name set in the connection string. Although a new DSN had been setup to point to the new SQL server, the staging database was not listed. So I added the "Database=GPImport" parameter to the connection string, and viola, the appropriate tables appeared in the Data Source…

I shy away from table import whenever possible. I prefer integration manager, or using SQL directly, or eConnect if I can. But sometimes I am reminded of how simple things can be done simply.

For the past few weeks, I have been working on an implementation that goes live Monday 12/28. We have had a fairly tight timeframe, so I have been trying to avoid manual data entry whenever possible.…

I've mentioned previously that I use Remote Desktop a lot to access many different Dynamics GP servers and environments. I tried the Remote Desktops add-on, but for technical reasons I didn't end up using it, so I currently have 21 rdp files saved to my desktop to access my virtual servers and client servers.

For most of the RDP connections to my virtual servers, I choose to have them run full screen on my second monitor. I like to be able to use ALT+Tab and not have to memorize the RDP equivalent keyboard shortcuts for the non-full screen windows.

But I do have a few RDP connections to my physical…

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…

Landed cost is the total cost it takes to "land" an item on your doorstep, including additional charges like freight, customs, and processing. More and more, I find that companies are interested in landed cost as they increase the use of international suppliers and manufacturers. By using landed cost, the "total" cost of the item is reflected in inventory and is valued as an asset, rather than immediately expensing those additional costs. Take the following example, where I purchase a widget from my European supplier:

• Purchase 100 widgets @ $20/each
• Overseas shipping costs are $1500
• Customs, Duties, and other Processing Fees are $500
• Domestic shipping costs are $400

If I were to value my widgets only at the cost from my vendor, the cost in inventory would be $2000 for the 100 widgets at $20/each. And then I would expense the additional $2400 in costs.
The situation changes though, if I am using landed costs. Using the same costs above, I would value my inventory at $4400 dollars in total, or 100 widgets at $44/each. Perhaps a more accurate picture of the inventory value? In turn, this increased cost will affect my Cost of Goods Sold on the sales side, impacting my margin appropriately.
So, now that we have an example, how about talking through how Landed Costs work in Dynamics GP? With Landed Costs in Dynamics GP, these costs can be:

• Recorded…

In my last post, I discussed a scenario where a client wanted to send e-mails to Business Portal users, and how Christina and I were able to lookup e-mail addresses in Active Directory with a SQL query.

Once we figured out how to get the e-mail addresses out of Active Directory, we needed to write a routine that could query a GP table and notify users if a transaction was created, or if one had not yet been submitted.

Let's start with a quick overview of Database Mail. Database Mail in SQL 2005 is a significant improvement over the prior SQL Mail feature. The biggest improvement in Database Mail is that it now uses SMTP instead of MAPI, so you no longer have to have a mail client configured on the database server. It is also very easy to configure and use.

Configuring Database Mail is relatively straightforward, so I won't cover it in detail here (if you want more info on the configuration, post a comment and let me know).

Once you have Database Mail configured and tested, you are ready to test a SQL statement to send an e-mail. Here is a simple example of the sp_send_dbmail procedure:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = 'recipient@domain.com',
@body = 'Test Database Mail Message',
@subject = 'Database Mail Test';

After executing this procedure, if…

Christina recently had the need to send notification e-mails to Business Portal users based on certain transactions that had been entered. To keep things simple, she was looking for a solution that could be implemented in SQL Server. After discussing it briefly, we were pretty confident that we could setup a simple routine to send e-mail from SQL Server. But there was a catch--we only had access to the user's Windows /Domain username in the SQL database, and we did not have the user's e-mail address stored anywhere in the GP database. However, the e-mail addresses were stored in Active Directory. So the search was on to find a way to access Active Directory from SQL Server.

It turns out that it is surprisingly easy to setup a simple SQL Server query that…

In Part 1 of this post, I discussed my challenge of trying to import unparsed addresses into Dynamics GP and the various options that I found to solve this age old problem.  I would have thought that there would be plenty of solutions available by now, but the ones I found had various limitations that prevented them from working for me.

Then, finally, I came across exactly what I was looking for:  An economical tool called RecogniContact, developed by LoquiSoft, that is specifically designed to perform full "contact" parsing.  It can be integrated into a custom application or web site, and allows you to programmatically parse and validate address information.

In addition to parsing addresses, it can also parse contact data, such as prefix (Mr., Mrs.), middle initial, suffix, title, department, phone number, and e-mail address.  It will even tell you the gender of the contact for over 20,000 first names.  But wait, there's more!  More, you say?

It uses international postal coding standards to intelligently parse addresses for 21 countries (currently US and Europe), parse addresses for another 21 countries based on address structure, and, as scary as this sounds, it can even process contact data in 13 different languages.  It can parse international phone numbers based on country-specific standards, and it can even determine if an area code is valid for a given country.  This actually thwarted my lazy test plans when I tried to use a fake phone number of (123) 456-7890.  It detected the invalid number and, sigh, I had to update my data to use a valid area code.

In addition to this exhaustive list, another very valuable feature is its ability to parse the fields that it can successfully recognize, but then output the specific values that could not be recognized.  This is actually a critical feature when parsing addresses, as it allows the user to skip past the contacts that imported fine, and makes it easier for them to specifically identify and manually review only the "partially" parsed addresses. 

I corresponded with Werner Noska, the owner of LoquiSoft, and he explained that the tool started as a research project for an Austrian federal agency, which resulted in ContactCopy, a desktop address parser.  Due to requests by companies looking to incorporate the technology into their software, he developed RecogniContact.

He explained some technical details about RecogniContact that were outside of my realm, such as structural parsing vs. content aware parsing, and its ability to recognize data elements even without separators.  I know that software developers are sometimes "ego enhanced", and therefore occasionally looking to prove something with their L337 development…