The following VBA example extends the Reference field on the GL Transaction Entry window from string field of 30 characters to a text field of 200 characters. The first 30 characters of the text field are populated into the original string field so it will contain data when viewed on a non-modified window or report.
The code uses the DUOS (Dynamic User Object Store) to store the contents of the text field when there are more than 30 characters. If there are 30 characters or less, it can just use the original field and does not require a DUOS record.
The code includes a function which can parse the 200 characters of the text field into lines of 80 characters or less without splitting words in half. This allows the lines to be stored in the DUOS which has a limit of 132 characters per value stored. These individual lines can then be used directly on reports.
The example modifies the GL_Transaction_Entry window, the GL_Journal_Entry_Inquiry window and the GL_Zoom_Current_Transaction window as well as the General Posting Edit List report, the General Posting Journal report, and the GL Journal Inquiry report.
Example code for v8.0, v9.0 & v10.0 is attached at the bottom of the article.
Please see the “Installation Instructions.txt” file in each version’s archive for more information. Don’t forget to change the security to use the modified forms and reports.
NOTE: This technique is not recommended for extending primary (unique) key/index fields. This is because if you have two records with key values which only differ in the characters beyond the length of the original field, the two records will be duplicates. The index on the table is still based on the number of characters in the original field.
07-Sep-2010: Added reminder about security settings.
16-Oct-2013: Updated code to use CLng() instead of CInt() as a Journal Entry Number higher than 32,768 would cause an overflow error. Thanks Don for spotting this issue.