Adding Internet Information (Email addresses) to Lists in SmartList Builder, etc

There are certain tables that you really should not link directly to in the Builders.  Internet Addresses (MS Dynamics > Company > Internet Addresses   In SQL:  SY01200 )  is one such table. 

Now, until recently this wasn’t a big deal since not many users took advantage of this table. BUT now in GP2010 this table is storing the Email addresses for the new Email functionality for Purchase orders, Invoices, etc.  Since this data is NOT shown on any stock SmartList or Navigation List, there is going to be a HUGE demand to view this data in the Builders.  

Below I will give you some SQL views to use to add this data to your Lists, but first let me explain why you shouldn’t link directly to this table.  It is a combination of a few factors:

• First this table stores ALL of the Internet information for Customers, Vendors, Items, etc. 
• When you link in the Builders there is no way to just select part of the data, just Customers for example.   (You could in SQL)  (Note to the MS programmers – being able to add a constant to the link section would help with these issues! )
• Not all Master records (Customer for example) will have records in the Internet Addresses table – so you will normally want to do a Left Outer link to this table (so that you can get all Customers and Internet addresses, if there are any.)
• Adding a restriction to Builder to filter on the Master Type for the Internet Addresses table will not work for two reasons:
  • Adding a restriction directly to table with a Left Outer link will remove all records that aren’t in this table – EG any records without Internet Addresses  (basically turning it into an equal link)
  • Also, the restriction will apply AFTER the join and may remove legitimate Master records. 

I think an example will highlight the problem:

You are building a customer list

You have a customer ABC001  and a vendor ABC001  – only the Vendor has internet addresses

when you link the table you will get the vendors internet data in the customer record

adding a filter to the type will then completely remove this record from the list

I agree that this may be rare – but I like to build lists that will ALWAYS work correctly.

OK – so here are the SQL views – (NOTE – I am having a font problem on this blog and can’t get the ’&’ to work correctly – after you cut & paste these you will need to retype the ‘and’ in the Where clause in SQL – sorry!):

First you will need to replace the constant in the Where clause with the correct code as well as change the name of the view on the first line:

• CUS - Customer
• VEN – Vendor
• ITM – Item    (only one key field – Address ID is not used, obviously)
• EMP – Employee
• SLP – Salesperson
• CMP – Company

Basic SQL view:

CREATE VIEW CustomerInternet AS
SELECT     SY01200.*
FROM         SY01200
WHERE     (Master_Type = ‘CUS’)

View in GP10 (or earlier) with nice field names (remember that these are changeable in the company set-up so you will want to modify to match yours, if different):

CREATE VIEW CustomerInternet AS
SELECT     Master_ID AS [Master ID], ADRSCODE AS [Address ID], INET1 AS Email, INET2 AS [Home Page], INET3 AS [FTP Site], INET4 AS Image, INET5 AS Login, INET6 AS Password, INET7 AS [User Defined 1], INET8 AS [User Defined 2], INETINFO AS [Additional Information]
FROM         SY01200
WHERE     (Master_Type = ‘CUS’)

View for GP2010 with the new fields and nice field names (remember that these are changeable in the company set-up so you will want to modify to match yours, if different):

CREATE VIEW CustomerInternet AS
SELECT     Master_ID AS [Master ID], ADRSCODE AS [Address ID], EmailToAddress AS [Email To Address], EmailCcAddress AS [Email CC Address], EmailBccAddress AS [Email BCC Address], INET1 AS Email, INET2 AS [Home Page], INET3 AS [FTP Site], INET4 AS Image, INET5 AS Login, INET6 AS Password, INET7 AS [User Defined 1], INET8 AS [User Defined 2], Messenger_Address AS [Messenger Address], INETINFO AS [Additional Information]
FROM         SY01200
WHERE     (Master_Type = ‘CUS’)

Don’t forget to run the grant script when you are done:

GRANT SELECT ON CustomerInternet TO DYNGRP

You last step is to go to Security and give access to your new view (MS Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security)

One last hint – this data is NOT stored per customer or vendor, but stored by ADDRESS ID on the customer or vendor.  So you will need to use both the Master ID field and the Address ID field as your link fields.

Happy Building!
Devon