I posted this issue 2-3 days before, today I found a new issue related to the same thing, the link of the new issue is listed below therefore I believe this is a BUG that needs to be looked into it since there has been no replies with a solution provided and only one question which did not help the bug. Here is the bug.
Multi Search on Grid does not work
I need help on setting up a grid with multi-search fields. I have a search on a grid app with multiple fields one is a date with search criteria = between two values and other search fields to filter records with.
On a grid app we have a Search menu. Under the search then settings there is an option named “Search Criteria” where we can select between using the “OR” condition or the “AND” condition when more than one search field is used.
I have a date search set with criteria to search records that have a date between 2 dates plus several other fields I can filter from.
When I use the date with an “OR” condition under setting, the query filter does not seem to work because it returns “all” the records on the table.
When I use the date with an “AND” condition nothing is returned.
Setting debug mode I can see how the queries are formed and that is the reason I get everything back or nothing.
This query has the “OR” option under Search Settings. This query returns EVERYTHING on the table
disregarding the date limitation (between jan1 2012 and 12-31 2012)
SELECT entry_date, field1, field2, field3, field4, field5
from table1
where entry_date between ‘2012-01-01 00:00:00’ and ‘2012-12-31 23:59:59’ and
field1 = 0 or field2 = 0 or field3 = 0 or field4 = 0 or field5 = 0 LIMIT 0,27 *
This query has the "AND’ option selected under Search Settings. This query results returns NO RECORDS
SELECT entry_date, field1, field2, field3, field4, field5
from table1
where entry_date between ‘2012-01-01 00:00:00’ and ‘2012-12-31 23:59:59’ and
field1 = 0 and field2 = 0 and field3 = 0 and field4 = 0 and field5 = 0 LIMIT 0,27 *
How can I make the query to only return the records between the dates selected and ignore the rest of the fields on the search since I don’t use them or who can I make that query work when using the date filter in conjunction with another filter ??
Note that all the search fields except for the date field are defined under the search menu as "SELECT’ fields with a query to select the record Id plus the descriptions from their appropriate tables so there is nothing out of the ordinary other than having more than one search field.
http://www.scriptcase.net/forum/showthread.php?5654-Search-field-with-multiple-values-doesn-t-work