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 5

David Meego - Click for blog homepageEveryone wants extra user defined fields on their Microsoft Dynamics GP windows. In the previous articles we showed how easy it is to add, modify and format the layout of extra fields using GP Power Tools with the new Custom Fields feature.

We thought we were done, but now the request has come through from the HR team to include the Pet related custom fields on the Employee report.

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.

One More Requirement

The HR Team now want to see the Pet related custom fields showing on the Detailed Employee List report, that can be printed from the Employee Maintenance window.

One More Implementation

Project Setup

To avoid changing the projects from the previous articles, it has been duplicated from EMP_CUSTOM_FIELDS4 into a new EMP_CUSTOM_FIELDS5 project, the Custom Table and Custom Field resources were automatically renamed to match. Also make the following changes:

  1. Project Description: Add the word “Again” back again!!!
  2. Make this project the Current Project.

NOTE: This duplication step is only necessary if you want to keep the previous iterations of the project. Don’t forget to disable the EMP_CUSTOM_FIELDS4 project.

Custom Table Setup

On the EMP_CUSTOM_FIELDS5 custom table the following changes have been made:

  1. Table Description: Add the word “Again” back again.
  2. The Technical Name: Change number “4” to “5”
  3. Physical Name: Change number “4” to “5” and respond “Create New” to the dialog. This will create a new table, so that the original table is not renamed.
  4. Click Save and acknowledge the warning that this table is used by Custom Fields, then close the window.

Custom Field Setup

On the EMP_CUSTOM_FIELDS5 custom field the following changes have been made:

  1. Field Description: Add the word “Again” back again.
  2. Click Save, then close the window.

On the EMP_CUSTOM_FIELDS5_INQ custom field the following changes have been made:

  1. Field Description: Add the word “Again” back again.
  2. Click Save, then close the window.

Modifying the Detailed Employee List Report

The simplest method to open the Report Writer to the correct report is to open the Employee Maintenance window, pull up an employee record and then print the report to screen using the report button on the top right of the window.

Once the report is displayed to the screen in the Screen Output window, click the Modify button to open the Report Writer.

Now that the report layout is open, we can see that this report is a little unusual as it is using a single body single field to display all the information with the contents of this field being populated into the UPR_Employee_List_TEMP table. This will not cause any issues as we will just place our custom fields into the F1 – MSTR Employee Footer.

A Little Bit of Theory

The method used to display data from Custom Tables directly or via Custom Fields has three steps:

  1. Open a connection to the Custom Table and store its Buffer Number. The connection closes automatically when the report is completed.
  2. Set the Key segments into the Buffer so that the appropriate record can be retrieved.
  3. Read the fields and display them on the report as desired. Repeat for as many fields as needed.

To facilitate these steps there are RW Functions available which can be used inside Calculated Fields in the Report Writer. There are two sets available, one for Custom Tables and one for Custom Fields. Both can be used, however, the advantage of the Custom Fields set is that you don’t need to specify Index information, as the index to use is already stored on the Custom Field Setup window.

The Report Writer functions have been mapped to use the generic rw_TableHeaderCurrency, rw_TableLineString and rw_TableLineCurrency functions which are available from any product dictionary. This information is fully documented in the GP Power Tools user guide manual.

Custom Table (Buffer) RW Functions

RW_Buffer_Open

FUNCTION_SCRIPT( rw_TableHeaderCurrency -5261 “TableOpen” “<Table ID>” 0 0 )
in integer dict_id; {Negative Dictionary ID: -5261}
in string IN_Function; {String constant: “TableOpen”}
in string IN_TableID; {Table ID of Custom Table}
in integer IN_NotUsed1; {Integer constant: 0}
in integer IN_NotUsed2; {Integer constant: 0}

RW_Buffer_Key_Set

FUNCTION_SCRIPT( rw_TableLineCurrency -5261 “TableSet” “<Key Value>” 1 1.0 (c) Buffer 0 )
in integer dict_id; {Negative Dictionary ID: -5261}
in string IN_Function; {String constant: “TableSet”}
in string IN_StringData; {String Data to set field to}
in integer IN_Index; {Index Number from Custom Table}
in currency IN_Key_Sequence; {Key Segment Sequence from Index}
in currency IN_Buffer; {Buffer Number from RW_Buffer_Open}
in integer IN_Array; {Array Index, if needed}

