Multi Search on Grid does not work

Applications Grid / Reports
#1

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 ??

#2

[QUOTE=javierx1;21033]
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 ??[/QUOTE]

The “rest of the fields”: how are there defined? I have for example a date field and two select fields and if I use none of these fields they are not in the sql query.

#3

I am not sure what you are asking?: The rest of the fields can be defined anything you want other than a date field, text, numeric etc.
Each search filter has to be defined as: Data Type Fiel = ‘SELECT’ field with the corresponding query to get the record Id plus the name of the field value for each record, there is nothing special other than that of a normal select field.
EXAMPLE: field1 --> defined as select field.
Query: Select field1_Id, field1_Name FROM table1 ORDER By field1_Id

Please let me know if I answer the question thank you.

#4

What’s your setting at Search / Settings / Empty Search? Correct are “yes” … With that setting empty fields (aka select without selection) are not in the search criteria / sql string (use debug mode and look at the generated string).

#5

The Search screen has 7 fields the user can use to search so user can search on any of the 7 fields and does not have to use all of them.
I have only included the date field set to select between 2 values and 3 fields only but all 6 fields besides the date field are all defined the same way as described below.

Search screen behavior:
When I enter a date range only and is the first time I enter the date range the rest of the fields are not included on the query however, the date range is ignored and the entire table is returned but it should sonly returned the records within the selected date range.
Here is an example of what debug returns on "first time date range selected)
(mysqlt): select count(*) from vicidial_list_ventas where last_local_call_time between ‘2012-01-01 00:00:00’ and ‘2012-30-01 23:59:59’ *
(mysqlt): SELECT venta_id, last_local_call_time, user, first_name, phone_number, middle_initial, venta_campaign_id, qual_venta1_status, qual_venta2_status, cobra_status1, cobra_status2 from vicidial_list_ventas where last_local_call_time between ‘2012-01-01 00:00:00’ and ‘2012-30-01 23:59:59’ LIMIT 0,27 *

When I enter a date range only and is NOT the first time, I hit the “return” (return to the search screen) button and select a different date range or same date range "all the fields are included on the query with an condition = AND field1 = 0 AND field2 = 0 AND field3 = 0, when I changed the AND to and OR I get the same thing just says OR instead of AND (OR field1=0 OR field2=0 OR field3=0)
Here is an example of what the Debug returns on second or more times date range selected (same or different)
(mysqlt): select count(*) from vicidial_list_ventas where last_local_call_time between ‘2012-01-01 00:00:00’ and ‘2012-30-01 23:59:59’ or qual_venta1_status = 0 or qual_venta2_status = 0 or cobra_status1 = 0 or cobra_status2 = 0 *
(mysqlt): SELECT venta_id, last_local_call_time, user, first_name, phone_number, middle_initial, venta_campaign_id, qual_venta1_status, qual_venta2_status, cobra_status1, cobra_status2 from vicidial_list_ventas where last_local_call_time between ‘2012-01-01 00:00:00’ and ‘2012-30-01 23:59:59’ or qual_venta1_status = 0 or qual_venta2_status = 0 or cobra_status1 = 0 or cobra_status2 = 0 LIMIT 0,27 *

FIELDS ARE SETUP THIS WAY:
Under Search Menu -> Settings:
Empty Search = Yes
Search Criteria -> there are only two choices (AND and OR) neither one works

Under Search Menu -->> Search Criteria for fields selected (The date field is set to “Between 2 values”)
Under Search Menu -->> Search Criteria, the rest of the 6 filter fields are set to “Equal To”
Under Search Menu -->> “Fields Menu” the following fields are defines as described:

  1. Date field:
    Data Type of Field = Date and Time,
    Values Format – >> Display ddmmyyyy
    Special Conditions -->> Normals

  2. field1:
    Data Type of Field = Select
    Search Lookup -> Lookup method=Automatic -->> SQL Select Statement = Select field_Id1, field1_name) from table1 ORDER BY field1_Id)

  3. field2:
    Data Type of Field = Select
    Search Lookup -> Lookup method=Automatic -->> SQL Select Statement= Select field_Id2, field2_name) from table2 ORDER BY field2_Id)

  4. field3:
    Data Type of Field = Select
    Search Lookup -> Lookup method=Automatic -->> SQL Select Statement = Select field_Id3, field3_name) from table3 ORDER BY field3_Id)

#6

If someone need it - I was having the same issue and I realized that the problem comes when “Empty” condition is turned on in every field in the Search Criteria, so I turned all of them off, and voil?, it works perfectly!