Search for 'ALL' entries from Dropdown

[den1s]

Hi and Happy New Year,

I have a dialog with Type, Date From and Date To fields. This allows the user to select the type of product and then the between dates for the warranty expiry.

How can i convert the code so that if the user does not select a 'type' then 'ALL' Types between the selected dates are shown?

Filter = "type =" +quote(Type)+" .and. warranty_expiry >= {"+fromdate+"} .and. warranty_expiry <= {"+todate+"}"

I would have thought by not selecting anything in the 'type' dropdown would have defaulted to ALL but unfortunately not.

Thanks

Denis

[den1s]

Re: Search for 'ALL' entries from Dropdown

I tried changing the code using an 'OR' statement, which worked with a blank TYPE, but when you do select a type it is ignored and ALL types still appear.

Filter = "type =" +quote(Type)+" .or. warranty_expiry >= {"+fromdate+"} .and. warranty_expiry <= {"+todate+"}"

I think i am looking for an and/or function.

Please help.

[Tim Kiebert]

Re: Search for 'ALL' entries from Dropdown

Try

[B]If alltrim(type) = "" then
   [/B][B]Filter = "warranty_expiry >= {"+fromdate+"} .and. warranty_expiry <= {"+todate+"}"
else
    [/B][B]Filter = "type =" +quote(Type)+" .and. warranty_expiry >= {"+fromdate+"} .and. warranty_expiry <= {"+todate+"}"[/B]
[B]End If[/B]

[den1s]

Re: Search for 'ALL' entries from Dropdown

Worked like a charm. Thanks Tim.

[den1s]

Re: Search for 'ALL' entries from Dropdown

Tim,

Making this even more useful, I have another field in the same dialog called 'OWNERSHIP_TYPE' which is a dropdown field. How can i add the data selected from this dropdown to be included in the overall filter?

I did try using two 'else' commands, but it did not seem to work. But it was probably me.

What do you think?

Denis

[den1s]

Re: Search for 'ALL' entries from Dropdown

I have part of it working, but not all.

If alltrim(ownership_type) = "" then
   Filter = "type =" +quote(type)+" .and. warranty_expiry >= {"+fromdate+"} .and. warranty_expiry <= {"+todate+"}"
else
    Filter = "ownership_type =" +quote(ownership_type)+" .and. warranty_expiry >= {"+fromdate+"} .and. warranty_expiry <= {"+todate+"}"
end if

Thanks

Denis

[den1s]

Re: Search for 'ALL' entries from Dropdown

I cant seem to get the code to work properly.

A user should be able to filter by

Type, Ownership_Type and Dates
Type and Dates
or
Ownership_Type and Dates

The code I attempted does not achieve this.

Denis

[Pat Bremkamp]

Re: Search for 'ALL' entries from Dropdown

Denis,

It is helpful to break the filters down into their components, then put them together. Here's a script that shows what I mean. It makes up a filter from a multi-select dropdown (vJobList), then a date range, then a couple of sections of check box selections. At the end, they are combined into vFilter.

 
' ==== pass dates to the report ====
Dim global vgStart_Date as C = vStart_date
Dim global vgEnd_Date as C = vEnd_Date
' ==== filter for the selected jobs ====
Dim F1 as C
Dim vJobList as C
vJobList = vJob.dump()
vJobList = remove_blank_lines(vJobList)
vJobList = *for_each(x,"Job_Id = " + quote(x),vJobList)
F1 = stritran(alltrim(vJobList), crlf()," .or. ")
' ==== filter for the date range ====
dim F2 as C 
F2 = "between(date," + s_quote(convert_type(vStart_Date,"D")) + "," + s_quote(convert_type(vEnd_Date,"D")) + ")"
' ==== filter for invoiced or not ====
dim F3 as C
select
 case convert_type(vInvoiced,"L") = .t. .and. convert_type(vNot_Invoiced,"L") = .f.
 F3="Invoiced = .t."
 case convert_type(vInvoiced,"L") = .f. .and. convert_type(vNot_Invoiced,"L") = .t.
 F3="Invoiced = .f."
 case convert_type(vInvoiced,"L") = .t. .and. convert_type(vNot_Invoiced,"L") = .t. 
 F3 = ".t."
end select
' ==== filter for selected type(s) ==== 
Dim F4 as C
If convert_type(vBillable,"L") = .t.
 F4 = "(type=\"Billable\" .or. "
 else
 F4 = "("
end if
If convert_type(vCharitable,"L") = .t.
 F4 = F4 + "type = \"Charitable\" .or. "
end if
If convert_type(vBusiness,"L") = .t.
 F4 = F4 + "type = \"Business\" .or. "
end if
If convert_type(vPersonal,"L") = .t.
 F4 = F4 + "type = \"Personal\")"
else
 F4 = F4 + ".f.)"
end if 
Dim vFilter as C
vFilter = "("+F1+")"+".and."+F2+".and."+F3+".and."+F4

Pat

[den1s]

Re: Search for 'ALL' entries from Dropdown

Thanks Pat. I will give this a try tonight.