robert fuschetto Posted July 22 Share Posted July 22 Instead of prompting for FISCALYEAR and PERIOD (&FISCALYEAR / &PERIOD) I want to derive &FISCALYEAR / &PERIOD for the user then use in subsequent WHERE conditions . How can I execute the code below to get the desired values without displaying the query results and then set &FISCALYEAR to the resultant TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.FISCALYEAR and the same for &PERIOD. TABLE FILE TBLMSTR_CALENDARPERIOD PRINT TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.FISCALYEAR TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.PERIOD WHERE TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.ROLLINGMONTHS EQ -4; ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL END Link to comment Share on other sites More sharing options...
Martin Yergeau Posted July 22 Share Posted July 22 (edited) TABLE FILE TBLMSTR_CALENDARPERIOD PRINT TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.FISCALYEAR TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.PERIOD WHERE TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.ROLLINGMONTHS EQ -4; ON TABLE HOLD AS YRPER END -RUN -READFILE YRPER -DEFAULTH &FISCALYEAR = '' -DEFAULTH &PERIOD = '' -TYPE FISCALYEAR = &FISCALYEAR -TYPE PERIOD = &PERIOD The above will automatically generates two variables (&FISCALYEAR, &PARIOD) having the same name as the table fields where their values are what you are looking for. Those variables can be used where ever you want. The -TYPE are there only to display the variable's value Edited July 22 by Martin Yergeau 1 Link to comment Share on other sites More sharing options...
robert fuschetto Posted July 22 Author Share Posted July 22 THANKS! Link to comment Share on other sites More sharing options...
robert fuschetto Posted July 22 Author Share Posted July 22 MARTIN...QUESTION! I tried to dumb thecode down as much as possible to illustrate the error I get when I your code in. Note the fex runs fine. I just can no longer access the GUI! This code is sort of just a few pieces stripped down to where it really makes no sense..but to illustrate a point: -*APP HOLD foccache -*TABLE FILE TBLMSTR_CALENDARPERIOD -*PRINT -* TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.FISCALYEAR -* TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.PERIOD -*WHERE TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.ROLLINGMONTHS EQ -4; -*ON TABLE HOLD AS HOLDFYPERIOD -*END -*-RUN -* -*-READFILE HOLDFYPERIOD -*-DEFAULTH &FISCALYEAR = '' -*-DEFAULTH &PERIOD = '' -*FUTURE BUDGET JOIN INNER TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.RPTBANBR IN tblmstr_budgetedunitsbymonthsummarized TO UNIQUE TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.RPTBANBR IN tblmstr_banbrbanamexref TAG J0 AS J0 END TABLE FILE TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED BY LOWEST TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.RPTBANBR WHERE ( TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.FISCALYEAR EQ &FISCALYEAR.(FIND TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.FISCALYEAR,TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.FISCALYEAR IN TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED|FORMAT=I11).FISCALYEAR:. ); ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE HOLD AS HOLD_FUTUREBUDGET FORMAT ALPHA ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty, $ ENDSTYLE END -* ACTUALS JOIN INNER TBLMSTR_UNITSWRVUSSMRY.TBLMSTR_UNITSWRVUSSMRY.RPTBANBR IN tblmstr_unitswrvussmry TO UNIQUE TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXBANBR IN tblmstr_banbrbanamexref TAG J1 AS J1 END TABLE FILE TBLMSTR_UNITSWRVUSSMRY BY LOWEST TBLMSTR_UNITSWRVUSSMRY.TBLMSTR_UNITSWRVUSSMRY.RPTBANBR ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty, $ ENDSTYLE END The code runs fine. I can access the GUI. Note the code to substitute FISCALYEAR is commented out this so it prompts me for a FISCALYEAR. If I run this: APP HOLD foccache TABLE FILE TBLMSTR_CALENDARPERIOD PRINT TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.FISCALYEAR TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.PERIOD WHERE TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.ROLLINGMONTHS EQ -4; ON TABLE HOLD AS HOLDFYPERIOD END -RUN -READFILE HOLDFYPERIOD -DEFAULTH &FISCALYEAR = '' -DEFAULTH &PERIOD = '' -*FUTURE BUDGET JOIN INNER TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.RPTBANBR IN tblmstr_budgetedunitsbymonthsummarized TO UNIQUE TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.RPTBANBR IN tblmstr_banbrbanamexref TAG J0 AS J0 END TABLE FILE TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED BY LOWEST TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.RPTBANBR WHERE ( TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.FISCALYEAR EQ &FISCALYEAR.(FIND TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.FISCALYEAR,TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED.FISCALYEAR IN TBLMSTR_BUDGETEDUNITSBYMONTHSUMMARIZED|FORMAT=I11).FISCALYEAR:. ); ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE HOLD AS HOLD_FUTUREBUDGET FORMAT ALPHA ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty, $ ENDSTYLE END -* ACTUALS JOIN INNER TBLMSTR_UNITSWRVUSSMRY.TBLMSTR_UNITSWRVUSSMRY.RPTBANBR IN tblmstr_unitswrvussmry TO UNIQUE TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXBANBR IN tblmstr_banbrbanamexref TAG J1 AS J1 END TABLE FILE TBLMSTR_UNITSWRVUSSMRY BY LOWEST TBLMSTR_UNITSWRVUSSMRY.TBLMSTR_UNITSWRVUSSMRY.RPTBANBR ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty, $ ENDSTYLE END It still runs fine. it no longer prompts for FISCALYEAR but rather is using the value form the first query. YEA! But the issue is that I can't get into the GUI anymore. I double click the fex and am met with this: THE GUI no longer allows me to open and view REPORTS after the first JOIN, In other words, I can see the GUI'ized report for setting the variables. I can see the GUI-ized report for the FUTURE BUDGET...but every REPORT thereafter you get that error...NO GUI Just code. Note in this example I only show one report thereafter: ACTUALS. As soon as I put the comments back..all is good! Any ideas,,, Link to comment Share on other sites More sharing options...
Solution David Beagan Posted July 22 Solution Share Posted July 22 Workaround could be to put the code TABLE FILE TBLMSTR_CALENDARPERIOD PRINT TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.FISCALYEAR TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.PERIOD WHERE TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.ROLLINGMONTHS EQ -4; ON TABLE HOLD AS HOLDFYPERIOD END -RUN -READFILE HOLDFYPERIOD -DEFAULTH &FISCALYEAR = '' -DEFAULTH &PERIOD = '' In one fex, and then have it -INCLUDE the code from your main GUI report. Link to comment Share on other sites More sharing options...
robert fuschetto Posted July 22 Author Share Posted July 22 ..will try... Link to comment Share on other sites More sharing options...
robert fuschetto Posted July 22 Author Share Posted July 22 that worked! Thanks all! 😀 2 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now