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!
By Steve Endow
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?
Tuesday, February 27, 2018
Sunday, February 25, 2018
T-SQL: MAX vs. TOP 1 - Which is better??
By Steve Endow
If you need to get the largest value for a field, should you use MAX in your query? Or should you use TOP 1 with ORDER BY?
Which is better? Which is faster? Is that always true?
Do you think you know the answer?
Place your bets, and then check out my video below, where I compare MAX vs TOP 1 on several Dynamics GP tables.
The results may surprise you!
Did I miss anything or make any mistakes in my testing? Are there other considerations when choosing between MAX vs. TOP 1?
If you need to get the largest value for a field, should you use MAX in your query? Or should you use TOP 1 with ORDER BY?
Which is better? Which is faster? Is that always true?
Do you think you know the answer?
Place your bets, and then check out my video below, where I compare MAX vs TOP 1 on several Dynamics GP tables.
The results may surprise you!
Did I miss anything or make any mistakes in my testing? Are there other considerations when choosing between MAX vs. TOP 1?
How to improve Dynamics GP with a little bit of VBA
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!
By Steve Endow
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.
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.
I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com
Thanks!
By Steve Endow
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 & "'"
Set oConn = UserInfoGet.CreateADOConnection
oConn.DefaultDatabase =
UserInfoGet.IntercompanyID
Set rsResult = oConn.Execute(strSQL)
strLastDocDate =
Trim(rsResult.Fields("DOCDATE").Value)
rsResult.Close
'Get the Remit To Address ID for the vendor
strSQL = "SELECT VADCDTRO FROM PM00200
WHERE VENDORID = '" & strVendorID & "'"
Set rsResult = oConn.Execute(strSQL)
strRemitID =
Trim(rsResult.Fields("VADCDTRO").Value)
rsResult.Close
oConn.Close
dtLastDocDate = CDate(strLastDocDate)
'If Doc Date is 1/1/1900, vendor has no
transactions
If dtLastDocDate =
CDate("1900-01-01") Then
Exit Sub
Else
intDays = DateDiff("d",
dtLastDocDate, DateTime.Date)
'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.
Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.
Saturday, February 24, 2018
Convert DEX_ROW_TS to a local time zone using AT TIME ZONE
By Steve Endow
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.
I previously wrote a post about DEX_ROW_TS and how to use some SQL date functions to convert the value to your local time zone, but that approach felt a bit like duct tape and twine, and I would have to look up the syntax every time to use it.
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.)
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.
I previously wrote a post about DEX_ROW_TS and how to use some SQL date functions to convert the value to your local time zone, but that approach felt a bit like duct tape and twine, and I would have to look up the syntax every time to use it.
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.)
Wednesday, February 14, 2018
My latest rookie SQL mistake...
By Steve Endow
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:
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:
Subscribe to:
Posts (Atom)