Here is a tip to help you find invalid email addresses associated to customer contact records. We have run across data had been imported from other systems with incomplete or wrong email addresses, that found their way into the EmailAddr field in the contact table.  These invalid email addresses will present a problem when it comes time to use the email address to send them an invoice from Document Transmittal for example.  The EmailAddr field is a varchar(256) length field that will allow blank values.  To find the contact records with invalid email addresses, here is an example of a query you could run:

SELECT EMailAddr,b.custid,b.custname,b.companyid

FROM tciContact a

INNER JOIN tarCustomer b

ON a.CntctKey=b.PrimaryCntctKey

WHERE a.EMailAddr NOT LIKE '%_@__%.__%' and a.EMailAddr <> ''

ORDER BY Companyid,CustID

The above query could probably be written many ways. The key piece in this query is in the WHERE clause. We are using pattern matching scheme with the LIKE logical operator. We want to exclude rows that have blank email addresses because those are not necessarily invalid.  We could have used a.EMailAddr NOT LIKE '%@%'  in the WHERE clause because email addresses should have the at sign symbol.  But what if the email address had the at sign but the rest of the address was incomplete.  I opted to compare against a mask that looks like what an email address normally looks like.

Once you find the list of invalid entries, I recommend changing them through the front email of Sage 500, Maintain Customers task. Use the CustID field from the query results to look up the customer record.

Before you run the query, make a copy of the database first and run it against that copy. Depending on the size of the customer and contact records, the query could take a while to complete.   Lastly, the above WHERE clause logic can be used against other tables such as stgCustomer for example that have an EmailAddr field. 

I hope you find this query useful and that it helps you clean up your email addresses.