Accessing Internet Information from Report Writer

This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Last week, I had a call from a partner asking how could they access data stored on the Internet Information window.

This is the window opened with the little italic i symbol next to the Address ID field for Company, Customer, Employee and Vendor Addresses as well as next to Employee ID and Salesperson ID fields.

The data for this window is stored in the coINetAddrs (SY01200) table and has eight (8) user defined fields and a text field. For Microsoft Dynamics GP 2010, the Email To, Email Cc, Email Bcc and Messenger Address fields were added to the window.  The prompts for the user defined fields are defined in the Company Setup window (Tools >> Setup >> Company >> Company >> Internet User Defined) and are stored in the coINetPrompts (SY04800) table.

Microsoft Dynamics GP 2010: Internet Information window

The usual method to add data onto a report is to define a relationship to the table in question and then add it to the report.  However, as the primary key for the coINetAddrs (SY01200) table starts with a Master Type field which is a 3 character constant value which represents the type of data being stored. As it is not possible to add a constant value when defining a table relationship, it is not possible to attach this table to a report.

The table below shows the values for the Master Type field:

Entity	Constant	Code
Company	CO_INETADDRS_COMPANY	CMP
Customer	CO_INETADDRS_CUSTOMER	CUS
Employee	CO_INETADDRS_EMPLOYEE	EMP
Item	CO_INETADDRS_ITEM	ITM
Salesperson	CO_INETADDRS_SALESPERSON	SLP
Vendor	CO_INETADDRS_VENDOR	VEN

So, to allow this data to be accessed, I added three Report Writer user defined functions (System series) to version 7.0 and later of Microsoft Dynamics GP.  Below are SDK entries from the RW_Func.doc document:

---

RW_GetInternetInfo

Module: 
System Manager

Series:
System

Script type: 
Global function

Form (If a form global): 

Category:  
Data Retrieval

Description:  
Returns Additional Internet Information fields 1 to 8 for the specified Master Type, Master ID and optional Address ID.

IN_MasterType should use the following values:-
CMP = CO_INETADDRS_COMPANY
VEN = CO_INETADDRS_VENDOR
CUS = CO_INETADDRS_CUSTOMER
EMP = CO_INETADDRS_EMPLOYEE
ITM = CO_INETADDRS_ITEM
SLP = CO_INETADDRS_SALESPERSON

IN_field must have a value between 1 and 8.

Note: Microsoft Dynamics GP 2013 allows an IN_field value of 9 to retrieve the Messenger Address.

Prerequisites:  
none

Parameters:  
in string IN_MasterType.
in string IN_MasterID.
in string IN_AddressID.
in integer IN_field.

Return Value:
function returns string OUT_string.

---

RW_GetInternetPrompt

Module: 
System Manager

Series:
System

Script type: 
Global function

Form (If a form global): 

Category:  
Data Retrieval

Description:  
Returns Additional Internet Information prompts 1 to 8 for the current company.

IN_field must have a value between 1 and 8.

Prerequisites:  
none

Parameters:  
in integer IN_field.

Return Value:
function returns string OUT_string.

---

RW_GetInternetText

Module: 
System Manager

Series:
System

Script type: 
Global function

Form (If a form global): 

Category:  
Data Retrieval

Description:  
Returns Additional Internet Information Text field as strings of given length for the specified Master Type, Master ID and optional AddressID.

IN_MasterType should use the following values:-
CMP = CO_INETADDRS_COMPANY
VEN = CO_INETADDRS_VENDOR
CUS = CO_INETADDRS_CUSTOMER
EMP = CO_INETADDRS_EMPLOYEE
ITM = CO_INETADDRS_ITEM
SLP = CO_INETADDRS_SALESPERSON

Note: Microsoft Dynamics GP 2013 adds an optional IN_field parameter, which can use values of 10, 11 or 12 to retrieve the to, cc and bcc email addresses. While the parameter is optional when called from Dexterity it is required when called from the Report Writer to avoid an Error in Equation message. Set it to 0 to retrieve the Text field contents.

Prerequisites:  
none

Parameters:  
in string IN_MasterType. { MasterType Code }
in string IN_MasterID.  { Master ID }
in string IN_AddressID.  { Optional Address ID }
in integer IN_characters. { Number of Characters per Line }
in integer IN_line.  { Line Number to Return }
optional in integer IN_field; {Text field desired, 10(to), 11(cc) 12(bcc)}   — GP 2013 only

Return Value:
function returns string OUT_string.

---

To use these Report Writer functions you need to create calculated fields using the User Defined functions in the System Series.  Below are examples of all 3 functions:

An example of using the RW_GetInternetPrompt() function return the text for the first prompt is:

Name:	(c) Internet Information Prompt 1
Result Type:	String
Expression Type:	Calculated
Calculated:	FUNCTION_SCRIPT(RW_GetInternetPrompt  1)

 

An example of using the RW_GetInternetInfo() function return the value for the first field for the primary vendor address is:

Name:	(c) Internet Information Value 1
Result Type:	String
Expression Type:	Calculated
Calculated:	FUNCTION_SCRIPT(RW_GetInternetInfo “VEN”  PM_Vendor_MSTR.Vendor ID  PM_Vendor_MSTR.Vendor Address Code – Primary  1)

 

Finally, an example of using the RW_GetInternetText() function to return 3 lines of 80 characters of the Text field for the same primary vendor address into 3 string calculated fields is:

Name:	(c) Internet Information Text 1
Result Type:	String
Expression Type:	Calculated
Calculated:	FUNCTION_SCRIPT(RW_GetInternetText  “VEN” PM_Vendor_MSTR.Vendor ID PM_Vendor_MSTR.Vendor Address Code – Primary  80  1)

 

Name:	(c) Internet Information Text 2
Result Type:	String
Expression Type:	Calculated
Calculated:	FUNCTION_SCRIPT(RW_GetInternetText “VEN” PM_Vendor_MSTR.Vendor ID PM_Vendor_MSTR.Vendor Address Code – Primary 80 2)

 

Name:	(c) Internet Information Text 3
Result Type:	String
Expression Type:	Calculated
Calculated:	FUNCTION_SCRIPT(RW_GetInternetText “VEN” PM_Vendor_MSTR.Vendor ID PM_Vendor_MSTR.Vendor Address Code – Primary 80 3)

 

With the use of these 3 functions you can pull the data from the Internet Information window onto your reports simply and easily.

Note: At this stage the RW_GetInternetInfo() Report Writer does not support returning of the three Email Address and the Messenger Address fields. This issue has been logged as Problem Report 62880, if you want this functionality, please log a support case and reference this bug number.

For more information see the post: Using the built-in Report Writer Functions

David

25-Oct-2012: Microsoft Dynamics GP 2013 fixes Problem Report 62880. The RW_GetInternetInfo() function accepts field number 9 for the Messenger Address and RW_GetInternetText() adds a field number parameter which accepts 10, 11 and 12 for the three email addresses. See Notes above.

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.