Searching multiple DB fields via a Relation

Classic Servoy
1

I’m having trouble with multiple search items in a relation. Below is the relation I have setup for reference:

722×241 5.08 KB

I have a search box (text field named txtSearch) that has the globals.searchTerm variable setup as the data provider. When a user enters anything in it I call the following function:

function onSearchClick(event) {
	globals.searchOperator = '%' + globals.searchTerm + '%';
	forms.frmMenu.elements.snipTree.refresh();
}

On this form I have a tree called snipTree (DBTreeView linked with multiple tables) that updates with the appropriate items based on the search criteria entered. Well, kind of.

What is happening is that if the search criteria match anything in snip_name, the relation returns the correct results and the tree gets updated correctly. However, if the serach criteria is something that is matched in any of the other two fields (snip_code or snip_desc) it does not return any results. It appears that only the first #like operation is being performed and the others are ignored. OR, is is using an AND instead of an OR? For instance, is is saying:

SELECT * FROM snip_snippets WHERE snip_name LIKE '%searchOperator%' AND snip_code LIKE '%searchTerm%' .....

OR this

SELECT * FROM snip_snippets WHERE snip_name LIKE '%searchOperator%' OR snip_code LIKE '%searchTerm%' .....

How can I tell it to use an AND vs. and OR statement in my relation setup, if that is indeed the problem?

For reference, here are the global variables I have setup as well if needed for understanding:

/**
 * @type String
 *
 * @properties={typeid:35,uuid:"ABC44185-855F-47A1-B14E-2076054E0CB2",variableType:12}
 */
var searchOperator = '%%';

/**
 * @type String
 *
 * @properties={typeid:35,uuid:"02767CB9-A71E-48A6-9DAD-03A8C3B27B03",variableType:12}
 */
var searchTerm = '';

/**
 * @type Number
 *
 * @properties={typeid:35,uuid:"24A72C88-38C9-4899-9E0E-8840FA5B098F",variableType:4}
 */
var notArchived = 0;

This is the first time I’ve tried to implement a search capability into a form so I may be going about this all wrong. If so please feel free to point me in the right direction.

Thanks,

Keith

2

if you create a relation like that it is an AND relation

So your 3 columns (snip_name, snip_code and snip_desc) all must return true for that global before the record is returned.

You can’t define OR in our relations.