Help with a Filter

[den1s]

Just a quicky. I cannot seem to get this bit of code to work together. Can someone spot where i have gone wrong?

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

* vDSR is a Dropdown (Character Field)
* Type is a Dropdown (Character Field)
* fromdate is a Date Field
* todate is a Date Field

A user should be able to filter by

Type, vDSR and Dates
Type and Dates
or
vDSR and Dates

Thanks

Denis

[peteconway]

Re: Help with a Filter

I'd use the report genie in the dialog to create the filter then use it for your purposes, it gets the syntax right everytime.

AfterValidate>Insert>Genie>Print Report

[den1s]

Re: Help with a Filter

Pete,

That was very very useful. Im surprised i got this far without realising the power of the genie.

I did what you advised and got the code below. The only problem is i cannot get the dropdown field to also work on their own. e.g. just DSR or just Type or just fromdate - todate.

IF eval_valid("vDSR") = .f. .or. eval_valid("Type") = .f. .or. eval_valid("fromdate") = .f. .or. eval_valid("todate") = .f. Then
	goto skipreport
end if 
dim filter as c 
dim order as c 
Filter = "(Dsr = \""+vDSR+"\"  )  .and. (Type = \""+Type+"\"  ) .or. (between(Date_disp_cert,ctod(\""+convert_type(fromdate,"C")+"\"),ctod(\""+convert_type(todate,"C")+"\")) )"
Order = ""
Dim filename as c 
filename = session.session_folder + "tempreport.pdf"
filename = report.saveas("rptWarranty_Rep@[PathAlias.ADB_Path]\assets.ddd","pdf",filter,order,filename,.f.)
if file.exists(filename) 
	currentform.RedirectTarget = session.session_url + "tempreport.pdf?" +  time("hms3")
end if 	
skipreport:

I tried adding .or. between the vDSR and Type but no success. Well yes the dropdown filter worked, but when you wanted to use both vDSR and Type together it gave the wrong results. I almost need an intelligent and/or depending whether the dropdown was used or not.

I also attempted to use ALL|* in the dropdown selection, but again didnt work.

What do you think?

Denis

[Pat Bremkamp]

Re: Help with a Filter

Denis,

You can use a series of if statements and build the filter step by step. For example,

 
if type="All"
filter=.t.
else
filter="Type = \""+Type+"\"" 
end if
 
if alltrim(vDSR)=""
filter=filter
else
filter=filter+".and. DSR = \""+vDSR+"\""
end if
 
filter=filter+".or. ....

Pat

[peteconway]

Re: Help with a Filter

Pat is right, build them as he shows, works well.
Peter

[den1s]

Re: Help with a Filter

Pat,

Thank you for the example. Have i structured this code in the correct way, so that your code works? Unfortunately, the Type and vDSR do not work together, but do work on their own. I also am not sure how i will construct the between dates for this to work.

IF eval_valid("vDSR") = .f. .or. eval_valid("Type") = .f. .or. eval_valid("fromDate") = .f. .or. eval_valid("toDate") = .f. Then
	goto skipreport
end if 

dim filter as c 
dim order as c 

Filter = "(Dsr = \""+vDSR+"\"  )  .or. (Type = \""+Type+"\"  ) .or. (between(Date_disp_cert,ctod(\""+convert_type(fromdate,"C")+"\"),ctod(\""+convert_type(todate,"C")+"\")) )"

if type="All"
filter=.t.
else
filter="Type = \""+Type+"\"" 
end if
 
if vDSR="All"
filter=.t.
else
filter="dsr = \""+vDSR+"\"" 
end if

if alltrim(Type)=""
filter=filter
else
filter=filter+".and. type = \""+Type+"\""
end if

if alltrim(vDSR)=""
filter=filter
else
filter=filter+".and. DSR = \""+vDSR+"\""
end if


Order = "Date_disp_cert"
Dim filename as c 
filename = session.session_folder + "tempreport.pdf"
filename = report.saveas("rptWarranty_Rep@[PathAlias.ADB_Path]\assets.ddd","pdf",filter,order,filename,.f.)
if file.exists(filename) 
	currentform.RedirectTarget = session.session_url + "tempreport.pdf?" +  time("hms3")
