Multiple Conditions for Quick Search

[tturaider2night]

Does anyone know how to add multiple conditions to a Quick Search?

I am attempting to pull account information for only a custom account type named "Supplier" but I haven't been able to do so. I have been able to pull this type of information for all accounts but now I am attempting to pull only this particular account type for a purchase order module.

Here is my custom quick search function under include\QuickSearchDefaults.php :

	function getQSSupplierAccounts($theid, $thename, $thestreet, $thecity, $thestate, $thepostalcode, $thecountry) {
		global $app_strings;
		
		$qsAccounts = array( 
					'method' => 'query',
					'modules' => array('Accounts'), 
					'group' => 'AND', 
					'field_list' => array('name', 'id', 'billing_address_street', 'billing_address_city', 'billing_address_state', 'billing_address_postalcode', 'billing_address_country'), 
					'populate_list' => array($thename, $theid, $thestreet, $thecity, $thestate, $thepostalcode, $thecountry), 
					'conditions' => array(array('name'=>'name','op'=>'like_custom','end'=>'%','value'=>''), array('name'=>'account_type','op'=>'contains','value'=>'%supplier%')), 
					'order' => 'name', 
					'limit' => '30',
					'no_match_text' => $app_strings['ERR_SQS_NO_MATCH']
					);
					
		return $qsAccounts;
	}

Drop me a line if anyone has any ideas.

I am currently looking at the construct_where function in \json_server.php to get some idea of how these array specs are read.

[tturaider2night]

Ok it appears I may have found my problem after looking at my log file and the /include/javascript/quicksearch.js file.

It seems that even when I had multiple conditions in my QuickSearch defaults all of my conditional values would always change to whatever it was that I had typed into the textbox. EVEN when the 'value' variable was explicitly set in the QuickSearchDefaults.php file.

I am running version 4.5.0b (Build 1144) and I am not sure if this will be corrected in future versions but here is my work around.

First here is my customized include/QuickSearchDefaults.php function:

function getQSSupplierAccounts($theid, $thename, $thestreet, $thecity, $thestate, $thepostalcode, $thecountry) {
   global $app_strings;
		
   $qsAccounts = array( 
					'method' => 'query',
					'modules' => array('Accounts'), 
					'group' => ' AND ', 
					'field_list' => array('name', 'id', 'billing_address_street', 'billing_address_city', 'billing_address_state', 'billing_address_postalcode', 'billing_address_country'), 
					'populate_list' => array($thename, $theid, $thestreet, $thecity, $thestate, $thepostalcode, $thecountry), 
					'conditions' => array(array('name'=>'name','op'=>'like_custom','end'=>'%','value'=>''), array('name'=>'account_type','op'=>'contains','value'=>'Supplier')), 
					'order' => 'name', 
					'limit' => '30',
					'no_match_text' => $app_strings['ERR_SQS_NO_MATCH']
					);
					
		return $qsAccounts;
	}

Now this is the code that I modified in the include/javascript/quicksearch.js file under the 'makeRPCCall' function:

function makeRPCCall(upEl, sqs_id) {
	if(typeof(sqs_objects[sqs_id]) != 'undefined') {
		window.clearTimeout(callDelay);
		if(sqs_objects[sqs_id]['multi']) 
			sqs_query = getUserInputToMatch(document.getElementById(sqs_id).value);
		else 
			sqs_query = document.getElementById(sqs_id).value;
		
		if(typeof(siw) != 'undefined' && sqs_query == '') hideSmartInputFloater(false);
		sqs_query = sqs_query.replace(/\\/gi,'').replace(/\[/gi,'').replace(/\(/gi,'').replace(/\./gi,'\.').replace(/\?/gi,'');
		sqs_query = sqs_query.replace(/[()]+/g,'');

		if (sqs_query.length > 0) {
			if(sqs_old_values[sqs_id].length > 0 && sqs_query.indexOf(sqs_old_values[sqs_id]) == 0
							&& typeof(collection) != 'undefined' && collection.length > 0 && collection.length < sqs_objects[sqs_id]['limit']) { // don't make an RPC call, use cache
				processSmartInput(upEl, sqs_id);
			}
			else if(sqs_old_values[sqs_id] != sqs_query) {
				// wait gif
				x = findElementPosX(upEl) - 19;
				y = findElementPosY(upEl);
				floaterWait.style.left = x;
				floaterWait.style.top = y;
				floaterWait.style.display="block";
				floaterWait.style.visibility="visible";	
				for(var i = 0; i < sqs_objects[sqs_id]['conditions'].length; i++) {
					if ((sqs_objects[sqs_id]['conditions'][i]['name'] == 'account_type')) {
							if (JSON.parse(sqs_objects[sqs_id]['conditions'][i]['value']) == false){
								sqs_objects[sqs_id]['conditions'][i]['value'] = JSON.stringify(sqs_objects[sqs_id]['conditions'][i]['value']);
							}
					}
					else
					{
						sqs_objects[sqs_id]['conditions'][i]['value'] = JSON.stringify(sqs_query);
					}
				}
				
				req_id = global_rpcClient.call_method(sqs_objects[sqs_id]['method'], sqs_objects[sqs_id]);
				
				global_request_registry[req_id] = [SugarQuickSearchObject, 'display'];
			}
		}
		sqs_old_values[sqs_id] = sqs_query;
		sqs_object_id = sqs_id;
	}
}

Notice that under my conditions I was looking for a condition name of 'account_type'. If the condition name matched this then it would check to see if the value was already stringified if not then it would stringify the original value declared in the Quick Search defaults definitions and not the value being typed in the text box.

This work around was only for my own account type column under the accounts table but it should at least give you an idea of a work around until this bug is fixed in future versions.

[tturaider2night]

I have submited bug# 11739 for this problem.

[balou]

Howdi,

I know this thread has been open for a long time, and it appears your ticket has not changed status yet.

I don't have the version you use, but in a later version (4.5.0h), you can specify a condition with a static argument
array('name'=>'address_type','op'=>'like_custom',' value'=>'test','static'=>'1'),

if you do so the value specified won't be modified by the user input.

Then the only matter is to properly define the way to group the conditions :
'group' => 'and',

Hope it help,

Vincent

[balou]

then .. even worst ... if you have to specify multiple condition that should be match with an 'OR' statement

ie. name ='user_input' AND (type =1 OR type=2)

but you still need an 'AND' statement to match the user input AND the specific static condition, you can always use a dirty hack like the following (if you have a limited set of static value allowed in the field - ie. a type selected by a dropdown)

'group' => 'and',
'conditions' => array(
    array('name'=>'name','op'=>'like_custom','end'=>'%','value'=>''),
    array('name'=>'type not','op'=>'like_custom','value'=>'3','static'=>'1'),
    array('name'=>'type not','op'=>'like_custom','value'=>'4','static'=>'1'),
),

with such a hack you will be able to run a sql query like this to populate the autocomplete "dropdown" that will match the types defined earlier (1 and 2 => thus not 3 nor 4 if you have only 4 types)

WHERE (name like 'user_value%' AND type not like '3' AND type not like '4')

It is quite ugly, but since the GROUP statement is defined only once for all the conditions, adding the NOT at the end of the condition name allows you to cutomize a little bit more your search.

However, I m afraid such kind of hack is really dependent on which version of SugarCRM you use. (4.5.0h is ok) Make your own tests ! sugarcrm.log in debug mode is your best friend

Vincent