Searches across modules

[caffeineborg]

Good day,

My goal: To search the accounts module for zip codes to get the account ids to match in the relations table (accounts_opportunities) to get the associated opportunity_ids ...

I am looking for some assistance / review / feedback.

I have updated the searchdefs.php and the SearchFields.php in the custom/modules/Opportunities/metadata/ dir.

A rebuild has the field available on the panel.

An entry in the field and a search results in no action. I am not seeing any entries in logs or errors (at DEBG level) when I make a search in the accounts.billing_address_postalcode field in the Advanced Search form. The web server spins when an entry and submit is made the count goes to 0 then back to a full result set.

I think the main piece is the SQL, I am using innerjoin (tried subquery earlier from other reading I had done -- no joy there). The INNER Join SQL is below. I have run in the SQL in a db command prompt and get expected results.

I can provide more information and am asking if you can provide more feedback.

In advance I thank you for your time. Let me know if you have any thoughts or pointers. I will continue to research and test but at this point seem to be at a stop point.


'innerjoin' => 'INNER JOIN accounts_opportunities on accounts_opportunities.opportunity_id=opportunitie s.id INNER JOIN accounts on accounts.id=accounts_opportunities.account_id WHERE accounts.deleted=0 AND billing_address_postalcode LIKE',

Here is an example of a complete SQL query that provides accurate results:

SELECT opportunities.id from opportunities opportunities INNER JOIN accounts_opportunities on accounts_opportunities.opportunity_id=opportunitie s.id INNER JOIN accounts on
accounts.id=accounts_opportunities.account_id WHERE accounts.deleted=0
AND billing_address_postalcode LIKE '92%';



Few related items I found while digging into this:

SugarCRM Developer Blog » Blog Archive » HOWTO: Add a search field that searches another module

Lairds Computer Services > Blog - SugarCRM Cross Module Search Fields

[christianknoll]

If you need it as a report take a reporting tool ... there it is easy and will not require any SQL know how.

If you need it in the listview look at the subquery you eg have for the email address in contacts. That is fairly easy as well but will not provide you the flexibility a report will do.

christian.

Good day,

My goal: To search the accounts module for zip codes to get the account ids to match in the relations table (accounts_opportunities) to get the associated opportunity_ids ...

I am looking for some assistance / review / feedback.

I have updated the searchdefs.php and the SearchFields.php in the custom/modules/Opportunities/metadata/ dir.

A rebuild has the field available on the panel.

An entry in the field and a search results in no action. I am not seeing any entries in logs or errors (at DEBG level) when I make a search in the accounts.billing_address_postalcode field in the Advanced Search form. The web server spins when an entry and submit is made the count goes to 0 then back to a full result set.

I think the main piece is the SQL, I am using innerjoin (tried subquery earlier from other reading I had done -- no joy there). The INNER Join SQL is below. I have run in the SQL in a db command prompt and get expected results.

I can provide more information and am asking if you can provide more feedback.

In advance I thank you for your time. Let me know if you have any thoughts or pointers. I will continue to research and test but at this point seem to be at a stop point.


'innerjoin' => 'INNER JOIN accounts_opportunities on accounts_opportunities.opportunity_id=opportunitie s.id INNER JOIN accounts on accounts.id=accounts_opportunities.account_id WHERE accounts.deleted=0 AND billing_address_postalcode LIKE',

Here is an example of a complete SQL query that provides accurate results:

SELECT opportunities.id from opportunities opportunities INNER JOIN accounts_opportunities on accounts_opportunities.opportunity_id=opportunitie s.id INNER JOIN accounts on
accounts.id=accounts_opportunities.account_id WHERE accounts.deleted=0
AND billing_address_postalcode LIKE '92%';



Few related items I found while digging into this:

SugarCRM Developer Blog » Blog Archive » HOWTO: Add a search field that searches another module

Lairds Computer Services > Blog - SugarCRM Cross Module Search Fields

[caffeineborg]