end if 	
skipreport:

Thank you very much. It will be great to get this one closed soon.

Denis

[den1s]

Re: Help with a Filter

Bump!

Can anyone please help me with this?

[peteconway]

Re: Help with a Filter

This should sort you out.

 query.Filter =  "Client_id= \""+Client_Code + chr(34) + " .and.  between(ctod(Date_Shipped),ctod(\""+From_Date+"\"),ctod(\""+To_Date+"\"))"

[den1s]

Re: Help with a Filter

Hi Peter, thanks for the code.

I have looked at it, but am not sure how that is going to help me. I have 4 fields in total on the page;

DSR
Type
fromDate
toDate

They should be able to all work together or independantly of eachother.

Do you know how i can incorporate yours and Pats code to achieve this.

Massive Brick Wall For Me At The Mo.

Any further help is really appreciated.

Denis

[peteconway]

Re: Help with a Filter

vDSR is a Dropdown (Character Field) is this a multiSelect?

[den1s]

Re: Help with a Filter

My four fields on the page are;

vDSR = Dropdown (Single select)
Type = Dropdown (Single Select)
fromDate = Textbox with Calendar icon
toDate = Textbox with Calendar icon

Denis

[peteconway]

Re: Help with a Filter

query.Filter =  "Dsr= \""+vDSR + chr(34) + " .OR. "Type= \""+Type + chr(34) + " .OR.  between(ctod(Date_disp_cert_[B]str[/B]),ctod(\""+fromdate+"\"),ctod(\""+todate+"\"))"

Inthe table
Date_disp_cert_str is a character field (not a date) This can be a calculated field based on a date and converted to string as a field Rule; e.g. Date_disp_cert_str =dtoc(Date_disp_cert) or just change Date_disp_cert to a "C" Character Field.

fromdate and todate a dates converted to string for the search (it's just one of those things that need to be done), but are set as Date (D) variables in the dialog to enable the calander etc. Ensure the settings are dd/mm/yyyy as the default is mm/dd/yyyy and it will trip you up everytime.


It's a little hard to really make it work without the table, so if you really get stuck send it to me.

Peter

[den1s]

Re: Help with a Filter

Peter,

Thank you for the extra info. I just cannot seem to put this all together.

I have attached this part of the database for you to look at.

Really appreciate your support on this one.

Thanks again.

Denis

[peteconway]

Re: Help with a Filter

I have done a new dialog and set up etc. they are in the webprojects. The dialog is called answer.

[peteconway]

Re: Help with a Filter

Here is the code if something goes wrong. Hope this helps, I know what it feel like to be stuck and under pressure.

Peter

IF eval_valid("VDsr") = .f. .or. eval_valid("VType") = .f. .or. eval_valid("VFromDate") = .f. .or. eval_valid("VToDate") = .f. Then
	goto skipreport
end if 
dim filter as c 
dim order as c 

Filter = "(Dsr = \""+VDsr+"\"  )  .or. (Type = \""+VType+"\"  ) .or. (between(Date,ctod(\""+convert_type(VFromDate,"C")+"\"),ctod(\""+convert_type(VToDate,"C")+"\")) )"
Order = "Dsr+cdate(Date)+Type"
Dim filename as c 


if Show_as ="PDF" then
 filename = session.session_folder + "tempreport.pdf"
 filename = report.saveas("rptWarranty_Rep@[PathAlias.ADB_Path]\assets.ddd","pdf",filter,order,filename,.f.)
 if file.exists(filename)  
	 currentform.RedirectTarget = session.session_url + "tempreport.pdf?" +  time("hms3")
 end if 	
end if


if Show_as ="HTML" then    
 filename = session.session_folder + "tempreport.html"
 filename = report.saveas("rptWarranty_Rep@[PathAlias.ADB_Path]\assets.ddd","html",filter,order,filename,.f.)
 if file.exists(filename) 
 	currentform.RedirectTarget = session.session_url + "tempreport.html?" +  time("hms3")
 end if 	
end if

skipreport: