error in search on sales module

pelicanit

we have just started getting an error in the sales module when searching.

Search works fine in contacts, but when in sales module we get
----------------------------
Type: E_USER_ERROR (256)
Message: mysqlt error: [1054: Unknown column 'f___first_name' in 'where clause'] in EXECUTE("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')")
File: /xxx/xxx/sitexx/web/crm/libs/adodb/adodb-errorhandler.inc.php
Line=77
error backtrace:
[PHP core called function]
function called: handle_epesi_error(256, "mysqlt error: [1054: Unknown column 'f___first_name' in 'where clause'] in EXECUTE(\"SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')\") ", "/xxx/xxx/sitexx/web/crm/libs/adodb/adodb-errorhandler.inc.php", 77, Array ("mysqlt", "EXECUTE", 1054, "Unknown column 'f___first_name' in 'where clause'", "SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')", false, Object (ADODB_mysqlt), "SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')", false, "mysqlt error: [1054: Unknown column 'f___first_name' in 'where clause'] in EXECUTE(\"SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')\") "))

File: /xxx/xxx/sitexx/web/crm/libs/adodb/adodb-errorhandler.inc.php
line 77
function called: trigger_error("mysqlt error: [1054: Unknown column 'f___first_name' in 'where clause'] in EXECUTE(\"SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')\") ", 256)

File: /xxx/xxx/sitexx/web/crm/libs/adodb/adodb.inc.php
line 1043
function called: ADODB_Error_Handler("mysqlt", "EXECUTE", 1054, "Unknown column 'f___first_name' in 'where clause'", "SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')", false, "Object (ADODB_mysqlt)")

File: /xxx/xxx/sitexx/web/crm/libs/adodb/adodb.inc.php
line 1018
function called: _Execute("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')", false)

File: /xxx/xxx/sitexx/web/crm/libs/adodb/adodb.inc.php
line 1392
function called: Execute("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')", null)

[PHP core called function]
function called: GetAssoc("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')", null)

File: /xxx/xxx/sitexx/web/crm/include/database.php
line 460
function called: call_user_func_array(Array (Object (ADODB_mysqlt), "GetAssoc"), Array ("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')", null))

File: /xxx/xxx/sitexx/web/crm/modules/Utils/RecordBrowser/RecordBrowserCommon_0.php
line 1130
function called: GetAssoc("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','bar','%') OR f___first_name LIKE CONCAT('%','bar','%')")

