Hey Everyone!

I wanted to get steps out there on how to add the "Additional Information" field from the Internet Information window to the SOP Blank Invoice Form report in Microsoft Dynamics GP. The steps can be applied to other Sales documents like the Picking ticket or Sales Order.

The Calculated field will end up looking like the following: 

FUNCTION_SCRIPT(RW_GetInternetText"CUS"RM_Customer_MSTR.Customer NumberRM_Customer_MSTR.Address Code8019 )

Open Report Writer

  1. Open Report Writer - Microsoft Dynamics GP > Tools > Customize > Report Writer

  2. If asked for a Product, select Microsoft Dynamics GP

  3. Click Reports

  4. On the Original Reports side, highlight SOP Blank Invoice Form and Insert to the right.

  5. Then on the Modified Reports side, highlight the same report and choose Open.

Link the RM Customer MSTR table to the Sales Transaction Work table

  1. In the Report Definition window choose the Tables Button.
  2. In the Report Table Relationships window, click Sales Transaction Work, and then click New.
  3. In the Related Tables window, click RM Customer MSTR, and then click OK. If the RM Customer MSTR table is not there, you will need to create the relationship.
    1. Click Tables >> Tables from the top
    2. Select the SOP_HDR_WORK table
    3. Click New
    4. In the Table Definition window, click Relationships
    5. Click on the ellipse next to Secondary Table
    6. In the Relationship Table Lookup window select RM Customer MSTR and click OK
    7. For the Secondary Table Key: select RM_Customer_MSTR_Key1
    8. From the Primary table match Customer Number with Secondary Table Customer Number
    9. Click OK
    10. Close the table relationship window
    11. Click OK in the Table Definition window
    12. Close tables window
    13. Start back with step 1 of Linking the RM Customer MSTR table to the Sales Transaction Work table
  4. Click Close
  5. In the Report Definition window, click layout.

Create the calculated field for the first line of the Additional Information field

  1. In the Toolbox window, click Calculated Fields, and then click New.

  2. In the Name field, type “Additional Information Field Line 1.”

  3. In the Result Type list, click String, and then click Calculated in the Expression Type area.

  4. Click the Functions tab, and then click User-Defined.

  5. In the Core list, click System, click RW_GetInternetText in the Function list, and then click Add.

  6. Click the Constants tab, click String in the Type list, type CUS in the Constant field, and then click Add. 

    Note: You can change "CUS" to one of the following to apply this function to other fields in GP:

    CMP = COMPANY
    VEN = VENDOR
    CUS = CUSTOMERPLOYEE
    ITM = ITEM
    SLP = SALESPERSON

  7. Click the Fields tab, click RM Customer MSTR under the Resources list, click Customer Number in the Fields list, and then click Add.

  8. From the Fields tab again, click RM Customer MSTR under the Resources list, click Address Code in the Fields list, and then click Add.

  9. Select the Constants tab.

  10. Click Integer in the Type list, type 80 in the Constant field, and then click Add.

  11. Type 1 in the Constant field, and then click Add.

  12. Type 9 in the Constant field, and then click Add.

    Note: The values 80,1 and 9 refer to the following:

    The 80 refers to the character length of the string.
    The 1 refers to the first line of text
    The 9 refers to the field on the window

    1 = Email
    2 = Home Page
    3 = FTP Site
    4 = Image
    5 = Login
    6 = Password
    7 = User Defined 1
    8 = User Defined 2
    9 = Addl Info
    10 = TO address
    11 = CC address
    12 = BCC address

    The Calculated field should display as follows:

    FUNCTION_SCRIPT(RW_GetInternetText"CUS"RM_Customer_MSTR.Customer NumberRM_Customer_MSTR.Address Code8019 )

  13. Click OK
  14. Repeat steps 1 through step 13 in this section if you want to print additional lines from the Additional Information field. In step 11, replace 1 with the number of the additional line that you want to print. For example, if you want to print the second line of the Additional Information field, type 2. 

Add fields to the layout of the report

  1. In the Toolbox window, click Calculated Fields, and then click Additional Information Field Line 1.

  2. Drag the field to the H2 section

  3. Double-click Additional Information Field Line 1

  4. In the Display Type list, click Data.

  5. Click OK.

Save the modified report and set security to the modified report. (Administration >> Setup >> System >> Alternate/Modified Forms and Reports)

If you are using the function to pull single fields from the internet informant window, for example the email field. You do not need to incorporate the character length of the string or the line of text. The function would look like the following:

FUNCTION_SCRIPT(RW_GetInternetInfo"CUS"RM_Customer_MSTR.Customer NumberRM_Customer_MSTR.Address Code1 )

 

Note: Steps were tested on GP 2015 R2 and GP 2016 R2.

Hope you find this information helpful!

Kelsey Koep | Support Engineer | Microsoft Dynamics GP