Finding contact by an email address

[ziaulhuda]

HI,

I have a question. I need to query the Contact which have a specific email address. I tried Contact.find :all, :conditions={:email1=>"='email'"} but it throws an exception as email1 is not a field of contacts table in db. I searched for the email in EmailAdress module like c = EmailAddress.find_by_email_address 'email' and find the email but if i do c.contact it is not available.

Any way to accomplish this job?

Regards

[davidsulc]

Sorry, I can't figure out how to do it either. I also do see how this can be accomplished through API calls, which is likely the main problem.

Hopefully @chicks will chime in with a solution...

[chicks]

Yeah, there's no REAL straightforward way to do this, but this is what I've used in the past:

SugarCRM.connection.get_entry_list(
  "Contacts",
  "contacts.id IN (SELECT bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (eabr.email_address_id = ea.id) WHERE bean_module = 'Contacts' AND ea.email_address_caps LIKE '%SUGARCRM.COM' AND eabr.deleted=0)"
)

In short, you need to swap "bean_module" to match whatever module you want to query against (i.e. Contacts, or Accounts), and then 'ea.email_address_caps' to match the capitalized version of the email address you want to find Contacts or Accounts for.

This is probably the single most COMMON Web Services question ever, is poorly documented, and confusing as hell.

David, maybe we should add a custom finder for email addresses so that we could do something like:

Contact.find_by_email_address("[email protected]") 

That just wraps that query.

[davidsulc]

Yeah, we should probably add such a custom finder.

We could probably add it to the finders file to have it included in all models, then simply raise an exception unless the object instance respond_to? email or something like that... Or (probably better), have a conditional method definition for it, so classes that don't implement the method won't respond_to? that method.

Does that make sense to you?

I'm not sure I'll have time to get around to this any time soon, so if you can't do it either we should document this as a new issue and "put it in the pipeline"...

[chicks]

Yep, I was going to add a conditional finder based on the email_addresses relationship. I'll take care of it sometime this week, just wanted your thoughts!

[davidsulc]

Yeah, I think we should add the functionality to the gem and this seems like the best way to do it. We should probably keep a note somewhere (e.g. in WATCHLIST.rdoc) to monitor SugarCRM's new versions for an API call that would implement this in a cleaner way.

If I recall correctly, there is also an issue/missing functionality with adding a new email address to a given instance (e.g. a Contact). I can't remember the specifics, but I remember running into issues when I tried to do that, and I think the issue is probably related. Maybe we could have an add_email_address method that would check if the record is related to that email, and if not, finds or creates the email address and links it to the record. (But I have no idea if this is even possible with the API calls...)

[ziaulhuda]

happy to point out something new for the gem. Would like to see the functionality soon.

[whereisaaron]

Joy! @chicks thanks for revealing this well kept secret :-) I was incredulous how opaque retrieving contact by email address was. Can someone get that recipe into the web service documentation somewhere?

I've coded against a lot of large and complicated web services, but never anything with as steep a learning curve as SugarCRM V6. I suspect it is much less opaque if you are already familiar with SugarCRM internals; but I think one goal of a web services is to keep the API independent of implementation details and safe against updates. Passing in entire SQL queries seems like it will lead to trouble one day. What if I install with a different database under SugarCRM? Could some web service clients who otherwise meet the WSDL contract just stop working if the database query syntax or case-sensitivity or something change?

But anyway, I am just thankful there is a web service API at all. Now back to using it... Thanks for the tip @chicks.

[chicks]

Hah! NP. I'll get around to adding a find_by_email method to the gem one fo these days.

[donsdgr81]

May I ask if this was ever impemented? I tried it on my project and seems like it was never implemented to the gem.

[chicks]

Nope, never got around to it. I'm interesting in accepting pull requests though :)

[agrass]

Is this feature developed by someone? It would be great be able to find Leads by email also

[mathai13]

Maybe try this as a starting point? Not sure if it will work as is

$sea = new SugarEmailAddress;
$id = $sea->getEmailGUID('[email protected]');

if ($sea->load_relationship('contacts'))
{
    //Fetch related beans 
    $relatedBeans = $sea->contacts->getBeans();
}