The Creation of the Support Debugging Tool
If you have seen me present on the Support Debugging Tool for Microsoft Dynamics GP, you know that I usually give a brief overview of why the tool was created. After a chat with my co-presenter, Mariano Gomez, The Dynamics GP Blogster, I decided that it would be worth explaining a bit more about the support case which is responsible for the creation of the tool.
Background
Back in the days of Microsoft Business Solutions - Great Plains 8.00, we had a site in the Asia Pacifc region which had an issue with the Payables Historical Aged Trial Balance report. They wanted to use the "Unposted Applied Credit Documents" option (see screenshot below) to stop unposted credit documents from affecting the outstanding balances.
What they found was that changing this checkbox setting in the report option did not affect the reports printed and that the unposted transactions were still being included.
Analysing the PM Historical Aged TB - Detail report and the code that generates the report identified that the filtering controlled by the checkbox in the report option used the 'Posted' field on the PM_Apply_To_WORK_OPEN table (PM10200.POSTED) to decide whether to include the document in the report.
Looking at the data we could identify transactions where the field Posted of table PM_Apply_To_WORK_OPEN was set to true even though the associated transaction was not yet posted. However, a newly created and applied transaction would have this field as false.
We used the following SQL query to compare against the PM_Key_MSTR (PM00400) table to see if the Document Status of the transaction shows as unposted.
select POSTED,PM10200.VENDORID,APTODCNM,VCHRNMBR,PM10200.DOCTYPE, APFRMAPLYAMT
from PM10200
INNER JOIN PM00400
ON PM10200.VCHRNMBR=PM00400.CNTRLNUM AND PM10200.DOCTYPE=PM00400.DOCTYPE
WHERE PM00400.DCSTATUS=1
order by PM10200.POSTED desc
Any transactions which were returned with the POSTED column showing as 1, had an apply record marked as Posted when the transaction was not yet posted.
So we knew that the data was being created correctly, but somehow the apply records were being marked as posted even thought the associated transaction was not yet posted. Many hours were spent reviewing the source code, but nothing could be found that would explain why the setting would change incorrectly. We could not identify when or how the data was being changed.
The support case stalled at this point for a period of time as no-one could replicate the issue on demand. We have involvement from the customer, the partner, the Asia Pacific support team, the North American support team, and even the Dynamics GP Development team and no-one could replicate the issue or explain what was happening.
Concept Design
So, I was thinking about what we could do to move forward.
Using a SQL trigger on the PM10200 table would at least give us a date, time and user ID of when the data changed, but then we would need to have to ask the user to remember what they were doing at that particular time. It would get us closer, but was not really good enough.
Using a Dexterity table trigger on the same PM_Apply_To_WORK_OPEN table could achieve a similar result, but we could also pop up a system dialog to alert the user. This would get us closer as we would have details of what the user was doing.
Then I remembered seeing a Knowledge Base (KB) article that explained how the Dexterity Script Debugger Options for Script Logging and Script Profiling could be programmatically controlled:
With some further research and discussions with the Dexterity Development team, I also found out how to control the DEXSQL.LOG logging programmatically.
We now had the necessary ingredients to create a Dexterity tool to watch for changes on a table and capture logs of how it happened.
Development
I thought about writing a hard coded Dexterity dictionary that could look for this one situation, but then decided that a generic tool would be more useful for support teams in the future. In August 2006, the MBS Support Debugging Tool was born (the MBS was later dropped).
I started by creating a tool which could manually turn on the 3 logging techniques and save them with user and datetime stamped filenames, this is now known as Manual Logging Mode.
Then using some of the techniques I used when I developed Field Level Security (originally Omni Field Security), I created a window which would allow a single table trigger to be defined including a script that could look for a condition to occur and then return a true or false value. This trigger would be registered when logging into Dynamics GP and would start capturing the logs while waiting for a record to be saved in the table. When the record is saved the conditional script is executed. If the script returned false the logs were deleted and logging started again. If the script returned true the logs were saved and a dialog was shown to the user. This was the first version of Automatic Debugger Mode.
As we wanted the tool to have a small footprint and be easily installed and removed, the settings were written into a xml file which could be sent along with the chunk file to be deployed into the customer's system. The tool did not need to create any objects in the SQL Server databases.
Deployment
So using the tool, we created a trigger on the 'Posted' field in the PM_Apply_To_WORK_OPEN table in product 0: Microsoft Dynamics GP, using the conditional script below to compare the posted flag against the PM_Key_MSTR table's 'Document Status' field.
Trigger Conditional Script
inout anonymous table PM_Apply_To_WORK_OPEN;
in integer IN_Operation;
in string IN_OldValue;
in string IN_NewValue;
out boolean OUT_Condition;
OUT_Condition = false;
case IN_Operation
in [TRIGGER_ON_DB_ADD]
if not empty(column("'Posted'") of table PM_Apply_To_WORK_OPEN) then
{ Posted set to 1 }
clear table PM_Key_MSTR;
case integer(column("'Document Type'") of table PM_Apply_To_WORK_OPEN)
in[PM_DOC_CR_MEMO]
'Control Type' of table PM_Key_MSTR = 0;
in[PM_DOC_PAYMENT]
'Control Type' of table PM_Key_MSTR = 1;
else
end case;
'Control Number' of table PM_Key_MSTR = string(column("'Voucher Number'") of table PM_Apply_To_WORK_OPEN);
get table PM_Key_MSTR;
if err() = OKAY then
if 'Document Status' of table PM_Key_MSTR = WORK then
OUT_Condition = true;
end if;
end if;
end if;
in [TRIGGER_ON_DB_UPDATE]
if IN_NewValue <> IN_OldValue then
if not empty(column("'Posted'") of table PM_Apply_To_WORK_OPEN) then
{ Posted set to 1 }
clear table PM_Key_MSTR;
case integer(column("'Document Type'") of table PM_Apply_To_WORK_OPEN)
in[PM_DOC_CR_MEMO]
'Control Type' of table PM_Key_MSTR = 0;
in[PM_DOC_PAYMENT]
'Control Type' of table PM_Key_MSTR = 1;
else
end case;
'Control Number' of table PM_Key_MSTR = string(column("'Voucher Number'") of table PM_Apply_To_WORK_OPEN);
get table PM_Key_MSTR;
if err() = OKAY then
if 'Document Status' of table PM_Key_MSTR = WORK then
OUT_Condition = true;
end if;
end if;
end if;
end if;
in [TRIGGER_ON_DB_DELETE]
else
end case;
After some in house testing, the Debugger.cnk and Debugger.xml files were deployed at the customer's site. Then we waited....
Success
We did not have to wait too long. On the fourth day, the system dialog popped up and notified the user that the event we had been wanting to find all this time had just occurred.... and this time we had logs of everything that happened just prior to the event. The user explained that he was just printing an Edit List of a batch of payables transactions when the dialog appeared.
The logs were sent back to me for analysis. Looking at the captured Dexterity Script Log, I opened the source code to the last script in the log before the Debugger's trigger fired. A quick "Find" search and I found the line that was causing all the problems.
set Posted of table PM_Apply_To_WORK_OPEN to true;
A little bit of theory: When you print an Edit List, Dynamics GP runs through all the pre posting validation using the same code as if you had asked it to post. However, once the validation is completed, the code will stop rather than continue with the actual posting process.
The line we had found was correct....when you were actually posting, but not when you were printing an edit list only. Adding a check if we were printing an edit list solved the issue.
if not INOUT_Boolean_Parms[PM_POST_BOOL_EDITLIST] then
set Posted of table PM_Apply_To_WORK_OPEN to true;
end if;
This change was needed in three related payables posting scripts.
Resolution
Once the cause of the issue was identified and a suggested code fix was logged with the Dynamics GP development team, they moved quickly to create a hotfix for the customer and apply the fix to the next Service Pack.
A few days later, a hotfix chunk file was installed at the customer and this long running issue was solved.
The Support Debugging Tool had shown its value on its first outing into the real world.
More Information
The Support Debugging Tool was shown to the partner community at the Microsoft Dynamics GP Technical Airlift 2008 in May 2008 and announced on the blog in July 2008. Finally in September 2008, build 9 of the tool was released onto PartnerSource. Since then 6 builds have been released bringing us to build 15.
For more information on the Support Debugging Tool, please have a look the Support Debugging Tool Portal page.
I hope you find this little bit of history interesting.
Please post comments to join me in wishing the Support Debugging Tool a happy 5th birthday.
David