Impossible to filter on Emails list view with Email from

Hello Enes Saridogan, 

Do you get any errors when trying to search, what are the errors? 

In stock 12.0.0 I can search for the from address correctly, I need to select the corresponding Sugar record, but it works: 

Can you provide more detail on your exact test scenario? 
Thanks in advance. 

Hello Enes,

if I'm understanding your question correctly, and if this is still relevant, you want to search by the actual e-mail address (i.e. someone@somewhere.com), and not a specific record. As far as I know, that's not possible in vanilla SugarCRM.

There are two things you can do:

1) Sync e-mail address to separate field, which is made searchable

Create a new text field on Emails called e.g. "from_address_c" and an after-save logic hook that fetches the e-mail address via the 'to' link, and fills this field out. Then, make this field filterable. Be aware that the Emails module is not accessible via Studio, so you'll have to do all this manually.

To create a custom field manually, take a look at e.g. support.sugarcrm.com/.../

As for making a field filterable, I haven't done this for a while, but I think you need to create a Sidecar extension and add the field to $viewdefs['Emails']['base']['filter']['default']['fields']. Take a look at modules/Emails/clients/base/filters/default/default.php for inspiration. Alternatively, if it turns out this isn't the proper way to do it, add some field in some module to filterable fields in Studio, and then take a look at the extensions it creates - that should tell you what you need to do.

Keep in mind that this approach is "sync intensive", which means that it's necessary to keep the value on the Email synchronized with the value accessed via the 'to' link. Since EmailAddresses is a separate module, I'm not sure what scenarios can happen, or if an after-save hook in Emails is enough.

2) Use QuickQuery

This, and much more, is supported out of the box. See https://docs.sugarquickqueryis.fun/useful-examples#_6xcm

Disclaimer: I am the author of QuickQuery.

Kind regards,

Gabriel

Maybe it's just our system because we have over 20M email addresses but that filter takes quite a while to come up with matches, when I type in an address it takes minutes to come up with a list of potential matches in Leads/Contacts.

It would nice if the Emails module searched directly in the emails_text.from_addr by address instead of attempting to link other beans through email_addr_bean_rel.

FWIW I also have a pet peeve (unless something changed in v12 or v13) with the global search when searching by email address because it does partial matches. Search for any gmail.com email like mikey_mouse@gmail.com and you will get EVERY Gmail address in the DB and the exact match is not even the first in the list, you have to dig to find it rendering the search useless....  I have yet to find a single user in our company who uses the Global Search.

FrancescaS

Hey Francesca,

I'm not sure there would be much of a difference in principle - both require joins, don't they?

As for Global Search, this is something you can change, but it does take some effort. Take a look at src/Elasticsearch/Provider/GlobalSearch/Handler/Implement/EmailAddressHandler.php, which is where e-mail addresses are dealt with. The reason for the behavior you're describing is that e-mail uses the same analyzers as regular text fields, where this behavior is desirable. I would expect that changing the analyzer would do the trick, e.g. using something like https://www.elastic.co/guide/en/elasticsearch/reference/current/analysis-uaxurlemail-tokenizer.html instead. This is actually used for URL fields, which are configured in MultiFieldHandler.php in the same folder.

The tricky part is doing this in an upgrade safe way. It can be done, but there are quite a few things you have to override - start at src/Elasticsearch/Container.php and work your way downward.

Kind regards,

Gabriel

Thank you for the tips on customization, I will certainly take a stab at it when I have a minute.

As for the query I may be wrong but if you are retrieving emails From a given address

select email_id
from emails_text
where from_addr = 'francescas@mydomain.com'

seems to be the easiest possible query rather than searching for related beans first.

I could even see the search using emails_email_addr_rel, without going through the bean

select email_id
from emails_email_addr_rel
where emails_email_addr_rel.address_type = 'from'
and emails_email_addr_rel.email_address_id = (select id 
   from email_addresses 
   where email_address = 'francescas@mydomain.com')

But what happens once you select a bean?
Is it going back to finding the address_id related to that bean in email_addr_bean_rel and then search in emails_email_addr_rel to find the "from" emails?
And if the bean has multiple addresses does it find more emails than just the address I asked for?

I've not looked into the code for the filter but it seems to me that it's going to unnecessary lengths, and possibly even limiting results (if it's using emails_beans), by translating the address into the related bean.

What happens if a lead sends me an email?
I assume the email is related to the Lead (emails_beans) and so is the email address (email_addr_beans_rel).
Now I add a Contact, not through Lead Conversion, I forgot the Lead exists, I just add a contact with that same email address.
The email is related to the Lead but not the Contact. There is no emails_beans for that email where the bean_module = Contacts.
If I search for emails from that address will the email on Lead be pulled in the result set? Or will I get just those related to the Contact via emails_beans?

Am I overthinking this?

FrancescaS

I noticed that emails_text.from_addr is deprecated:

/**
     * The name and email address of the email's sender, formatted for use in the email's FROM header.
     *
     * @var string
     * @deprecated Use {@link Email::$from} to link the sender to the email.
     */
        var $from_addr;
    /**