Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Modifier - Extending the length of a Field Example

Modifier - Extending the length of a Field Example

  • Comments 23

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.

Attachment: GL Long Reference Example.zip
  • PingBack from http://blogs.msdn.com/developingfordynamicsgp/pages/modifier-vba-samples.aspx

  • Hi Folks,

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

  • 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

  • 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

  • Hey guys,

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

  • 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

  • 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

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

  • 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

  • Post from Jivtesh Singh at About Dynamics, Development and Life

    www.jivtesh.com/.../dynamics-gp-customizations-best.html

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

  • 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

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

  • Hi Charles

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

    David

  • 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

Page 1 of 2 (23 items) 12
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post