>>>The parenthesis are to keep its order of operations straight. You do need to be careful with them and OR conditions to be sure you receive the correct results.
>>>When I have optional parameters, they're usually to filter the result set, so not sending that parameter would have no effect. The WHERE clause in that case looks like IIF(EMPTY(?vp_optional, .T., table.optional = ?vp_optional). I don't use NULLs, but you should be able to adapt that for them.
>>>
>>>Chris.
>>
>>Hi Chris,
>>
>>Are you able to put it in the View Designer? Could you please post one of your views using this technique so I may try to adopt it for my needs?
>>
>>Right now I'm playing with my original idea from the program. I was unable to save this view in View Editor.
>
>This is my best/worst view, depending on how you look at it. All it does is create a list of records that meet six fully optional specified conditions. The last two options pull records from other views (or not).
>
>SELECT Enroll.enrollid;
> FROM ;
> smagic!enroll;
> WHERE ( ( ( ( ( IIF(EMPTY(?vp_scheduleid),.T.,Enroll.scheduleid=(?vp_scheduleid)) ;
> AND IIF(EMPTY(?vp_programid),.T.,Enroll.programid=(?vp_programid)) );
> AND IIF(EMPTY(?vp_startdateid),.T.,Enroll.startdateid=(?vp_startdateid)) );
> AND IIF(EMPTY(?vp_enddateid),.T.,Enroll.enddateid=(?vp_enddateid)) );
> AND ( EMPTY(?vp_actionid) ;
> OR Enroll.enrollid IN (SELECT enrollid FROM v_actionflag) ) );
> AND ( EMPTY(?vp_hasflag) ;
> OR Enroll.enrollid IN (SELECT enrollid FROM v_mustflag) ) );
> AND Enroll.enrollid NOT IN (SELECT enrollid FROM v_notflag)
>
Interesting. You put it in the View Designer, correct? I think, it is better than my own view, I'm going to play with your's idea now. Too bad I already spent so much energy trying to make my own work (and it works, BTW :))
If it's not broken, fix it until it is.
My Blog