- Email: [email protected]
Spreadsheet-based user-interface for a crop model
Computers and Electronics in Agriculture, 5 ( | 991 ) 3 l 5 - 3 2 5
315
Elsevier Science Publishers B.V., A m s t e r d a m
Spreadsheet-based user-interface for a crop model Thomas A. Costello, Judy L. Costello, Karl W. VanDevender and James A. Ferguson Biological and Agricultural Engineering Department, University olA rkansas, Fayetteville, A R 72701, U.S.A. (Accepted 8 August 1990)
ABSTRACT Costello, T.A., Costello, J.L., VanDevender, K.W. and Ferguson, J.A., 1991. Spreadsheet-based userinterface for a crop model. Comput. Electron. Agric., 5:315-325. A user-interface was developed to facilitate simulation of rice crop production using the Arkansas Rice Model ( A R M O D ) . The interface was programmed using spreadsheet software. Initially, the inlcnded users were researchers !nvolved in calibrating and validating ARMOD. The interface will be adapted for use by rice farmers in making management decisions. The interface provided interactive creation of input files prior to simulation and then displayed model predictions in tabular and graphical formats. Although the interface was functional, the spreadsheet software was cumbersome to program and execution was relatively slow.
INTRODUCTION
Historically, mathematical models of crops have been used for research purposes. The increased capability and affordability of the personal computer (PC) has enhanced the feasibility of adapting crop models for on-farm use by crop production managers in testing crop management strategies. Utilizing the PC, one can access crop models more readily due to a simpler interactive operating system, dedicated graphics displays and printers, immediate program execution, and elimination of time-sharing costs associated with main-frame computers. Parker ( 1986 ) described the procedure for executing a corn model (CERESMaize) on a PC. The program did not have an interactive user-interface, and a line editor was recommended for modification of input files. In executing a rice model (CERES-Rice) on a PC, Alocilja and Ritchie (1987) provided interactive capabilities for: (a) selecting among various default management, 0168-1699/91/$03.50
© 1991 - - Elsevier Science Publishers B.V.
316
T.A. COSTELLO ETAL.
soil, and weather files, (b) modifying selected management details, and (c) specifying the frequency of three groups of output data. Boote et al. (1989) improved the usefulness of results from a soybean model (SOYGRO) by providing a graphics interface to display various relationships computed within the model. McKinion and L e m m o n ( 1985 ) used an expert system (COMAX) to act as a front-end to a cotton model (GOSSYM) in which multiple simulations were executed automatically by sequentially creating input data files, executing the model, and examining the results. The Arkansas Rice Model ( A R M O D ) was developed as a component in an overall management support system for rice producers (Costello et al., 1988). The predictions from A R M O D can be used to select optimal timing of agronomic practices (such as fertilization and pesticide application) and to test proposed management strategies. During initial calibration and validation of ARMOD, an interface was needed to facilitate the numerous simulations to be performed by researchers. Following calibration and validation, A R M O D will be made available for use by farmers; hence, an effective interface was needed for non-scientist users as well as researchers. OBJECTIVE
The objective of the research was to develop a user interface for A R M O D (programmed using spreadsheet software) to be used initially by researchers in calibrating and validating the model. Spreadsheet software was chosen because of its inherent capabilities, such as built-in graphics and macro-programming, in order to reduce software development time. This paper outlines the structure and function of the interface and evaluates its potential usefulness as a tool for crop simulation by scientists and crop producers. DESCRIPTION OF USER-INTERFACE
The user-interface for A R M O D was written using commercial spreadsheet software (1-2-3, Version 2.01, Lotus Development Corp.~). Two separate spreadsheets, one for input and another for output, were created and linked to execute ARMOD (Fig. 1 ). The following batch program was executed from MS-DOS to automatically proceed through each step in the simulation: COPY IN. WK1 AUTO123.WK1 123.EXE ARMOD.EXE COPY OUT.WK1 AUTO123.WK1 123.EXE The batch program utilizes the auto-execute spreadsheet, AUTO 123.WK1, to ~Mention of specific products does not imply endorsement by the University of Arkansas.
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
USER
SOFTWARE
[INPUT
LECT
31 "/
DATA FILES
J
~-
, JS'R=A SREETI i
PUT R ARMOD ~ C E M O D E L 1" OD. EXE I
J
F __]
I
OUTPUT FILES I
FRO= A OO J
Z[EWS
--
Io TPUT
i
SPR=ADS===T I !OUT
Fig. 1. Diagram of batch file program flow in automatically executing ARMOD with the userinterface spreadsheets.
load and begin executing the input and output spreadsheets in proper sequence. The function of the input spreadsheet is to create the input data files required by ARMOD and the function of the output spreadsheet is to read ARMOD's output files. The data file structure of ARMOD is outlined below. Strltclttre Of ARMOD
A list of input files required by ARMOD is given in Table 1. A simulation initialization file, INITSIM.DAT, is accessed first to read the starting and ending days of the simulation and the names of the other input files. The path to INITSIM.DAT can be passed to the program on the command line. The other data files can have any name and those names selected are listed in INITSIM.DAT. The crop status file and soil status file define the values of all state variables on the starting day of the simulation. Additional computed data are periodically appended onto these files throughout the simulation by ARMOD. The crop parameter file and soil parameter file contain values of all process-related variables used by ARMOD. Since these values are not numerically coded directly into ARMOD, researchers have flexibility in modifying the model without recompiling the program. The aerial environment file and field schedule file contain the environmental and management conditions to be imposed during the simulation. The output frequency file specifies how often various groups of data will be written to output files by ARMOD. There are seven output files created by ARMOD, as listed in Table 2. Simulation results can be written to any file on any day selected by the user (and
318
T.A. COSTELLO ET AL.
TABLE 1 List of input files for A R M O D Default file name 0_ ( 1 ) INITSIM.DAT
(2) CROPSTAT.DAT (3) SOILSTAT.DAT (4) AERENV.DAT
(5) FLDSCHED.DAT (6) CROPPARM.DAT (7) SOILPARM.DAT
Contents Initial information: start day, stop day, input and output file names Crop status at start of simulation, includes mass, composition, leaf area, etc. Soil status at start of simulation, includes soil water content, soil nitrogen, etc. Aerial environment data for each day of simulation, includes air temperature, solar radiation, etc. Field schedule data, listing details for each irrigation or fertilization event Crop parameter values used in simulating crop growth processes, including factors for photosynthesis, translocation, senescence, etc. Soil parameter values used in simulating soil processes, including factors for evapotranspiration and nitrogen losses, etc.
TABLE 2 List of output files from ARMOD Default file name
Contents
( 1) (2) (3) (4) (5) (6) (7)
Model predictions for canopy photosynthesis Model predictions for crop growth dynamics Model predictions for crop nutrient use Crop status throughout the simulation Model predictions for soil nitrogen processes Soil status throughout the simulation Model predictions for soil water processes
CANPHOTO.DAT CROPGRO.DAT CROPNUT.DAT CROPSTAT.DAT SOILNIT. DAT SOILSTAT.DAT SOILWAT.DAT
defined in the output frequency file - - an input file ). The crop and soil status files contain input (the first line in each file) and output (subsequent lines of data appended to each file). The predicted rates of various plant and soil processes and values of various intermediate quantities are written to separate files containing related groups of variables to allow the user flexibility in selecting only the desired output.
Input spreadsheet The function of the input spreadsheet, IN.WK1, is to allow the user to specify the desired simulation, specifically to: ( 1 ) Read in default values from disk files for input data; (2) Facilitate any modifications to the default data the user may desire; and ( 3 ) Create the newly-defined input data files for use by ARMOD.
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
319
An auto-executed macro presents the user with the opening title screen and prompts the user for input of preliminary information - - path to save files, start and stop days, year for weather data, default soil series, and default rice variety. The soil and rice variety are chosen via 1-2-3 style menus with predefined selections. After this information is entered, the macro calls another macro which displays the m a i n menu. The main m e n u macro branches to other macros depending upon the user's choice among: ( 1 ) 'Edit/view', to edit or view default data files; (2) 'Run A R M O D ' , to save data files, exit to MS-DOS, and execute ARMOD; ( 3 ) 'Reset', to erase any data entered and begin again; or (4) 'Quit', to exit to MS-DOS without saving files. The overall m e n u structure is illustrated in Fig. 2.
'Edit~view'. The Edit m e n u allows the user to select which group of default input files to edit/view, or to quit (return to the main m e n u ) . An additional m e n u is displayed with each group selection. Each additional m e n u contains a selection which allows the user to return to the Edit menu. Macro execution for each group selected is described below. These sub-menu choices are also shown in Fig. 2. When 'Parametr' is selected from the Edit menu, an additional m e n u presents seven groups o f parameters to edit. W h e n any group is selected, the cursor is m o v e d to the appropriate input screen containing titles and instructions. For each input variable in the group, there are three columns: variable name, default value, and new value. An example input screen for canopy photosynthesis parameters, choice 'CanPho', is given in Fig. 3. The cells in the default value column are automatically copied into the cells in the new value column. The default values are then protected. The user is instructed to move the cursor to any cell needing modification and to enter the new value. The user is instructed to type [alt-b] to return to the P a r a m e t e r m e n u when all modifications are completed. The next group of parameters can then be selected from the m e n u for editing. The values for the beginning status variables used initially in the simulation are defined by selecting 'BegStat' from the Edit menu. The values can be specified in two ways. The first way is analogous to the parameter editing described above, with return to the Beginning Status m e n u by typing [alt-c I. The second way is to specify the names o f existing status files to be imported. A user may do this to select the ending status from a previous simulation as the starting point for a subsequent simulation. The sub-menus utilized for this purpose are shown in Fig. 2. Weather data and m a n a g e m e n t information can be edited with the daily input, 'DailyIn', selection. An entire year's weather data is imported into the workspace and the appropriate days are copied to the input screen. Management options include irrigation and fertilizer amounts and timing. The user
320
T.A.COSTELLOETAL
]
INITIAL SCREEN Input: [ Path Rice variety menu~
1
[
Start Soil type menu Stop Weather year
J ]
MAIN MENU Edit/view
Run ARMOD Reset
Quit
I
I
~Input fitenames
Return to DosJ
Save files Return to DOS
Erase screens Return to Init. Screen EDIT MENU Paramtr
Quit
BegStat
OutFreq
Daityln
[ Return to MAIN
UserDef
EndSim
Quit
Crop*[Soil* Retur~toEDIT] [Input filenames--~ Weathr*
FLdSchd
Quit
i Return to
* Whenselected, ]
Manual*
EditJ
Automatic
user may view | or modify valuesJ
I
Input
]
Flood day, IFLd depth-target & min Drain day
Quit
Reglnterval
ManualStct*
lReturn to EDIT] CStatus
[
SStatus
i
NitrBls
WatrBIs
BalNutrC
GrwthBts
I
I
i
I
PhotoCan
• . . (Input first day, interval, and last day} . . .
CanPho*
MorPhen*
GroPart*
DynComp*J RespSen*J So[tPar* Quit J Return to EDITJ
Fig. 2. Menu structure for the input spreadsheet.
P J
Quit
[ Prey. menu
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
Move Type When
32 [
t h e c u r s o r u s i n g t h e a r r o w keys. in d e s i r e d c h a n g e s . all changes are complete, press ALT-b.
Plant Canopy/Photosynthesis ...................................................... Name InitAssimSlope LeafNitAssimSlope LeafNitAssimOffset AttenFactorEarly AttenFactorLate CritLAI
Default 0.04 2.93 -0.28 0.75 0.45 2.00
NewValue 0.04 2.93 -0.28 0.75 0.45 2.00
Fig. 3. Examplescreenfor inputting new valuesinto the input spreadsheet. is given the opportunity to modify either group of data and then return to the 'DailyIn' menu by typing [ alt-d ]. Output information that is desired from ARMOD by the user is specified with the output frequency, 'OutFreq', selection. A range of seven columns (for each of seven groups of output data shown in Table 2) with a row for each day of the simulation is filled with zero's. The user must change zero's to one's in the cells representing the days in which output for a particular group is requested. This can be done manually or automatically (using a selected fixed interval of days) using sub-menu selection (Fig. 2 ). Output for the soil and crop status files is automatically selected for the ending day of simulation. After all selections from the Edit menu are completed, the user may select 'Quit' to return to the main menu.
'Run ARMOD'. After editing input data files, the user may select 'Run ARMOD' from the main menu. Macro execution will then proceed to copy the new values defined within each input screen into appropriate places in the workspace in order to create the file structure required by ARMOD. Once assembled in the workspace, the newly-defined files are written to the disk using file names provided by the user. These names are also included in the file INITSIM.DAT. After writing the files, the macro exits 1-2-3 and returns to MS-DOS (where the batch program will continue). Output spreadsheet The function of the output spreadsheet, OUT.WK1, is to present the predictions of ARMOD to the user, specifically to: ( l ) Read the output files from ARMOD; and (2) Facilitate viewing of selected data sets in either tabular or graphical format.
322
T.A. COSTELLO ET AL.
Input: path]
l Main Menu
Cao'ho Cro~ro]Crop.utI CropSta I rvl°' °210[ rO'l021031O'f0210'IQI
Soi 'Nit I Soi lSta I Sol LWat
Ivl0, G2 uI01 02 °l
OLit I O[ [Return to DOS]
IUl011021 °'r°'fQ] 0:
V = View Data Q = Quit; Return to MAIN GI,G2,etc = Pre-defined Graphs
Fig. 4. Menu structurefor the output spreadsheet. An auto-executing macro presents the opening screen and prompts the user for the path to the file INITSIM.DAT which contains the output file names used by ARMOD. The user is then presented with the main menu with the option to select one of seven output categories or quit. The menu structure for OUT.WK1 given in Fig. 4. When an individual output category is selected, the appropriate output file from ARMOD is imported and macro execution branches to the appropriate sub-menu. Each sub-menu has selections to view the data in tabular format, to view predefined graphs, or to quit (return to the main menu).
Tabular data. A sub-menu choice to view data in a tabular format will move the cursor to the appropriate output screen. In each category, table headings for each variable were predefined in the output screen. The output data is imported below the heading. After viewing the data, the user is instructed to type [alt-b ] to return to the previous menu. Prior to typing [alt-b ], the user has control of the spreadsheet and may manipulate the data using any of the usual spreadsheet commands. Graphics. Each sub-menu also lists the graphs which were pre-defined for the individual output data category. When a particular graph is selected from the menu, the macro will first display the graph with two pre-defined d u m m y data pairs. After the user presses any key, the macro will then expand the ranges for each variable in the graph to include all of the actual observations imported from the output data file. The graph is then displayed with appropriate titles and axis labels. After viewing a graph, the user is instructed to press any key to return to the previous sub-menu.
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
323
The pre-defined graphs include: two for canopy photosynthesis (including CO2 assimilation), six for crop growth dynamics (including partitioning and respiration), two for crop nutrition (carbohydrate and inorganic nitrogen)~ five for crop status (including mass and leaf area), four for soil nitrogen dy-namics (including fertilizer, uptake and losses), two for soil status (water and nitrogen), and two for soil water dynamics (transpiration and evaporation). EVALUATION
The spreadsheet-based user-interface was evaluated by the programmers in terms of ease of use, ease of programming, and potential usefulness by growers. Overall, the input spreadsheet was easy to use compared to manual creation of input data files using a line editor or word processor. Input file creation was facilitated by automatic loading of the appropriate default data files. The menu structure allowed the large bulk of required data to be broken into coherent pieces which could be simply selected. Each parameter was clearly identified eliminating any confusion regarding which data values are to be modified. Modification of unacceptable default values by browsing the selected screen of the spreadsheet seems to be more efficient and less monotonous than typical question and answer formats often used to input data into programs. The output spreadsheet was very simple and straight forward to use. The spreadsheet, with pre-defined table headings, provided a quick and concise method of presenting the predicted data from the crop model. The menu structure helped organize data file selection and viewing. The pre-defined graphs were very useful and time-saving. Advanced users could perform further data analysis by manually selecting c o m m a n d s from 1-2-3, if so desired. Users with little spreadsheet experience, nevertheless, could view the tables and graphs easily. Several tasks within the spreadsheets seemed particularly slow, including: loading the input spreadsheet, reading the weather data and deleting those days not included in the simulation period, - s a v i n g the field schedule file (which involved deleting any day with no scheduled events), and - selecting and preparing a graph for viewing. Nominal execution times for these four example tasks, as performed on three selected PC's, are listed in Table 3. Several exceedingly slow procedures were eliminated from earlier versions of the interface and replaced with techniques less convenient to the user. During the time that the user was able to modify default input values, the control of the input spreadsheet was relinquished from the macro and given -
-
324
T.A. COSTELLO ET AL.
TABLE 3 Time of execution of selected spreadsheet macro tasks Description of task
Execution time (s) PC type a
( 1) (2) (3) (4)
Load input spreadsheet Read and sort weather data Sort and save field schedule file Select and view a pre-defined graph
8088
80286
80386
235 278 411 18
10 100 154 13
9 47 75 8
aPC type: 8088, Zenith Model Z-159, 8088 CPU, 8 MHz. 80286, Zenith Model Z-248, 80286 CPU, 12 MHz. 80386, IBM PS/2 Model 80, 80386 CPU, 20 MHz. All PC's had hard disk and math co-processor.
to the user. This allowed the user to move between different cells as necessary using the cursor movement keys. Macro execution was regained by following the displayed instructions to type, for example, [alt-b]. There was concern that an inexperienced user may move outside the pertinent input screen and become confused about how to return. This situation may also occur in the output spreadsheet while viewing tabulated data. Serious programming using 1-2-3 macros was often frustrating to the interface developers who were well acquainted with programming languages, such as FORTRAN and QUICKBASIC. The macro programmer must anticipate and record the necessary sequence of keystrokes to perform some task. During development, the spreadsheet often behaved unexpectedly resulting in errors during macro execution. For example, while saving the input files, 1-2-3 unexpectedly presented the message 'file already exists' and gave the choices 'Cancel' or 'Replace'. No simple method was found to automatically check for each file's existence prior to issuing the macro keystrokes to save the file. This was solved, in an indirect manner, using an error-trapping technique. Due to the nature of spreadsheet macros, other difficulties (too numerous to mention here) were encountered and eventually circumvented in the final version. SUMMARY AND CONCLUSIONS
An interface which handles input and output communications between the user and a crop model was developed. The intended users were scientists (involved in calibrating the model ) and rice producers (to assist in management
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
325
decisions). Spreadsheet software was utilized because of its inherent advantages in viewing and modifying tabular data and in displaying graphics. Although the spreadsheet-based user-interface performed its defined tasks, some limitations of macro programming and spreadsheet execution that were noted suggest that a programming language may prove to be a more effective tool for developing similar user interfaces in the future.
REFERENCES Alocilja, E.C. and Ritchie, J.T., 1987. User documentation of the CERES-Rice simulation model. Version 1.20 (2nd Edition). International Benchmark Sites Network for Agrotechnolog?y Transfer, University of Hawaii, Honolulu, HI. Boote, K.J., Bennet, J.M., Jones, J.W. and Jowers, E.H., 1989. On-farm testing of peanut and soybean models in North Florida. ASAE Microfiche 89-4040, American Society of Agricullural Engineers, St. Joseph, MI. Costello, T.A., VanDevender, K.W. and Ferguson, J.A., 1988. The Arkansas rice model: prelim-. inary structure. ASAE Microfiche 88-4062, American Society of Agricultural Engineers, St Joseph, MI. McKinion, J.M. and Lemmon, H.E., 1985. Expert systems for agriculture. Comp. Electron. Agric.. 1: 31-40. Parker, S.H., 1986. A step-by-step procedure to run CERES-Maize on a personal computer. In: C.A. Jones and J.R. Kiniry (Editors), CERES-Maize. Texas A&M University Press, College Station. TX, pp. 145-165.
315
Elsevier Science Publishers B.V., A m s t e r d a m
Spreadsheet-based user-interface for a crop model Thomas A. Costello, Judy L. Costello, Karl W. VanDevender and James A. Ferguson Biological and Agricultural Engineering Department, University olA rkansas, Fayetteville, A R 72701, U.S.A. (Accepted 8 August 1990)
ABSTRACT Costello, T.A., Costello, J.L., VanDevender, K.W. and Ferguson, J.A., 1991. Spreadsheet-based userinterface for a crop model. Comput. Electron. Agric., 5:315-325. A user-interface was developed to facilitate simulation of rice crop production using the Arkansas Rice Model ( A R M O D ) . The interface was programmed using spreadsheet software. Initially, the inlcnded users were researchers !nvolved in calibrating and validating ARMOD. The interface will be adapted for use by rice farmers in making management decisions. The interface provided interactive creation of input files prior to simulation and then displayed model predictions in tabular and graphical formats. Although the interface was functional, the spreadsheet software was cumbersome to program and execution was relatively slow.
INTRODUCTION
Historically, mathematical models of crops have been used for research purposes. The increased capability and affordability of the personal computer (PC) has enhanced the feasibility of adapting crop models for on-farm use by crop production managers in testing crop management strategies. Utilizing the PC, one can access crop models more readily due to a simpler interactive operating system, dedicated graphics displays and printers, immediate program execution, and elimination of time-sharing costs associated with main-frame computers. Parker ( 1986 ) described the procedure for executing a corn model (CERESMaize) on a PC. The program did not have an interactive user-interface, and a line editor was recommended for modification of input files. In executing a rice model (CERES-Rice) on a PC, Alocilja and Ritchie (1987) provided interactive capabilities for: (a) selecting among various default management, 0168-1699/91/$03.50
© 1991 - - Elsevier Science Publishers B.V.
316
T.A. COSTELLO ETAL.
soil, and weather files, (b) modifying selected management details, and (c) specifying the frequency of three groups of output data. Boote et al. (1989) improved the usefulness of results from a soybean model (SOYGRO) by providing a graphics interface to display various relationships computed within the model. McKinion and L e m m o n ( 1985 ) used an expert system (COMAX) to act as a front-end to a cotton model (GOSSYM) in which multiple simulations were executed automatically by sequentially creating input data files, executing the model, and examining the results. The Arkansas Rice Model ( A R M O D ) was developed as a component in an overall management support system for rice producers (Costello et al., 1988). The predictions from A R M O D can be used to select optimal timing of agronomic practices (such as fertilization and pesticide application) and to test proposed management strategies. During initial calibration and validation of ARMOD, an interface was needed to facilitate the numerous simulations to be performed by researchers. Following calibration and validation, A R M O D will be made available for use by farmers; hence, an effective interface was needed for non-scientist users as well as researchers. OBJECTIVE
The objective of the research was to develop a user interface for A R M O D (programmed using spreadsheet software) to be used initially by researchers in calibrating and validating the model. Spreadsheet software was chosen because of its inherent capabilities, such as built-in graphics and macro-programming, in order to reduce software development time. This paper outlines the structure and function of the interface and evaluates its potential usefulness as a tool for crop simulation by scientists and crop producers. DESCRIPTION OF USER-INTERFACE
The user-interface for A R M O D was written using commercial spreadsheet software (1-2-3, Version 2.01, Lotus Development Corp.~). Two separate spreadsheets, one for input and another for output, were created and linked to execute ARMOD (Fig. 1 ). The following batch program was executed from MS-DOS to automatically proceed through each step in the simulation: COPY IN. WK1 AUTO123.WK1 123.EXE ARMOD.EXE COPY OUT.WK1 AUTO123.WK1 123.EXE The batch program utilizes the auto-execute spreadsheet, AUTO 123.WK1, to ~Mention of specific products does not imply endorsement by the University of Arkansas.
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
USER
SOFTWARE
[INPUT
LECT
31 "/
DATA FILES
J
~-
, JS'R=A SREETI i
PUT R ARMOD ~ C E M O D E L 1" OD. EXE I
J
F __]
I
OUTPUT FILES I
FRO= A OO J
Z[EWS
--
Io TPUT
i
SPR=ADS===T I !OUT
Fig. 1. Diagram of batch file program flow in automatically executing ARMOD with the userinterface spreadsheets.
load and begin executing the input and output spreadsheets in proper sequence. The function of the input spreadsheet is to create the input data files required by ARMOD and the function of the output spreadsheet is to read ARMOD's output files. The data file structure of ARMOD is outlined below. Strltclttre Of ARMOD
A list of input files required by ARMOD is given in Table 1. A simulation initialization file, INITSIM.DAT, is accessed first to read the starting and ending days of the simulation and the names of the other input files. The path to INITSIM.DAT can be passed to the program on the command line. The other data files can have any name and those names selected are listed in INITSIM.DAT. The crop status file and soil status file define the values of all state variables on the starting day of the simulation. Additional computed data are periodically appended onto these files throughout the simulation by ARMOD. The crop parameter file and soil parameter file contain values of all process-related variables used by ARMOD. Since these values are not numerically coded directly into ARMOD, researchers have flexibility in modifying the model without recompiling the program. The aerial environment file and field schedule file contain the environmental and management conditions to be imposed during the simulation. The output frequency file specifies how often various groups of data will be written to output files by ARMOD. There are seven output files created by ARMOD, as listed in Table 2. Simulation results can be written to any file on any day selected by the user (and
318
T.A. COSTELLO ET AL.
TABLE 1 List of input files for A R M O D Default file name 0_ ( 1 ) INITSIM.DAT
(2) CROPSTAT.DAT (3) SOILSTAT.DAT (4) AERENV.DAT
(5) FLDSCHED.DAT (6) CROPPARM.DAT (7) SOILPARM.DAT
Contents Initial information: start day, stop day, input and output file names Crop status at start of simulation, includes mass, composition, leaf area, etc. Soil status at start of simulation, includes soil water content, soil nitrogen, etc. Aerial environment data for each day of simulation, includes air temperature, solar radiation, etc. Field schedule data, listing details for each irrigation or fertilization event Crop parameter values used in simulating crop growth processes, including factors for photosynthesis, translocation, senescence, etc. Soil parameter values used in simulating soil processes, including factors for evapotranspiration and nitrogen losses, etc.
TABLE 2 List of output files from ARMOD Default file name
Contents
( 1) (2) (3) (4) (5) (6) (7)
Model predictions for canopy photosynthesis Model predictions for crop growth dynamics Model predictions for crop nutrient use Crop status throughout the simulation Model predictions for soil nitrogen processes Soil status throughout the simulation Model predictions for soil water processes
CANPHOTO.DAT CROPGRO.DAT CROPNUT.DAT CROPSTAT.DAT SOILNIT. DAT SOILSTAT.DAT SOILWAT.DAT
defined in the output frequency file - - an input file ). The crop and soil status files contain input (the first line in each file) and output (subsequent lines of data appended to each file). The predicted rates of various plant and soil processes and values of various intermediate quantities are written to separate files containing related groups of variables to allow the user flexibility in selecting only the desired output.
Input spreadsheet The function of the input spreadsheet, IN.WK1, is to allow the user to specify the desired simulation, specifically to: ( 1 ) Read in default values from disk files for input data; (2) Facilitate any modifications to the default data the user may desire; and ( 3 ) Create the newly-defined input data files for use by ARMOD.
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
319
An auto-executed macro presents the user with the opening title screen and prompts the user for input of preliminary information - - path to save files, start and stop days, year for weather data, default soil series, and default rice variety. The soil and rice variety are chosen via 1-2-3 style menus with predefined selections. After this information is entered, the macro calls another macro which displays the m a i n menu. The main m e n u macro branches to other macros depending upon the user's choice among: ( 1 ) 'Edit/view', to edit or view default data files; (2) 'Run A R M O D ' , to save data files, exit to MS-DOS, and execute ARMOD; ( 3 ) 'Reset', to erase any data entered and begin again; or (4) 'Quit', to exit to MS-DOS without saving files. The overall m e n u structure is illustrated in Fig. 2.
'Edit~view'. The Edit m e n u allows the user to select which group of default input files to edit/view, or to quit (return to the main m e n u ) . An additional m e n u is displayed with each group selection. Each additional m e n u contains a selection which allows the user to return to the Edit menu. Macro execution for each group selected is described below. These sub-menu choices are also shown in Fig. 2. When 'Parametr' is selected from the Edit menu, an additional m e n u presents seven groups o f parameters to edit. W h e n any group is selected, the cursor is m o v e d to the appropriate input screen containing titles and instructions. For each input variable in the group, there are three columns: variable name, default value, and new value. An example input screen for canopy photosynthesis parameters, choice 'CanPho', is given in Fig. 3. The cells in the default value column are automatically copied into the cells in the new value column. The default values are then protected. The user is instructed to move the cursor to any cell needing modification and to enter the new value. The user is instructed to type [alt-b] to return to the P a r a m e t e r m e n u when all modifications are completed. The next group of parameters can then be selected from the m e n u for editing. The values for the beginning status variables used initially in the simulation are defined by selecting 'BegStat' from the Edit menu. The values can be specified in two ways. The first way is analogous to the parameter editing described above, with return to the Beginning Status m e n u by typing [alt-c I. The second way is to specify the names o f existing status files to be imported. A user may do this to select the ending status from a previous simulation as the starting point for a subsequent simulation. The sub-menus utilized for this purpose are shown in Fig. 2. Weather data and m a n a g e m e n t information can be edited with the daily input, 'DailyIn', selection. An entire year's weather data is imported into the workspace and the appropriate days are copied to the input screen. Management options include irrigation and fertilizer amounts and timing. The user
320
T.A.COSTELLOETAL
]
INITIAL SCREEN Input: [ Path Rice variety menu~
1
[
Start Soil type menu Stop Weather year
J ]
MAIN MENU Edit/view
Run ARMOD Reset
Quit
I
I
~Input fitenames
Return to DosJ
Save files Return to DOS
Erase screens Return to Init. Screen EDIT MENU Paramtr
Quit
BegStat
OutFreq
Daityln
[ Return to MAIN
UserDef
EndSim
Quit
Crop*[Soil* Retur~toEDIT] [Input filenames--~ Weathr*
FLdSchd
Quit
i Return to
* Whenselected, ]
Manual*
EditJ
Automatic
user may view | or modify valuesJ
I
Input
]
Flood day, IFLd depth-target & min Drain day
Quit
Reglnterval
ManualStct*
lReturn to EDIT] CStatus
[
SStatus
i
NitrBls
WatrBIs
BalNutrC
GrwthBts
I
I
i
I
PhotoCan
• . . (Input first day, interval, and last day} . . .
CanPho*
MorPhen*
GroPart*
DynComp*J RespSen*J So[tPar* Quit J Return to EDITJ
Fig. 2. Menu structure for the input spreadsheet.
P J
Quit
[ Prey. menu
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
Move Type When
32 [
t h e c u r s o r u s i n g t h e a r r o w keys. in d e s i r e d c h a n g e s . all changes are complete, press ALT-b.
Plant Canopy/Photosynthesis ...................................................... Name InitAssimSlope LeafNitAssimSlope LeafNitAssimOffset AttenFactorEarly AttenFactorLate CritLAI
Default 0.04 2.93 -0.28 0.75 0.45 2.00
NewValue 0.04 2.93 -0.28 0.75 0.45 2.00
Fig. 3. Examplescreenfor inputting new valuesinto the input spreadsheet. is given the opportunity to modify either group of data and then return to the 'DailyIn' menu by typing [ alt-d ]. Output information that is desired from ARMOD by the user is specified with the output frequency, 'OutFreq', selection. A range of seven columns (for each of seven groups of output data shown in Table 2) with a row for each day of the simulation is filled with zero's. The user must change zero's to one's in the cells representing the days in which output for a particular group is requested. This can be done manually or automatically (using a selected fixed interval of days) using sub-menu selection (Fig. 2 ). Output for the soil and crop status files is automatically selected for the ending day of simulation. After all selections from the Edit menu are completed, the user may select 'Quit' to return to the main menu.
'Run ARMOD'. After editing input data files, the user may select 'Run ARMOD' from the main menu. Macro execution will then proceed to copy the new values defined within each input screen into appropriate places in the workspace in order to create the file structure required by ARMOD. Once assembled in the workspace, the newly-defined files are written to the disk using file names provided by the user. These names are also included in the file INITSIM.DAT. After writing the files, the macro exits 1-2-3 and returns to MS-DOS (where the batch program will continue). Output spreadsheet The function of the output spreadsheet, OUT.WK1, is to present the predictions of ARMOD to the user, specifically to: ( l ) Read the output files from ARMOD; and (2) Facilitate viewing of selected data sets in either tabular or graphical format.
322
T.A. COSTELLO ET AL.
Input: path]
l Main Menu
Cao'ho Cro~ro]Crop.utI CropSta I rvl°' °210[ rO'l021031O'f0210'IQI
Soi 'Nit I Soi lSta I Sol LWat
Ivl0, G2 uI01 02 °l
OLit I O[ [Return to DOS]
IUl011021 °'r°'fQ] 0:
V = View Data Q = Quit; Return to MAIN GI,G2,etc = Pre-defined Graphs
Fig. 4. Menu structurefor the output spreadsheet. An auto-executing macro presents the opening screen and prompts the user for the path to the file INITSIM.DAT which contains the output file names used by ARMOD. The user is then presented with the main menu with the option to select one of seven output categories or quit. The menu structure for OUT.WK1 given in Fig. 4. When an individual output category is selected, the appropriate output file from ARMOD is imported and macro execution branches to the appropriate sub-menu. Each sub-menu has selections to view the data in tabular format, to view predefined graphs, or to quit (return to the main menu).
Tabular data. A sub-menu choice to view data in a tabular format will move the cursor to the appropriate output screen. In each category, table headings for each variable were predefined in the output screen. The output data is imported below the heading. After viewing the data, the user is instructed to type [alt-b ] to return to the previous menu. Prior to typing [alt-b ], the user has control of the spreadsheet and may manipulate the data using any of the usual spreadsheet commands. Graphics. Each sub-menu also lists the graphs which were pre-defined for the individual output data category. When a particular graph is selected from the menu, the macro will first display the graph with two pre-defined d u m m y data pairs. After the user presses any key, the macro will then expand the ranges for each variable in the graph to include all of the actual observations imported from the output data file. The graph is then displayed with appropriate titles and axis labels. After viewing a graph, the user is instructed to press any key to return to the previous sub-menu.
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
323
The pre-defined graphs include: two for canopy photosynthesis (including CO2 assimilation), six for crop growth dynamics (including partitioning and respiration), two for crop nutrition (carbohydrate and inorganic nitrogen)~ five for crop status (including mass and leaf area), four for soil nitrogen dy-namics (including fertilizer, uptake and losses), two for soil status (water and nitrogen), and two for soil water dynamics (transpiration and evaporation). EVALUATION
The spreadsheet-based user-interface was evaluated by the programmers in terms of ease of use, ease of programming, and potential usefulness by growers. Overall, the input spreadsheet was easy to use compared to manual creation of input data files using a line editor or word processor. Input file creation was facilitated by automatic loading of the appropriate default data files. The menu structure allowed the large bulk of required data to be broken into coherent pieces which could be simply selected. Each parameter was clearly identified eliminating any confusion regarding which data values are to be modified. Modification of unacceptable default values by browsing the selected screen of the spreadsheet seems to be more efficient and less monotonous than typical question and answer formats often used to input data into programs. The output spreadsheet was very simple and straight forward to use. The spreadsheet, with pre-defined table headings, provided a quick and concise method of presenting the predicted data from the crop model. The menu structure helped organize data file selection and viewing. The pre-defined graphs were very useful and time-saving. Advanced users could perform further data analysis by manually selecting c o m m a n d s from 1-2-3, if so desired. Users with little spreadsheet experience, nevertheless, could view the tables and graphs easily. Several tasks within the spreadsheets seemed particularly slow, including: loading the input spreadsheet, reading the weather data and deleting those days not included in the simulation period, - s a v i n g the field schedule file (which involved deleting any day with no scheduled events), and - selecting and preparing a graph for viewing. Nominal execution times for these four example tasks, as performed on three selected PC's, are listed in Table 3. Several exceedingly slow procedures were eliminated from earlier versions of the interface and replaced with techniques less convenient to the user. During the time that the user was able to modify default input values, the control of the input spreadsheet was relinquished from the macro and given -
-
324
T.A. COSTELLO ET AL.
TABLE 3 Time of execution of selected spreadsheet macro tasks Description of task
Execution time (s) PC type a
( 1) (2) (3) (4)
Load input spreadsheet Read and sort weather data Sort and save field schedule file Select and view a pre-defined graph
8088
80286
80386
235 278 411 18
10 100 154 13
9 47 75 8
aPC type: 8088, Zenith Model Z-159, 8088 CPU, 8 MHz. 80286, Zenith Model Z-248, 80286 CPU, 12 MHz. 80386, IBM PS/2 Model 80, 80386 CPU, 20 MHz. All PC's had hard disk and math co-processor.
to the user. This allowed the user to move between different cells as necessary using the cursor movement keys. Macro execution was regained by following the displayed instructions to type, for example, [alt-b]. There was concern that an inexperienced user may move outside the pertinent input screen and become confused about how to return. This situation may also occur in the output spreadsheet while viewing tabulated data. Serious programming using 1-2-3 macros was often frustrating to the interface developers who were well acquainted with programming languages, such as FORTRAN and QUICKBASIC. The macro programmer must anticipate and record the necessary sequence of keystrokes to perform some task. During development, the spreadsheet often behaved unexpectedly resulting in errors during macro execution. For example, while saving the input files, 1-2-3 unexpectedly presented the message 'file already exists' and gave the choices 'Cancel' or 'Replace'. No simple method was found to automatically check for each file's existence prior to issuing the macro keystrokes to save the file. This was solved, in an indirect manner, using an error-trapping technique. Due to the nature of spreadsheet macros, other difficulties (too numerous to mention here) were encountered and eventually circumvented in the final version. SUMMARY AND CONCLUSIONS
An interface which handles input and output communications between the user and a crop model was developed. The intended users were scientists (involved in calibrating the model ) and rice producers (to assist in management
SPREADSHEET-BASED USER-INTERFACE FOR A CROP MODEL
325
decisions). Spreadsheet software was utilized because of its inherent advantages in viewing and modifying tabular data and in displaying graphics. Although the spreadsheet-based user-interface performed its defined tasks, some limitations of macro programming and spreadsheet execution that were noted suggest that a programming language may prove to be a more effective tool for developing similar user interfaces in the future.
REFERENCES Alocilja, E.C. and Ritchie, J.T., 1987. User documentation of the CERES-Rice simulation model. Version 1.20 (2nd Edition). International Benchmark Sites Network for Agrotechnolog?y Transfer, University of Hawaii, Honolulu, HI. Boote, K.J., Bennet, J.M., Jones, J.W. and Jowers, E.H., 1989. On-farm testing of peanut and soybean models in North Florida. ASAE Microfiche 89-4040, American Society of Agricullural Engineers, St. Joseph, MI. Costello, T.A., VanDevender, K.W. and Ferguson, J.A., 1988. The Arkansas rice model: prelim-. inary structure. ASAE Microfiche 88-4062, American Society of Agricultural Engineers, St Joseph, MI. McKinion, J.M. and Lemmon, H.E., 1985. Expert systems for agriculture. Comp. Electron. Agric.. 1: 31-40. Parker, S.H., 1986. A step-by-step procedure to run CERES-Maize on a personal computer. In: C.A. Jones and J.R. Kiniry (Editors), CERES-Maize. Texas A&M University Press, College Station. TX, pp. 145-165.