FOCUS Set two parameters to resultant values of query

[robert fuschetto]

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

[Martin Yergeau]

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

[robert fuschetto]

THANKS!

[robert fuschetto]

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,,,

 

 

[David Beagan]

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.

[robert fuschetto]

..will try...

[robert fuschetto]

that worked!

 

Thanks all!

😀