I recently fielded a question on Experts Exchange on how to add workflow support for a Dynamics GP ISV solution.
I know that there is a Workflow SDK, but I have never installed it or looked at the documentation. I am only aware of one or two GP clients that I have worked with that used Workflow in a very limited manner, so I haven't seen the value of spending time to learn how to develop with the Workflow SDK.
The requester on Experts Exchange ultimately received a response from GP developer support with some tips on how to get started, but the gist of the response was that attempting to create a new workflow or even extend an existing one can be quite complex, and the best bet is to try and utilize an existing GP workflow.
So I'm wondering if anyone has worked on a real project that used the GP Workflow SDK. If you have, can you share your thoughts on how easy or difficult it was, and any tips or suggestions on using (or not using?) the Workflow SDK based on your experience?
Wednesday, January 27, 2010
Monday, January 25, 2010
Backup! Backup! Backup! And then backup your backups.
Eating right. Flossing. Exercising regularly. Vitamins. Saving for retirement. Drinking 8 glasses of water a day. Regular backups.
Pick up on a theme yet?
I currently use Hyper-V, and very much appreciate the flexibility and efficiency it provides, allowing me to host over 25 virtual servers for various clients and projects on just a few physical servers.
But, now that I have a veritable virtual server farm, I have the corresponding challenge of backing up all of those virtual servers.
This weekend a client had an issue with a custom GL Clearing Entry Integration that I developed for them. To test the issue, I tried closing the fiscal year in GP on my server to confirm how the clearing entries behave against a closed year. Well, let's just say that I didn't have a backup of the test database on my virtual server before I closed 2009. Ooops. Classic mistake. (And good time to plug Atul Gawande's new book: The Checklist Manifesto)
Here is some background on my backup regimen. I suspect it sounds neurotic, but if you've lost an entire hard drive or had that sinking feeling in your gut when you knew you lost critical client files or data, you'll understand.
First, I backup my project files to a zip file every day that I make changes. So SQL scripts, package files, VBA, Visual Studio projects, source files, and any project documents that I create on a virtual server get backed up to a date-time stamped zip file on that virtual server. But wait, there's more!
That zip file then gets copied to my main workstation. This triggers an automatic backup to "the cloud" via both SugarSync and Carbonite. SugarSync, in addition to maintaining the last 5 versions of all of my files, also automatically synchronizes the files over to my laptop. But that's not all! Don't touch that dial!
I then have a scheduled batch file that uses RoboCopy to backup my workstation files to my 4TB TeraStation on a weekly basis. I stopped trying to count how many backups that makes.
That sounds fine, right? Very much a "belt and suspenders" approach ready for most natural disasters.
But that's the easy part. Those data files constitute less than 10GB of data.
So, what about all of those massive VHD files on my Hyper-V servers? If I forget to backup some SQL script or table or other database object, the VHD is my only backup.
For backing up VHDs, I currently lack an elegant solution. (Although I'm working on one.)
How in the world do you make regular backups of multiple VHD files that are anywhere from 15GB to 50GB each? The files do compress pretty well using WinRAR, but it can take several hours to compress and copy each one, which can be 5GB to 10GB each.
I'm not willing to spend thousands of dollars on backup software or dedicated backup hardware. Those bring entirely new complexity and headaches to the party.
So, at the moment, my routine is to WinRAR a VHD file or two to my TeraStation in the evening. It takes anywhere from 1 hour to 4 hours, so it works when I remember to do it. But naturally I don't remember to do this every evening, and I don't currently track which VHDs I've backed up and when, and which ones need to be backed up vs. those that have not changed in a few months.
Fortunately, for the Clearing Entry issue I mentioned earlier, I did have a backup of the VHD from December, which I intentionally made before we started the Clearing Entry import project. So I believe I'll be able to restore my GP databases from that older VHD.
But that VHD is over a month old, so clearly I got pretty lucky.
My idea at this point is to use the command line version of WinRAR (just called RAR), to create scheduled jobs that will backup the VHDs to my TeraStation each evening.
A few tedious elements to this. Two that come to mind are:
1) I'll want to skip the backup if the VHD hasn't been changed in the last X days
2) I'll want to shut down the virtual server if it is running when the backup starts
I started on a basic command line script to automate the WinRAR process. Next I'll need to refresh my knowledge of the scripting required to detect the virtual server state and shut it down if it is running. I last did with Virtual Server 2005 a few years ago, so I'll need to learn about any differences for Hyper-V. And then I'll need to figure out a schedule to coordinate the backup of my different VHDs so that only a few are done at a time, as the TeraStation is pretty slow for large file copies (don't get me started).
I know there are probably dozens of possible approaches, but I'll start with this to work out the kinks and learn. And ultimately I'll need to figure out a process to purge the VHD backups, since I already have 800GB of compressed VHD files on my TeraStation.
Anyway, this is yet another lesson, and reminder, that you need to make backups, and then, ideally, make backups of your backups.
And with that, I leave you with a favorite computer geek joke.
Pick up on a theme yet?
I currently use Hyper-V, and very much appreciate the flexibility and efficiency it provides, allowing me to host over 25 virtual servers for various clients and projects on just a few physical servers.
But, now that I have a veritable virtual server farm, I have the corresponding challenge of backing up all of those virtual servers.
This weekend a client had an issue with a custom GL Clearing Entry Integration that I developed for them. To test the issue, I tried closing the fiscal year in GP on my server to confirm how the clearing entries behave against a closed year. Well, let's just say that I didn't have a backup of the test database on my virtual server before I closed 2009. Ooops. Classic mistake. (And good time to plug Atul Gawande's new book: The Checklist Manifesto)
Here is some background on my backup regimen. I suspect it sounds neurotic, but if you've lost an entire hard drive or had that sinking feeling in your gut when you knew you lost critical client files or data, you'll understand.
First, I backup my project files to a zip file every day that I make changes. So SQL scripts, package files, VBA, Visual Studio projects, source files, and any project documents that I create on a virtual server get backed up to a date-time stamped zip file on that virtual server. But wait, there's more!
That zip file then gets copied to my main workstation. This triggers an automatic backup to "the cloud" via both SugarSync and Carbonite. SugarSync, in addition to maintaining the last 5 versions of all of my files, also automatically synchronizes the files over to my laptop. But that's not all! Don't touch that dial!
I then have a scheduled batch file that uses RoboCopy to backup my workstation files to my 4TB TeraStation on a weekly basis. I stopped trying to count how many backups that makes.
That sounds fine, right? Very much a "belt and suspenders" approach ready for most natural disasters.
But that's the easy part. Those data files constitute less than 10GB of data.
So, what about all of those massive VHD files on my Hyper-V servers? If I forget to backup some SQL script or table or other database object, the VHD is my only backup.
For backing up VHDs, I currently lack an elegant solution. (Although I'm working on one.)
How in the world do you make regular backups of multiple VHD files that are anywhere from 15GB to 50GB each? The files do compress pretty well using WinRAR, but it can take several hours to compress and copy each one, which can be 5GB to 10GB each.
I'm not willing to spend thousands of dollars on backup software or dedicated backup hardware. Those bring entirely new complexity and headaches to the party.
So, at the moment, my routine is to WinRAR a VHD file or two to my TeraStation in the evening. It takes anywhere from 1 hour to 4 hours, so it works when I remember to do it. But naturally I don't remember to do this every evening, and I don't currently track which VHDs I've backed up and when, and which ones need to be backed up vs. those that have not changed in a few months.
Fortunately, for the Clearing Entry issue I mentioned earlier, I did have a backup of the VHD from December, which I intentionally made before we started the Clearing Entry import project. So I believe I'll be able to restore my GP databases from that older VHD.
But that VHD is over a month old, so clearly I got pretty lucky.
My idea at this point is to use the command line version of WinRAR (just called RAR), to create scheduled jobs that will backup the VHDs to my TeraStation each evening.
A few tedious elements to this. Two that come to mind are:
1) I'll want to skip the backup if the VHD hasn't been changed in the last X days
2) I'll want to shut down the virtual server if it is running when the backup starts
I started on a basic command line script to automate the WinRAR process. Next I'll need to refresh my knowledge of the scripting required to detect the virtual server state and shut it down if it is running. I last did with Virtual Server 2005 a few years ago, so I'll need to learn about any differences for Hyper-V. And then I'll need to figure out a schedule to coordinate the backup of my different VHDs so that only a few are done at a time, as the TeraStation is pretty slow for large file copies (don't get me started).
I know there are probably dozens of possible approaches, but I'll start with this to work out the kinks and learn. And ultimately I'll need to figure out a process to purge the VHD backups, since I already have 800GB of compressed VHD files on my TeraStation.
Anyway, this is yet another lesson, and reminder, that you need to make backups, and then, ideally, make backups of your backups.
And with that, I leave you with a favorite computer geek joke.
Thursday, January 21, 2010
Integration Manager 10 Bugginess: Data Sources, Relationships, and Scripts
I haven't been a big fan of Integration Manager lately because of various annoying bugs that were introduced during it's apparent re-write / migration to .NET (not sure exactly what all was changed) for version 10.
I have had to work on two different IM 10 integrations in the last few weeks, and seem to have discovered a theme regarding data sources, Relationships, and IM errors and instability.
From what I can tell, the Relationships feature / window in IM 10 has very poor error handling, causing it to throw unhandled errors or cause IM to crash if it runs into any type of problem. From what I have found, these manifest themselves in at least these two situations:
1) If you have an existing integration with multiple data sources and existing relationships, and then make a change to one or more of your data sources, the Relationships window throws errors and fails to open. For instance, if a column name in your data source is changed, you may get an error such as "Internal Error in System.Windows.Forms" dialog: "InvalidArgument=Value of '1' is not valid for 'index'."
2) If you have any type of problem with your data sources, such as an invalid grouping, the Relationships window will typically throw an error or even cause IM to crash.
The only workaround I have found is to remove all but one of your data sources so that the Relationships are removed. Then add your additional data sources back into your integration, verify that they don't have any issues, and then attempt to rebuild your relationships.
This issue seems to be consistent regardless of service pack--I'm able to consistently reproduce the problem on IM 10 with SP4.
A second issue I've run into consistently is that certain VB scripts will cause the script window to throw an error when you try and open your script. For example, you may have 10 lines of a script, then you click on Save, and when you try and re-open the script, you will get an error that prevents the script window from opening. I don't know if it is a formatting issue, a syntax issue, or if it's just a random parsing issue in the script window--I haven't had the patience to bother to research it.
In those situations, the only workaround I have found is to delete the script by using the "Remove" button, and then recreate it. If you don't have a backup of your script, tough luck.
Feel the love!
I have had to work on two different IM 10 integrations in the last few weeks, and seem to have discovered a theme regarding data sources, Relationships, and IM errors and instability.
From what I can tell, the Relationships feature / window in IM 10 has very poor error handling, causing it to throw unhandled errors or cause IM to crash if it runs into any type of problem. From what I have found, these manifest themselves in at least these two situations:
1) If you have an existing integration with multiple data sources and existing relationships, and then make a change to one or more of your data sources, the Relationships window throws errors and fails to open. For instance, if a column name in your data source is changed, you may get an error such as "Internal Error in System.Windows.Forms" dialog: "InvalidArgument=Value of '1' is not valid for 'index'."
2) If you have any type of problem with your data sources, such as an invalid grouping, the Relationships window will typically throw an error or even cause IM to crash.
The only workaround I have found is to remove all but one of your data sources so that the Relationships are removed. Then add your additional data sources back into your integration, verify that they don't have any issues, and then attempt to rebuild your relationships.
This issue seems to be consistent regardless of service pack--I'm able to consistently reproduce the problem on IM 10 with SP4.
A second issue I've run into consistently is that certain VB scripts will cause the script window to throw an error when you try and open your script. For example, you may have 10 lines of a script, then you click on Save, and when you try and re-open the script, you will get an error that prevents the script window from opening. I don't know if it is a formatting issue, a syntax issue, or if it's just a random parsing issue in the script window--I haven't had the patience to bother to research it.
In those situations, the only workaround I have found is to delete the script by using the "Remove" button, and then recreate it. If you don't have a backup of your script, tough luck.
Feel the love!
Wednesday, January 20, 2010
Replacement for Cisco VPN Client on 64-bit Windows
If you are a GP consultant, you likely need to connect to client networks and servers remotely, often using a VPN connection.
By far the most popular VPN that I have used with my clients is Cisco, which requires the Cisco VPN Client software.
It's worked great for me for many years, up until I tried to use it on 64-bit Windows. The latest version refuses to install on a 64-bit OS, and Cisco's web site states that they will not be creating a 64-bit version, pointing you to a different version of their VPN software (not free) that will work on 64-bit Windows.
After some searching, I found the Shrew Soft VPN client. This is a free VPN client that is fully compatible with Cisco VPN connections, and even supports importing the Cisco .pcf configuration files.
It seems to work great. The only minor issue I've had is that DNS resolution for the client's network requires the use of a fully qualified domain name (FQDN), such as server.domain.local, instead of just "server", which is a common problem I've had with other VPN clients other than Cisco. But it's easy enough to workaround, or you can always just use an IP address.
By far the most popular VPN that I have used with my clients is Cisco, which requires the Cisco VPN Client software.
It's worked great for me for many years, up until I tried to use it on 64-bit Windows. The latest version refuses to install on a 64-bit OS, and Cisco's web site states that they will not be creating a 64-bit version, pointing you to a different version of their VPN software (not free) that will work on 64-bit Windows.
After some searching, I found the Shrew Soft VPN client. This is a free VPN client that is fully compatible with Cisco VPN connections, and even supports importing the Cisco .pcf configuration files.
It seems to work great. The only minor issue I've had is that DNS resolution for the client's network requires the use of a fully qualified domain name (FQDN), such as server.domain.local, instead of just "server", which is a common problem I've had with other VPN clients other than Cisco. But it's easy enough to workaround, or you can always just use an IP address.
Tuesday, January 19, 2010
Installing GP VS Tools 10 with Visual Studio 2008
For the last year or so, I have exclusively used GP and Visual Studio on virtual servers, and after wiping my laptop to install Windows 7, never bothered to reinstall GP, SQL, or Visual Studio. Since I nearly always have internet access on my laptop through WiFi or my 3G card, I've always been able to login to my servers remotely and work on GP on a server.
But I had a flight recently and wanted to work on a prototype of a new GP add-on, so I installed GP 10 on my laptop so that I could work during the flight.
I installed SQL 2008, Visual Studio 2008, and GP 10. It took forever to install everything and then install all of the service packs, but it went relatively smoothly.
But when I tried to install GP 10 VS Tools, I received a message that said that Visual Studio 2005 was not installed, so the setup could not continue.
After poking around and finding KB Article 968060, I finally discovered the workaround.
When first installing the base GP 10 VS Tools, you have to uncheck the Visual Studio 2005 Templates feature.
Once VS Tools is installed, install the latest service pack. I believe that the Visual Studio 2008 Templates were first released with VS Tools SP 2, so you will want to at least install SP2, although SP5 is now available.
Here is the link to the GP 10 VS Tools downloads.
After the service pack is installed, go into Control Panel and change the features for VS Tools, listed as "Visual Studio Tools for Microsoft Dynamics GP 10 SDK". You should see Visual Studio 2008 Templates listed. Add that feature, and then let the setup install the necessary files.
You should then be able to use VS Tools with Visual Studio 2008.
There may be a more direct workaround, but since I had already installed the base VS Tools, this was simple enough once I figured it out.
I recently installed the GP 2010 Beta, and see that the GP 2010 Visual Studio Tools includes templates for both Visual Studio 2005 and 2008, so this is a non-issue with GP 2010. At least until Visual Studio 2010 is released.
But I had a flight recently and wanted to work on a prototype of a new GP add-on, so I installed GP 10 on my laptop so that I could work during the flight.
I installed SQL 2008, Visual Studio 2008, and GP 10. It took forever to install everything and then install all of the service packs, but it went relatively smoothly.
But when I tried to install GP 10 VS Tools, I received a message that said that Visual Studio 2005 was not installed, so the setup could not continue.
After poking around and finding KB Article 968060, I finally discovered the workaround.
When first installing the base GP 10 VS Tools, you have to uncheck the Visual Studio 2005 Templates feature.
Once VS Tools is installed, install the latest service pack. I believe that the Visual Studio 2008 Templates were first released with VS Tools SP 2, so you will want to at least install SP2, although SP5 is now available.
Here is the link to the GP 10 VS Tools downloads.
After the service pack is installed, go into Control Panel and change the features for VS Tools, listed as "Visual Studio Tools for Microsoft Dynamics GP 10 SDK". You should see Visual Studio 2008 Templates listed. Add that feature, and then let the setup install the necessary files.
You should then be able to use VS Tools with Visual Studio 2008.
There may be a more direct workaround, but since I had already installed the base VS Tools, this was simple enough once I figured it out.
I recently installed the GP 2010 Beta, and see that the GP 2010 Visual Studio Tools includes templates for both Visual Studio 2005 and 2008, so this is a non-issue with GP 2010. At least until Visual Studio 2010 is released.
Monday, January 18, 2010
Troubleshooting eConnect "System Error" Message
Over the last several months, I have developed several eConnect integrations for a client. The integrations work fine on my development machine, but sometimes when the integrations are run on the client server, eConnect generates errors and simply returns the text "System Error", with no additional detail.
After much poking around, I discovered that in general, this is often caused by a required eConnect field that is not being populated when the data is sent to eConnect. But other times, I have been unable to explain why the System Error is occurring.
I thought I had searched the GP Knowledge Base previously to see if there was any reference to these frustrating "System Error" messages, but today, after yet another error, I decided to check the KB again.
Sure enough, there is KB article 943133 that discusses the System Error. Not sure why I didn't see it earlier.
It appears that I used an older copy of my .NET class that calls eConnect. I had a catch block for an eConnectException and a generic Exception, but not for a SqlException.
I know that Steve Gray has pointed this out on his excellent eConnect forums, so I knew that any call to the eConnect_EntryPoint should have all three catch blocks, but I just didn't have the SqlException catch block in my code for this client, and it didn't dawn on me that this is why I only received "System Error" with no additional detail.
I take the blame for this oversight, but I don't think the errors are all my fault. The client does not have the latest release of eConnect 9 installed, so it does seem that eConnect 9.0.1 does have some flaws. Instead of returning errors for certain field level issues in the XML data, it seems to miss these errors and attempt to send the XML off to the SQL stored procedures, which is why the SqlExceptions are being thrown.
Running the exact same .NET integration with the same source data on eConnect 9.04 on my server does not generate any errors, so my guess is that these are quasi-bugs in eConnect 9 that have been resolved in service packs.
So lesson #2 is to make sure to install eConnect service packs!
And with that, I return to the pouring rain here in Los Angeles...
After much poking around, I discovered that in general, this is often caused by a required eConnect field that is not being populated when the data is sent to eConnect. But other times, I have been unable to explain why the System Error is occurring.
I thought I had searched the GP Knowledge Base previously to see if there was any reference to these frustrating "System Error" messages, but today, after yet another error, I decided to check the KB again.
Sure enough, there is KB article 943133 that discusses the System Error. Not sure why I didn't see it earlier.
It appears that I used an older copy of my .NET class that calls eConnect. I had a catch block for an eConnectException and a generic Exception, but not for a SqlException.
I know that Steve Gray has pointed this out on his excellent eConnect forums, so I knew that any call to the eConnect_EntryPoint should have all three catch blocks, but I just didn't have the SqlException catch block in my code for this client, and it didn't dawn on me that this is why I only received "System Error" with no additional detail.
I take the blame for this oversight, but I don't think the errors are all my fault. The client does not have the latest release of eConnect 9 installed, so it does seem that eConnect 9.0.1 does have some flaws. Instead of returning errors for certain field level issues in the XML data, it seems to miss these errors and attempt to send the XML off to the SQL stored procedures, which is why the SqlExceptions are being thrown.
Running the exact same .NET integration with the same source data on eConnect 9.04 on my server does not generate any errors, so my guess is that these are quasi-bugs in eConnect 9 that have been resolved in service packs.
So lesson #2 is to make sure to install eConnect service packs!
And with that, I return to the pouring rain here in Los Angeles...
Friday, January 8, 2010
VS Tools Developers Rejoice!
Oh how fun it has been to write absolutely goofy and embarrasing workarounds to avoid the VS Tools modal window dialog bug! (and boy, did I find some creative ones!)
But alas, finally, the glorious and exalted GP team has released patches that (hopefully!) fully resolve this, and one other issue that have both been haunting GP AddIn developers.
VS Tools developers, please immediately proceed to Patrick Roth's blog entry on Developing for Dynamics GP for his brief post and links to the downloads. Do not pass Go, do not collect $200.
http://blogs.msdn.com/developingfordynamicsgp/archive/2010/01/08/modal-issues-resolved-in-visual-studio-tools-10-0.aspx
But alas, finally, the glorious and exalted GP team has released patches that (hopefully!) fully resolve this, and one other issue that have both been haunting GP AddIn developers.
VS Tools developers, please immediately proceed to Patrick Roth's blog entry on Developing for Dynamics GP for his brief post and links to the downloads. Do not pass Go, do not collect $200.
http://blogs.msdn.com/developingfordynamicsgp/archive/2010/01/08/modal-issues-resolved-in-visual-studio-tools-10-0.aspx
Tuesday, January 5, 2010
Ouch, Modifying Direct Deposit Files
I have definitely had my fair share of ouch moments over the generation of ACH files in Dynamics GP. If a payroll is processed with incorrect information like a mistyped routing number or account number, or if a direct deposit is voided after the payroll is posted, the ACH file that is generated contains incorrect information.
The party line is to void the entire payroll and then re-run with corrected information. Although this is fine for small companies, it doesn't work all that well for large companies with large payrolls.
So, we used to edit the ACH file manually. This worked great, although it was always a little hit and miss to make sure you caught all of the fields that needed to be updated. However, in recent years this is no longer an option due the encoding created at the time the ACH file is created in GP (referred to as "hash" entries). These hash entries will vary based on the information in the file, so there is no reasonable way to update the file manually and still have it accepted by the bank.
So what to do? Well, if the client has an understanding bank, or the bank has a full-featured website, often you can upload the file to the bank and either make the changes in their website or the bank will make the changes for you. But what to do if the bank's website does not have that capability, or the bank itself is not helpful?
Well, in those cases, I have found you can use the following script to update the Direct Deposit records in GP and then regenerate the file (which will recreate the correct hash entries as well). Now, I must say, I have anecdotal experience that this works. But it may not work in all instances, so you need to test it with the bank and also make sure you have a functional backup before doing this!
These scripts assume that the payroll build is still available in the Generate ACH window (Transactions>>Payroll>>Generate ACH). The script should be run against the company database.
UPDATE DD10500 SET DDAMTDLR=’1500.00000’ WHERE DEX_ROW_ID=’insert dex row ID of record you need to change’
The field in the script above (DDAMTDLR) will change the dollar amount, DDTRANUM would update the routing number, and DDACTNUM will update the account number. In all cases, once you make the change, you can regenerate the file and the footer information (totals, hash info, etc) will be correct. To view the contents in the table, use SELECT * FROM DD10500 and you can find the correct dex_row_ID of the record you want to update. You will notice that the records in the table are identified with the build number from the Generate ACH window (field INDEX1 in the table).
Since I sort of stumbled on this through trial and error under pressure, please share any insights you have or other fixes or pitfalls you have found.
The party line is to void the entire payroll and then re-run with corrected information. Although this is fine for small companies, it doesn't work all that well for large companies with large payrolls.
So, we used to edit the ACH file manually. This worked great, although it was always a little hit and miss to make sure you caught all of the fields that needed to be updated. However, in recent years this is no longer an option due the encoding created at the time the ACH file is created in GP (referred to as "hash" entries). These hash entries will vary based on the information in the file, so there is no reasonable way to update the file manually and still have it accepted by the bank.
So what to do? Well, if the client has an understanding bank, or the bank has a full-featured website, often you can upload the file to the bank and either make the changes in their website or the bank will make the changes for you. But what to do if the bank's website does not have that capability, or the bank itself is not helpful?
Well, in those cases, I have found you can use the following script to update the Direct Deposit records in GP and then regenerate the file (which will recreate the correct hash entries as well). Now, I must say, I have anecdotal experience that this works. But it may not work in all instances, so you need to test it with the bank and also make sure you have a functional backup before doing this!
These scripts assume that the payroll build is still available in the Generate ACH window (Transactions>>Payroll>>Generate ACH). The script should be run against the company database.
UPDATE DD10500 SET DDAMTDLR=’1500.00000’ WHERE DEX_ROW_ID=’insert dex row ID of record you need to change’
The field in the script above (DDAMTDLR) will change the dollar amount, DDTRANUM would update the routing number, and DDACTNUM will update the account number. In all cases, once you make the change, you can regenerate the file and the footer information (totals, hash info, etc) will be correct. To view the contents in the table, use SELECT * FROM DD10500 and you can find the correct dex_row_ID of the record you want to update. You will notice that the records in the table are identified with the build number from the Generate ACH window (field INDEX1 in the table).
Since I sort of stumbled on this through trial and error under pressure, please share any insights you have or other fixes or pitfalls you have found.
Friday, January 1, 2010
Let Lonely Dynamics GP Talk to Your Windows Apps!
Hey guys, it compiled! Guys? Hey, where is everyone? Wait a minute, you mean not everyone is developing prototypes of Dynamics GP integrations on New Year's Eve? Clearly I'm the only one who likes to have real fun.
Speaking of being alone, imagine how poor old Dynamics GP feels, all by its lonesome. Sure, Integration Manager occasionally soaks it with a fire hose of data, and occasionally an arrogant VS Tools AddIn pushes it around, but when was the last time that GP got to talk to another app? I mean REALLY TALK.
Well, the last several hours I've been tinkering around to see if I could get GP to interact with another windows application. This idea started based on a question posted to Experts Exchange. The member asked if he could access the SOP number on the SOP Transaction Entry window so that he could use it to automatically display data in his .NET application. Well, rather than have a .NET application try and figure out what SOP number is being accessed in GP by a particular user, why not have GP talk to your .NET app in real time and tell it what is going on?
The basic question is how can you get two windows applications to talk to each other? This took a bit of research, as there are many different approaches. Some people recommending writing to a text file, and having the receiving app detect the presence of a new file. Others talked about passing data via the registry, a database, etc. These allow you to pass data, but don't really let two apps talk directly to each other.
On the other end of the spectrum, there are recommendations to have the two apps communicate via network protocols: sockets, TCP/IP, etc. This approach is required when you need two apps on different machines to talk across a network, but it's a bit more than I wanted. I was just looking to get GP to talk to another windows app on the same machine.
Fortunately, there are Windows APIs that allow two windows apps to communicate in a simple manner. The apps can pass integers and strings, and can immediately respond to the receipt of those messages. The approach I chose was to use the SendMessage Win32 API.
This excellent blog post gave me the code to handle sending and receiving messages between two windows applications, and I was able to relatively quickly pass messages between two separate .NET test apps.
Okay, but how can I get GP to send messages to a .NET application?
Well, let's say that I want a user to be able to click a button on the SOP Transaction Entry window, or Transaction Inquiry Zoom window, that will automatically pull up a corresponding window or record in my application. And I want to have GP send my custom app the following information:
1) Company database (INTERID)
2) SOP Type
3) SOP Number
4) Customer ID
There are probably a few alternatives, but the approach I came up with is:
1) Use Modifier to add a custom button to the SOP Transaction window
2) Use VBA to send the required GP data to a COM assembly
3) Have the COM assembly issue the SendMessage call to my application
Simple right? Well, sort of.
Standing on the shoulders of many people much smarter than I (i.e. blatantly copying code from their blog posts), I was able to cobble together a .NET assembly and some VBA that gets the job done.
I'll discuss the 3 steps above in reverse order.
First, you'll need to create a .NET Class Library to provide the SendMessage functionality. I called my project GPMessage, and my class MessageHelper. Using the SendMessage code from the blog post I referenced earlier, plus several tweaks required to get it to work, you'll have a very simple assembly that does a great job of sending messages to other apps.
But, in order to use it with VBA, you'll need to make it visible to COM. If you're not familiar with this process, it's a bit arcane, with alot of bad advice out there, but here is a good post on how to do it.
And here are two time saving caveats that are only mentioned in passing in most articles on the topic.
In Visual Studio 2005, enable the following options:
Properties --> Build --> Register for COM interop
Properties --> Signing --> Sign the assembly (setup a key file)
Then just compile, and violà, you now have your COM visible assembly! Wasn't that easy?
Next, use Modifier to add your custom button to the SOP transaction window (I called mine SendToApp) and add the SOP window, your new button, the "SOP Type Database" field (read this great article for background), SOP number, and Customer ID fields to VB.
Then, open the VB Editor in GP and pull up the code window for your SendToApp button.
Since you selected "Register for COM interop", Visual Studio did you the favor of making your new COM component visible in the References list. To add the reference, click on Tools -> References and select the GPMessage reference.
You are now ready to code!
A few key lines from my VBA:
Dim GPMessage As New GPMessage.MessageHelper
strMessage = strINTERID & "," & intSOPType & "," & strSOPNumber & "," & strCustomerID
lngWindowID = GPMessage.GetWindowId(strNull, strAppName)
lngResult = GPMessage.SendStringMessage(lngWindowID, 0, strMessage)
blnResult = GPMessage.BringAppToFront(lngWindowID)
The VBA simply instantiates the GPMessage component, gets the window ID of your custom app, and then sends the SOP transaction information.
Finally, in your app, you add the WndProc procedure to receive the message and handle it accordingly.
protected override void WndProc(ref Message m)
{
switch (m.Msg)
{
case WM_USER:
uiMessage.Text = m.WParam + " – " + m.LParam;
break;
case WM_COPYDATA:
COPYDATASTRUCT mystr = new COPYDATASTRUCT();
Type mytype = mystr.GetType();
mystr = (COPYDATASTRUCT)m.GetLParam(mytype);
//Your code to handle the message goes here
DisplayMessage(mystr.lpData);
break;
}
base.WndProc(ref m);
}
Here is an example of the final product. When I click on the Send To App button, my custom app instantly receives the SOP transaction information.
And that, folks, is how you make Dynamics GP a more social application!
Happy New Year!
UPDATE: After pondering this approach further, there is one caveat. This solution (with the custom button and VBA) would work well for a client that is licensed for Modifier & VBA, or a Customization Site License; however, for a client that does not have such licensing, it won't work.
If you are a partner delivering this solution to a client, you could create a GP AddIn using VS Tools and assign a shortcut key to the AddIn. So instead of the user clicking on a button, they could press a shortcut key to invoke your AddIn to send the data to the external application (or they could always use the Additional Menu). This approach also allows you to avoid the hassle of using COM, as your .NET AddIn would be able to talk directly to your .NET app. And no additional licensing required by the client.
One thing I like about the button approach is that it gives a visual cue to the user, and provides a little more consistent user experience, vs. an obscure / non-standard shortcut key, which is less obvious to most users.
But, the AddIn approach is a simpler to develop and certainly simpler and easier to deploy than a Modifier+VBA+COM DLL, so it has some benefits. Also, something I had forgotten about: AddIns can be setup with Event Handlers to automatically respond to events in GP. So, for instance, after a user selects a sales order in the SOP Transaction Entry window, the AddIn can automatically fire code to perform certain actions, which is a nice plus.
In case that appeals to you, I have created a sample AddIn project and included the link below.
Here are links to my projects and code:
Sample Send and Receive prototype apps
Project for GPMessage.dll COM assembly
GP 10 Package file with custom button and VBA
VBA Code by itself
Project for GPMessageAddIn (no Modifier or VBA required)
Speaking of being alone, imagine how poor old Dynamics GP feels, all by its lonesome. Sure, Integration Manager occasionally soaks it with a fire hose of data, and occasionally an arrogant VS Tools AddIn pushes it around, but when was the last time that GP got to talk to another app? I mean REALLY TALK.
Well, the last several hours I've been tinkering around to see if I could get GP to interact with another windows application. This idea started based on a question posted to Experts Exchange. The member asked if he could access the SOP number on the SOP Transaction Entry window so that he could use it to automatically display data in his .NET application. Well, rather than have a .NET application try and figure out what SOP number is being accessed in GP by a particular user, why not have GP talk to your .NET app in real time and tell it what is going on?
The basic question is how can you get two windows applications to talk to each other? This took a bit of research, as there are many different approaches. Some people recommending writing to a text file, and having the receiving app detect the presence of a new file. Others talked about passing data via the registry, a database, etc. These allow you to pass data, but don't really let two apps talk directly to each other.
On the other end of the spectrum, there are recommendations to have the two apps communicate via network protocols: sockets, TCP/IP, etc. This approach is required when you need two apps on different machines to talk across a network, but it's a bit more than I wanted. I was just looking to get GP to talk to another windows app on the same machine.
Fortunately, there are Windows APIs that allow two windows apps to communicate in a simple manner. The apps can pass integers and strings, and can immediately respond to the receipt of those messages. The approach I chose was to use the SendMessage Win32 API.
This excellent blog post gave me the code to handle sending and receiving messages between two windows applications, and I was able to relatively quickly pass messages between two separate .NET test apps.
Okay, but how can I get GP to send messages to a .NET application?
Well, let's say that I want a user to be able to click a button on the SOP Transaction Entry window, or Transaction Inquiry Zoom window, that will automatically pull up a corresponding window or record in my application. And I want to have GP send my custom app the following information:
1) Company database (INTERID)
2) SOP Type
3) SOP Number
4) Customer ID
There are probably a few alternatives, but the approach I came up with is:
1) Use Modifier to add a custom button to the SOP Transaction window
2) Use VBA to send the required GP data to a COM assembly
3) Have the COM assembly issue the SendMessage call to my application
Simple right? Well, sort of.
Standing on the shoulders of many people much smarter than I (i.e. blatantly copying code from their blog posts), I was able to cobble together a .NET assembly and some VBA that gets the job done.
I'll discuss the 3 steps above in reverse order.
First, you'll need to create a .NET Class Library to provide the SendMessage functionality. I called my project GPMessage, and my class MessageHelper. Using the SendMessage code from the blog post I referenced earlier, plus several tweaks required to get it to work, you'll have a very simple assembly that does a great job of sending messages to other apps.
But, in order to use it with VBA, you'll need to make it visible to COM. If you're not familiar with this process, it's a bit arcane, with alot of bad advice out there, but here is a good post on how to do it.
And here are two time saving caveats that are only mentioned in passing in most articles on the topic.
In Visual Studio 2005, enable the following options:
Properties --> Build --> Register for COM interop
Properties --> Signing --> Sign the assembly (setup a key file)
Then just compile, and violà, you now have your COM visible assembly! Wasn't that easy?
Next, use Modifier to add your custom button to the SOP transaction window (I called mine SendToApp) and add the SOP window, your new button, the "SOP Type Database" field (read this great article for background), SOP number, and Customer ID fields to VB.
Then, open the VB Editor in GP and pull up the code window for your SendToApp button.
Since you selected "Register for COM interop", Visual Studio did you the favor of making your new COM component visible in the References list. To add the reference, click on Tools -> References and select the GPMessage reference.
You are now ready to code!
A few key lines from my VBA:
Dim GPMessage As New GPMessage.MessageHelper
strMessage = strINTERID & "," & intSOPType & "," & strSOPNumber & "," & strCustomerID
lngWindowID = GPMessage.GetWindowId(strNull, strAppName)
lngResult = GPMessage.SendStringMessage(lngWindowID, 0, strMessage)
blnResult = GPMessage.BringAppToFront(lngWindowID)
The VBA simply instantiates the GPMessage component, gets the window ID of your custom app, and then sends the SOP transaction information.
Finally, in your app, you add the WndProc procedure to receive the message and handle it accordingly.
protected override void WndProc(ref Message m)
{
switch (m.Msg)
{
case WM_USER:
uiMessage.Text = m.WParam + " – " + m.LParam;
break;
case WM_COPYDATA:
COPYDATASTRUCT mystr = new COPYDATASTRUCT();
Type mytype = mystr.GetType();
mystr = (COPYDATASTRUCT)m.GetLParam(mytype);
//Your code to handle the message goes here
DisplayMessage(mystr.lpData);
break;
}
base.WndProc(ref m);
}
Here is an example of the final product. When I click on the Send To App button, my custom app instantly receives the SOP transaction information.
And that, folks, is how you make Dynamics GP a more social application!
Happy New Year!
UPDATE: After pondering this approach further, there is one caveat. This solution (with the custom button and VBA) would work well for a client that is licensed for Modifier & VBA, or a Customization Site License; however, for a client that does not have such licensing, it won't work.
If you are a partner delivering this solution to a client, you could create a GP AddIn using VS Tools and assign a shortcut key to the AddIn. So instead of the user clicking on a button, they could press a shortcut key to invoke your AddIn to send the data to the external application (or they could always use the Additional Menu). This approach also allows you to avoid the hassle of using COM, as your .NET AddIn would be able to talk directly to your .NET app. And no additional licensing required by the client.
One thing I like about the button approach is that it gives a visual cue to the user, and provides a little more consistent user experience, vs. an obscure / non-standard shortcut key, which is less obvious to most users.
But, the AddIn approach is a simpler to develop and certainly simpler and easier to deploy than a Modifier+VBA+COM DLL, so it has some benefits. Also, something I had forgotten about: AddIns can be setup with Event Handlers to automatically respond to events in GP. So, for instance, after a user selects a sales order in the SOP Transaction Entry window, the AddIn can automatically fire code to perform certain actions, which is a nice plus.
In case that appeals to you, I have created a sample AddIn project and included the link below.
Here are links to my projects and code:
Sample Send and Receive prototype apps
Project for GPMessage.dll COM assembly
GP 10 Package file with custom button and VBA
VBA Code by itself
Project for GPMessageAddIn (no Modifier or VBA required)