Recently, Mike from Thybar Corporation contacted me about an issue he was seeing in Microsoft Dynamics GP v18.7 with General Journal document attachments.
Journal Entry attachments that had been added before upgrading Microsoft Dynamics GP to v18.7 could no longer be viewed from the Journal Entry Inquiry window after the upgrade.
Mike had done some research and already narrowed down the issue, so it was just a quick matter for me to confirm his information and to check in the Dynamics GP Source Code what was going on. It actually took me longer to write this article than to confirm what had happened.
The Problem
Mike captured a Dexterity Script Log while opening the attachment from the Journal Entry Inquiry window and found the line in the log that generates the Business Object Key for the attachment. This is a unique key based on series, module, document type, document number and document date. The line of interest was:
‘GetBusObjKeyForGLEntry() of form coAttachManagement’, “”, 525492, 1/29/2024
However, as the Script log shows the parameters as they are passed into the script, it did not show the result returned by the function.
If you look at the following lines in the Script Log, you will see the Business Object Key being generated. Scroll right to see the full Business Object Key.
[code]'GetBusObjKeyForGLEntry() of form coAttachManagement', "", 525492, 1/29/2024
'sqlDate()', "", 1/29/2024
'CreateBusObjKey() of form coAttachManagement', "", "0\GL\Transaction Entry\", "515492~20240129", ""
'OpenWindow of form coAttachManagement', 2, "Journal Entry", "515492", "", "0\GL\Transaction Entry\515492~20240129", DocumentsAttached, DocumentsNotAttached, "", 0, 0, 0, ""
[/code]
Note: With GP Power Tools, generating a Dexterity Script Log is as simple as a single mouse click to start and a single click to stop after you have performed the action you wanted to capture logs for.
Mike used a Dexterity trigger on the function to display the result in a warning dialog, showing the same information as seen in the Script Log:
0\GL\Transaction Entry\515492~20240129
This all looks fine, until you look at the Business Object Key in the Attachments table at the SQL level and see the key used when the attachment was created is:
0\GL\Transaction Entry\515492~1/29/2024
You will notice that the date format is different. The original transaction in SQL is using MM/DD/YYYY format when the code is now looking for YYYYMMYY format. I could straight away see that this is the difference between using the str() function and the sqlDate() function in Dexterity.
There have been MANY, MANY issues caused by the str() function in Microsoft Dynamics GP. Most of them have been fixed years ago. However, this one was only recently fixed in GP v18.7. You might ask what is the problem?
The issue is that the str() function converts a date to a string using the local machine’s control panel regional settings. So, in the USA, it will use MM/DD/YYYY when most other countries use DD/MM/YYYY or even YYYY/MM/DD.
Most of the issues in the past occurred when a date was passed into SQL Server code. When str() was used the code worked fine in the USA as SQL Server expects MM/DD/YYYY. But then run GP in Australia, New Zealand, UK, South Africa, etc. and it “appears” to work fine for the first 12 days of a month and then on the after the 13th, it generates errors because SQL Server cannot handle DD/MM/YYYY when there is a month 13. These problems were found and fixed quickly because exceptions where generated.
In this case, the date is being used as part of a string and will not generate an error. But it is part of a primary key to a table and if the str() function is used, the key will be different depending on the regional setting of the machine running the code. Therefore, if I tried to access the same record from my machine in Australia, it would not find the data as it would be looking for:
0\GL\Transaction Entry\515492~29/01/2024
But even if I was in the USA, regional settings can control if there is a leading zero or not:
0\GL\Transaction Entry\515492~01/29/2024
By now you can see that the str() function is bad, evil and should never be used….. except when displaying data to the user. When using a date in data or passing a date to SQL, using a consistent format regardless of country or regional settings is required which is why the sqlDate() function exists and should be used.
The Code Fix
Looking at the source code for the different versions of GP, I was able to confirm exactly what I suspected. Below are the two scripts and you can see that in the GP v18.7 script the old line was commented out (using braces/squiggly brackets) and a new line using sqlDate() was used instead.
Note: Scroll to the right to see the use of the str() and sqlDate() functions.
GP v18.6: GetBusObjKeyForGLEntry() of form coAttachManagement;
[code]function returns string sBusObjKey;
in 'Journal Entry' nJournalEntry;
in 'TRX Date' dTrxDate;
clear sBusObjKey;
sBusObjKey = CreateBusObjKey(KEYGLTRANSACTIONENTRY, str(nJournalEntry) + CH_TILDE + str(dTrxDate)) of form coAttachManagement;
[/code]
GP v18.7: GetBusObjKeyForGLEntry() of form coAttachManagement;
[code]function returns string sBusObjKey;
in 'Journal Entry' nJournalEntry;
in 'TRX Date' dTrxDate;
clear sBusObjKey;
sBusObjKey = CreateBusObjKey(KEYGLTRANSACTIONENTRY, str(nJournalEntry) + CH_TILDE + sqlDate(dTrxDate)) of form coAttachManagement;
{sBusObjKey = CreateBusObjKey(KEYGLTRANSACTIONENTRY, str(nJournalEntry) + CH_TILDE + str(dTrxDate)) of form coAttachManagement;}
[/code]
While this has fixed the problem moving forward it does not resolve the issue for the existing data created before GP v18.7 or later was installed.
Did you notice the call to sqlDate() function in the Script Log above? You would not have seen that if the log was generated in a version earlier than GP v18.7.
The Data Fix
Now that the code has been fixed from GP v18.7 onwards, after installing the update, you will need to fix your data to match.
To view the records, use the following queries from SQL Server Management Studio:
[code language=”sql”]select * from CO00102 where BusObjKey like '0\GL\Transaction Entry\%'
select * from CO00103 where BusObjKey like '0\GL\Transaction Entry\%'
select * from CO00104 where BusObjKey like '0\GL\Transaction Entry\%'
select * from CO00105 where BusObjKey like '0\GL\Transaction Entry\%'
[/code]
After making a backup of your company database, update the BusObjKey column from str() format to sqlDate() format using the following update statements.
Note: You will need to change the source format code from 101 if you are not using MM/DD/YYY as your regional setting. Use code 103 for DD/MM/YYYY or see this article for other codes. The target format is 112 for YYYYMMDD.
[code language=”sql”]update CO00102 set BusObjKey = SUBSTRING(BusObjKey, 1, CHARINDEX('~', BusObjKey, 1)) +
CONVERT(VARCHAR, CONVERT(DATE, SUBSTRING(BusObjKey, CHARINDEX('~', BusObjKey, 1)+1, 10) , 101) , 112)
where BusObjKey like '0\GL\Transaction Entry\%'
update CO00103 set BusObjKey = SUBSTRING(BusObjKey, 1, CHARINDEX('~', BusObjKey, 1)) +
CONVERT(VARCHAR, CONVERT(DATE, SUBSTRING(BusObjKey, CHARINDEX('~', BusObjKey, 1)+1, 10) , 101) , 112)
where BusObjKey like '0\GL\Transaction Entry\%'
update CO00104 set BusObjKey = SUBSTRING(BusObjKey, 1, CHARINDEX('~', BusObjKey, 1)) +
CONVERT(VARCHAR, CONVERT(DATE, SUBSTRING(BusObjKey, CHARINDEX('~', BusObjKey, 1)+1, 10) , 101) , 112)
where BusObjKey like '0\GL\Transaction Entry\%'
update CO00105 set BusObjKey = SUBSTRING(BusObjKey, 1, CHARINDEX('~', BusObjKey, 1)) +
CONVERT(VARCHAR, CONVERT(DATE, SUBSTRING(BusObjKey, CHARINDEX('~', BusObjKey, 1)+1, 10) , 101) , 112)
where BusObjKey like '0\GL\Transaction Entry\%'
[/code]
More Information
Terry Heley from Microsoft confirmed that this is a known issue and is documented in the following article:
Note: There are fixing scripts in the article which will work, but are not as elegant and the update statements above.
Conclusion
Thank you Mike, for bring this issue to my notice. Hopefully this information and fix will be helpful to the Microsoft Dynamics GP community.
If you want to learn more about working with Dexterity, understanding logs or using GP Power Tools, register your interest on the survey from this article:
Thanks
David
17-Mar-2026: Updated with Upgrade Known Issue article from Microsoft.
This article was originally posted on http://www.winthropdc.com/blog.