I am trying to create a virtual field with DEFINE that uses the selection of a parameter in its logic. When a value like 'CANCELED' is selected it works great, but when "ALL" (foc_null) is selected the DEFINE field seems to not get created.

I've tried variations of the logic with and without quotes around the parameter but nothing seems to work properly for both selected values and "ALL" cases.

  

DEFINE FILE CLUBMEMBERS

BypassDate/MDYY=
	IF ( &CLUBSWITCHTYPEFILTER EQ '_FOC_NULL' )  THEN '01/01/1920' ELSE
	IF ( &CLUBSWITCHTYPEFILTER EQ 'CANCELED' )   THEN '&CLUBMEMBERDATEFROM' ELSE '01/01/1920';

END

TABLE FILE CLUBMEMBERS
PRINT
BypassDate

0 ERROR AT OR NEAR LINE 95 IN PROCEDURE club_member_movement_report_fex_v1FOCEXEC * (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: _FOC_NULL 0 ERROR AT OR NEAR LINE 120 IN PROCEDURE club_member_movement_report_fex_v1 (FOC003) THE FIELDNAME IS NOT RECOGNIZED: BypassDate BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT


Any ideas?

This message has been edited. Last edited by: Kerry,

Try determining the Bypass Date in Dialogue Manager, then use it in the DEFINE. The FOCNULL is active in the DEFINE (preventing the line of code from being parsed) but will not be in Dialogue Manager.

-SET &BYPASSDATE = 
-  IF ( &CLUBSWITCHTYPEFILTER EQ '_FOC_NULL' ) THEN '01/01/1920' ELSE
-  IF ( &CLUBSWITCHTYPEFILTER EQ 'CANCELED' )  THEN &CLUBMEMBERDATEFROM ELSE '01/01/1920';


DEFINE FILE CLUBMEMBERS
BypassDate/MDYY = &BYPASSDATE;
END

By the way, you may need to use the WITH attribute for the defined field...

DEFINE FILE CLUBMEMBERS
BypassDate/MDYY WITH real-field = &BYPASSDATE;
END

You will be better off using COMPUTE instead of DEFINE:

TABLE FILE CLUBMEMBERS
PRINT
COMPUTE BypassDate/MDYY = &BYPASSDATE;
...
END

Thank you for the post. I believe a COMPUTE will not work for me in that I need to use this is a WHERE clause.

I am trying the following:

-SET &BYPASSDATE = 
-  IF ( &CLUBSWITCHTYPEFILTER EQ '_FOC_NULL' ) THEN '01/01/1920' ELSE
-  IF ( &CLUBSWITCHTYPEFILTER EQ 'CANCELED' )  THEN &CLUBMEMBERDATEFROM ELSE '01/01/1920';

DEFINE FILE CLUBMEMBERS
BypassDate/MDYY = &BYPASSDATE;
 
TABLE FILE CLUBMEMBERS
PRINT BypassDate

and while syntax is good, it appears all results for the defined BypassDate are blank, and not the value just set.

Perhaps because

BypassDate/MDYY = &BYPASSDATE;

is incorrect. It should be

BypassDate/MDYY = '&BYPASSDATE';

I didn't plan on debugging all the posted code.

Also, a DEFINE is wrong if you "need to use this in a WHERE clause". The WHERE clause should use the Dialogue Manager variable. Try

WHERE TEST_DATE GT '&BYPASSDATE';

Of course, the code may not simply be something like this, because, depending on the data source, you may need to use a function, like this:

WHERE DUEDATE FROM DT(&DTTM_FROM) TO DT(&DTTM_TO);

, or convert the / to - or make the date an integer or

I wouldn't create a constant DEFINE field that will only be used in a WHERE clause. With DEFINE, you're creating the field for every row in the input table, which may be inefficient.

Thanks again, and just to close the loop, here's the final (edited) version of what worked. Essentially this 'hack' allowed the report to bypass a "From Date" parameter on specific filter selections.

 

...
SET ALL = PASS

-SET &BYPASSDATE = 
-  IF ( &CLUBSWITCHTYPEFILTER EQ '_FOC_NULL' )     THEN '01/01/1900' ELSE
-  IF ( &CLUBSWITCHTYPEFILTER EQ '''ON HOLD''' )   THEN '01/01/1900' ELSE &CLUBMEMBERDATEFROM;

DEFINE FILE CLUBMEMBERS
BypassDate/MDYY = '&BYPASSDATE';

END

TABLE FILE CLUBMEMBERS
PRINT 
     ...
	 
WHERE CLUBMEMBERS.CLUBMEMBERS.DATEADDED GE '&BYPASSDATE';
...


 

Paul,

It's not really a hack, just, unfortunately, one of the only ways to deal with _FOC_NULL.

By the way, do you really need the DEFINE. It can be a COMPUTE if you need it in the report.

Well since you're dealing with DM and DM-parameters. You should DM handle it. Not Focus.

-IF &CLUBSWITCHTYPEFILTER EQ _FOC_NULL THEN L_FOCNULL ELSE CONTINUE;
-IF '&CLUBSWITCHTYPEFILTER' EQ 'CANCELED' THEN L_CANCELED ELSE CONTINUE;

-*
-*  Do your define here for default situation.
-*
-GOTO L_END

-L_FOCNULL
-*
-*  Do your define here for '_FOC_NULL' situation.
-*
-GOTO L_END

-L_CANCELED
-*
-*  Do your define here for 'CANCELED' situation.
-*
-GOTO L_END

-L_END

G'luck

All you needed to really do was to put quotes around &CLUBSWITCHTYPEFILTER and you could have kept your define without dialogue manager.

Without quotes, the literal value is inserted rather than the variable.

Perhaps, but creating a DEFINE field for filtering is not efficient.