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,
WebFocus 7.7.02 WinXP
January 26, 2010, 03:27 PM
Dan Satchell
Since you -SET &who = Role ; (which requires the semi-colon), your -IF test will always invoke -HTMLFORM nodata.
WebFOCUS 7.7.05
January 26, 2010, 03:41 PM
JOE
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?
WebFocus 7.7.02 WinXP
January 26, 2010, 03:56 PM
Dan Satchell
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 ;
WebFOCUS 7.7.05
January 26, 2010, 04:10 PM
JOE
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
WebFocus 7.7.02 WinXP
January 26, 2010, 04:18 PM
Dan Satchell
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
WebFOCUS 7.7.05
January 26, 2010, 08:20 PM
JOE
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
WebFocus 7.7.02 WinXP
January 27, 2010, 04:39 AM
Dan Satchell
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.
WebFOCUS 7.7.05
January 27, 2010, 05:00 AM
GamP
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:
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