Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: How to edit search query?!

  1. #11
    kuske's Avatar
    kuske is offline Sugar Community Member
    Join Date
    Oct 2007
    Location
    Germany
    Posts
    2,678

    Default Re: How to edit search query?!

    Too less information to help you.
    You must post more details about your changes and try to open a new thread, I do not think that your problem is the same as yassos.
    Harald Kuske
    Senior Sales Engineer Central Europe

    SUGARCRM Deutschland GmbH
    Erika-Mann-Str. 53, 80636 Munich, Germany
    Email: hkuske@sugarcrm.com
    Home: http://www.sugarcrm.com

    PLEASE SWITCH TO
    https://community.sugarcrm.com/, the FORUMS will be closed !


  2. #12
    Big Ring's Avatar
    Big Ring is offline Senior Member
    Join Date
    Sep 2009
    Location
    Minnetonka, MN, USA
    Posts
    44

    Default Re: How to edit search query?!

    Kuske,
    I used the exact solution provided here only in the cases module and different custom fields. The sql that is produced is exactly what I expected except the values from my search field are wrapped in N'value' because I am using M$ SQL.
    Thanks,

    Tim

  3. #13
    kuske's Avatar
    kuske is offline Sugar Community Member
    Join Date
    Oct 2007
    Location
    Germany
    Posts
    2,678

    Default Re: How to edit search query?!

    What types have the custom field?
    Did your write any vardefs.php?
    How does the sql statement look?
    What does the sugarcrm.log say if you set logging level to debug?
    Harald Kuske
    Senior Sales Engineer Central Europe

    SUGARCRM Deutschland GmbH
    Erika-Mann-Str. 53, 80636 Munich, Germany
    Email: hkuske@sugarcrm.com
    Home: http://www.sugarcrm.com

    PLEASE SWITCH TO
    https://community.sugarcrm.com/, the FORUMS will be closed !


  4. #14
    Big Ring's Avatar
    Big Ring is offline Senior Member
    Join Date
    Sep 2009
    Location
    Minnetonka, MN, USA
    Posts
    44

    Default Re: How to edit search query?!

    Sorry, my last post was from my cell, so I didn't have that data handy. I should have waited to get to the office to post my reply.

    Quote Originally Posted by kuske View Post
    What types have the custom field?
    The custom Cases fields are enum, as are the search fields.
    Quote Originally Posted by kuske View Post
    Did your write any vardefs.php?
    I created the fields in Studio, should I add them in vardefs as well? I have not done that.
    Quote Originally Posted by kuske View Post
    How does the sql statement look?
    Code:
     ...LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c                                     
        LEFT JOIN  users jt0 ON
    jt0.id= cases.assigned_user_id AND jt0.deleted=0
     AND jt0.deleted=0 
     where ((( cases_cstm.casesproductcodeone_c in (N'MAB421') 
       OR cases_cstm.casesproductcodetwo_c in (N'MAB421') 
       OR cases_cstm.casesproductcodethree_c in (N'MAB421') ))) 
    AND cases.deleted=0
    The value that I selected from the dropdown was 'MAB421' and it is searching the fields I want, but because it is using the 'IN' operator on a NVARCHAR it blows up (see error below). I am not sure why it is using an 'IN' since I am not using a muti-select -- so is there some way to change that to an '='?
    Quote Originally Posted by kuske View Post
    What does the sugarcrm.log say if you set logging level to debug?
    Code:
    Fri Apr  2 02:11:29 2010 [8244][1][FATAL] SQL Server error: The data types text                                       and nvarchar are incompatible in the is operator.

    Thanks for your help.
    Thanks,

    Tim

  5. #15
    Big Ring's Avatar
    Big Ring is offline Senior Member
    Join Date
    Sep 2009
    Location
    Minnetonka, MN, USA
    Posts
    44

    Default Re: How to edit search query?!

    kuske,
    I think I have it.
    In the /include/SearchFields/SearchFields2.php I added the following around line # 533:
    PHP Code:
    } else {
                            
    // --------->TIM'S CHANGE from: $operator = 'in'; //to:
                            
    $operator = ( $GLOBALS['db']->dbType == 'mssql' and $type == 'enum' ) ? 'like' 'in';
                                foreach(
    $parms['value'] as $key => $val) {
                                    if(
    $val != ' ' and $val != '') {
                                        if (!empty(
    $field_value)) {
                                            
    $field_value .= ',';
                                        } 
    Just changing that one line seems to do the trick.

    Thanks for all of your help
    Thanks,

    Tim

  6. #16
    kuske's Avatar
    kuske is offline Sugar Community Member
    Join Date
    Oct 2007
    Location
    Germany
    Posts
    2,678

    Default Re: How to edit search query?!

    Ok, but that works only if you do not select more than one element from the select box...
    Harald Kuske
    Senior Sales Engineer Central Europe

    SUGARCRM Deutschland GmbH
    Erika-Mann-Str. 53, 80636 Munich, Germany
    Email: hkuske@sugarcrm.com
    Home: http://www.sugarcrm.com

    PLEASE SWITCH TO
    https://community.sugarcrm.com/, the FORUMS will be closed !


  7. #17
    Big Ring's Avatar
    Big Ring is offline Senior Member
    Join Date
    Sep 2009
    Location
    Minnetonka, MN, USA
    Posts
    44

    Default Re: How to edit search query?!

    kuske,
    Ouch! Thanks for pointing that out. Big hole in my solution. Do you have another suggestion to get around all of that?

    I appreciate all of your help on this!!
    Thanks,

    Tim

  8. #18
    kuske's Avatar
    kuske is offline Sugar Community Member
    Join Date
    Oct 2007
    Location
    Germany
    Posts
    2,678

    Default Re: How to edit search query?!

    Perhaps you must convert the left side of the compares to nchar too.
    I think a

    where ((( convert(nchar(45),cases_cstm.casesproductcodeone_c ) in (N'MAB421')

    should work, or on the right side for each selected dropdown value something like

    where ((( cases_cstm.casesproductcodeone_c in (convert(varchar(45),N'MAB421'))
    Harald Kuske
    Senior Sales Engineer Central Europe

    SUGARCRM Deutschland GmbH
    Erika-Mann-Str. 53, 80636 Munich, Germany
    Email: hkuske@sugarcrm.com
    Home: http://www.sugarcrm.com

    PLEASE SWITCH TO
    https://community.sugarcrm.com/, the FORUMS will be closed !


  9. #19
    Big Ring's Avatar
    Big Ring is offline Senior Member
    Join Date
    Sep 2009
    Location
    Minnetonka, MN, USA
    Posts
    44

    Default Re: How to edit search query?!

    kuske,
    Thanks for the reply, I will try that. As I was poking around in the SearchForm2.php I noticed that around line 512 the following has an interesting part of the IF commented out considering the comment below it.
    PHP Code:
                         if(/*$GLOBALS['db']->dbType != 'mysql' &&*/ !empty($this->seed->field_name_map[$field]['isMultiSelect']) && $this->seed->field_name_map[$field]['isMultiSelect']) {
                            
    // construct the query for multenums
                            // use the 'like' query for all mssql and oracle examples as both custom and OOB multienums are implemented with types that cannot be used with an 'in' 
    I have changed it to this and it seems to work fine.
    PHP Code:
                         if($GLOBALS['db']->dbType != 'mysql' && $type == 'enum') {
                            
    // construct the query for multenums
                            // use the 'like' query for all mssql and oracle examples as both custom and OOB multienums are implemented with types that cannot be used with an 'in' 
    I think that there is more to do, but it does work
    Last edited by Big Ring; 2010-04-03 at 02:51 PM.
    Thanks,

    Tim

  10. #20
    kuske's Avatar
    kuske is offline Sugar Community Member
    Join Date
    Oct 2007
    Location
    Germany
    Posts
    2,678

    Default Re: How to edit search query?!

    I'm amazed about some details of SugarCRM implementation...
    Harald Kuske
    Senior Sales Engineer Central Europe

    SUGARCRM Deutschland GmbH
    Erika-Mann-Str. 53, 80636 Munich, Germany
    Email: hkuske@sugarcrm.com
    Home: http://www.sugarcrm.com

    PLEASE SWITCH TO
    https://community.sugarcrm.com/, the FORUMS will be closed !


Page 2 of 3 FirstFirst 123 LastLast

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Similar Threads

  1. where to edit the query?
    By Jerrym in forum Developer Help
    Replies: 3
    Last Post: 2009-03-02, 11:30 AM
  2. Quick Search Query Customization
    By consultsales in forum General Discussion
    Replies: 0
    Last Post: 2008-12-02, 01:47 PM
  3. Replies: 0
    Last Post: 2008-11-03, 08:33 AM
  4. How to get the query of saved search functionality...?
    By maniram2202 in forum Developer Help
    Replies: 0
    Last Post: 2008-10-15, 01:20 PM
  5. Search Query
    By manahar in forum Developer Help
    Replies: 0
    Last Post: 2007-07-16, 07:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •