My blog has moved! Please visit the new blog at: https://blog.steveendow.com/
I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com
Thanks!
Wife: "Steve, can you install an exhaust fan in the small bathroom?"
Steve: "Sure, hunny, no problem. I just ordered the fan and I'll call Sam to install it."
Customer: "Can you add this simple little feature to our application?"
Developer: "Sure, no problem. I'll get right on that."
No big deal.
Sam: "Steve, I cut a hole in the bathroom ceiling for the fan, but something is strange. There's an extra layer of drywall on the ceiling, and it's not attached properly and it's sagging."
Steve: "Hmmm, that doesn't look right. Let's remove the extra drywall and see what the prior homeowner was covering up."
Customer: "So how's that new simple little feature coming along?"
Developer: "Well, I looked through the code, and the original developers didn't design the software to handle this feature, so it's going to require some redesign of the customization."
A customer asked for sample XML for the RMApplyType / taRMApply eConnect transaction type. I couldn't find one handy during a search, so I had to cobble together some .NET code and generate the XML.
I'm wondering if there is an easier way to generate the sample eConnect XML. In theory, eConnect Requester with the eConnect Outgoing Service can send certain XML documents to MSMQ, but that is a hassle to setup properly, and I don't know that all transaction types are supported by eConnect Requester--such RMApplyType.
So, here is a sample Dynamics GP eConnect XML document for RM Apply (RMApplyType / taRMApply)
Dynamics GP partners and customers often hire me to develop custom Dynamics GP integrations, GP customizations, Visual Studio Tools AddIns for GP, or even custom web APIs for Dynamics GP.
I develop the solution, usually using .NET, then I prepare a deployment guide and deployment package that can be installed on the customer's servers. The solution is tested, I fix some bugs and refine the solution, I prepare new deployments that get installed, and once everything looks good, the customer goes live.
Everybody's happy, and I'm all done, right?
Except for one critical piece.
Any guesses?
What about the source code?
"What about the source code?", you might reply.
I diligently check in my source code to Git, and the code is pushed to an online Git repository for safe keeping and accessibility. And I have backups of backups of backups, both on site and off site. Great. So I'm all done, right?
Not really.
What if I disappear? What if I win the lottery? What if I decide that this whole modern civilization thing is overrated and go live off the grid?
I've had a few Dynamics GP customers that purchase software from me every few years, and a few of them have mailed checks to my old mailing address from 4 years ago. How can this happen?
Well, the Dynamics GP Payables Transaction Entry window does not display the vendor Remit To address, so verifying the vendor address is not an obvious natural step in the invoice entry process. Yes, there is a link to open the Vendor Address Maintenance window, but what if internal controls prevents the user who enters vendor invoices from editing vendor addresses? The user would need to go through a separate process to verify the vendor address...for every invoice. Not ideal.
How can VBA help?
In just a few minutes, VBA can be added to the Payables Transaction Entry window to check if the vendor has not had a transaction in over 60 days, prompt the user to verify the vendor address, and even open the Vendor Inquiry window to review the current Remit To address in Dynamics GP.
It's really easy!
Here's a video discussing the background and walking through the entire process of adding the VBA to Dynamics GP.
First, within Dynamics GP, you add the desired windows to Visual Basic by clicking on Tools -> Customize -> Add Current Window to Visual Basic.
After the window is added, I click on Add Fields to Visual Basic and then click on the Vendor ID field.
Since I am assuming that the user entering invoices will not have access to edit vendors or vendor addresses, I'm going to add the Vendor Inquiry window to Visual Basic and add 3 fields on the Inquiry window to VB: Vendor ID, Address ID, and the Address ID Next button.
Once I have those windows and fields added to Visual Basic, I press CTRL+F11 to open the Dynamics GP Visual Basic Editor. If you don't have access to the VB Editor, you may not be licensed to use it, or you may not have permissions--in which case, talk with your GP administrator or GP partner.
In the VB Editor window, I'll select the PayablesTransactionEntry window on the left, then select the VendorID field and the AfterUserChanged event.
I wrote the simple VBA code below to demonstrate how you can quickly and easily add some VBA to add some valuable functionality to Dynamics GP to save users time and improve data entry.
The code finds the most document date for any vendor transaction in Dynamics GP, and if that date is over 60 days ago, it opens the Vendor Inquiry window and displays the vendor Remit To address for the user to review and verify.
Private Sub
VendorID_AfterUserChanged()
Dim strVendorID As String
Dim strSQL As String
Dim dtLastDocDate As Date
strVendorID = VendorID.Value
'Find the most recent document date for the
vendor
strSQL = "SELECT
COALESCE(MAX(DOCDATE), '1900-01-01') AS DOCDATE FROM PM00400 WHERE VENDORID =
'" & strVendorID & "'"
'If the last doc date is > X days
ago, display a dialog
If intDays > 60 Then
msgResult = MsgBox("This
vendor has not had a transaction since " & strLastDocDate & "
(" & intDays & " days ago)." & vbNewLine &
vbNewLine & "Please review the current vendor Remit To address and
compare to the invoice address", vbOKOnly, "Verify Vendor
Address")
VendorInquiry.Open
VendorInquiry.VendorID.Value =
strVendorID
VendorInquiry.Activate
VendorInquiry.Show
While VendorInquiry.AddressID.Value
<> strRemitID
VendorInquiry.NextButtonWindowArea.Value
= 1
Wend
End If
End If
End Sub
In just a few minutes, you can have this customization running in Dynamics GP without any additional development tools.
If you have more complex requirements, you can easily add more advanced functionality using VBA. If you prefer using a separate development tool, you could also develop this customization using .NET or Dexterity, but the appeal of VBA is its simplicity and ease of use.
So if you have some small problem or additional business requirement that you'd like to handle in Dynamics GP, VBA might come in handy.
This week I attended another great webinar by Kendra Little of SQLWorkbooks.com. (If you aren't familiar with Kendra, check out her free webinars and her excellent catalog of online courses.)
One neat thing that Kendra always seems to do in her webinars and courses is subtly use new(er) SQL Server features. This week, she just happened to use the AT TIME ZONE statement in one of her queries. As soon as I saw it, I knew I had to try it with Dynamics GP.
Dynamics GP doesn't have much time zone sensitive data, but one field that I am starting to rely on more frequently is the DEX_ROW_TS field, which is now present in several key GP tables. This field stores a last updated date time stamp.
DEX_ROW_TS is a bit unique for GP for at least two reasons. First, it's a rare time stamp field. While GP has many date fields, those date fields normally have a time of 00:00:00.000--so it's just a date at midnight, with no timestamp.
The second unique thing about DEX_ROW_TS is that it stores the datetime with a UTC timezone offset. So if you ever query the DEX_ROW_TS field you need to remember that it isn't local time.
Enter the very cool SQL Server 2016 AT TIME ZONE function. This function makes it very easy to assign a time zone to a datetime value, and then convert it to another time zone.
(I'm calling AT TIME ZONE a function for now because I haven't found a better name for it. It doesn't read like a typical function, but it acts like one, so Function is the best name I have so far. If you know of the proper technical name for it, let me know in the comments below.)
I just discovered a fun mistake that I made in a SQL script. It's a rookie mistake, but it's one of those somewhat novel mistakes that I think is easily missed in many projects.
I developed a Dynamics GP SOP Invoice import for a customer using .NET and eConnect. It has been in use for over 3 years and working great, but recently they finally had a scenario where they uncovered the latent bug.
After reviewing my code and looking at the data that triggered the bug, I found that I had a design flaw in a SQL statement. The flaw wasn't discovered during testing because I never anticipated a specific use case and boundary condition, so I never tested the scenario, and it took over 3 years for the customer to encounter it.
The customer is unique in that they will import an invoice, such as invoice number 123456, that relates to contract number 123456. Then a few days later they will need to make an adjustment to the contract, so they will issue a related invoice to add services to the contract. To help track the related transaction, the new invoice is imported into GP with a suffix on the invoice number, such as 123456-1. A few days later, they will issue a credit memo to decrease the contract amount, and that CM will be imported as document number 123456-2, etc. These numeric suffixes are added to the document number by the eConnect import.
Last week, the customer emailed me with a problem. They were getting this eConnect error:
