Grid Search options

I have a table with mostly character fields, but there are a couple of integer fields.

To make it easier on my users, I would like the default search option for the character fields to be ‘contains’.

If I make this the only option for the Quick Search, then ScriptCase is not smart enough to realize that you can’t do:

WHERE num LIKE '%1234%'

and I get an SQL error.

If I add the ‘equal’ option, then that option appears first in the list, and my users need to select the ‘contains’ option or try get a 20 character field exactly right.

I tried putting only the most used character fields in the quick search, and then including a full filter/search.

The problem with that, is when ScriptCase generates the SQL for the search, it includes ALL the fields from the search, even if the user hasn’t entered anything in them. So, if the user just wants to find ‘skid_number=1234’, either nothing is found because the criteria is ANDed with all the other fields=’’, or extra rows are found because the criteria is ORed with all the other fields=’’ (so it picks up anything that has a null in any of the fields).

Anybody got a suggestion on how to set this up?

Thanks.

First of all there’s an option to be set if you want to search with an ‘or’ or with an ‘and’ criterium. Afaik if a field is empty the field is not used in building the search expression. At least in version 6, but I’ll try to reproduce in 7. You can set the search options yourself and arange them as needed. So if equal appears first you can shift it down. For integer fields you need to supply ‘between’ instead of contains which is used in strings. SC might be ‘not smart enough’, but point is that SC is database independent and not all sql are the same.

Thank you Albert.

I am running SC 7. And tried the AND/OR switch. I also turned on DEBUG to see the generated SQL, and all the fields show up in the SQL

You can change the order of the options in SEARCH, but not in the QUICK SEARCH (there you just have check boxes).

And I understand that different databases handle things differently, but if I need to use ‘BETWEEN’ for integers, and I’ve told SC that this is an integer field, it should at least warn/alert the end user that they need to change.

Even better would be to not even show the CONTAINS option when an integer field is picked.

Nick

Hello,

Please contact our support regarding this issue. Our chat is active on workdays from 8:00am to 6:00pm (GMT -3), and our ticket system is always available.

regards,
Bernhard Bernsmann