Thanks Chris,

I was planning on the report but the request was for the search on the list view.

I looked at the Contacts module and believe you are directing me to this subquery:

'operator' => 'subquery',
'subquery' => 'SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE',

So, the suggestion is:

a) to use 'subquery' NOT 'innerjoin' , correct?

b) where is the connection or is it implied, between the query and the main display of details from the Opportunies module.

c) I will put this in place , but memory is that is how I started --> that is I used a subquery .

I will post back here.

My thanks for your comments

[caffeineborg]

I updated SearchFields.php and did a rebuild.

Here is the contents now:

<code>

$searchFields['Opportunites']['account_postcode'] = array(
'query_type' => 'default',
'operator' => 'subquery',
'subquery' => 'SELECT accounts_opportunities.opportunity_id from accounts_opportunities INNER JOIN accounts on accounts.id=accounts_opportunities.account_id WHERE accounts.deleted=0 AND billing_address_postalcode LIKE ',
'db_field' => array(
'id',
),
);

</code>

I did the above edits, a Quick Repair and rebuild.

Same results and still not finding any log details (looking in php and sugarlogs).

thoughts?

[christianknoll]

I think you need to add "accounts." to the billing_address ... in any case .. set the log level to info. do a search and look at the SQL statement sugar submits. That usually helps to resolve such things quickly.

christian.

I updated SearchFields.php and did a rebuild.

Here is the contents now:

<code>

$searchFields['Opportunites']['account_postcode'] = array(
'query_type' => 'default',
'operator' => 'subquery',
'subquery' => 'SELECT accounts_opportunities.opportunity_id from accounts_opportunities INNER JOIN accounts on accounts.id=accounts_opportunities.account_id WHERE accounts.deleted=0 AND billing_address_postalcode LIKE ',
'db_field' => array(
'id',
),
);

</code>

I did the above edits, a Quick Repair and rebuild.

Same results and still not finding any log details (looking in php and sugarlogs).

thoughts?

[caffeineborg]

Chris,
Thanks for the eyes on this. I made the updates/changes (accounts. to the query and INFO (note DEBUG) for the logging).

No joy. I am not seeing, in the sugar log, anything from my search. I look for the search term, I looked for the column (/postalcode or /post in vi) details in the log.

Now I feel I am spinning a bit. Even the logs are not showing details to debug with. This is even after I logged out, stopped the web and database servers, restarted and re-logged in ...

I will keep digging and any additional input / thoughts are welcome.

I am also going to set up another v6.1 environment, create opportunies + accounts and search. I will update this thread if there is anything that comes from that.

my gratitude for your time

[caffeineborg]

No joy in the v6.1 build either.

As an additional element here is the stanza from the searchdefs.php

'account_postcode' =>
array (
'name' => 'account_postcode',
'label' => 'LBL_Account_postcode',
'type' => 'varchar',
'default' => true,
'width' => '10%',
),


##
The new environment, v6.1, I had populate wiht the sample data put in the searchdefs.php and SearchFields.php files did a repair, and also checked via the studio for the filed in the form. Logging set to
info and no details for the query in the log.

lost at this point

[caffeineborg]

a ping ...

Any thoughts from any body on this one?

Have I missed something global? I see no logging details, no errors and get no results.

Do I need to provide more details?

[caffeineborg]

another ping ...

Any thoughts from any body on this one?

Have I missed something global? I see no logging details, no errors and get no results.

Do I need to provide more details?

Still not getting any log details, search responses as I suspect I should.

Is it the version of sugar, do I need an upgrade. This is at v6.1 (holding as have plans to use Kinamu)

[caffeineborg]

another ping ...

Any thoughts from any body on this one?

Have I missed something global? I see no logging details, no errors and get no results.

Do I need to provide more details?

Still not getting any log details, search responses as I suspect I should.

Is it the version of sugar, do I need an upgrade. This is at v6.1 (holding as have plans to use Kinamu)