Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
Comments
Anonymous
July 24, 2008
PingBack from http://blogs.msdn.com/developingfordynamicsgp/pages/modifier-vba-samples.aspxAnonymous
March 04, 2009
Hi Folks, Is there any way for doing the same to the distibution reference at the grid window regarding the same example? any idea?Anonymous
March 04, 2009
The comment has been removedAnonymous
March 05, 2009
The comment has been removedAnonymous
May 21, 2009
Hey guys, Can this be used on a sales transaction description field? I'm having trouble fitting in the information needed into my invoices.Anonymous
May 24, 2009
The comment has been removedAnonymous
March 21, 2010
Hi, This is regular requirement in all company to have large reference (narration) filed in GL Entry screen. This is very useful in such case. SantoshAnonymous
July 27, 2010
Hi, Instead of using DUOS to add the additional reference data, is it possible to create a new table and add the table in data dictionary and then work with Modifier.Anonymous
July 27, 2010
Hi Amish You cannot use Modifier to add or modify table definitions in the data dictionary. Only Dexterity can do that and if you are using Dexterity you would not need to use Modifier (unless the window being modified was a 3rd party window). That said, you don't have to use the DUOS to store data when working with Modifier. It is just easier as it has built in commands. You can also use UserInfoGet (v10+) or RetrieveGlobals (v8 or v9) to get credentials and then ADO to communicate directly with SQL Server and either send select, insert and update commands or work with record sets. See the following article for more information: How to use ActiveX Data Object (ADO) with VBA on a window with Microsoft Dynamics GP and with Microsoft Business Solutions - Great Plains 8.0 (KB 942327) Note you can also use a view with the DUOS to make it easier to join with other tables. Please see http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/03/11/creating-sql-views-of-duos-data.aspx DavidAnonymous
August 19, 2010
Post from Jivtesh Singh at About Dynamics, Development and Life www.jivtesh.com/.../dynamics-gp-customizations-best.htmlAnonymous
September 12, 2010
Hi Dave, The script worked for me. Its a great guide and we eventually got our field size increased from the deafult 30 characters to the maximum 255 characters. Thanks Dave.Anonymous
November 03, 2010
Is there anyway on how to extend a description field in recievables transaction entry following the same procedure above? in GP 2010 Transactions>Sales>Transaction Entry... (Description). Regards, CharlesAnonymous
November 04, 2010
Why doesn't Microsoft just make this field longer in sql and within the application? Seems like a simple upgrade?Anonymous
November 04, 2010
Hi Charles The exact same concept can be used to extend any field (except primary key fields). DavidAnonymous
November 04, 2010
Hi Ryan Please log a product suggestion via MS Connect to get the field extended. The questions I would ask you are: How long would you extend it to (max 255 char)? What other fields would you extend? on what other windows? What flow through affects are there, where the fields flow to other transaction types? Where does it stop? Did you consider the increase in storage requirements? As you can see, your simple question is not so simple to implement. :-) DavidAnonymous
November 03, 2011
Is there a way to do this if you have a client that does not own modifier?Anonymous
November 03, 2011
Corrie, Someone who does own mod/vba has to do it. Then they can create a package and the customer could then import that package in an run it. So they can't write it but they can use it (as long as they are registered for the Customization Site License).Anonymous
October 15, 2013
The comment has been removedAnonymous
October 15, 2013
Hi Don Well spotted, the CInt() function needs to be replaced with the CLng() function to allow it to work with larger numbers. I did not realised that VBA's CInt was limited to 32768. I have fixed the code and updated the archive file. DavidAnonymous
April 06, 2014
Hi David, Does this work with GP 2013 or do you have a new code for GP2013. I have a client who needs this for GP 2013. 30 characters is simply not enough. Thanks BimalAnonymous
April 06, 2014
Hi Bimal The v10 code should work fine for GP 2010 and GP 2013. Thanks DavidAnonymous
April 21, 2014
Can we do the same thing to input serial numbers for items having size more than 20................Anonymous
April 21, 2014
Hi Thomas The Serial and Lot numbers are key fields (ie. included in a table's primary key) because they are unique. So, no, this technique would not work for Serial Number Field. If there are a lot of numbers which don't change, you could enter in a shorter serial number into the actual field and use a custom field or Extender field to store the full length number. I would suggest adding a product suggestion via MS Connect to extend the Serial and Lot number fields. DavidAnonymous
January 10, 2015
please help me 1st step is done files are imported in GP now how can it works ?Anonymous
January 30, 2015
Mustafa Did you change the security settings to access the modified window? That should be all that is needed. DavidAnonymous
August 18, 2015
Hi David Got an error "Unable to apply changes to WindowControl '(L) wfOriginalBatchApproval Status' of Window 'GL_Transaction_Entry', upon import of modified Forms, in GP 2015. So i guess this means the code is not GP 2015 compatible? Is it possible to make compatible or is it too different?Anonymous
August 20, 2015
Hi Shaun The code should be the same, it is the modified window causing the issue. GP 2015 has some new fields and they are not included in the modified window. As I am no longer a Microsoft employee, I am unable to update the site or the example. You can look at the package with notepad.exe to see the changes made and the scripts. If you cut & paste the scripts, you will need to find " and replace with " to fix the double quotes. DavidAnonymous
August 24, 2015
Hello David, I downloaded your instructions and noticed I dont have access to Tools, Customise ,Visual Basic Editor. It is greyed out. Is it still possible for me to increase the field size?Anonymous
August 26, 2015
Hi Subrata Sounds like you don't have registration keys for Modifier or don't have security access granted for your user. David