The informational keywords set up the background necessary to take actions. Most apply only to the action that follows, though some, such as :CONSTANTS are persistent.
:BIND dbfield=modelvar
Creates an association between a database field and a model (or dataset) variable. This is an alternative method for reading from and writing to the database that does not use :VARNAME, :VARID or :Y (though you will still need to use :TIME/:X when working with dynamic variables and Lookups). This should be used when the database is structured with a number of different variable values all contained in a single record.
There can be any number of :BIND statements.
All model variables must have the same subscripts, though the order of the subscripts can be different. It is also permissible to have additional subscript constants in some of the variables. The subscript ranges appearing in the model variable names must map to those specified in the :SUB statements (again order is not important, just that the subscript ranges all be the same).
Note that if you bind a variable with fewer subscripts than specified with the :SUBS statements it will still work, but may lead to inconsistent results. On the output side multiple values for variables will be written, as if the variable actually had the additional subscript. All these value will be the same. On the input side multiple values will be read. If they are different, the one actually used will be determined by the order records are provided by the database. This may not always be the same.
Note that when reading from a database the :BIND order is opposite of what might seem intuitive since this is really setting the model variable to be equal to the database value. However, this ordering is consistent with the :FIELD and :SUB assignments and you simply need to remember that the database field name always appears on the left of the = sign.
:CONSTANTS
For input: Specifies that data coming in will refer to constants. This means that a value (or y) will be expected but no time (or x). Only a single value will be retrieved. If multiple records exist for the same constant the last record fetched will provide the value. The order in which records are fetched is determined by the database and may not always be the same.
For output :CONSTANTS specifies that a single value will be written. This value could be for a constant or for a dynamic variable at a specific time. For the latter you can use :TIME #value or leave this blank to return the value at the final time of the simulation.
The :CONSTANTS setting remains active until a :LOOKUP or :DATA is encountered.
:DATA
For input this specifies that the data coming in will refer to time series data. Data requires a series of Time and Value pairs associated with each variable (one or more). There is no requirement for any ordering of these pairs. Each variable for which data is retrieved must be a Data variable.
For output species that data will be written as time and value pairs. You can also use this to write out the value of Lookups.
The :DATA setting remains active until a :LOOKUP or :CONSTANTS keyword is encountered.
:DATASET datasetname
Output only. Specifies the dataset that the data will be extracted from. If not specified, Vensim will used the first loaded dataset (*1) just as for graphs. You can use wildcards such as *2 just as for graphs and you can also use an exclamation point ! to indicate that the current run name should be used as the source dataset.
Note that the dataset name is always a literal so you do not need the # sign in front of it, though the # sign can be used to maintain consistency.
:FIELD name=value
Specifies a field name and a value to set that field to or require the field to be equal to. For input and updating this will be appended to the WHERE clause on the select statement using AND. For inserting this will be used in the definition of the table to insert values in. For INSERT the combinations will appear in the insert statement itself. For SELECT and UPDATE the name=value combinations will be repeated in the WHERE clause separated by AND.
You can use an exclamation point ! for the value of the field to indicate the name of the current run. This will appear without the .vdf extension. This can be a useful way to archive simulation results and fetch changes by scenario name as is shown in the examples.
:LOOKUPS
Input only: Specifies that data coming in will refer to Lookups. Lookups need a series of x and y values associated with each variable (each variable requires a minimum of two values). There is no requirement for any ordering of these pairs as there is no guarantee what the realized order of selection will be in any case.
If you want to store a Lookup for output use the :DATA keyword.
The :LOOKUP setting remains active until a :DATA or :CONSTANTS is encountered.
:SUB prefix+fieldname=range or :SUB fieldname=range
Specified the field in which a subscript will be found, and the subscript range from the model that this corresponds to. The optional prefix+ portion tells Vensim that the model subscript elements will differ from the database values by a prefix. This is useful, for example, when the database values are numeric. Using a prefix will prevent the requirement that everything be surrounded by quotes, and also will allow values that might overlap (for example item 1 at store 1) to be used in Vensim without difficulty (Store1, Item1). You can have multiple :SUB lines. When the data is read, Vensim will construct a complete variable name by combining the :VARNAME result with the :SUB results as in:
Varname[s1,t1,u1]
Where the order of subscripts is determined from the order they appear in the model variable. An empty :SUB lines will be ignored. If there are no :SUB entries, or all are empty, the complete variable name will just be used for the :VARNAME field of the :VARID lookup.
For backward compatibility if =range is left off the subscript range will be assumed to be that of the variable being processed and the order of the :SUB statements will determine the order the subscript ranges are mapped to database fields. Note that in this case all variables much have the same subscript ranges in the same order or the results will not make sense.
:TIME fieldname
Specifies the field in which the time for which a value applies is stored. This field must be a number or a string containing a number. :TIME and :X are the same. :TIME is typically used for Data and :X for Lookups.
This field is ignored for constants except that for output you can use this to specify a literal value for the time at which dynamic variables should be retrieved from a dataset for storage. If no time is specified then the value of dynamic variables at the final time of the simulation will be used.
NOTE Don’t use Time as a field name. This is a reserved word and will cause problems.
:VARID fieldname
Specifies the fieldname in which the variable ID will be found. See :VARLOOKUP for more details. If VARID is used, then VARNAME should be left blank.
:VARLOOKUP
Specifies that the information that follows will be used to associate variable IDs with variable names. It is common in a relational database to associate a relatively short unique identifier (often just a number) with a table entry so that other tables can refer to that identifier rather than a longer name. For example you might have a table VARIABLES with fields Name, Sub1, Sub2, Sub3, Sub4, Type and Comment. Having a series of data points refer to that the table DATA would then need fields Name, Sub1, Sub2, Sub3, Sub4, Time and Value. If, however, a unique ID were to be added to the table VARIABLE then the table DATA could just have ID,Time,Value which saves a lot of space both for data and for indexing.
When this is done, however, the table VARIABLE needs to be referred to in order to interpret the values from the table DATA. :VARLOOKUP allows this to happen. It needs to include a:VARNAME reference, 0 or more :SUB references and a :VARID reference. Vensim will then know which variable name to associate the ID with and can read in the data.
For input, the :VARLOOKUP statement can come before or after any :CONSTANTS, :DATA or :LOOKUPS entries. It is recommended, however, that it appears last as this should give the best performance. There can be more than one :VARLOOKUP, and the variables referred to in :VARLOOKUP can be of all types. See the examples below.
For output the :VARLOOKUP statement must come before the :UPDATE or :INSERT actions.
The :VARLOOKUP sequence must end in a :SELECT statement. .
:VARLIST
Output only. Indicates that the information that follows indicates the list of variables to be output to the database. What follows can be either a list of variable names each on its own line, or the information necessary to perform a select and fetch the variable list from the database. Vensim recognizes a list by the lack of a colon : in the lines that follow and the termination of the variable list by the presence of a colon : in the next line.
:VARNAME fieldname
Specifies the field in which the variable name will be found. The variable name can be fully subscripted, in which case no :SUB lines should appear, or the subscripts might be stored in separate fields.
:VALUE fieldname
Specifies the field in which the value is stored. This field must be a number or a string containing a number. :VALUE and :Y are the same though :Y is typically only used with Lookups.
NOTE Don’t use Value as a field name. This is a reserved word and will cause problems.
:X fieldname
Specifies the field in which the X value for a Lookup pair is stored. Actually a synonym for :TIME.
:Y fieldname
Specifies the field in which the Y value for a Lookup pair is stored. Actually a synonym for :VALUE.
