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.
Posting from Mark Polino at DynamicAccounting.net
msdynamicsgp.blogspot.com/…/accessing-internet-information-from.html
LikeLike
Posting from Rubalpal at Dynamic GP Help
dynamicsgphelp.com/…/error-in-calculated-field-using-rw_getinternettext-error-after-upgrade-to-gp-2013
LikeLike
Hi David,
Thank you for the valuable information you are sharing. I have used your example above and was able to print customer e-mail address. I would like to print the sales person email address as well but I can not figure our the master table to use. I have linked the RM Sales Person table to my report but there is no address code field available. if possible to give me an example will be much appreciated.
Thanks
Maria
LikeLike
Hi David
This may be a really silly question – but I can't see the solution – how do you get this part of the parameter:
PM_Vendor_MSTR.Vendor Address Code – Primary
I can add the PM_Vendor_MSTR.Vendor Address Code part of it, but not the rest
Cheers
Heather
LikeLike
Hi Heather
When working with Vendor Internet Information:-
The first parameter is the three letter code as a string constant: "VEN"
The second parameter is the Vendor ID.
The third parameter is the Address ID for that Vendor. There are 5 address IDs available on the Vendor Master table for Primary, Remit To and Ship From, Purchase and 1099, or you can use an ID from another table, such as the transaction tables.
Hope this helps.
David
LikeLike
Hi David
This is extremely useful information. I had been using this for the additional information for inventory item.
The information is pulled in the SOP forms.
This works fine for GP10 and GP2010. I am having issues now with the SOP forms showing an equation error for the calculated field in GP2013.
Noted a blog on which Rubal indicated something close to this, except it is for CMP and not ITM for example before upgrade FUNCTION_SCRIPT(RW_GetInternetText “CMP” sInetID sAddressCode 50 1 ) to after upgrade FUNCTION_SCRIPT(RW_GetInternetText “CMP” sInetID sAddressCode 50 1 0 )
Is there a change in the table structure?
Any valuable information for us?
Appreciate your help.
Thank you.
LikeLike
Hi Lay Chin
There is an extra parameter which can tell the function return the email address information.
See the highlighted bits in this article in red for the changes that were made.
David
LikeLike
Hi David
Thank you, I finally found the extra parameter at the end.
Appreciate and thank you very much for your help.
Best Regards
Lay Chin
LikeLike
Hi David,
I am trying to get the function RW_GetInternetInfo to work so that I can pull internet information per company but it does not work. I cannot figure out what I am doing wrong.
“‘RW_GetInternetInfo'( \”CMP\” globals.’Intercompany ID’\”PRIMARY\” 1 ) ”
When I preview the report, I get an error “Error in equation ‘CompanyEmail1’.
Any idea what would be causing this?
Much thanks,
Donald Wisch
LikeLike
Hi Donald
Lucky I still monitor comments on my old blog. 😉
I was able to replicate the error you were having. This is just the report writer being weird.
The solution is to create a string calculated field with the expression Global.’Intercompany ID’ and use that in the RW function.
Hope that helps.
David
LikeLike
Grateful that you still monitor this blog David! Thank you for your advice and all that you do for the GP community… have a great day 🙂
LikeLike
That worked Dave… thank you so very much!!!
LikeLike
Hello,
Hopefully you still monitor this blog!
I am trying to pull the email address that is entered into the “To:” field on the Internet Information window and I cannot seem to get the calculated fields right when trying to set it up.
My goal is to have the customer’s email address display on the invoice.
Do I need to have all 3 calculated fields entered and on the report?
My current expression that comes up with an calculation error when printing the report is:
FUNCTION_SCRIPT( RW_GetInternetInfo “CUS” RM_Customer_MSTR.Customer Number RM_Customer_MSTR.Primary Shipto Address Code 1)
The 1 is an Integer and the Result Type is set to String.
I am new to report writer so it’s probably a simple error.
LikeLike
Hi Adam
Yes, I still keep an eye on comments on my old blog.
To get the email address you need to use a different function, here is an example for your situation and what the parameters are:
RW_GetInternetText(“CUS” RM_Customer_MSTR.Customer Number RM_Customer_MSTR.Primary Shipto Address Code 80 1 10)
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 }
in integer IN_field; {Text field desired, 10(to), 11(cc) 12(bcc)}
The only issue is the fix made in GP 2013 to a field number as an optional parameter did not work as optional parameters don’t work for RW functions. This was fixed by making it a fixed (non-optional) parameter for GP 2013 R2.
Hopefully you are on GP 2013 R2 or later.
David
LikeLike
This is great. It works for me but when I print multiple Sales Invoices the text for the 1st invoice is that of the second invoice for this calculated field. Did I do it wrong?
FUNCTION_Script (RW_GetInternetText “CUS” SOP_HDR_HIST.CustomerNumber SOP_HDR_HIST.PrimaryBilltoAddress Code 80 1 10)
LikeLike
Hi Scott
If the calculated field has been dragged into a footer section of the layout, please make sure it is set to a type of DATA and not the default LAST type. Double click on the field to change this setting.
David
LikeLike