form -> live report

[sandersbrett]

I currently have a live report that accepts four fields. However, its full of AND statements, which requires the user to input all four fields in my form. How to i change my sql statement to perform and AND on only the data entered into the form. i.e. If the user enters fields 1 and 3, the sql queries for all records with 1 & 3.

 

[appnair]

As an eg see this query--

select dataid,parentid,subtype from dtree where dataid=:A1 and subtype=:A2 and parentid=:A3 and name=:A4

So for eg if user has boxes for dataid,subtype,parentid and name and if user puts 12345 in dataid and 144 in there you would like your query to become

 select dataid,parentid,subtype from dtree where dataid=12345 and subtype=144

I do not know how flexible the OT live report interface is .I think it expects all four to be there.I am thinking of a weird possibility here.You could perhaps create some live reports that uses some permutation combination of the above.Using a standard html code and javascript you could create a case statement and execute one of those live reports.A more elegant solution would be to looks the form and create the SQL on the fly which IMHO should never be allowed to execute from a form.Do post in the web reports discussion to see if the bright guys there has some solution for you

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[ggriffit]

Brett,
Can you provide the SQL that you are using and some more information to help us understand your issue ?

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[sandersbrett]

here is my sql in my live report

select * from po_cattbl, dtree where po_cattbl.dataid=dtree.dataid and (po_cattbl.PONumber >=((%1)-10)) and(po_cattbl.PONumber <=((%1)+90)) and(po_cattbl.RigNum=%2) and (lower(VendorName) LIKE ('%%' + lower(%3) + '%%')) and(dtree.subtype=144) and(dtree.ownerid <> '18328') order by PONumber asc

i would like the user to be able to input PONumber, RigNum, and or vendorname, or any combination of the above.