Modifier – Extending the length of a Field Example


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.

GL Long Reference Example.zip

Comments (29)

  1. Tarek says:

    Hi Folks,

    Is there any way for doing the same to the distibution reference at the grid window regarding the same example? any idea?

  2. David Musgrave says:

    Hi Tarek

    While this is technically possible it will be significantly harder.

    You can use the DUOSObjectCombineID() method to combine together the string representation of the Journal Number and the string representation of the Sequence Line.

    The problem is that the Sequence Line for a new line is not assigned until the line is saved. So you will have to work out a method of identifying the new line’s Sequence Line value to save you DUOS data.  This will also need to be able to handle inserted lines or gaps left by deleted lines.

    I will see if any of my colleagues have ideas.

    David

  3. David Musgrave says:

    Hi Tarek

    My colleague, Pat, has come up with a solution for the GL_Transaction_Entry window.

    Looking at the Line change script for the scrolling window we can see that the next sequence number for the line (when it has a value of zero) comes from the ‘(L) Next Sequence DLR’ of window GL_Transaction_Entry field.

    So you can write your code to get this value when the value in the window is zero, else take the value in the window.

    Good luck

    David

  4. Cory says:

    Hey guys,

    Can this be used on a sales transaction description field? I’m having trouble fitting in the information needed into my invoices.

  5. David Musgrave says:

    Hi Cory

    This concept can be used with any window.  You can also look at using the Item Master Record Note or the Line Item level comments.

    David

  6. santoshsurti says:

    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.

    Santosh

  7. Amish says:

    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.

  8. David Musgrave says:

    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) Secure Link

     

    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

    David

  9. David Musgrave says:

    Post from Jivtesh Singh at About Dynamics, Development and Life

    http://www.jivtesh.com/…/dynamics-gp-customizations-best.html

  10. Charles Siele says:

    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.

  11. Charles Siele says:

    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,

    Charles

  12. Ryan says:

    Why doesn't Microsoft just make this field longer in sql and within the application?  Seems like a simple upgrade?

  13. David Musgrave says:

    Hi Charles

    The exact same concept can be used to extend any field (except primary key fields).

    David

  14. David Musgrave says:

    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. 🙂

    David

  15. Corrie says:

    Is there a way to do this if you have a client that does not own modifier?

  16. Patrick Roth [MSFT] says:

    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).

  17. Don Comontofski says:

    I have installed the code on my personal machine on my laptop, Windows 7, on GP 2013 and it works great. I installed it on a GP 2013, Windows 2012, and SQL Server 2012 machine and I get an error, both are the same version of 2013, sp2. The error I get is Runtime error '6', overflow, debug takes me to this line of code highlighted,

    LongReference = LoadAndJoin(CInt(JournalEntry.Value))

    Located here:

    Private Sub JournalEntry_Changed()

       ' Retrieve DUOS Data

       If JournalEntry.Empty = False Then

           LongReference = LoadAndJoin(CInt(JournalEntry.Value))

       Else

           LongReference = ""

       End If

    Any thoughts?

    The value in JOUrnalentry.value it shows is 53,168.

  18. David Musgrave says:

    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.

    David

  19. Bimal Chandra says:

    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

    Bimal

  20. David Musgrave says:

    Hi Bimal

    The v10 code should work fine for GP 2010 and GP 2013.

    Thanks

    David

  21. Thomas Mathew says:

    Can we do the same thing to input serial numbers for items having size more than 20…………….

  22. David Musgrave says:

    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.

    David

  23. Mustafa says:

    please help me

    1st step is done files are imported in GP

    now how can it works ?

  24. David Musgrave says:

    Mustafa

    Did you change the security settings to access the modified window?  That should be all that is needed.

    David

  25. Shaun says:

    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?

  26. 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.

    David

  27. Subrata Purkayastha says:

    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?

  28. Hi Subrata

    Sounds like you don't have registration keys for Modifier or don't have security access granted for your user.

    David