RW_Buffer_Field_Get (can return as String or Currency)

FUNCTION_SCRIPT( rw_TableLineString -5261 “TableGet” “<Field Name>” 0 0.0 (c) Buffer 0 )
FUNCTION_SCRIPT( rw_TableLineCurrency -5261 “TableGet” “<Field Name>” 0 0.0 (c) Buffer 0 )
in integer dict_id; {Negative Dictionary ID: -5261}
in string IN_Function; {String constant: “TableGet”}
in string IN_Field; {Field Name for Table Buffer field}
in integer IN_Characters; {Optional Max Characters per Line}
in currency IN_NotUsed; {Currency constant: 0.0}
in currency IN_Buffer; {Buffer Number from RW_Buffer_Open}
in integer IN_Line; {Optional Line number to return}

Custom Fields RW Functions

RW_Custom_Fields_Open

FUNCTION_SCRIPT( rw_TableHeaderCurrency -5261 “FieldOpen” “<Field ID>” 0 0 )
in integer dict_id; {Negative Dictionary ID: -5261}
in string IN_Function; {String constant: “FieldOpen”}
in string IN_FieldID; {Field ID of Custom Fields}
in integer IN_NotUsed1; {Integer constant: 0}
in integer IN_NotUsed2; {Integer constant: 0}

RW_Custom_Fields_Key_Set

FUNCTION_SCRIPT( rw_TableLineCurrency -5261 “FieldSet” “<Key Value>” 1 0.0 (c) Buffer 0 )
in integer dict_id; {Negative Dictionary ID: -5261}
in string IN_Function; {String constant: “FieldSet”}
in string IN_StringData; {String Data to set field to}
in integer IN_Key_Sequence; {Key Segment Sequence from Index}
in currency IN_NotUsed; {Currency constant: 0.0}
in currency IN_Buffer; {Buffer Number from RW_Custom_Fields_Open}
in integer IN_Clear_First; {Non Zero to clear Table Buffer first}

RW_Custom_Fields_Field_Get (can return as String or Currency)

FUNCTION_SCRIPT( rw_TableLineString -5261 “FieldGet” “<Field Name>” 0 0.0 (c) Buffer 0 )
FUNCTION_SCRIPT( rw_TableLineCurrency -5261 “FieldGet” “<Field Name>” 0 0.0 (c) Buffer 0 )
in integer dict_id; {Negative Dictionary ID: -5261}
in string IN_Function; {String constant: “FieldGet”}
in string IN_Field; {Field Name for Table Buffer field}
in integer IN_Characters; {Optional Max Characters per Line}
in currency IN_NotUsed; {Currency constant: 0.0}
in currency IN_Buffer; {Buffer Number from RW_Custom_Fields_Open}
in integer IN_Line; {Optional Line number to return}

There is one extra Custom Fields Report Writer function which can be used to convert a List or Multi-Select List Box field from a numeric value to a string value depending on the selection.

RW_Custom_Fields_List_Value

FUNCTION_SCRIPT( rw_TableLineString -5261 “FieldList” “” 1 (c) Data (c) Buffer 0 )
in integer dict_id; {Negative Dictionary ID: -5261}
in string IN_Function; {String constant: “FieldList”}
in string IN_NotUsed1; {String constant: “”}
in integer IN_Field_Sequence; {Field Sequence for the Custom Field}
in currency IN_Data; {Field Data to retrieve string for}
in currency IN_Buffer; {Buffer Number from RW_Custom_Fields_Open}
in integer IN_NotUsed2; {Integer constant: 0}

Implementing the RW Functions

Here are the steps to get the Custom Fields we have created for the Employee Maintenance window:

