Customer E-mail Addresses – List

Here’s one for the SQL script vault. As of GP 2018 R2, GP STILL does not provide the customer email address as a field in Smartlist. Here are a few quick options to get what you’re looking for:

Option 1.) SmartList Builder. Create a new report, link the existing view ‘Customer Addresses’ to SY01200 ‘Internet Addresses’

Option 2.) Run the following script in SQL (can also deploy as a view and attach a SmartList builder on top)

select a.custnmbr       'Customer ID', 
       a.custname       'Customer Name', 
       b.adrscode       'Address ID', 
       b.cntcprsn       'Contact Person', 
       c.emailtoaddress 'To E-Mail Address' 
from   rm00101 a 
       join rm00102 b 
         on a.CUSTNMBR = b.CUSTNMBR 
       join sy01200 c 
         on b.ADRSCODE = c.ADRSCODE 
            and b.CUSTNMBR = c.Master_ID 
            and c.Master_Type = 'CUS' 
order  by a.CUSTNMBR

Frequent Tags: Reporting , Integrations, SQL, Automation, SmartConnect, Management Reporter, SSRS, Lease Accounting