Welcome to the sixth article in the series of articles that explain in detail the steps to add a user defined custom field to a window using Modifier, Report Writer and GP Power Tools to add the business logic.
The series should be read in order starting with the introduction article:
In this article we will use Report Writer to modify the Detailed Item List report to add a calculated field which in turn calls the custom RW Function created in the previous article.
For the business example we are working through, we need to add an internet URL field to the Item Maintenance window which can be opened in an internet browser.
The process to do this can be broken down into 6 Steps:
- Modifying the Window; Use Modifier to add the URL field to the Item Maintenance window with a hyperlinked prompt.
- Saving the Data; The data will be stored in the DUOS (Dynamic User Object Store) SY90000 table and business logic will be added to handle saving the URL field.
- Using the Data; Make the hyperlink open the URL in a browser window.
- Creating the Report Writer Function; Use Power Tools to create a Report Writer Function to allow the data to be pulled into reports.
- Modifying the Report; Use Report Writer to add a calculated field to call the Report Writer Function.
- Publishing the Project; Change settings to make the project available to all users.
Follow the steps below to complete Step 5: Modifying the Report. We will be using Report Writer to add a calculated field and placing it on to the layout for the Detailed Item List report so the custom URL field can be included on the report. (Click on images to see full sized versions.)
- Open the Project Setup window and select Start >> Start Project Triggers to start the triggers.
- Open the modified Item Maintenance window. Pull up an Item and make sure there is URL entered into the custom field.
- Click the Print Button. Print the Detailed Item List report to the screen.
- Click Modify to go to the Report Writer for this report.
- In the Toolbox window on the Layout tab change the drop down list to Calculated Fields.
- Click New to add a Calculated Field. Set the Name to URL, Result Type to String and Expression Type to Calculated.
- Click on the Functions tab. Select User-Defined, Core: System and Function: rw_TableHeaderString (to match the custom script purpose used for the custom RW function created in the previous article). Click Add.
- Click on the Constants tab. Select Type: Integer, Constant: 5261 (GP Power Tools Product ID). Click Add.
- Stay on the Constants tab. Select Type: String, Constant: ITEM URL GET (Name of RW Function script to call). Click Add.
- Click on the Fields tab. Select Resources: Item Master, Field: Item Number (comes through as MBS_Number in script). Click Add.
- Click on the Constants tab. Select Type: Integer, Constant: 0 (comes through as MBS_Type in script). Click Add. Leave Constant: 0 (comes through as MBS_Control in script). Click Add.
- Click OK to create the URL Calculated Field.
- We now need to add the calculated field to report layout. We want to add the URL field to the same report section as where the Item Number is shown. So Select the (Additional Header 1) H1 tab on the left hand side of the layout window and drag it down one grid position.
- From the Layout tab in the Toolbox window, drag the URL field and place it onto the report layout in the space just created in the H1 section.
- Drag out the right edge of the URL field to the vertical line, so that it is at least 560 (80 characters * 7 pixels per character). You can see the field size shown at the bottom of the Toolbox window.
- We are now going to modify the report headings in both the Page Header (PH) and Report Header (RH) sections of the report layout. For this report, we are going to remove the dashed line between Item Number and Item Type as they are actually both in the same section. We can then move all the headings on the Item Type line up one line to make room. Use click and shift-click to select all the headings and then drag and drop them. Repeat for both PH and RH sections.
- Use the “A” Text Tool from the Layout tab on the Toolbox window and click in the space made in the PH and RH sections and add the heading “Web URL Link”.
- Best practice is to make the headings and the field the same width, so click on the URL field and shift click on the two headings. Then click on the Arrange tab on the Toolbox window and click the Size to Widest button.
- The changes are now completed, so click on the close box on the Report Layout window and select Save when asked. Click OK to close the Report Definition. Then select File >> Microsoft Dynamics GP to exit Report Writer.
Note: We have used the simple version of the RW function script from the previous article, which will only show the first 80 characters from the custom field, due the 80 character limitation in Report Writer for string calculated fields. If we want to get all of the characters, we can use the enhanced version of the script. Then, in the first calculated field we pass the final two constants as 1 (MBS_Type as Line 1) and 1 (MBS_Control as Control 1). Then create a second calculated field with the same values except using the final two constants as 2 (MBS_Type as Line 2) and 1 (MBS_Control as Control 1). Then in the layout make sure the first calculated field is exactly 80 characters * 7 pixels = 560 pixels and place the second calculated field right next to it. This will “join” the two fields back together (on a text report).
We can use GP Power Tools Dynamic Product Selection feature to make the modified report available to your user while we are still developing the customization.
Using the GP Power Tools Area Page, select Setup >> Dynamic Product Selection. Use the lookup on the left hand side to select the Modified/Alternate ID that your User ID is using. Change the Resource Type to Reports and expand nodes on the left hand pane to the Inventory series. Check the Detailed Item List in the left hand pane and make sure both the original and modified versions are selected on the right hand side.
Click on the Users Button and apply the filter to your User ID only, click OK. Then click Save.
Now if you open the Item Maintenance window, pull up an Item and click the Print Button. Select the Modified version when asked.
Print the Detailed Item List report to the screen, you will see the custom field shown.
Note: The Report Writer function works whether the triggers for the project are enabled or not. However, the triggers will need to be enabled for the Modified window to work.
In the next article we will publish the customization to make it available to all users in the system.
Hope you find this series useful.
This article was originally posted on http://www.winthropdc.com/blog.
One thought on “#GPPT Beginner’s Guide to Adding Custom Fields – Modifying the report”