Open the Custom Fields Table Buffer

  1. On the Toolbox, select Calculated Fields and click New.
  2. Create the Calculated Field, Name: (c) Buffer, Type: Calculated, Result: Currency.
  3. Enter the Calculated Expression. Select Functions, User Defined, Core: System, Function: rw_TableHeaderCurrency and click Add.
  4. Select Constants, Type: Integer, Value: -5261 and click Add.
  5. Select Constants, Type: String, Value: FieldOpen and click Add.
  6. Select Constants, Type: String, Value: EMP_CUSTOM_FIELDS5 and click Add.
  7. Select Constants, Type: Integer, Value: 0, and click Add twice.
  8. Click on the Calculated prompt to show the full expression, if correct, click OK and OK to Save.

FUNCTION_SCRIPT(rw_TableHeaderCurrency -5261 "FieldOpen" "EMP_CUSTOM_FIELDS5" 0 0 )

Set the Key Segment to retrieve the table record

  1. On the Toolbox, select Calculated Fields and click New.
  2. Create the Calculated Field, Name: (c) Status, Type: Calculated, Result: Currency.
  3. Enter the Calculated Expression. Select Functions, User Defined, Core: System, Function: rw_TableLineCurrency and click Add.
  4. Select Constants, Type: Integer, Value: -5261 and click Add.
  5. Select Constants, Type: String, Value: FieldSet and click Add.
  6. Select Fields, Resource: Payroll Master, Field: Employee ID and click Add.
  7. Select Constants, Type: Integer, Value: 1 (for Key Segment) and click Add.
  8. Select Constants, Type: Currency, Value: 0.00000 (unused) and click Add.
  9. Select Fields, Type: Calculated Fields, Field: (c) Buffer and click Add.
  10. Select Constants, Type: Integer, Value: 1 (for clear first) and click Add. Note: Clear First should only be used when setting the first key segment if there are multiple segments.
  11. Click on the Calculated prompt to show the full expression, if correct, click OK and OK to Save.

FUNCTION_SCRIPT(rw_TableLineCurrency  -5261  "FieldSet"  UPR_MSTR.Employee ID  1  0.00000  (c) Buffer  1 )

Get the Fields to Display on the Report

  1. On the Toolbox, select Calculated Fields and click New.
  2. Create the Calculated Field, Name: (c) Pet Names1, Type: Calculated, Result: String.
  3. Enter the Calculated Expression. Select Functions, User Defined, Core: System, Function: rw_TableLineString and click Add.
  4. Select Constants, Type: Integer, Value: -5261 and click Add.
  5. Select Constants, Type: String, Value: FieldGet and click Add.
  6. Select Constants, Type: String, Value: Pet Names and click Add.
  7. Select Constants, Type: Integer, Value: 80 (return 80 characters, max limit for calculated fields ) and click Add.
  8. Select Constants, Type: Currency, Value: 0.00000 (unused) and click Add.
  9. Select Fields, Type: Calculated Fields, Field: (c) Buffer and click Add.
  10. Select Constants, Type: Integer, Value: 1 (return first set of 80 characters) and click Add.
  11. Click on the Calculated prompt to show the full expression, if correct, click OK and OK to Save.

FUNCTION_SCRIPT(rw_TableLineString  -5261  "FieldGet"  "Pet Names"  80  0.00000  (c) Buffer  1 )

  1. On the Toolbox, select Calculated Fields and click New.
  2. Create the Calculated Field, Name: (c) Pet Names2, Type: Calculated, Result: String.
  3. Enter the Calculated Expression. Select Functions, User Defined, Core: System, Function: rw_TableLineString and click Add.
  4. Select Constants, Type: Integer, Value: -5261 and click Add.
  5. Select Constants, Type: String, Value: FieldGet and click Add.
  6. Select Constants, Type: String, Value: Pet Names and click Add.
  7. Select Constants, Type: Integer, Value: 80 (return 80 characters, max limit for calculated fields ) and click Add.
  8. Select Constants, Type: Currency, Value: 0.00000 (unused) and click Add.
  9. Select Fields, Type: Calculated Fields, Field: (c) Buffer and click Add.
  10. Select Constants, Type: Integer, Value: 2 (return second set of 80 characters) and click Add.
  11. Click on the Calculated prompt to show the full expression, if correct, click OK and OK to Save.