File: /xxx/xxx/sitexx/web/crm/modules/Utils/RecordBrowser/RecordBrowserCommon_0.php
line 1296
function called: build_query("premium_salesopportunity", Array (#12), false, Array ())

File: /xxx/xxx/sitexx/web/crm/modules/Utils/RecordBrowser/RecordBrowser_0.php
line 790
function called: get_records_count("premium_salesopportunity", Array (#12), false, Array ())

File: /xxx/xxx/sitexx/web/crm/modules/Utils/RecordBrowser/RecordBrowser_0.php
line 296
function called: show_data(Array (), Array (), Array ())

[PHP core called function]
function called: body()

File: /xxx/xxx/sitexx/web/crm/include/module.php
line 793
function called: call_user_func_array(Array (Object (Utils_RecordBrowser), "body"), Array ())

File: /xxx/xxx/sitexx/web/crm/include/module.php
line 713
function called: get_html_of_module(Object (Utils_RecordBrowser), null, null)

File: /xxx/xxx/sitexx/web/crm/modules/Premium/SalesOpportunity/SalesOpportunity_0.php
line 33
function called: display_module("Object (Utils_RecordBrowser)")

[PHP core called function]
function called: body()

File: /xxx/xxx/sitexx/web/crm/include/module.php
line 793
function called: call_user_func_array(Array (Object (Premium_SalesOpportunity), "body"), Array ())

File: /xxx/xxx/sitexx/web/crm/include/module.php
line 713
function called: get_html_of_module(Object (Premium_SalesOpportunity), null, null)

File: /xxx/xxx/sitexx/web/crm/modules/Base/Box/Box_0.php
line 146
function called: display_module("Object (Premium_SalesOpportunity)")

File: /xxx/xxx/sitexx/web/crm/include/epesi.php
line 209
function called: body()

File: /xxx/xxx/sitexx/web/crm/include/epesi.php
line 254
function called: go(Object (Base_Box))

File: /xxx/xxx/sitexx/web/crm/include/epesi.php
line 277
function called: process("__action_module__=%2FBase_Box%7C0%2FPremium_SalesOpportunity%7Cmain_0%2FUtils_RecordBrowser%7Cpremium_salesopportunity%2FUtils_GenericBrowser%7Cpremium_salesopportunity&__location=1374868892.901", false, true)

File: /xxx/xxx/sitexx/web/crm/process.php
line 32
function called: process("_qf__libs_qf_5d8c6b24c7d47c6b8f9b0f3a77345f8d=&submited=1&show_all_pressed=0&submit_search=Search&search=bar&show_all=Show%20all&__action_module__=%2FBase_Box%7C0%2FPremium_SalesOpportunity%7Cmain_0%2FUtils_RecordBrowser%7Cpremium_salesopportunity%2FUtils_GenericBrowser%7Cpremium_salesopportunity", false)

this has been working fine until today
---------------------------------------------------
version 1.52 --
FYI we do have some clients that have no first name so we have had to use a "." as the field is a mandatory field , we have being doing this since the start of use and had no issue,

please can you let me know any ideas what may be causing it

Thanks and regards
Richard

pelicanit

additional info re our config, we have turned off caching etc to see if that fixed it, but the error still displays with this config

Data directory is writeable OK
PHP version: 5.3.3 OK
Curl loaded OK

EPESI config.php
epesi version: 1.5.2
epesi revison: 10766
Database Name: lbcrm
Database Driver: mysqlt
epesi Local Dir: xxxxxxxweb/crm
epesi Dir: /crm/
epesi URL: http://www.xxxxxx/crm
System Timezone: Europe/London
Debug: NO
Module Times: NO
Display sql queries processing times: NO
Strip output html from comments: NO
Display additional error info: YES
Report all errors (E_ALL): NO
GZIP client web browser history: NO
Reducing Transfer: NO
Minify Encode: NO
Minify sources: NO
Suggest Donation: NO
Check epesi version: YES
JS Output: NO
Set Session: YES
Read Only Session: NO
Mobile Device: NO
First Run: YES
Hosting Mode: NO
Trial Mode: NO
Demo Mode: NO

pelicanit

its all very quiet on the forum.. and support seems to have disappeared

is their another way to get support please. happy to discuss chargeable support options,

Thanks
Richard

jasiek

Sorry for the delay. We were out of reach for few days.

The error shows: Unknown column 'f___first_name
It looks like you disabled this field. What modification to the module did you do? Permissions were changed?

ajb

It looks that someone edited Sales Oportunity select parameters and you have some space characters at the beginning of the field name. It should be "Last Name,First Name" without any spaces around comma.

Regards,
Adam

pelicanit

Hi Adam

Thank you for getting back to me ,
that's very strange as i am the only one looking after this for them and have not changed anything like that

Please can you direst to where i need to make this change,

thanks and regards

Richard

ajb

Hi,

Menu / Administrator / RecordBrowser / [change select at the top] Recordset: Sales Opportunity / [tab] Manage fields

Check every field where related recordset is Contact. Edit field and make sure that param is "Last Name,First Name".

Regards,
Adam

pelicanit

Hi Adam

thanks for the reply, I have checked and they are all ok

General Page Split neral Page Split
enquiryref autonumber Yes No No
LB Job Number Text Yes No No Length 30
Opportunity Name Text Yes Yes No Length 124 Yes
Opportunity Manager Select field Yes Yes No Source: Record Set
Recordset: Contacts
Related field(s): Last Name, First Name
Crits callback: CRM_ContactsCommon::contact_format_no_company Yes Yes
Employees Multiselect field Yes Yes No Source: Record Set
Recordset: Contacts
Related field(s): Last Name, First Name
Crits callback: CRM_ContactsCommon::contact_format_no_company Yes Yes
Type Select field No No Yes Source: CommonData
Table: Premium/SalesOpportunity/Type
Order by: Key
Lead Source Select field Yes No Yes Source: CommonData
Table: Premium/SalesOpportunity/Source
Order by: Key
Probability (%) Integer Yes Yes No
Status Select field Yes Yes Yes Source: CommonData
Table: Premium/SalesOpportunity/Status
Order by: Key Yes
Start Date Date No Yes No
Follow-up Date Date No No No
Close Date Date No No No
Contract Amount Currency Yes No No
Notes Calculated Yes No No Yes
Description Long text No No No
Survey Addtress 1 Text No No No Length 64
survey address 2 autonumber Yes No No
Survey Addtress 2 Text No No No Length 64
survey postcode autonumber Yes No No
Survey Addtress 3 Text No No No Length 64
Survey postcodes Text Yes No No Length 64
Estimate Address 1 Text No No No Length 64
Estimate Address 2 Text No No No Length 64
Estimate Address 3 Text No No No Length 64
Estimate postcode Text No No No Length 64
Type of property Multiselect field Yes No No Source: CommonData
Table: propertytype
Order by: Key
Status or property Multiselect field Yes No No Source: CommonData
Table: statusproperty
Order by: Key
Property Age Multiselect field No No No Source: CommonData
Table: propertyage
Order by: Key
Description of property Multiselect field No No No Source: CommonData
Table: propertydescription
Order by: Key
LOCATION SIZE OF EXISTING CELLAR Long text No No No
Property Descriptionold Select field No No No Source: CommonData
Table: propertydescription
Order by: Key
Property Ageold Select field No No No Source: CommonData
Table: propertyage
Order by: Key
Customers Multiselect field Yes Yes Yes Source: Record Set
Recordset: ---
Related field(s):
Crits callback: --- Yes Yes
APPROX SIZE OF EXCAVATION Long text No No No
HEIGHT OF EXISTING CELLAR Long text No No No
SUSPENDED OR SOLID FLOOR Long text No No No
Proposed use of space by client Long text No No No
Client Budget Currency No No No
APPROX AMOUNT QUOTED VERBALLY Text No No No Length 129
Survey Date Date Yes No No
Prelim estimate Sent Checkbox Yes No Yes
Date prelim Estimate Sent Date Yes No No
SITE INFORMATION Page Split
Listed Select field No No No Source: CommonData
Table: Listed Building
Order by: Key
Conservation Area Select field No No No Source: CommonData
Table: yesno
Order by: Key
Proximity of Trees to Property Text No No No Length 150
Drainage Information Long text No No No
Borehole Locations Long text No No No
ANY OTHER INFORMATION Long text No No No
PLANNING Page Split
Council Select field Yes No Yes Source: CommonData
Table: council
Order by: Key
Planning Applied For Checkbox Yes No Yes
Planning Submission Date Date No No No
Validation Determination Date Date No No No
Planning Officers details Long text No No No
Conditions to be discharge OTHER Text No No No Length 255
Management Company (HGST etc) Text No No No Length 255
Decision Text No No No Length 255
Conditions to be discharged Multiselect field No No No Source: CommonData
Table: Conditions to be discharged
Order by: Value
PLANNING NOTES Long text No No No
SUPPLEMENTARY INFORMATION Page Split
Hydrogeological Checkbox No No No
CTMP Checkbox No No No
BIA Checkbox No No No
Structural Checkbox No No No
Sustainability Assessment Eco Checkbox No No No
Arboricultural Checkbox No No No
Heritage statement (PPS5) Checkbox No No No
Acoustic report Checkbox No No No
SUPPLEMENTARY INFORMATION NOTES Long text No No No
Drainage CCTV survey Checkbox No No No
EXPLORATORY WORKS Page Split
Borehole soil goundwater INV Checkbox No No No
EXPLORATORY WORKS NOTES Long text No No No
Details 2 Page Split

However when doing a simple search we in sales module we still get ( Note advance search seems to work ok)
Type: E_USER_ERROR (256)
Message: mysqlt error: [1054: Unknown column 'f___first_name' in 'where clause'] in EXECUTE("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')")
File: /home/.sites/22/site13/web/crm/libs/adodb/adodb-errorhandler.inc.php
Line=77
error backtrace:
[PHP core called function]
function called: handle_epesi_error(256, "mysqlt error: [1054: Unknown column 'f___first_name' in 'where clause'] in EXECUTE(\"SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')\") ", "/home/.sites/22/site13/web/crm/libs/adodb/adodb-errorhandler.inc.php", 77, Array ("mysqlt", "EXECUTE", 1054, "Unknown column 'f___first_name' in 'where clause'", "SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')", false, Object (ADODB_mysqlt), "SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')", false, "mysqlt error: [1054: Unknown column 'f___first_name' in 'where clause'] in EXECUTE(\"SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')\") "))

File: /home/.sites/22/site13/web/crm/libs/adodb/adodb-errorhandler.inc.php
line 77
function called: trigger_error("mysqlt error: [1054: Unknown column 'f___first_name' in 'where clause'] in EXECUTE(\"SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')\") ", 256)

File: /home/.sites/22/site13/web/crm/libs/adodb/adodb.inc.php
line 1043
function called: ADODB_Error_Handler("mysqlt", "EXECUTE", 1054, "Unknown column 'f___first_name' in 'where clause'", "SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')", false, "Object (ADODB_mysqlt)")

File: /home/.sites/22/site13/web/crm/libs/adodb/adodb.inc.php
line 1018
function called: _Execute("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')", false)

File: /home/.sites/22/site13/web/crm/libs/adodb/adodb.inc.php
line 1392
function called: Execute("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')", null)

[PHP core called function]
function called: GetAssoc("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')", null)

File: /home/.sites/22/site13/web/crm/include/database.php
line 460
function called: call_user_func_array(Array (Object (ADODB_mysqlt), "GetAssoc"), Array ("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')", null))

File: /home/.sites/22/site13/web/crm/modules/Utils/RecordBrowser/RecordBrowserCommon_0.php
line 1130
function called: GetAssoc("SELECT id, id FROM contact_data_1 WHERE false OR f_last_name LIKE CONCAT('%','dor','%') OR f___first_name LIKE CONCAT('%','dor','%')")

File: /home/.sites/22/site13/web/crm/modules/Utils/RecordBrowser/RecordBrowserCommon_0.php
line 1296
function called: build_query("premium_salesopportunity", Array (#12), false, Array ())

File: /home/.sites/22/site13/web/crm/modules/Utils/RecordBrowser/RecordBrowser_0.php
line 790
function called: get_records_count("premium_salesopportunity", Array (#12), false, Array ())

File: /home/.sites/22/site13/web/crm/modules/Utils/RecordBrowser/RecordBrowser_0.php
line 296
function called: show_data(Array (), Array (), Array ())

[PHP core called function]
function called: body()

File: /home/.sites/22/site13/web/crm/include/module.php
line 793
function called: call_user_func_array(Array (Object (Utils_RecordBrowser), "body"), Array ())

File: /home/.sites/22/site13/web/crm/include/module.php
line 713
function called: get_html_of_module(Object (Utils_RecordBrowser), null, null)

File: /home/.sites/22/site13/web/crm/modules/Premium/SalesOpportunity/SalesOpportunity_0.php
line 33
function called: display_module("Object (Utils_RecordBrowser)")

[PHP core called function]
function called: body()

File: /home/.sites/22/site13/web/crm/include/module.php
line 793
function called: call_user_func_array(Array (Object (Premium_SalesOpportunity), "body"), Array ())

File: /home/.sites/22/site13/web/crm/include/module.php
line 713
function called: get_html_of_module(Object (Premium_SalesOpportunity), null, null)

File: /home/.sites/22/site13/web/crm/modules/Base/Box/Box_0.php
line 146
function called: display_module("Object (Premium_SalesOpportunity)")

File: /home/.sites/22/site13/web/crm/include/epesi.php
line 209
function called: body()

File: /home/.sites/22/site13/web/crm/include/epesi.php
line 254
function called: go(Object (Base_Box))

File: /home/.sites/22/site13/web/crm/include/epesi.php
line 277
function called: process("__action_module__=%2FBase_Box%7C0%2FPremium_SalesOpportunity%7Cmain_0%2FUtils_RecordBrowser%7Cpremium_salesopportunity%2FUtils_GenericBrowser%7Cpremium_salesopportunity&__location=1376065139.2514", false, true)

File: /home/.sites/22/site13/web/crm/process.php
line 32
function called: process("_qf__libs_qf_5d8c6b24c7d47c6b8f9b0f3a77345f8d=&submited=1&show_all_pressed=0&submit_search=Search&search=dor&show_all=Show%20all&__action_module__=%2FBase_Box%7C0%2FPremium_SalesOpportunity%7Cmain_0%2FUtils_RecordBrowser%7Cpremium_salesopportunity%2FUtils_GenericBrowser%7Cpremium_salesopportunity", false)

Any further ideas would be appreciated

thanks and regards

Richard

ajb

I am pretty sure that on of the fields is broken. Execute such code in DB to get a field that may be broken in sales opportunity



SELECT * FROM premium_salesopportunity_field WHERE param LIKE "%|  First Name%"

number of underscores in error report indicates that there are two spaces before "First Name", so in sql above there are also two spaces before "First Name" and it's important part of the query.

Regards,
Adam

pelicanit

Hi Adam

thanks for getting back to me

the query result was
---------------
MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0004 sec )
SELECT *
FROM premium_salesopportunity_field
WHERE param LIKE "%| First Name%"
LIMIT 0 , 30
-----------------------------
if I run it with out the spaces I get back the Opportunity Manager field

thanks and regards
Richard

pelicanit

Hi Adam

I ran the query with 3 spaces, and it came back with the
Employees filed which had the following in the parameters

contact::Last Name| First Name;CRM_ContactsCommon::contact_format_no_company;Premium_SalesOpportunityCommon::salesopportunity_employees_crits

I removed the 3 spaces from this fields parameters and it has fixed the search issues.

thank you very much for your help and support, much appreciated 😃

kind regards
Richard

ajb

Interesting, that error report was indicating two spaces... maybe I've missed something in the code and one space was removed somewhere.
Anyway I'm happy that you've solved this issue 🙂

Regards,
Adam

Epesi ENS | Epesi Academy | epesi.cloud PaaS | GitHub

Help Me! | Download from SourceForge