Posted on by WinthropDC Dynamics, GP, GP Power Tools, Microsoft, Products

#GPPT Adding Custom Fields to Microsoft Dynamics GP without using Code or Modifier part 1

David Meego - Click for blog homepageEveryone wants extra user defined fields on their Microsoft Dynamics GP windows.  It is possible to add extra fields but there have always been compromises. Either it requires custom development with scripting and modified or alternate windows, or fields are in a separate window or sidebar, and the data is often stored in multiple records across multiple tables.

This year, as we celebrate 20 years of GP Power Tools, the upcoming Build 33 adds Custom Fields. This amazing new feature allows you to add almost any field to any window without any code or using Modifier.

Custom Fields combines the Custom Tables functionality (added in Build 32) and the Virtual Fields functionality (added in Build 31) and provides a simple configuration to add any field from a Custom Table, onto any window, in any product dictionary of Microsoft Dynamics GP, without needing any scripting, or any changes to the window with Modifier.

The custom fields are displayed in the same window (not an extra window or sidebar) and are stored as a single record in a custom table (not separate records for each field or across multiple tables). They can be of almost any data type, including complex data types such as drop-down lists and multi-select list boxes. String fields can also have lookups and validation of data entered.

The custom fields are added at the bottom of window after the window is expanded and the Status Bar fields (Browse buttons, sort by drop down list, OK or Cancel buttons) are moved down to make space.

The best way to explain how this feature works is to create a simple demonstration.

Requirements

The HR Department has decided they would like to track the details of their employees’ pets. They want to track the name and species of the pets.

Implementation

Project Setup

It is always best to combine all the elements into a project. This allows for easy management as well as exporting and importing, enable and disabling. Go to GP Power Tools >> Cards >> Project Setup to open the Project Setup window. Use the following steps to create the project:

  1. Enter the Project ID: EMP_CUSTOM_FIELDS.
  2. Enter the Project Description: Employee Maintenance Custom Fields.
  3. As we are working on this project mark it as the Current Project.
  4. Optional: Select the form to open when testing, in our case we will use the Menu Explorer and select Cards >> Payroll >> Employee Maintenance.
  5. If you want to limit the access to the custom fields, you can use the User Button and select who has access by User and/or Company, User Class, Security Role, Security Task or Alternate Modified ID.
  6. If you want to enable GitHub integration, to have your project exported to GitHub as well as locally, you can enable and configure those options.

Custom Table Preparation

The next step is to create a Custom Table to store the Custom Fields in. We want this linked to the Employee Master table. To create a parallel table to the Employee Master table we need to have the same primary key field(s) as our primary key. To quickly identify the primary key, use the following steps:

  1. From the Project Setup window, click the Open Form (optional) hyperlinked prompt to open the Employee Maintenance window.
  2. From the menus, select Tools >> Resource Finder. If the window has a linked table, it will be selected and the primary key fields highlighted. So, Employee ID in the UPR_MSTR (UPR00100) table is the field we need. You can scroll the field list to the right for more field details.
  3. Alternate (older) Method or for more information, click the Resource Info button from Resource Finder to open Resource Information, or select Tools >> Resource Information from Employee Maintenance. Now the form information is displayed. Click Associated Tables, select the desired table and click OK (or double click). This will show the table information. Click Display Keys and it will show you the key fields for all of the keys. You can also click on the Table Technical Name lookup to view the columns of the table with their technical and physical names and datatypes. Using Resource Finder is simpler 😊.

Custom Table Setup

Now we have the information we need, we can create our Custom Table definition, which will create and maintain the table in all company database on the SQL Server for us. From the Project Setup window, select Add >> Custom Table Setup. Use the following steps to create the custom table:

  1. Enter the Table ID: EMP_CUSTOM_FIELDS.
  2. Enter the Table Description: Employee Maintenance Custom Fields.
  3. Enter the Technical Name: GPPT_UPR_MSTR_Custom.
  4. Enter the Physical Name: GPPT_UPR00100_Custom.
  5. Select the Options: Check Auto Create and Enable Buffer.
  6. Add the Primary Key field(s), so enter Employee ID into the Field Technical Name in the scrolling window.
  7. Click on the Plus Sign to the right of the Indexes pane to add an index.
  8. Click back on the Employee ID field row and click the Insert current Field into selected index Button (down arrow to the right of the scrolling window) to insert the field into the index.
  9. Now, we can add some fields for the extra fields we want. You can select any field from an existing Dexterity product dictionary or select Product Name as Custom SQL Table (press C) and manually enter the field details. Add Pet Name of String 80 and Pet Type of Integer.
  10. Click Save and close the window.

Custom Field Setup

The final step is to use the Custom Fields Setup window to configure where you want the Custom Fields displayed. From the Project Setup window, select Add >> Custom Field Setup. Use the following steps to create the custom fields:

  1. Enter the Field ID: EMP_CUSTOM_FIELDS.
  2. Enter the Field Description: Employee Maintenance Custom Fields.
  3. Select the form and window to add the custom fields to. We will use the Menu Explorer and select Cards >> Payroll >> Employee Maintenance form >> Main: Employee Maintenance window (UPR_Employee_MNT).
  4. If the Table Name is not auto populated, or is not the table you are linking to, change the table to the desired table. It must be a table attached to the form.
  5. The Optional Changed Flag will be automatically populated when GP Power Tools detects once of the standard Changed Flag fields on the window.
  6. Select the Table ID, use the custom table created earlier: EMP_CUSTOM_FIELDS.
  7. The Index will be selected automatically and should not need changing assuming that the first index is the primary key for the custom table.
  8. The Key fields and additional fields will be displayed in the Custom Fields tree.
  9. As we used the exact field (Employee ID) for the key field, it has been automatically matched for us. The custom SQL fields will need to be mapped to window fields.
  10. Double click on Pet Name, select Use Automatic Field Types, Use Field Type: String, and Length: 80 and click OK.
  11. Double click on Pet Type, select Use Automatic Field Types, Use Field Type: List, Result: Data, click the Expansion Button and enter Pet Species (Name and Data value):
  12. Click OK and click OK on the Field Setup.
  13. You can save and test by clicking on the Form Name hyperlink.
  14. Click Save and close the window.
  15. From the Project Setup window, click the Open Form (optional) hyperlinked prompt to open the Employee Maintenance window.

Solution

The custom fields are now fully operational and will be displayed on any version of the window (original, modified, alternate or modified alternate).

Stay tuned for the next article where the inevitable “Scope Creep” happens and HR want to make some changes.

Downloading and Installing

Download the example code, import using the Project Setup window (requires at least build 33 as it uses Custom SQL Tables and Custom Fields):

The code will be active on next login or after switching companies, or you can use start the triggers manually from the Project Setup window.

More Information

Here are all the parts of the series (available once published):

For more information see:

If you are interested getting early access to the Custom Fields functionality and Build 33 of GP Power Tools, please contact us. As always, your feedback will help improve the product and its features.

To stay informed for more details and release notices, please subscribe to this blog and our Newsletter mailing list.

Tell us in the comments how you would use this great new feature.

Thanks

David

This article was originally posted on http://www.winthropdc.com/blog.

Leave a Reply