This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
Today I worked with my colleague Rob on an Integration Manager for Microsoft Dynamics GP issue.
We had a customer who was importing some General Ledger journals into their system and wished for the journals to be set up as reversing journals with the Reversing Date set up as the first date of the next month. The plan was to use the following script to manipulate the Transaction Date to create the Reversing Date.
Field ScriptText for Reversing Date
' Added by the IM Script Library ' Category: Microsoft Dynamics GP Scripts ' Script Type: field Script dtTrxDate = DestinationFields("Transaction Date") 'Add one month to the transaction date. This will handle short months correctly. For example, adding 1 'month to January 31st will result in February 28th (or the 29th if it’s a leap year). dtDate = DateAdd("m", 1, dtTrxDate) 'Set the new date to the first of the month following the transaction date dtDate = DateSerial(Year(dtDate), Month(dtDate), 1)
This is the code that the customer had in their system. It did not cause any errors, but the Reversing Date was being set to the current system date.
While the code looks fine, I noticed that there was nothing in the code to “return” the dtDate variable to the Reversing Date field. Adding the following line to the code resolved this issue:
CurrentField.Value = dtDate
However, when we run the integration, the script failed on the above line saying it was unable convert the value “01/06/2012 12:00:00 a.m.” to a date.
When I saw this a little alarm bell went off at the back of my head. I noticed something that was a (little) known issue…..
The time suffix with periods/dots/full stops is not understood by SQL Server when converting strings to datetime variables. This is discussed in the blog post: Quick Tip: Passing Date and Time Variables to SQL Server.
After getting the customer to change their Time regional setting back to AM & PM rather than a.m. & p.m., the integration progressed, but gave us the following error:
DOC 1 ERROR: The reversing date must be the same as or later than the transaction date.
This then made me think that Integration Manager was trying to interpret “01/06/2012 12:00:00 AM” as January 6th 2012 rather than the correct 1st June 2012. To test we temporarily changed their Date regional setting to MM/DD/YYYY and surprise, surprise…. the integration worked.
So, we restored the Date regional setting back and added code to convert the date into the required US format (MM/DD/YYYY).
Updated Field ScriptText for Reversing Date
' Added by the IM Script Library ' Category: Microsoft Dynamics GP Scripts ' Script Type: field Script dtTrxDate = DestinationFields("Transaction Date") 'Add one month to the transaction date. This will handle short months correctly. For example, adding 1 'month to January 31st will result in February 28th (or the 29th if it’s a leap year). dtDate = DateAdd("m", 1, dtTrxDate) 'Set the new date to the first of the month following the transaction date dtDate = DateSerial(Year(dtDate), Month(dtDate), 1) sDay = Right("00" + Trim(Day(dtDate)), 2) sMonth = Right("00" + Trim(Month(dtDate)), 2) sYear = Right("0000" + Trim(Year(dtDate)), 4) sDate = sMonth + "/" + sDay + "/" + sYear CurrentField.Value = sDate
Now the integration worked, regardless of the regional setting value. As the time component was no longer included in the script, the issue with 12 hour time suffixes also would not occur.
However, as the 12 hour time suffix issue can cause errors (below) elsewhere, we still suggested that this be changed system wide.
/* Date: 12/15/2011 Time: 10:44:04
SQLSTATE:(22008) Native Err:(241) stmt(99398048):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string.*/
So please be aware that when working with dates in Integration Manager you will need to ensure the resulting values are in US date format.
For more information on supported date formats, see Mariano’s post at Supported Date Formats in Integration Manager.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
3 thoughts on “Working with Dates in Integration Manager VBScript”
I did some work for a client a few weeks ago.
Receivables and standard journals seem quite happy with UK date formats but the reversing date on a reversing journal and Payables Transactions want US or Japanese (yyyy-mm-dd) formats.
IM wouldn't be so bad if it was at least consistent across the board with how it handles dates.
p.s. the other problem to watch for when using VBA is a blank line at the very top of the script which causes it to break and stop you reopening the script to fix.
Cool! Good to know.
Posting from Mark Polino at DynamicAccounting.net