Freigeben über

Date Function Errors from SmartList Account Transactions fixed in Microsoft Dynamics GP 2013 Service Pack 2

  • Artikel

David Meego - Click for blog homepageA little while ago I spent some time in a long screen sharing session to identify the cause of some really unusual errors being generated from SmartList Account Transactions favourite in Microsoft Dynamics GP 2013 Service Pack 1. This issue has now been fixed for Microsoft Dynamics GP 2013 Service Pack 2.

The errors did not occur on the RTM build, before Service Pack 1 was installed, and do not occur with all transactions listed in the SmartList. The script errors will be in the form:

Unhandled script exception:
Invalid year '20' in date function. Script terminated.
EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE
SCRIPT_CMD_SETDATE

Unhandled script exception:
Invalid month '13' in date function. Script terminated.
EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE
SCRIPT_CMD_SETDATE

After some research including capturing logs with the Support Debugging Tool, reviewing the source code and analysing the way the source code works using Runtime Execute and SQL Execute scripts in the Support Debugging Tool, we were able to identify the cause of the issue.

Service Pack 1 contains a fix for Problem Report 56666: Drill back on recurring transaction from Account Transaction Report in SmartList brings up incorrect transaction information. The code changes for this fix work most of the time, but fails for a recurring transaction that has been repeated more than 9 times.

This fix adds the Recurring TRX Sequence number and Transaction Date (in YYYYMMDD format) to the hidden string field in the SmartList window used for linking back to the transactions.

For example:

1                   1                   1GL_Normal      120141.0000020131231

The code that extracts the date from this string expects the Year, Month and Day in specific positions:

l_year = substring(l_string, 69, 4); = 2013
l_month = substring(l_string, 73, 2); = 12
l_day = substring(l_string, 75, 2); = 31

giving a date of 31/12/2103 (in DD/MM/YYYY format)

However, when the Recurring Sequence Number is greater than 9.00000, it causes the string to grow and the date information to be shifted:

5426                57185               1GL_Normal      12013266.0000020130701

This example has a sequence number of 266.00000 which causes the date to be shifted two more characters to the right.

l_year = substring(l_string, 69, 4); = 0020  -- Not valid
l_month = substring(l_string, 73, 2); = 13    -- Not valid
l_day = substring(l_string, 75, 2); = 07

Giving a date of 07/13/0020 (in DD/MM/YYYY format)

Depending on the date and the number of characters shifted, you will get date function errors for year, month or day.

This issue was reported and is now fixed in Microsoft Dynamics GP 2013 Service Pack 2.

While the customer waited for Service Pack 2, I created a fix using the Support Debugging Tool. I used a Non-Logging Trigger to update the hidden string when a line in the SmartList is clicked on. The trigger script removes some of the decimal place characters from the Recurring Sequence Number so that the date is then in the expected position.

For example: 266.00000 is reduced to 266.000 which is still the 7 expected characters. Which in turn leaves the 8 characters of the date (in YYYYMMDD format) in the correct location.

The trigger is attached to the bottom of this article for your reference, however, the best solution is to install Service Pack 2. If the trigger is installed on Service Pack 1, it will need to be removed once Service Pack 2 is installed to allow the fixed code to work.

Hope you find this useful.

David

Debugger Settings Bug 70000 For Blog.dbg.zip

Comments