Including E-mail addresses in Smartlists

While email information was added for customers, vendors and employees quite a while ago (GP 2010), Microsoft has never gotten around to adding those “internet information” fields to the smartlists that ship with the product. Fortunately the Smartlist Designer tool gives you some capabilities to create some relationships and pull in these email fields.

We recently looked into an issue where the email addresses displayed fine but when the user tried to “search” on the email address, nothing appeared in the smartlist. It didn’t matter what the actual search was (contains, greater than, not equal to).

This seemed like the internal workings of Smartlist, so it was perplexing that the data displayed, we could sort the email addresses, but any search yielded no results. After verifying other fields on the Internet Information window could be added to the Smartlist and did allow the “search” to work properly, we took a closer look at the table. Turns out the email addresses (to, CC and BCC) are “text” fields, not the normal “character” field type.

Putting our Transact SQL skills to work, we edited the SQL view to use the “CAST” command to turn those addresses into “CHAR” fields. All the expected “search” functionality almost mystically started working again.

Another problem vanquished!