Hi, I need to have this file read a stored procedure and determine if the PP_NUMBER is first in the file and if it is, then run the proper foc determine by the role field using dialogue manager. For this code example, the PP_NUMBER is forced and the role is being assigned a variable &who. When running this it is running the HTML form(if record EQ 0) which is not true for this example. Pulse4a should run. Any help would be appreciated.

Thanks

  -* File roletest.fex
-DEFAULT &ppnum = 'PT26621'
-*-SET &ppnum = 'USERID2'


ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.ROLE
;

-* Checks if logon is in the table

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
-SET &who = Role
TABLE FILE SQLOUT
PRINT
PP_NUMBER
Role
WHERE PP_NUMBER EQ '&ppnum'
ON TABLE HOLD AS HOLD1
END

-RUN

-* Checks if it exists
-IF &LINES NE 0 AND &who EQ 'Core Service' THEN GOTO RUNREC;
-HTMLFORM nodata
-EXIT

-RUNREC
-INCLUDE pulse4a


DEFINE FILE SQLOUT
USERID/A8='&IBIC_user';
USERID9/A8=(USERID);
USERNUM/A6=EDIT(USERID9, '$$999999');
USERID2/A8=UPCASE(7,USERID9,'A7');

END

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

Since you -SET &who = Role ; (which requires the semi-colon), your -IF test will always invoke -HTMLFORM nodata.

How do I correct this? Role is a field that determines what focexec will run. There can be up to 7 types of roles (which I was going to have up to 7 if statements calling 7 focexec). Can I have a field name in the dialogue manager statement?

Try this:

TABLE FILE SQLOUT
PRINT
PP_NUMBER
Role
WHERE PP_NUMBER EQ '&ppnum';
ON TABLE SAVE AS PPROLE
END

-RUN
-SET &PPLINES = &LINES ;
-READ PPROLE &PPNUM.A7 &WHO.A<length of Role here>

-* Checks if it exists
-IF &PPLINES NE 0 AND &WHO EQ 'Core Service' THEN GOTO RUNREC ;

Thanks for the reply however, keep getting HTML form

 -* File roletest.fex
-DEFAULT &ppnum = 'PT26621'
-*-SET &ppnum = 'USERID2'


ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.ROLE
;

-* Checks if logon is in the table

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
-SET &WHO = Role;
TABLE FILE SQLOUT
PRINT
PP_NUMBER
Role
WHERE PP_NUMBER EQ '&ppnum';
ON TABLE SAVE AS PPROLE
END

-RUN
-SET &PPLINES = &LINES ;

-READ PPROLE &PPNUM.A9 &WHO.A25

-* Checks if it exists
-IF &PPLINES NE 0 AND &WHO EQ 'Core Service' THEN GOTO RUNREC ;


-HTMLFORM nodata
-EXIT

-RUNREC
-INCLUDE pulse4a 

First delete the line -SET &WHO = Role;

After the -READ line put these lines so you can see what values are being generated:

-TYPE &PPNUM &WHO
-EXIT

I added the type command and got this:

TOTAL
000007P T26612 000012Core Service

 -* File roletest.fex
-DEFAULT &ppnum = 'PT26612'
-*-SET &ppnum = 'USERID2'


ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.ROLE
;

-* Checks if logon is in the table



TABLE FILE SQLOUT
PRINT
PP_NUMBER
Role
WHERE PP_NUMBER EQ '&ppnum';
ON TABLE SAVE AS PPROLE
END

-RUN
-SET &PPLINES = &LINES ;
-READ PPROLE &PPNUM.A7 &WHO.A26
-TYPE &PPNUM &WHO
-EXIT


-* Checks if it exists
-IF &PPLINES NE 0 AND &WHO EQ 'Core Service' THEN GOTO Serv ;
-HTMLFORM nodata
-EXIT

-Serv
-INCLUDE pulse4a
-EXIT

-Sales
-INCLUDE pulse18a
-EXIT


END 

It appears you are extracting more than just PP_NUMBER and Role from your stored procedure. I suggest you remove PP_NUMBER from the TABLE request and experiment with the -READ until you obtain the desired result. For example, you could start with this code:

-* File roletest.fex
-DEFAULT &ppnum = 'PT26612'

ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.ROLE
;

-* Checks if logon is in the table

TABLE FILE SQLOUT
 PRINT Role
 WHERE PP_NUMBER EQ '&ppnum';
    ON TABLE SAVE AS ROLESAVE
END

-RUN
-SET &PPLINES = &LINES ;
-READ ROLESAVE &WHO.A26
-TYPE &WHO
-EXIT

If &WHO = "000012Core Service", similar to your last post, then change the -READ to:

-READ ROLESAVE &X.A6 &WHO.A20

Experiment with the lengths of variables &X and &WHO in the -READ until variable &WHO contains only the value "Core Service". Then remove the -TYPE and -EXIT statements and your subsequent -IF statement should work.

Don't know exactly where the word TOTAL comes from in your result, but the rest comes from the TABLE. Apparently both fields (PP_NUMEBR and Role) are defined as VARCHAR, which is why these numbers (actually the real length of the fields) is being stored in the SAVE file. These lengths alway are 6 digits, so you can just skip these characters. Your statement then would read something like:

-READ PPROLE &x.6. &PPNUM.7. &x.6. &WHO.A26.
-TYPE &PPNUM &WHO

I believe I figured it out thanks to Dan! I basically Defined the fields again and Read off of the new defines. Used Dan's first example and it worked fine! Thanks for all the suggestions. Much appreciated.

 -* File roletest.fex
-DEFAULT &ppnum = 'PP09319'
-*-SET &ppnum = 'USERID2'


ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.ROLE
;

-* Checks if logon is in the table
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END

DEFINE FILE SQLOUT
ROLEA/A26=(Role);
PPNUME/A7=(PP_NUMBER);
END

TABLE FILE SQLOUT
PRINT
PPNUME
ROLEA
WHERE PPNUME EQ '&ppnum';
ON TABLE SAVE AS PPROLE FORMAT ALPHA
END

-RUN
-SET &PPLINES = &LINES ;
-READ PPROLE &PPNUM.A7 &WHO.A26




-* Checks if it exists
-IF &PPLINES NE 0 AND &WHO CONTAINS 'Core Service' THEN GOTO Serv ;
-IF &PPLINES NE 0 AND &WHO CONTAINS 'Core Sales' THEN GOTO Sales ;
-HTMLFORM nodata
-EXIT

-Serv
-INCLUDE pulse4a
-EXIT

-Sales
-INCLUDE pulse18a
-EXIT


END