Contacts Accounts Search Relationship

[Jax.Smith]

Hi there,

I am trying to replicate the search feature that is available in the SugarCrm. My sugar is currently loaded with demo data. The image below shows that when I search for a contact, it will return all accounts related to this contact.



If you could please tell me what files or codes I need to edit.

Thank You,
Jax

[datasponge]

Hi Jax,

Do you mean the php files or the database files?

In the underlying sugarcrm database there are three relevant tables:
accounts
contacts
accounts_contacts

accounts and contacts each have a field named `id`. accounts_contacts has fields `id`, `account_id` and `contact_id`.

You would find the accounts for a contact with a query something like:

SELECT * from (accounts INNER JOIN accounts_contacts ON accounts.id = accounts_contacts.account_id) INNER JOIN contacts ON accounts_contacts.contact_id = contacts.id WHERE contacts.last_name = 'Smith';

This would give you a recordset with all the accounts and contacts fields for which the last name in contacts is 'Smith'

Phil

[Jax.Smith]

Hi Phil,

Thanks for replying. I am more into php files because it can create the table when i rebuild. The code you gave me where exactly do I put it?

Thanks again,
Jax