Scheduling Batch Reports with Foxfire!
Add Report Scheduling to your application in Minutes!
By Andrew
MacNeill,
The Batch Builder has been a feature of Foxfire! since 6.0 was first introduced in 1999 but how much have you really used it? A common request many developers get is can I run my reports at a scheduled time? With the Batch Builder and some minor tweaks to the FFSTART program, you can offer your users a way to run reports through the Windows Task Scheduler with minimal effort. This article shows you how.
Foxfire! can be started with two different
ways: one way is to call it and pass parameters (this is the traditional
approach).
DO FOXFIRE.APP WITH
"REQUESTS","","","FFCONFIG.PRG"
Since 3.0, users have also been able to use a
batch file (referred to as FF_BATCH) and add entries to this file. The
FFSTART.PRG program uses this approach as well:
create
cursor ff_batch;
(request C(8),;
Action C(40),;
User_ID C(15),;
User_Dept C(15),;
Config M,;
timeout n(4,0),;
MacroOK L,;
NoPrtPrmpt L,;
FltStrAls M,;
FltTitle M,;
FltArrays M,;
FltDisplay M,;
SendTo C(10),;
OutputFile M,;
DOSPrtr M,;
WinPrtr1 M,;
WinPrtr2 M,;
MacPrtr M,;
AskAtRun M,;
heading M,;
FF_Filter M,;
FF_FrxName M,;
FF_SQL M,;
FF_Count n(9,0),;
system M,;
error M,;
PrefSet C(20),;
ServerFile M,;
SqlType
C(20),;
Use_Login L,;
conHandle
n(2,0),;
servername M)
append blank
replace Action with "REQUESTS"
replace User_id
with "System"
replace config with
"ffconfig.prg"
replace Use_Login
with .t.
ENDIF
DO
("FOXFIRE")
The structure of the FF_BATCH cursor is also the
SAME structure as the files created when using the Batch Builder in Foxfire!
6.0. Select the Batch Builder option from the Tools menu to create a new batch.
Select the reports you want to run and click
Save Batch. The generated DBF file will contain the same structure as the
FF_BATCH cursor. You can open this file
from within FoxPro and change values if you like. When you want to run a batch
interactively, you would select Open Batch and then click Run Batch. All of the
queries within the batch would then be run. If they had any ask at runtime
values, they would be prompted as they were being run. Using the Batch Builder
is a great way of running month end reports and the like but now, you can take
it to the next level.
Important Fields in the Batch Cursor
To run a report, the most important fields in
the Batch file are the REQUEST and ACTION fields. The REQUEST field contains the name of the
Request to be run. The ACTION field contains the action to perform, in this case,
RUN. There is only one change that must
be made to Batch files created using the Batch Builder. You must provide the
USER_ID. In the FFSTART program above, you can see that it fills it in with
SYSTEM. This is a generic account that you can also use.
Changes to FFSTART.PRG
There are only 3 real changes and a total of
8 lines to be made to FFSTART to let it accommodate automatic execution of
batch files. The first is to replace the existing parameters to the starting
line. By default, FFSTART accepts two
parameters, one for the starting location of FOXFIRE and another as a logical
variable to show or hide the splash screen. Since you will be starting this
program from the Foxfire! folder, there is no need for the first parameter and
the second one you can hard-code yourself.
PARAMETER tcBatchFile
lcFF_home
= ""
lcNoSplash
= .T.
The parameter tcBatchFile
will refer to the location and name of the Batch file to be run.
The second change is right before the line
CREATE CURSOR FF_BATCH. Instead, put an IF ELSE statement there that refers to
the tcBatch file.
IF NOT EMPTY(tcBatchFile)
USE (tcBatchFile)
ALIAS ff_batch
ELSE
CREATE CURSOR …
ENDIF
The last required change is to ensure that
the USER_ID field is filled in the FF_BATCH file. You can do this by simply
saying
REPLACE ALL user_id WITH "SYSTEM"
With these changes to FFSTART.PRG, all you
need to do is compile the program into an executable and you're off to the
races. In my case, I've created the program as FFSCHED.EXE.
Running
Your New Foxfire! Scheduler
Once your EXE has
been built, call FFSCHED and pass it the three parameters: 1) the location of
FOXFIRE, 2) a logical variable to show or hide the splash screen and 3) the
batch file.
DO FFSCHED.EXE WITH
"C:\MYBATCH"
That's it! Now set it up to run automatically by using
the Windows Scheduled Task folder. Since it will be running as an executable,
you won't add the "DO" or WITH statements so your code might look
like
FFSCHED.EXE
C:\MYBATCH
In the Windows
Scheduled Task Folder, click on Add Scheduled Task. Your new Foxfire! Scheduler will likely not
be listed so click on Browse and find the FFSCHED.EXE that you just built. Fill
in the name as well as how often you want to run this task.
Once the Wizard is
finished, click the option to open the Advanced properties of the scheduled
item.
Specify the
parameters in the Run dialog so it can find the Batch file to run. In the above
example, it is scheduled to run C:\MyBatch. With the Scheduled Task, you can
also have it automatically log into various network drives if you need to.
Hit OK and you're
done! That's all there is to it.
Tip: AVOID Ask At Runtime Options
Now that you've got your Scheduler ready to
run, you can add Foxfire! requests to your Batch file as you need to. Remember
that Batch requests will be run sequentially and since you're running them on a
schedule, you likely don't want to have any user interaction. Your best bet
here is to avoid using the Ask At Runtime option.
If you want to run a monthly or a weekly
report, use a Data Expression instead for your Filter condition. For example,
if your Request was supposed to print out all invoices for the past month, your
filter might be Invoice Date is On or After. Instead of hard-coding a date into
it, choose the Build Expression option for your Ask At Runtime value.
Put in the
appropriate FoxPro expression for your filter. In the above case, it might be
DATE( YEAR(DATE()), MONTH(DATE() )-1, 1).
Now your report
won't be prompting the user for any values that aren't needed.
If you look at the
structure of the FF_BATCH cursor in the first part of this article, you will
see a field named TIMEOUT. If you fill in this value, Foxfire! will apply a
timeout to any dialogs that do appear to the user. This means that any ask at
runtime values should have a default value if you want the report to run.
Why Reinvent the Wheel?
Many developers we
know have spent time building their own scheduling tool and coming up with
complex ways of handling scheduled requests in Foxfire!. Before Foxfire! 6.0,
a lot of effort was required to write schedulers and plan how these requests
were being called. Now, thanks to the Batch Builder and some minor tweaks
to FFSTART, you can offer scheduled reports to your users in minutes!