ViewDBFWin Macro Language Documentation
Go Back to 'Using ViewDBFWin'
Go to 'MJ Library Functions'
Creating a Macro for ViewDBFWin
You can enter macro statements using the "Execute Macro" option under the Tools menu, or by
simply clicking the small double arrow button on the toolbar. The macro entry form has a macro
commands window in which you enter macro statements. You can enter huge swathes of macro
commands if you want. Each new statement entered must be started on a new line. You can save
and load macro files (plain text files containing macro commands). The macro entry screen has
the same format as that for SQL, except there is no error message
window. When an error occurs, a message appears explaining the problem, and gives you the
opportunity to continue or abandon execution of the macro. If, during a lengthy operation the
Stop button is pressed, the rest of the macro is not executed. A display on the panel tells
you which line of the macro is currently executing.
If you want to add comments, prefix them with // or ; for each line of comment. For example,
// The next line sends the records to a temporary file called phonestmp.
// If phonestmp had something in it before, it is overwritten with the new records.
COPYRECORDS \addresses\phonestmp
// We then append these records to the phones database.
OPENDB \addresses\phones
IMPORTDBF \addresses\phonestmp
If you need to put a very long line into the macro, but want it to still be legible on
the screen, you can use a trailing backslash \ on the end of the line to denote that
it continues on to the next line. See IF...THEN...ELSE for an example of
this. The lines entered should syntactically match the commands as laid out below. Record level
operations are not catered for, but all database-wide operations are supported. However, the
following macro shows how a record-by-record approach can be achieved :-
CLOSEDB
MODE EXCLUSIVE
RUN del dest.*
OPENDB dest
CHANGEFIELD /FDATE,D;FTIME,C,12;FNAMATT,C,200;FSIZE,N,10,0/NAME
OPENDB trax
DEFINE ii=1
:top
IF #ii#>#NUMRECS THEN GOTO end
FILTER RECNO()=#ii#
DEFINE fld1=FILEDATE
DEFINE fld2=FILETIME
DEFINE fld3=RTRIM(FILENAME)+' : '+ATTRIBUTES
DEFINE fld4=FILESIZE
DEFINE res=MJADDREC(%dest,fdate=#fld1#|ftime=#fld2#|fnamatt=#fld3#|fsize=#fld4#)
FILTER
DEFINE ii=#ii#+1
GOTO top
:end
OPENDB dest
This takes fields from each record of the database trax.dbf with the following structure :-
FILENAME,CHARACTER,160
FILEDATE,DATE,8
FILETIME,CHARACTER,12
FILESIZE,NUMERIC,10,0
ATTRIBUTES,CHARACTER,5
defines new fields from them, and then uses MJADDREC
to add the new fields into records in the destination database dest.dbf.
Flow Control and Other Directives
You can use the SETDIR path directive to change the current working drive and
directory at any point during the macro's execution. For example, SETDIR d:\mydocs
will set the current drive to D: and the current directory to \MYDOCS
You can ask for input from the user as the macro executes using a combination of
DEFINE and the MJ Library Function MJASK. Have a look at MJASK
for an example of how to do this.
If you embed the mnemonic #NUMRECS in any macro line, it is substituted for the number
of records in the currently opened database. This enables you to, for example, list the last
20 records of a database using :-
OPENDB MYLIST
FILTER RECNO()>#NUMRECS-20
RUNREPORT MYREPORT.TPL
If you embed the mnemonic #SPLFILE in any macro line, it is substituted for the
current directory's sample file. For example, if you are in the directory W1022, #SPLFILE will
be substituted with the full path and filename for w1022_SPL.dbf. Similarly, #DATFILE
and #DBFDATFILE will be substituted with the full path and filename for the DKapture
format .dat file (w1022.dat) and the DBF data file (w1022_DAT.dbf) respectively.
DISPLAY Expr
If Expr is valid, Expr is evaluated, and the resultant string is displayed
on the screen, otherwise Expr is displayed, with an OK button to close the display and
continue the macro, and a Cancel button to close the display and stop the macro. If
Expr is * (asterisk) as in "DISPLAY *", then all defined macro variables are displayed
in a window to enable easier debugging. Do not use this debug form of the command on a live
production macro deployed on a web server, since it will display even under a web
environment. This enables macro debugging when using a local server to generate web pages.
GOTO name
This sends macro execution to the line specified by :name. Any line that starts with a colon
is regarded as a label for a GOTO command.
DEFINE name=expr
This allows you to define a variable denoted by name and assign it either the result of
evaluating expr if this is a valid expression, or the expression expr literally. If the
expression is a date-type expression, then the resultant string has CTOD('...') added to ensure
that when it used in a date context after definition, it works as expected. Whenever
subsequent lines contain #name#, this is substituted for what name is defined as. All
definitions have a lifetime of the ViewDBFWin session and can be used across multiple macro
runs and within RUNREPORT templates. For example, you could do this :-
DEFINE UVC='tangram'
DEFINE mydt=DATE()+9
DEFINE myfilter=%LOANDATE>DATE()-14
SET FLAG1='#UVC#'
SET FLAG2=%#UVC#
SET FLAG3=#mydt#-5
SET FLAG4=DTOC(#mydt#)+' '+TIME()+' #UVC#'
FILTER #myfilter#
name is case-insensitive. If expr is not an expression, it will be taken as a literal string.
If you are trying to define a string that could potentially contain all possible quotation
characters, it is difficult to subsequently use it in expressions. In this case, use the
MJTRIM library function which will ensure that the string
is quoted properly.
SINGLESTEP ON or OFF
This will put the macro editor into or out of "single step mode" where each line is executed
individually. The highlighted line is the one about to be executed. When switched on, the
"Execute" button on the macro editor screen has its caption changed to "Step". You can also
toggle this mode by right-clicking on this button. This is useful when debugging macros. You
can also see DEFINEd variables by hovering the mouse over them in the editor screen. Do
not leave "SINGLESTEP ON" lines in a live production macro deployed on a web server, since
it will stop on each line even under a web environment.
IF condition THEN command1 [ELSE command2]
This allows you to conditionally execute a macro command, depending on whether condition
evaluates to true if it is a valid expression. Otherwise, condition is assumed to be false. If
no ELSE command is found and condition is false, then nothing is executed for this line. If
condition is false and an ELSE command is specified, then command2 (which itself could be an
IF... command) will be executed. If condition is true, then command1 is executed. For example,
the following macro lines jump to different sections of the macro, depending on the time of
day, and demonstrates the use of the trailing backslash \ :-
IF TIME()>'22:00' THEN GOTO LATE\
ELSE IF TIME()<'08:00' THEN GOTO EARLY\
ELSE IF TIME()>'20:00' THEN GOTO EIGHT\
ELSE IF TIME()<'10:00' THEN GOTO TENMORN\
ELSE IF TIME()<'12:00' THEN GOTO MIDMORN\
ELSE GOTO AWAY
Here is an example macro that demonstrates all these features combined. It uses a loop to
populate a FLAG field with the result of a scientific calculation, where the result changes
every 100 records, until the end of the database :-
DEFINE II=0
:TOP
FILTER RECNO()>#II#*100 AND RECNO()<=(#II#+1)*100
SET FLAG=MJSINH(#II#)
FILTER
DEFINE II=#II#+1
IF #II#*100<#NUMRECS THEN GOTO TOP
Please note:-
Expressions that contain MJ Library Functions cannot be used
in indexes, but can be used anywhere else.
Advantage Expressions can be used anywhere.
Index maintenance needs exclusive access - use MODE EXCLUSIVE before attempting these operations.
Most Yes/No prompts are auto-answered for you. The documentation below specifies exactly how
any given macro command will auto-answer any prompts.
Commands and arguments are case insensitive.
Arguments given below that are in square brackets [...], are optional.
ViewDBFWin Macro Command Reference
Opening, Creating, and Restructuring Databases
OPENDB dbf-name
Opens the database dbf-name. If it does not exist, it is created with a single NAME field of
20 characters width, and then opened.
CLOSEDB
Closes the currently open database. If there is no database open, it does nothing.
MODE EXCLUSIVE or SHARED or READONLY
Sets the database open mode. If EXCLUSIVE is used, the database is opened so you have exclusive
access to it, and you can (re)build and delete indexes, and pack records (exclusive
activities). If SHARED is used, you can still update the records but you cannot do exclusive
activities, and other people can open the database in shared or readonly modes. If READONLY is
used, no updates can be done to the data, although reporting and filtering is possible.
DBFTYPE DBF or ADT
Sets the database type for all subsequent opens. If DBF is used, extensions for the table are
assumed to be .DBF, for the index .CDX and the memo file .FPT. If ADT is used, extensions for
the table are assumed to be .ADT, for the index .ADI and the memo file .ADM. If 'dbf' and 'adt
cannot be found in the type argument, the current type is left as it is (default DBF type).
The database type is also set by opening a database and specifying its extension (.dbf or
.adt) and this overrides this macro command. When using this command it is better not
specify the extension of databases you are opening subsequently, unless you want override it
back to a certain type, or mix database types (as in converting one to the other).
CPDASSOC cpd-file
Associates the currently open database with a compiled file. Useful when doing
an export to DOS DKapture data format. If no database is open, it does nothing.
ADDFIELD new-field-definition[;new-field-definition2 ...]
Adds the field(s) new-field-definition(s) to the current database. The definitions should be
separated by semi-colons, and specified in the following format :-
field-name,C[haracter] or N[umeric] or M[emo] or D[ate] or L[ogical],field-width,number-decimals
Field names can only have A-Z and 0-9 and _ characters and should not be more than 10
characters wide. If you specify Memo, Date or Logical field types, then you do not need to
enter the field-width or number-decimals parts. If you specify Character field type, then you
must supply a field-width, but the number-decimals is not required. If you specify Numeric
field type, then you must specify the lot. There are further field types that can be used and
are listed in the Database Field Types section of the main
documentation. Definitions are case insensitive. A few examples :-
ADDFIELD LREF,CHARACTER,6
// The next macro statement adds 3 fields in one go :-
ADDFIELD REQUESTED,D;PLAYTIME,C,8;HOWMUCH,N,12,2
ADDFIELD REDEMTYPE,NUMERIC,2,0;STAGGER,L
A database restructure occurs after adding the field(s). Please note, any restructure recreates
all index orders for the database.
DELFIELD field-name[;field-name2 ...]
Removes the field(s) specified by field-name(s) from the current database. If a field-name does
not exist, it is not removed. Otherwise, the fields are removed from the database, and a
restructure occurs. Please note, any restructure recreates all index orders for the database.
CHANGEFIELD change-definitions[/add-fields/delete-fields]
where change-definitions is of the form field-change-definition1[;field-change-definition2 ...],
add-fields is of the form documented for ADDFIELD above and similarly for
delete-fields (DELFIELD).
Changes fields in the current database according to the definitions supplied. If there is a
problem with any of the field change definitions, the macro is terminated with an appropriate
error message. Failure will occur if you attempt to change the type, width or name of any
field used in an index ordering. The format for a field-change-definition is as follows :-
old field-name,new field-name[,C or N or M or D or L,field-width,number-decimals,(position)]
Field names can only have A-Z and 0-9 and _ characters and should not be more than 10
characters wide. If you only specify old and new field names, then the field will be
renamed and retain its field characteristics. If you specify Memo, Date or Logical field types,
then you do not need to enter the field-width or number-decimals parts. If you specify
Character field type, then you must supply a field-width, but the number-decimals is not
required. If you specify Numeric field type, then you must specify the lot. Definitions are
case insensitive. An example that changes the field name ' lastname' to the new name 'surname'
is: 'lastname, surname, char, 20'. If desired, the field definition can be appended with a
comma followed with an integer within parentheses (ex ,(3)). That integer will indicate the
position of the field within the table. For example, 'Lastname, Lastname, char, 20, (5)' will
move the field 'Lastname' to the fifth position in the field list. A backup is made of the
database before the changes, and has the same name as the original, but with the .BAK extension
appended to the DBF, CDX, and FPT files. add-fields and delete-fields are optional and
separated from each other by forward slashes. If any of the fields you are changing have an
index built on them, CHANGEFIELD may fail.
RENDATAFLDS oldname1,newname1;oldname2,newname2;...
Finds the fields DATARECORD and/or SVYSTATE in the sample file and/or the DKWEBUSERS file
associated with the currently open DKapture data file, and renames the DKapture question names
stored in the memos according to the parameters specified. This is useful when questions in
the DKapture compiled file have changed their names and you wish to maintain the partially
complete interviews stored in the DATARECORD and SVYSTATE memo
fields.
SAVESTRUC struc-file
Stores the current database structure in the file struc-file. If struc-file exists, it is
overwritten. The output is one line per field, in the format above for
ADDFIELD.
SLOWSTRUC struc-file
Performs a slow restructure (where field types may be changed) using the structure read
from the file struc-file. Please note, any restructure recreates all index orders for
the database.
FASTSTRUC struc-file
Performs a fast restructure (where field types may not be changed) using the structure
read from the file struc-file. This can be significantly faster than the
SLOWSTRUC command on large databases. Please note, any restructure recreates all index
orders for the database.
Importing and Exporting Records
IMPORTDBF dbf-file [FAST]
Appends the records in dbf-file to the current database as best it can. Works best if the source
and destination databases have identical or similar structures. Use the optional "FAST" keyword
to denote that it should use the fast technique to append the records instead of the default
slow and safe field-by-field method.
IMPORTSDF sdf-file
Imports data from a fixed-width field file (SDF) into the current database. The sdf-file should
have one record per line, and each line should consist of plain text in fixed width fields that
correspond to the destination database field widths.
IMPORTCSV csv-file [HEADER]
Imports data from a comma-separated variable file, forming a new database and deriving the
database structure intelligently from the underlying data in the CSV file. Fields within
records are delimited by commas and any double quotes enclosing text are not taken into the
data, but can be used to wrap text in fields that contain commas. It will try to intelligently
decipher date formats for date fields. After import, the current database will have the same
name as the CSV file but with the .DBF extension. If it existed before, it is overwritten. The
new database structure has fields that are one character wider than the maximum width
encountered in the CSV file data, for character and numeric data types. If the optional
"HEADER" keyword is supplied, the first line of csv-file contains a comma-separated row of
field names to name the fields in the new database. If the supplied field names are longer than
10 characters, they are truncated. If, after truncation, duplicate field names exist, they
have the last character changed to one of 2-9, A-Z until they become unique. Otherwise, the
fields are called CHARx for character fields, DATEx for date fields, VALUEx for numeric fields,
and LOGICALx for boolean fields, where x is an incrementing sequence number. MEMO fields are
imported as character fields.
CHARCSV csv-file [HEADER]
Imports data from a comma-separated variable file, forming a new database and forcing the
database structure to be character fields matching the widths from the underlying data in the
CSV file. Fields within records are delimited by commas and any double quotes enclosing text
are not taken into the data, but can be used to wrap text in fields that contain commas. After
import, the current database will have the same name as the CSV file but with the .DBF
extension. If it existed before, it is overwritten. The new database structure has fields that
are one character wider than the maximum width encountered in the CSV file data, for any
particular field. If the optional "HEADER" keyword is supplied, the first line of csv-file
contains a comma-separated row of field names to name the fields in the new database. If the
supplied field names are longer than 10 characters, they are truncated. If, after truncation,
duplicate field names exist, they have the last character changed to one of 2-9, A-Z until they
become unique. Otherwise, the fields are called CHARx for character fields, DATEx for date
fields, VALUEx for numeric fields, and LOGICALx for boolean fields, where x is an incrementing
sequence number. MEMO fields are imported as character fields.
APPENDCSV csv-file [DROPHEADER|HEADER]
Imports data from a comma-separated variable file, appending the records to the current
database. It does its best to match CSV data with the field type it is supposed to populate. It
will try to intelligently decipher date formats for date fields. If the optional "DROPHEADER"
keyword is supplied, the first line of csv-file is skipped because it is assumed to be a
fieldname header row. If instead, the optional "HEADER" directive is specified, the append is
done in two stages. First it makes a DBF from the specified CSV file (same name, DBF extension,
overwritten each time) and uses the first row of the CSV file to form the fieldnames for the
temporary database. Then it appends these database records to the current database. This latter
option will slot fields into the correct places if the fields arrive in the CSV file in a
different order, but the field names have not changed.
MERGEDBF [+]dbf-file @KeyExpression
Adds the fields in dbf-file to the current database (or replaces data in matching fields if the
optional plus sign prefix is used), and then populates them by searching for KeyExpression in
the current index ordering and filling in the extra/matching fields from the matching record
in dbf-file. This essentially adds or merges the data from records in dbf-file to the current
database, using the current index ordering and the evaluated KeyExpression expression to
determine which record in the current database the fields are for. KeyExpression is evaluated
in dbf-file, not the current database. If no KeyExpression is specified and the current
database is in natural record order, matching is done by record number, so the first record in
dbf-file will add its fields to the first record in the current database, and so on. If no
KeyExpression is specified and the current database is in an index order, KeyExpression is
taken to be the current index expression. If there is no match, the fields from the merge
record are not added or merged to any record in the current database, and are ignored.
If problems occur, you are prompted as to whether you want to roll back the database to its
original state before the merge was attempted. If you prefix dbf-file with a plus sign (+), the
fieldnames from the merge database are matched against fieldnames in the main database, and
where matches occur, data is overwritten with data from corresponding field in the merge
database. For example, if the current database is indexed on UPPER(SURNAME) :-
MERGEDBF PCODES @NAME
adds fields from the PCODES database to each record in the current database whose
SURNAME field matches the NAME field in the merge database.
EXPORTCSV [+]output-file [YYYYMMDD] [HEADER] [DKAPTURE] [SSSMAP] [DELIM=x]
Outputs records to output-file in CSV (delimited) or fixed width format. If the optional + sign
in front of the file name is used, the records are appended to output-file, otherwise output-
file is initially truncated. If the optional YYYYMMDD format specifier is used, then all dates
are output in this format. If the optional HEADER argument is supplied, then a header row of
field names is written to the output file, unless appending to the output, or using fixed
width. The default delimiter character is comma but this can be changed by using the optional
DELIM= parameter. This must always be the last option on the line. To use a semicolon instead,
you would use DELIM=; as the last part of the line. To output to fixed width format, then use
DELIM= with nothing after the equals sign. If the optional DKAPTURE keyword is specified, then
the delimiter parameter should not be specified and fixed width is assumed. Also, the multi-
coded questions are output in DKapture data file format, and the lines are separated with \r
instead of the usual new line characters \r\n. When using the comma as a delimiter, if a field
contains double quotes, they are converted to single quotes. Also, when using the comma as a
delimiter, if a field contains commas, it is output with double quotes around it. When using
comma as a delimiter or fixed width output (no delimiter), if any individual field contains new
line characters, they are converted to ASCII 160 (looks like a space, but isn't!). To specify
control characters for the delimiter, use Hex Character Code(s) prefixed with %. For example,
DELIM=%09 for tab delimited and DELIM=%0D%0A for CR-LF delimited. If you use the SSSMAP flag,
and there is a DKapture compiled file associated with the DKapture data database, a SSS version
2.0 XML map file is produced with the same name as the output file but with a .sss extension
instead. Also, the output is CSV format with multi-coded data output in zero-padded fixed
format, for example, with a 16 answer code frame and no 0-code, ",5,8,11,14," would be output
to the CSV file as "05081114000000000000000000000000".
DKFLDTODATA CPDFile DKDataFile SerialNoField DataRecField [WRITEBLANKS]
This will take the data held in the multi-line database field DataRecField
(usually DATARECORD or SVYSTATE) and use CPDFile to make a DKapture data file called
DKDataFile out of it. The field should have the data in multiple lines
of the format
question label==answer
DKDataFile is always overwritten. The field SerialNoField is used to set the
serial number for each data record. If any of the arguments contain spaces, enclose the
argument in double quotes. Only the currently visible database records are exported, and they
are written in their current order. If DKDataFile is a database file rather than a DOS
DKapture file, that file is automatically opened afterwards, and CPDFile is
associated with it. If the optional WRITEBLANKS keyword is used,
then records whose DataRecField are blank, will cause corresponding blank data records
in DKDataFile, rather than the default behaviour of not writing DKapture data records
for database records with blank DataRecField's.
Reporting on Records
RUNREPORT template-file [NOSTART]
Sends each visible record through a template file, with output written to the template filename
with the extension .RTF (for Rich Text Format templates with fonts and styles for text), .HTM
for HTML templates, and .TXT for plain text templates. This can be changed using the $OUTPUT=
directive in the template itself. Files are overwritten, unless the $OUTPUT= directive has a
'+' prefix on the filename, in which case, output is appended. The template file extension
defaults to .TPL and it should consist of lines of text containing embedded fieldnames or
expressions (Advantage Expressions which can contain
MJ Library Functions). The embedding character is ` (under the
Escape key) and this should wrap a fieldname or expression to be evaluated on each record. To
quote data from a DKapture data file in verbose (answer texts included) format, prefix the
DKapture field with the ~ character (tilde, for example ~Q1). If you surround a DKapture
field with the ¬ character (for example ¬Q1¬), then the first couple of lines of the
question text are used here instead of the question label.
You can use HTML and RTF version 1 to design your templates, as well as plain text. You can use
a trailing backslash at the end of a line ( \ ) to make long lines more
legible, as in a macro. You can include macro-DEFINEd
variables in the template using the usual macro syntax #varname#. You can include a
header line for the report using the $HEADER= directive and/or a footer line with the $FOOTER=
directive. These two directives have to be on one line or use the backslash line continuation
character. However, if you use either the $$HEADEREND or $$FOOTEREND directives respectively
(must be on a line by itself), you can have multi-line sections that are the header or footer
for the report. You can also use $$NEWLINE to have multiple lines in the header or footer
sections without using the trailing backslash line continuation character. For example :-
$HEADER=<H2>MJ Testing 3455w</H2>
<TABLE CELLPADDING=2 CELLSPACING=3 BORDER=1 BGCOLOR="#E6E2DD">
<TR><TH>ACTION_1</TH><TH>ACTION_2</TH><TH>ACTION_3</TH></TR>
$$HEADEREND
... Rest of Template ...
OR
$HEADER=<H2>MJ Testing 3455w</H2>\
<TABLE CELLPADDING=2 CELLSPACING=3 BORDER=1 BGCOLOR="#E6E2DD">\
<TR><TH>ACTION_1</TH><TH>ACTION_2</TH><TH>ACTION_3</TH></TR>
... Rest of Template ...
You can insert pictures from files using the $LOGO= directive (again, the filename can contain
expressions enclosed in `s - filename must be enclosed in double quotes and can optionally be
followed by a comma and a scaling percentage). You can embed control characters in the
template using %% followed by the hexadecimal code for the character, zero-padded to a width of
2 (for example, %%A9 means copyright symbol). You can also embed mnemonics, for example,
$$DBNAME for the name of the current database, $$STN is the station number of the PC generating
the report (from the environment variable STN), $$PAGENO is the current page number (default
1), $$DATE for today's date, and $$TIME for the current time.
There is also a template auto-designer for setting up new templates.
$$NEWPAGE is for a new page. In HTML templates, it puts <HR class="pb"> and this, coupled
with the
<STYLE TYPE="text/css">HR.pb {width: 0%; page-break-after: always;}</STYLE>
command in the <HEAD> section of the HTML template, will page-break wherever you put a
$$NEWPAGE directive. The automatic template designer ('D' button when designing a report
template) will put this in for you, so you don't have to worry about doing it manually. This
only applies to HTML templates where you want the output paginated.
If the optional NOSTART parameter is used, the report output is not automatically fired
up in the appropriate application after it has been produced. For example, template EX1.TPL
You said `reason2` as an explanation.
would produce a text file EX1.TXT with one line per record as per the template. The field
"REASON2" would be substituted on each line with data from the relevant record.
$OUTPUT=FILE`recno()`.OUT
There were `NUMBOXES` boxes on order.
`ALLOC` boxes have already been allocated.
would produce text files named FILEn.OUT where n is the database record number for the relevant
record. Another example would be a record listing template, which allows multiple users to run
this template at the same time ($$STN keeping the outputs separate), as follows :-
$OUTPUT=\DKFILES\LISTING$$STN.OUT
$HEADER=$$DBNAME dated $$DBDATE$$NEWLINE
Name Tel.No. Last Visit Amount Paid
`padr(FIRSTNAME,10)``padr(TELNO,15)``LASTDATE``padl(AMOUNTPAID,20)`
$FOOTER=`space(35)``padl(mjstats(,AMOUNTPAID,,,1),20)`
An example use of the $LOGO= directive inside an RTF template :-
$LOGO="f:\jobs\mj\mypic`SERIALNO`.jpg,`if(SERIALNO==5,250,100)`"
which scales picture f:\jobs\mj\mypic5.jpg by 250 percent and all others unscaled (100 percent).
An example use of the $$NEWPAGE directive at the bottom of an RTF template :-
`if(recno()%2==0,'$$NEWPAGE','')`
which emits a new page every 2 records (outputs 2 records per page).
Report Template Directives and Mnemonics Summary
| $OUTPUT= | Redirect output to filename |
| $HEADER= | Header directive |
| $$HEADEREND | End of Header directive, should be on a line by itself |
| $FOOTER= | Footer line |
| $$FOOTEREND | End of Footer directive, should be on a line by itself |
| $LOGO= | Insert picture from file - Filename must be enclosed in double quotes, and can optionally be followed by a comma and a scaling percentage (eg. $LOGO="mypic.jpg,50") - Only Allowed in RTF Templates |
| $COLS= | Number of columns per page - Only Allowed in RTF Templates |
| $RECSPERPAGE= | Number of records per page - Not Allowed in RTF Templates |
| $$DBNAME | Full name of the current database file |
| $$DBPATH | Full pathname to the current database file - includes trailing backslash |
| $$DBDATE | Day, Date and Time of the current database file |
| $$DATE | Today's date |
| $$TIME | Today's time |
| $$STN | Station number of the PC or zero if not set |
| $$LANDSCAPE | Set Page Orientation to Landscape - Only Allowed in RTF Templates |
| $$PAGENO | Current Page Number |
| $$NEWLINE | New Line - Useful for Multiple $HEADER= Lines |
| $$NEWPAGE | New Page - Puts <HR class="pb"> in HTML Templates |
| $$NOBLANK | No Blank Lines in the Output (use once in template body somewhere) |
| $$NOCOPYRIGHT | No Copyright Notice in the Output (use once in template body somewhere) |
| %%nn | Replaced by the character represented by hexadecimal code nn |
| `expression` | Replaced by whatever expression evaluates to |
HTMAPPEND main-HTM-report-file HTM-file-to-append
Appends the report contained in HTM-file-to-append to the end of main-HTM-report-file.
It takes care to use the correct header and footer for legal HTML syntax, and will keep
any copyright notice at the end of the resulting output. Please note that the file specified
by main-HTM-report-file is permanently altered and no backup will be available. You can use
this command to build long HTML reports during the execution of a macro. For example,
opendb calls
pivottable {OUTCOME} {INTERVIEWR} export(mjresmac.htm) title(Survey MJ - Outcome by Interviewer)
// The next line filters the view of the calls file to the last 20 records
filter recno()>#numrecs-20
// The HTML calls listing template outputs to the file callslist.htm
runreport calls_listing_htm.tpl
// The next line appends the calls listing output to the end of the html pivot table
htmappend mjresmac.htm callslist.htm
opendb mj_spl
filter STATUS=2
pivottable {dtos(APPT_DATE)+' '+mjshortdate(APPT_DATE)} {INTERVIEWR} export(+mjresmac.htm) title(Bookings Summary for Web Survey MJ)
exit
Pivot tables will always safely append, so you can use the + prefix for the output filename.
Report listings have to handle multiple output file collation, and this makes them handle
appends differently, hence the use of the htmappend command when interspersing pivot tables and
template report listings to the same HTML destination output file.
Deleting Records
ZAP
Clears all records from the database and reindexes it. This is an irreversible operation.
You will not be prompted to confirm the operation - it will just go ahead and do it. Beware.
PACK
Removes all deleted records from the database. There is no confirmation prompt.
DELFILTERED
Marks the currently filtered records as deleted. It does not prompt you to pack the database
afterwards. You would have to do this yourself with a PACK command on the next line of the macro.
If there is no filter in place, nothing is deleted.
DEDUPEALL [@][dedupe-expression]
Marks as deleted all records that are duplicated on the dedupe-expression. The records must be
in some index order to begin with. The expression is compared between adjacent records to
determine whether a duplicate has been found or not. Usually the expression is the index order
expression, which is what dedupe-expression defaults to if it is not specified. See
Help on Expression Syntax for more details on expressions.
Both matching records are marked as deleted. If the optional @ sign in front of the dedupe
expression is specified, all deleted records are copied to a back-up database
(dbfname_dels.dbf). The database is then packed.
DEDUPESUB [collation-fields or NONE] [@][dedupe-expression]
Marks as deleted subsequent records that are duplicated on the dedupe-expression. The
records must be in some index order to begin with. The expression is compared between adjacent
records to determine whether a duplicate has been found or not. Usually the expression is the
index order expression, which is what dedupe-expression defaults to if it is not specified.
See Help on Expression Syntax for more details on
expressions. Only subsequent records are marked as deleted. Optional collation-fields specify
numeric fields for the non-deleted record in the database which will have added to them the
values of these fields from all the subsequent records marked for deletion by this process. The
fields should be delimited from each other by using the semicolon character (;). If the optional
@ sign in front of the dedupe expression is specified, all deleted records are copied to a
back-up database (dbfname_dels.dbf). The database is then packed.
LIMITRECS max.of.each [limit-expression]
The records in the database have to be in an index order for this to work. The max.of.each
argument controls how many from each matching index key or expression are allowed - all the
rest are marked as deleted. If limit-expression is not specified, the current index ordering
expression is used. The expression is evaluated against consecutive records. If there are more
records with the same value for this expression than are allowed for by the max.of.each
argument, the excess records are marked for deletion. To actually shed the records from the
database, you have to issue a PACK command afterwards. For example, if your database is in
SURNAME order, and you only want 7 surnames from each letter of the alphabet, you could use
LIMITRECS 7 LEFT(SURNAME,1)
Indices and Filters
ORDER [index-name or REINDEX]
Puts the database in index-name order. If REINDEX is used instead, then the database is completely
re-indexed. If the argument is omitted, then the database is put into natural record order.
INDEX index-name index-expression
Creates a new index called index-name using the expression index-expression to control the ordering
of records. Index-expression should evaluate to strings of the same length for different records,
if it is a character type index.
See Help on Expression Syntax for more details on expressions.
DELETEINDEX index-name
Removes the index ordering specified by index-name from the index orders master file (.CDX). It does
this immediately without prompting. If index-name does not yet exist, nothing happens.
FILTER [filter-expression]
Sets the currently imposed filtering to the expression specified by filter-expression. If the
optional filter-expression is omitted, any currently imposed filtering is revoked, and all
records become visible. See Help on Expression Syntax for
more details on expressions.
COPYRECORDS [+]dbf-file [@record-number]
Copies the currently filtered records to dbf-file. If no filter is in operation, all records
are copied. Dbf-file becomes the new master database. If dbf-file exists, it is overwritten,
unless prefixed with the + (plus) sign, when it is appended to instead. If the optional
record-number is supplied and there is no filter or index order in place, the copy starts at
that record number and continues to the end of the database. If the record-number is outside of
the range 1 to the last record, no copying takes place, and the master database remains the
same as it was before the command was issued. For example :-
DEFINE whfrom=#NUMRECS-99
COPYRECORDS newcalls @#whfrom#
copies the last 100 records from the current database to the database file newcalls.dbf
and opens it. All index orders and filters are recreated in the destination database after the
copy, unless you were appending the records to an existing database.
COPYINDEXED [+]dbf-file @Key
Copies the currently indexed records with keys partially or wholly matching Key to dbf-file.
Deleted records are not copied. If no index is in operation, the macro stops with a
message. Dbf-file becomes the new master database if records match the key, else the original
one remains. If dbf-file exists, it is overwritten, unless prefixed with the + (plus) sign,
when it is appended to instead. For example, if the current database is indexed on
UPPER(SURNAME) :-
COPYINDEXED alljacs @JAC
copies all non-deleted records whose SURNAME field begins with JAC to the database file
alljacs.dbf and opens it.
CONVERTRECORDS dbf-file
Converts the currently filtered records to dbf-file. Format is dictated by dbf-file's
extension. If no filter is in operation, all records are converted. Indexes are recreated as
best as possible (indexes with expressions that contain fieldnames that had to be renamed, will
be skipped). When converting from ADT to DBF format, field types are converted to DBF DATE,
CHARACTER, LOGICAL, NUMERIC or MEMO format. Timestamps are converted into 2 fields; the date
part, and a numeric part representing the number of milliseconds elapsed since the start of the
day, to denote the time part. The time part can be made more legible by using the
MJDHMS MJ library function, for example,
MJDHMS(FIELD3/1000,true) will give "16:11:36" when FIELD3 holds 58296000.
SET field-name=[%]field-expression
For backward compatibility, 'SET field-name [%]field-expression' (space instead of equals)
is also supported.
For the currently visible records, this sets the field specified by field-name to the
expression (evaluated on each record) specified by field-expression. If field-expression is
prefixed with a % sign, then what follows should be a literal value to assign to the field,
rather than an expression. The expression can contain
MJ Library functions in addition to the standard set supported
by the expression engine. If the field being set is a raw, binary or image type field, then the
expression should yield a filename, from whose contents the field will be set. If the file does
not exist, then the raw, binary or image type field will be blanked out.
See Help on Expression Syntax for more details on expressions.
Flags and Subsets
FLAGFIELD num-width
Makes a field called FLAGVDBFW on the current database as a character field of width num-width characters.
If the field already exists, it is adjusted to be num-width characters wide.
FLAGSTRING flag-text
Sets the flag string to flag-text. When a subset is selected, the database field FLAGVDBFW is
filled with this text, in either append or overwrite mode. This is so that random subsets
can be made mutually exclusive. Records picked for a subset will not have flag-text in the
database field FLAGVDBFW already, but will have after being selected. If flag-text is blank or
non-existent, no FLAGVDBFW field need be present to perform the subset, and, during the subset process,
nothing is done to the FLAGVDBFW field if there is one present.
FLAG APPEND
Switches the Flag Field indicator to Append mode. Used in SUBSETs.
FLAG OVERWRITE
Switches the Flag Field indicator to Overwrite mode. Used in SUBSETs.
SUBSET num-required [+]destination-dbfile
Copies a random subset of num-required records to the database destination-dbfile.
If the flag string is blank, no FLAGVDBFW field need be present to perform the subset, and, during
the subset process, nothing is done to the FLAGVDBFW field if there is one present. Likewise, if
there is no FLAGVDBFW field in the database, the flag string is ignored. If you are not using the
FLAGVDBFW field in a subset, ignore comments about flags in the following. If you are using flag
strings and have a FLAGVDBFW field in the database, the flag string must have been set prior to
calling this command. Records are picked at random, and, when using flags, if the FLAGVDBFW field of
the record has the flag string text in it, it is not written to the subset destination-dbfile.
Otherwise it is copied to the destination-dbfile. The process continues until either
num-required records have been copied to destination-file, or it has looped 20 times the number
of records in the main database since the last successful hit. Hence, it is possible that less
than num-required records are copied to destination-file. If using flags, the FLAGVDBFW field of
all copied records on the source and destination databases is overwritten or appended with the
flag string text, dependent on the setting of the Flag Field indicator. The destination-dbfile
becomes the new master database. If destination-dbfile exists, it is overwritten, unless
prefixed with the + (plus) sign, when it is appended to instead.
SSFF definition-file
Performs a sequence of filtering and subsets using definitions found on each line of
definition-file. The format of each line, and an explanation of the parameters, can be found
at SSFF Syntax and Parameter Explanation.
Miscellaneous
EXECUTEMACRO MacroFile
This is a way of embedding a frequently-executed set of macro statements stored in the file
MacroFile, into the current macro. It essentially "includes" the other macro at this point
in the macro.
EXECUTESQL [#]SQLStatement
Executes the SQL statement in the command line, or, if prefixing with the optional # sign,
the SQL statements contained in the file specified by SQLStatement. See the
SQL Reference for more information on the SQL syntax supported.
PIVOTTABLE {Side[@Description]} [{Break[@Description]} {Cells[@Description]}]
[SORTSIDE{row sort expression}] [SORTBREAK{column sort expression}] [AVG] [ndps] [SORTTOTAL]
[HYPERLINK(hyplnk[|rws[|cls]])] [ROWFILTER{items}] [DPS%(xxx)] [SIDE%[ONLY]] [AUTOTRANSPOSE] [AUTOREFRESH]
[INCLUDEDELS] [EXCLUDESIDEBLANKS] [EXCLUDEBREAKBLANKS] [EXPORT([+]Filename)] [TITLE(Table Title)]
[DROPSIDECODES] [DROPBREAKCODES] [DROPSIDETOTALS] [DROPBREAKTOTALS] [HIDECODING] [MERGE] [NOHINT]
[FILTERTITLE(Titling for the Filter)] [TEMPLATE(HTML Template Filename)]
[FLAT] [BAR([w,h,miscoptions,bgcolour,bartype,breakdetail,barwidth])] or
[FLAT] [PIE([w,h,miscoptions,bgcolour,piecutoff,pielabeldistance])] or
[FLAT] [HORBAR([w,h,miscoptions,bgcolour,bartype,breakdetail,barwidth])]
[CHARTFONTS(BottomAxis_Or_PieLabelFont,TitleFont,LeftAxisFont,LegendFont,AnnotationFont,BarValuesFont)]
[LEGEND(right OR left OR top OR bottom OR invisible[,vertoff,horzoff])]
Since this particular command is very complex, you can design the table and/or chart inside
ViewDBFWin interactively, and then use the button on the Charting Options dialog that forms a
macro command to produce your output, and copies it to the clipboard, so you can then paste it
into the macro editor or any other text editor.
Performs a Pivot Table with the Side Variable expression Side, and optional Break
and Cells expressions (these default to "1"). The optional Description can be
used to label elements in the table and any associated chart. It must be separated from the
expression by an '@' character.
When HYPERLINK(hyplnk[|rws[|cls]]) is specified, the text in hyplnk is used to generate
a hyperlink for each table cell that has text in it. hyplnk has
ROW=row&COL=col&ROWD=rd&COLD=cd&SIDE=sv&BREAK=bv appended to it for each cell, where row is the
base 0 row number, col is the base 0 column number, rd is the header or description for that
row as text, cd is the header or description for that column as text, sv is the side expression
or variable, and bv is the break expression or variable. If you specify the optional row and
column allowable range arguments rws and/or cls, then hyperlinks are only
generated on those cells in the table. For example, you could specify
HYPERLINK(http://webbot.com/scripts/fred.cgi||1-5,8) so that hyperlinks only occur on columns 1
to 5 and column 8. HYPERLINK(http://webbot.com/scripts/fred.cgi||1-) would disallow hyperlinks
in the leftmost column of the table. HYPERLINK(http://webbot.com/scripts/fred.cgi|1-) would
disallow hyperlinks in the topmost row of the table. Remember that column and row numbering are
base 0 and that dropped columns or rows will leave gaps in the numbering.
Optional AVG means fill cells
with average of expression Cells. Optional ndps should be a number, which is the number
of decimal places the cells should be represented with (defaulting to 0 if no cell contents
expression is specified, and 2 otherwise). If optional EXPORT is used, the visible Pivot
Table is either printed to an image file if Filename's extension is bmp, jpg, jpeg, emf, wmf,
png, or ico. If Filename's extension is htm, html, doc, ppt, xls or rtf, the table is exported
in HTML format, through the
HTML Pivot Table Export TemplateFile. Otherwise, the whole
table is exported to a CSV-format file named Filename (or Filename.CSV if Filename does not
have an extension). If exporting to a filename that starts with a plus, the file is appended to
instead of overwritten. If you specify SORTSIDE then the row sort expression is used to
sort the table rows instead of the Side variable itself. This can useful when you are
tabulating dates using the MJDATETIME function, for
example. Column sorting can also be overridden by specifying a SORTBREAK column sort
expression. Note that both these expressions are enclosed in curly brackets {...} and that they
have no effect if you are using the SORTTOTAL keyword. If the optional keyword
SORTTOTAL occurs, the table is sorted so that the most frequent occurrences are towards the
top and left of the table, otherwise the side and break columns are in alphabetical or
numerical order. If the optional keywords EXCLUDESIDEBLANKS and/or EXCLUDEBREAKBLANKS
are used, records where the side and/or break expressions evaluate to blank, are excluded
from the table. If the optional keywords DROPSIDETOTALS and/or
DROPBREAKTOTALS are used, then those totals are dropped from the final table.
If optional AUTOREFRESH is used, the table is redone every minute. If
you specify the optional TITLE(...), then ... will be the title both on screen and when
printed, otherwise the title is "ViewDBFWin Pivot Table". If you specify DROPSIDECODES
then tabulating DKapture single and multi-coded questions will drop rows which have no data
for them, and similarly for DROPBREAKCODES and columns: the default for DKapture data is
to include any codes with no data for them, to give a complete picture of the code frame(s)
used. If you use INCLUDEDELS, then deleted records are included in the pivot table
(default is to exclude deleted records from the figures). If you specify the optional
FILTERTITLE (???), then ??? is used to supplant the filter expression when the table is
filtered. If you specify SIDE%, then side variable percentages are included in the table
in addition to the values. If you use SIDE%ONLY then only column percentages are
included in the table and chart. If you specify DPS%(xxx), then column percentages will
be displayed to xxx decimal places (default 0). If you specify AUTOTRANSPOSE, then if
there are more columns than rows in the resultant table, the table is automatically transposed.
HIDECODING will not display the "1) ... 2) ..." numbered codings for DKapture single
and multi questions when they are tabulated or charted. TEMPLATE(xxx) overrides the
default export HTML template file $$PIVTEMPLATE.HTM with the one specified in brackets. If th
e .htm extension is omitted, it is appended to the filename for you. ROWFILTER{items}
can be optionally specified to hide rows whose side expression does not match any of the '|'
delimited entities in the items string. For example, if you only wanted to display rows
with codes 3, 5 and 7 for a numeric side expression, then you can specify
ROWFILTER{3|5|7}. If the side expression is a DKapture single or multi-coded variable,
you should use numeric codes to specify "items". If you have used a SORTSIDE expression
to sort the rows in the table, you should use the results of evaluating that expression for
the "items" string, rather than the text labels for the rows themselves. In all other
instances, you should use the text labels corresponding to the rows you want to include, for
the "items" string. If you use MERGE then this table is merged with the previous table.
If no previous table was ever done in this session of ViewDBFWin, then the MERGE keyword
is ignored. If you use NOHINT, then no hints appear on tables whose cell contents are
not simple counts. This will also apply to exports to HTML.
Charting Options
If you use BAR (bar chart), HORBAR (horizontal bar chart) or PIE (pie
chart), then a chart is produced, based around the data in the pivot table. If you have also
specified a table EXPORT, the chart will be embedded in the output wherever the _CHART_
directive is found in the HTML Pivot Table Export Template
File. Chart directive parameters are as follows (at least one must be specified) :-
w is the chart width in pixels (default 640)
h is the chart height in pixels (default 480)
miscoptions is used to indicate various chart options. See
example below for layout look. Currently, these are :-
- % to include side percentages in the chart (default none)
- -base to drop the [Base:Count] from the titling of the chart (default is to include this)
- -margin to drop the side margins around bar charts. Use this to make bars go right up to
the edge of the chart (default is to have margins)
- C{VDC filename} to change the colours of the bars or pie slices to the colour scheme
specified by the file "VDC filename" (default DKFILES\default.vdc). Schemes can be set up from
the chart options dialog.
- +explode explodes the largest segment of a pie chart (default no exploded segment)
- +topdown stacks the bars in a stacked vertical bar chart from the top to the bottom,
or from right to left on a stacked horizontal bar chart (default is not to do this)
- +inside puts values or percentages inside the bars or segments on the chart. Any
labels will omit these extra details, and a pie chart will have a legend so that segments can
be identified, unless you make the legend invisible (default no details inside bars or segments
and labels have extra details for values and percentages)
- +rotate will rotate the values or percentages used inside bars on horizontal bar
charts when the +inside option is used (default is for all such labels to be horizontal)
- min{val} overrides the bar axis labelling start value with val (default 0).
- max{val} overrides the bar axis labelling end value with val (default 100).
- +flipped will stack the side variable instead of the break (useful on column percentage charts).
- +legtitle will put a title on top of the legend.
- +border draws a black border line around each bar or segment in the chart (default no line).
- 3d{val} makes the 3D amount percentage val % (default 15 - only affects 3D charts).
- rad{val} makes the pie radius val % of the chart height/width (default 25 - only affects pie charts).
- thresh{val} sets the floating point number val as the threshold amount below which a
value or percentage is not shown when +inside is used to draw the value inside a bar or segment.
bgcolour is the chart background colour, for example, Pale Golden Rod or a hex RGB
value like AF3497. It defaults to White. It can be 2 colours to form a gradient fill by
delimiting the second colour from the first using the '/' (slash) character, for example,
tan/orange. All valid colour names can be found in the dkfiles\mjcolours.dbf database.
piecutoff is the threshold percentage below which slices are grouped into a slice
called "Other Values" to avoid an overcrowded pie (default 0 - all slices detailed - only
applies to PIE charts)
pielabeldistance is the overriding distance from the edge of the pie circle where
labels will be drawn (default -50 if +inside is used and 0 otherwise - only applies to
PIE charts). If it is greater than 0 then dotted lines between the labels and the segments will
appear.
bartype is the type of bar to draw in the chart (default SOLID - only applies to
BAR charts)
- SOLID - a solid bar
- CYLINDER - a cylinder
- ARROW - an arrow
- GRADIENT - a bar with gradient fill
- ELLIPSE - an ellipse-shaped bar
- PYRAMID - a pyramid-shaped bar (apex at the top)
- INVPYRAMID - a inverse pyramid-shaped bar (apex at the base)
breakdetail allows a way of detailing the break variable in the chart. If there
are less than 26 break columns in the chart, then you can use this to show them on the chart
(default No Detail - only applies to BAR charts)
- SIDE BY SIDE - plot separate bars for each break column next to each other
- STACKED - stack the break bars on top of each other for each side variable
- STACKED 100% - stack the break bars on top of each other for each side variable and
scale them to fit the height or width (depending on whether you are using a BAR or HORBAR) of
the chart
- BACK TO FRONT - plot separate bars for each break column in a 3-D perspective from back
to front, for each side variable (only applies to 3-D charts - see FLAT keyword below)
barwidth is the percentage of the column width occupied by a bar (0 to 100%)
(default 70)
Additionally, you can override the default chart fonts using the CHARTFONTS(...)
directive. The fonts are specified in the same format as the "fontstring" parameter in
the DKapture {FONT ...} directive. Once set, they apply until
the end of the ViewDBFWin session or until set again, either through this macro command or via
the chart options user interface on the pivot table itself.
BottomAxis_Or_PieLabelFont is used to draw the labels on pie charts, or the marks or labels
on the bottom axis of bar charts.
TitleFont is used to draw the chart title.
LeftAxisFont is used to draw the marks or labels on the left axis of bar charts.
LegendFont is used to draw the legend on multiple series bar charts, and pie charts with
values inside the segments.
AnnotationFont is used to draw the "Total ... -->" or "Average ... -->" text on bar charts.
BarValuesFont is used to draw the values or percentages inside of bars on bar charts.
You can also use the optional keyword LEGEND(...) to reposition or make invisible the
legend. Legends are produced by default for bar charts with breakdetail specified on a
chart with a break expression yielding between 2 and 25 different columns. If you use the
miscoptions directive +inside on a pie chart, you will also get a legend,
unless you use this directive to render the legend invisible. The optional vertoff
parameter allows you to adjust the vertical offset of the legend, and can be positive to move
it further down, or negative to move it further up. You can similarly adjust the horizontal
placement of the legend with the horzoff parameter, with -ve meaning move it to the left
and +ve meaning to the right.
If any of these charts are prefixed with the FLAT keyword, they are rendered in 2
dimensions instead of the default 3.
For example :-
PIVOTTABLE {LEFT(DTOS(SIGNDATE),6)} {CUSTGROUP} SORTTOTAL SIDE% TITLE(Date Spread of Groups Joined)
FLAT HORBAR(,,% c{f:\dkfiles\new.vdc},asparagus)
(all on one line - split for legibility)
would give a table of the year and month of the join date down the side, by the customer group
they joined across the top. The highest numbers of members joining would be sorted to the top
left corner of the pivot table, which would include side percentages. A 640 by 480 2D
horizontal bar chart is produced from the table, with an asparagus colour background. It would
incorporate side percentages and would use colour scheme f:\dkfiles\new.vdc for the bar colours.
Notice that the side and break expressions have to be contained in curly braces.
IMPORTDKAPTURE DKaptureDataFile [SPLIT]
Creates a new database using the DKapture compiled and datafiles to concoct the new database.
The database name is the same as the compiled file name, but with the .DBF extension, and _DAT
added to the name. If it existed before, it will be overwritten. Multi-type data is read in as
comma-separated codes, with a comma at the beginning and end of the field to help analysis.
If the optional keyword SPLIT is used, then multis are split into separate single-digit
numeric fields for each code in the code-frame. The fields are named multi question label
plus _ plus the code number. For example, using SPLIT on a multi called Q4b with 4 codes and a
no answer code would yield fields Q4b_0, Q4b_1, Q4b_2, Q4b_3, and Q4b_4. All other fields are
named with question label for the data they contain, up to the 10-character limit for field
names. Field names may be renamed to ensure uniqueness in the overall database. If a question
is skipped in the DKapture data file, the data in the database will be blank. After
manipulation, data can be re-exported back to DKapture data file format, using the
ExportCSV command, unless you used the SPLIT option.
IMPORTDKSAMPLE DKaptureSampleFile
Creates a new database using the DKapture sample file to concoct the new database. The database
name is the same as the sample file name, but with the .DBF extension, and _SPL added to the
name. If it existed before, it will be overwritten. All data is read in as written in the SPL
file, except for the special flags on the end of each sample record, which are translated into
8-digit numeric fields holding interviewer number, outcome code, number of no answers, status
code, and number of calls respectively. All sample fields are made 1 wider than specified in
the sample file to allow for future expansion, and are all character fields.
INCBACKUP backupfile
Switches ViewDBFWin into READONLY mode and then performs an
incremental backup of DKapture/All files specified in backupfile (default extension .bkp). This
can be run while live interviewing is going on, and it will not interfere with it. The format
of backupfile is :-
semicolon-delimited source paths to backup from
target path to backup to (source paths are appended to this)
All subdirectories of the source paths are backed up too. If any source path is prefixed with
an asterisk (*), then all files from that directory and subdirectories will be backed up,
otherwise just the ones with the extensions as detailed below are. Here is an example
backupfile that backs up all live jobs to the local C: drive :-
f:\jobs;f:\exec;f:\dkfiles
c:\
This file can be created more easily by using the Incremental Backup option in the Tools menu.
Once created, it can be used over and over again to perform incremental backups. CALLS files
will only copy records that are past the end of the destination CALLS file, since their records
are rarely changed, and usually used as an audit trail. All other database files use the
COPYRECORDS mechanism to copy the entire source database over the
destination, replacing it. All other file types are copied as files through the OS. The file
types that are copied (unless the source path is prefixed with an asterisk, in which case all
file types are considered) are as follows :-
*.dbf *.adt *.txt *.pre *.cpd *.doc *.xls *.ppt *.mdb *.docx *.xlsx *.pptx *.mdbx *.csv *.mac *.xpr *.bat *.tpl *.pgp *.cfg *.htm *.spc *.mp3 *.dat *.bdt *.rtf *.zip *.zipx *.thmx *.tab *.idx *.fnt *.jpg *.jpeg *.bmp *.png *.pdf
These extensions are usually sufficient to reproduce any DKapture job on another drive. It is
important to remember that this special macro command runs in silent mode. That means that any
errors encountered during the backup procedure are recorded in the file DKWEBSERVE_MSGS.LOG in
the ViewDBFWin executable directory. You should view this file after a backup run if you
suspect something has not worked properly. It is also a useful indicator as to what fields are
missing for DKapture data files. If the above backup file was called c:\dkfiles\mjbacknet.bkp,
and the ViewDBFWin executable is in the c:\utils directory, then an example macro which also
displays errors after such a backup might be :-
define lgfl=c:\utils\dkwebserve_msgs.log
define xx=mjfilesize(#lgfl#)
incbackup c:\dkfiles\mjbacknet
define yy=mjfilesize(#lgfl#)
if #yy#>#xx# then display mjsubstr(mjreadfile(#lgfl#),#xx#+1)
exit
RUN [WAIT] command-string
Runs command-string in the underlying operating system. The command-string can be a set of
commands separated by semi-colons (;), and can use internal command.com instructions like echo,
pipes and redirection. If so, RUN will wait until finished before returning to the macro. If
the command-string is not an internal COMMAND.COM command or a batch file, and does not
use semicolon-delimited commands, then you can use the optional WAIT directive to tell it to
wait until the command has completed before continuing on with the next macro command.
Otherwise, it will continue with the macro before the command-string has finished running. An
example macro that uses a RUN command :-
OPENDB silly
RUN dir c:\*.* /s/ah>c:\q.csv
APPENDCSV c:\q.csv
This puts a directory of every file on the C drive onto the end of the database silly.
Yes, it is a silly example, but shows you what can be done with the RUN command.
START file
Launches file with the appropriate application according to its extension. For example,
OPENDB MYDBASE
RUNREPORT MYREPORT.TPL NOSTART
START MYREPORT.HTM
WAIT number-of-seconds
Waits for number-of-seconds seconds before returning to macro execution. The WAIT can be
interrupted by clicking on the macro command line display at the top of the main window when a
macro is running. To wait until a certain time and date to occur, you could write :-
:loop
IF DTOS(DATE())+' '+TIME()>='20100319 09:30' THEN GOTO finished
WAIT 10
GOTO loop
:finished
DISPLAY It is now 9:30 am on 19th March 2010.
EXIT
Closes the database and exits the program. It will not pack the database
if there are deleted records.
Back to the Top
Back to DKapture Help Index
© DK Computing