FUNCTION_SCRIPT(rw_TableLineString  -5261  "FieldGet"  "Pet Names"  80  0.00000  (c) Buffer  2 )

  1. On the Toolbox, select Calculated Fields and click New.
  2. Create the Calculated Field, Name: (c) Pet Types, Type: Calculated, Result: Currency.
  3. Enter the Calculated Expression. Select Functions, User Defined, Core: System, Function: rw_TableLineCurrency and click Add.
  4. Select Constants, Type: Integer, Value: -5261 and click Add.
  5. Select Constants, Type: String, Value: FieldGet and click Add.
  6. Select Constants, Type: String, Value: Pet Types and click Add.
  7. Select Constants, Type: Integer, Value: 0 (only used for strings longer than 80 characters) and click Add.
  8. Select Constants, Type: Currency, Value: 0.00000 (unused) and click Add.
  9. Select Fields, Type: Calculated Fields, Field: (c) Buffer and click Add.
  10. Select Constants, Type: Integer, Value: 0 (only used for strings longer than 80 characters) and click Add.
  11. Click on the Calculated prompt to show the full expression, if correct, click OK and OK to Save.

FUNCTION_SCRIPT(rw_TableLineCurrency  -5261  "FieldGet"  "Pet Types"  0  0.00000  (c) Buffer  0 )

  1. On the Toolbox, select Calculated Fields and click New.
  2. Create the Calculated Field, Name: (c) Pet Types String, Type: Calculated, Result: String.
  3. Enter the Calculated Expression. Select Functions, User Defined, Core: System, Function: rw_TableLineCurrency and click Add.
  4. Select Constants, Type: Integer, Value: -5261 and click Add.
  5. Select Constants, Type: String, Value: FieldList and click Add.
  6. Select Constants, Type: String, Value:  (unused) and click Add.
  7. Select Constants, Type: Integer, Value: 3 (Custom Field Sequence) and click Add.
  8. Select Fields, Type: Calculated Fields, Field: (c) Pet Types and click Add.
  9. Select Fields, Type: Calculated Fields, Field: (c) Buffer and click Add.
  10. Select Constants, Type: Integer, Value: 0 (unused) and click Add.
  11. Click on the Calculated prompt to show the full expression, if correct, click OK and OK to Save.

FUNCTION_SCRIPT(rw_TableLineString  -5261  "FieldList"  ""  3  (c) Pet Types  (c) Buffer  0 )  

Adding other custom fields is just a matter of repeating the process using the rw_TableLineString or rw_TableLineCurrency report writer functions and the “FieldGet” command for each field. If the field is a List or Multi-Select List Box, use the rw_TableLineString report writer function with the “FieldList” command to convert the value to a string.

Adding the Calculated fields to the report

  1. Drag the F1 tab down to create space.
  2. Add a Prompt for Pet Names.
  3. Drag out the (c) Pet Names1 field and expand the width to 80 characters by 7 pixels = 560 pixels
  4. Drag out the (c) Pet Names2 field onto the next line and expand to 560 pixels to match.
  5. Add a Prompt for Pet Types.
  6. Drag out the (c) Pet Types String field and expand to 80 characters (560 pixel).
  7. Close the Report Layout Window and save. Click OK on the Report Definition to save the calculated fields.
  8. From the menus select File >> Microsoft Dynamics GP to return to the application.

To get the Modified Report to be displayed, the Modified Alternate ID used by the users needs to be changed. For testing, you can use GP Power Tools’ Dynamic Product Selection.

When properly deployed, please update the Security via the Alternate/Modified Forms and Reports ID and remove any settings added by Dynamic Product Selection.

One More Solution

Go to the Project Setup window for the EMP_CUSTOM_FIELDS5 project and click on Add >> Customization Maintenance, check the Detailed Employee List report and click OK to add it to the project.

Now click on the Open Form prompt to open the Employee Maintenance window, select an employee with custom fields entered and print the report to the screen. The custom fields will show at the bottom of the report.

This really now concludes the introduction series for Custom Fields. As you can see, the options are endless and limited only by your imagination and we used no code and no Modifier and only a little bit of Report Writer.

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 custom fields will be active immediately. The modified report will be visible after changing the Security Modified Alternate ID to point to the Modified report or when GPPT’s Dynamic Product Selection is active to redirect the report to the Modified version.

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 https://www.winthropdc.com/blog.

Leave a Reply