3,225
I am trying to build either a smart list or excel report that just has the customer number and email address for (invoices, orders, etc) I need to take those emails and upload them to the statement portion so I can email statements. Can someone tell me what table the email field is in? I can not seem to find it. I do not use SQL just the builders.
Thank you
*This post is locked for comments
Hi Katie,
I created a SQL view for that. We use Liaison Messenger to email everything; but, Collections uses the same email field as the email Statement function in GP.
Here is the SQL code
CREATE VIEW [dbo].[VALL_LiaisonEmailAdd]
As
/********************************* [dbo].[VALL_LiaisonEmailAdd] *************************************************************
*******************************************************************************************************************************
************ Created by: Joseph Guyton ****************************************************************************
************ Created Date: 05/06/2014 ***************************************************************************
************ SQL View for SmartList with Customers and email addresses *********************************
************ Debug: Select * from RM00101 ***********************************************************
************ Select * from SY01200 ***********************************************************
******************************************************************************************************************************/
SELECT a.CUSTNMBR AS [Customer Number],
a.CUSTNAME as [Customer Name],
a.USERDEF2 as [Liaison Pref],
b.INET1 as [Liaison Email],
b.EmailToAddress as [Collections Email] <----- This is the field that the statements are sent to.
FROM RM00101 a INNER JOIN SY01200 b
ON a.CUSTNMBR = b.Master_ID
AND b.Master_Type = 'CUS'
Be sure and run the SQL statement "Grant Select on viewname to DYNGRP
You'll be able to add the view to SmartList Builder as a custom StartList.
Hi Katie,
Is this still a problem? I read through the responses, and it looks like you should probably have your answer.
The 'eMail Statements' functionality has been around awhile, so the e-mail is in a different spot. Jamie went over that. The eMail address for Invoices, etc. is in the INET table (SY01200). Again, I think Jamie covered that one too.
If all is well, please mark your question as answered so others reading the Forum can know where to find the answer. If not, let us know what's missing so we can take another run at it.
Kind regards,
Leslie
Hi,
if you are using GP2010 or GP2013; and have added Internet Addresses (SY01200) table to your Smartlist using Smartlist Builder; you should see the email columns as indicated in previous post. GP 10 or earlier would not have the columns.
If you go into Smartlist Builder, choose series as Company, give a simple name; add just the Internet Addresses Table; when you click OK, you should see a listing of all columns that are held in table; clicking on the column for defaults will then default with these columns. Click OK to save the List you created. Launch Smartlist and under Company, you should see the list with the indicated columns.
thanks
Jamie
Thank you for the help. Yes I pulled in the 1200 table, but the only fields I have to chose from are:
Master Type
Master ID
Address code
INET 1 2 3 4 5 6 7 8
INETinfo
Hi,
It looks like in Smartlist Builder, you were able to add the SY01200 (Internet Addresses) Table to pull in information for email addresses. The fields that will hold the email addresses in this table are the Email To Address, Email CC Address and Email BCC address. You can limit the returns into the Smartlist by setting the Master Type = 'CUS' and this should show all the customers you have setup with email addresses.
If you are using GP2010, you will want to fill out the email address that would be found under Options on the customer card. In here, you will see an area near bottom where you populate who you want to receive the statement. This email information is held in the RM00106 (RM Statements Email Addresses) and will only have a record if you have actually entered email address within the field. So if you are attempting to populate records, you will need to actually insert records into this table to correspond to correct customer id associated.
In GP2013, we have introduced capabilities to email statements using the Word Template functionality that allows customers to use the template functionality.
Thanks,
Jamie
Katie,
I know you said you're using just the builders, but if you can have someone create the SQL view using my code here: victoriayudin.com/.../sql-view-for-sop-email-setup-in-gp-2010, then all you have to do is point one of the builders to that view and you would be all set.
We are still on GP 2010.
I have a smartlist that pulls in the INet1 field, but this seems to be the Internet information field and not the Email field. I need the field that says To.... because sometimes we have different email address in each field.
Thank you for any suggestions.
Hi,
Are you looking for where the statements email address would be held if you are not using the new feature introduced in GP2013 to send statements using Word Templates? If you are looking for the email statement addresses found under options in the customer card, this would be the RM00106 (RM Statements E-Mail addresses).
Another question: are you attempting to use the new email functionality for statements in GP2013? If so, do you have a different Address ID for the Statement To field on Customer Maintenance than you do for example with Primary, Ship To or Bill To?
Thanks
Jamie
What version of GP are you running? There are tools like DocuFire which allow you to print the statements directly to an email, including copies of all invoices and, if you scan the checks, checks received.
Katie,
SY01200 table hold email addresses. Mater_Type field differentiate it is Customer or Employee Data.
Thanks
Sandip
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (