#GPPT Adding a Custom Salesperson ID Field with Lookup, Zoom and On-The-Fly Adds

David Meego - Click for blog homepageA long time customer recently asked if they could get a second Salesperson ID associated with a customer on the Customer Maintenance (RM_Customer_Maintenance) window using GP Power Tools.

There is already really good guide for adding custom fields to windows and reports published (see article: #GPPT Beginner’s Guide to Adding Custom Fields), but that example does not add a field linked to an existing table and so does not have the standard GP features of a lookup button, a zoom button and on-the-fly adding of records.

IMPORTANT: This example uses functionality only available in GP Power Tools Build 28.9.

This example adds a lookup button to call a standard GP lookup and a zoom back to the standard GP window as well as validating the data selected and offering to on-the-fly add, if the record does not already exist.

Modifying the Window

The first step is to modify the Customer Maintenance window to add the custom field to the window.

  1. Open the Customer Maintenance window and then select Tools >> Customize >> Modify Current Window.
  2. Create the local fields to add to the window:
    ‘(L) Salesperson ID’ as datatype: String, length: 15, format: STR_LBAU.
    ‘(L) Lookup Button’ as datatype: Push Button, Type: Picture: Picture: Field_Lookup_PB_Up.
    ‘(L) Zoom Button’ as datatype: Push Button, Type: Native: Picture, No text or picture.
  3. Make the space for the field (using mouse to move fields one grid at a time). If needed increase the window size (use Window Visual Properties, add 19 to Size-Height or 16 to Size-Width).
  4. From Local Fields in the Toolbox, drag out the Field and use the align tools to align and size it to the correct positions.
  5. Use the Static Text tool from the Toolbox to add the desired prompt “Salesperson ID 2” use the align tools to align and size it to the correct positions. Change the Visual Properties of the prompt, Appearance: 3D Highlight, Border: True, Zoom: True.
  6. From the Tools Menu, select Link Prompt and click on the field and drag to the prompt. Then select Link Prompt again to disable linking mode.
  7. From Local Fields in the Toolbox, drag out the Lookup Button and use the align tools to align and size it to the correct positions. Change the Visual Properties of the button, Appearance: 2D Border and Object Properties, TabStop: False, Tooltip: “Lookup (Ctrl+L)”.
  8. From the Tools Menu, select Link Lookup and click on the field and drag to the lookup button. Then select Link Lookup again to disable linking mode.
  9. From Local Fields in the Toolbox, drag out the Field and use the align tools to align and size it to the correct positions. Change the Visual Properties of the button, Visible: False, Zoom: True and Object Properties, TabStop: False.
  10. From the Layout Menu, select Set Tab Sequence. Then use tab to move through the fields until it skips past the newly added field. Then double click on the new fields to insert it into the sequence at that location. Then select Set Tab Sequence to disable tab sequence mode.
  11. From File menu, select Microsoft Dynamics GP and select save when asked to return to the application.

Note: While developing the customization project, GP Power Tools Dynamic Product Selection can be used to open the modified window or the original window. Once ready to deploy, Dynamics Product Selection should be disabled and the Alternate Modified ID used to adjust security to use the modified window.

Adding the Business Logic

Then the next step uses the Project Setup window as our starting point to add the business logic to make new fields added to the modified window work. The business logic includes standard triggers to handle the CRUD (Create, Read, Update and Delete) events as well as additional code for the field change event and the Lookup and Zoom buttons.

The customization contains the following triggers and scripts:

Trigger CUST SLSPRSN 0

Trigger on window open to set the Field Object Property Hyperspace: True using code for the Lookup Button. This is because the Modifier does not allow you to set fields to Hyperspace = True.

Note: Hyperspace allows the lookup button to be pressed without moving the focus to the lookup button.  This means the lookup can be used without running the change script of the field where the cursor currently is. This is essential if you want to partially enter data to “seed” the lookup without being asked if you want to add the record.

Trigger CUST SLSPRSN 1

Trigger after the Save Record field change script that confirms the record has been saved by checking the value of the Save Record field and then saves the data from the modifier added Salesperson ID field to the DUOS (Dynamics User Object Store SY90000 table). If the field is blank, it removed the record rather than storing a blank record.

Trigger CUST SLSPRSN 2

Trigger after the Display Existing Record field change script to read the data from the DUOS and display it to the Salesperson ID field in the window.

Trigger CUST SLSPRSN 3

Trigger after a delete table event on the RM_Customer_MSTR (RM00101) table on the Customer Maintenance (RM_Customer_Maintenance) form to delete all DUOS data stored for the DUOS object ID for the current customer.

Trigger CUST SLSPRSN 4

Trigger after the modifier added Salesperson ID field change script to validate the entered Salesperson ID against the RM_Salesperson_MSTR table and if it does not exist ask if you want to create the record. If you select Add it will run the CUST SLSPRSN 6 Runtime Execute script to drill back (zoom) to the Salesperson Maintenance (RM_Salesperson) window.

Note: This trigger uses the option on the Actions Tab to keep the focus on the field, however the restart field functionality is currently not working correctly as per Build 28.9, so it manually has the force change command. This will be fixed in the next GP Power Tools build.

Trigger CUST SLSPRSN 5

Trigger after the Lookup Button field change script to open the lookup and return the data to the Salesperson ID field.

Note: This trigger calls the CUST SLSPRSN 5 Runtime Execute script so that we can change the dictionary context from the Modified dictionary to the Original Dictionary. This is required because the when a script is executed in the context of the Modified dictionary, only modified forms can be referenced, so we need to run a second script to change context.

This script captures a reference to the modifier added Salesperson ID field and stores this for use in the called runtime execute script. It also passes the value from the Sales Territory field through as this is used by the call to the lookup.

Note: Capturing a field reference is only supported for build 28.9 or later.

Trigger CUST SLSPRSN 6

Trigger after the ZoomButton field change script to open the Salesperson Maintenance (RM_Salesperson) window and pull up the current Salesperson ID.

Note: This trigger calls the CUST SLSPRSN 5 Runtime Execute script so that we can change the dictionary context from the Modified dictionary to the Original Dictionary. This is required because the when a script is executed in the context of the Modified dictionary, only modified forms can be referenced, so we need to run a second script to change context.

This script passes the value from the Salesperson ID field through as this is used by the runtime execute script to drill back to the Salesperson Maintenance window.

Runtime Execute Script CUST SLSPRSN

This script is a custom RW Function linked to the rw_TableHeaderString() function which returns the Salesperson ID from the DUOS table and also can return the First Name and Last Name from the Salesperson Master (RM_Salesperson_MSTR) table if desired.

Runtime Execute Script CUST SLSPRSN 5

This Runtime Execute script retrieves the memory variables stored away by the calling trigger code  and the deletes them to clean up. It then opens the lookup form and passes in the parameters.

The Salesperson ID field must be passed as a reference (pointer) so that the open form return to command knows which actual field to return the selected Salesperson ID value to. As the Salesperson ID field itself is in a different dictionary (Modified dictionary) it cannot be referenced in this script. Passing the field as a reference allows us to get around this limitation.

Note: Capturing a field reference is only supported for build 28.9 or later.

Runtime Execute Script CUST SLSPRSN 6

This Runtime Execute script retrieves the memory variable stored away by the calling trigger code  and the deletes it to clean up. It then opens the Salesperson Maintenance form and sets the current record by setting and running the ‘(L) Temp Control Number’ field.

The Salesperson ID field can just be passed as string value as we just need to know what the value is but don’t need to return anything to it for this script.

Report Writer

To complete the sample code, the RM Customer Report was modified to add the Salesperson ID 2 prompt and a calculated field to call the CUST SLSPRSN custom RW Function:

  • FUNCTION_SCRIPT(rw_TableHeaderString 5261 “CUST SLSPRSN” RM_Customer_MSTR.Customer Number 0 1 )

Note: While developing, GP Power Tools Dynamic Product Selection can be used to open the modified report or the original report. Once ready to deploy, Dynamics Product Selection should be disabled and the Alternate Modified ID used to adjust security to use the modified report.

Downloading and Installing

Download the example code, import using the Project Setup window (now possible for first time imports with Build 28.8 onwards), or use Configuration Export/Import window (for older builds):

WARNING: Installing the modified forms and reports will overwrite any existing changes to those forms or reports. Make sure you also change the Alternate Modified ID to use the modified windows and reports.

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

For more information see:

David

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

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.