AcmeBase is a central repository of attributes describing a relational database, its tables, columns, data entry forms, lists, reports, audit trails, and so on. All the programs in the AcmeBase system refer to this central repository of database properties to provide a web interface to a SQL database.

An AcmeBase database is built from text configuration files. It is built dynamically as needed by CGI programs, or if using mod_perl, it is built once and stored in RAM on the web server.

AcmeBase programs include a Form Builder, Form Filler, and a Form Saver. There is a Searcher which used with the Lister and with forms to look up field values. The Calendar can render any database records with a date column and it can superimpose multiple calendars onto one. There was suppose to be a Reporter but the Lister has encompassed that role, plus it can be used as a tool to set up a list form to edit lists of records.

Acmebase provides the basic tools for setting up a comprehensive corporate information system using the Web and Open Software. It is especially good at tracking customer data. It can secure the access to tables, columns, and rows, and it can monitor and track all changes to the data in an audit trail. It can also track all user interaction with a customer -- the mailings, phone calls, email, and so forth in log tables, and it can schedule future callbacks to the customer.

Status of this Document

This document is a description of all the property settings of AcmeBase. It is a working document and it is updated first whenever any new features are added to AcmeBase.

AcmeBase

AcmeBase is an object oriented description of a SQL database. Table names and column (field) names are objects in a hierarchical structure which have collections of properties. For example, the type property of the customer_name column of the customers table is accessed like:

        $schema->{customers}->{customer_name}->type

This database which describes a database, a meta-database, is intended to be attached to a web server so it can construct data entry forms on the fly and display database information over the Internet according to the user's security constraints.

AcmeBase is used to:

Create SQL DDL statements (create table..., create index...).
Provide hints to search engine on which fields to search.
Construct lists of information from database tables.
Create data entry forms to add new information and to edit existing records.
Create query forms to search and list database information.
Create list edit forms to edit entire lists of database records.
Define help text for database tables and fields.
Define names to use in information and error messages.

Of all the properties described below, only

        Table table_name
        Field field_name
        Type  db_type

are required. All the rest have default values or are inferred by other options.

Schema Files

AcmeBase is built from one or more text files of property settings. Properties are simple key/value pairs, the key being the first word and the value being everything else. Four property names are special key words: Table, Index, Field, and Display. All other key/value settings apply to the previous Table, Index, Field, or Display. (See Examples)

All key words are translated to lowercase words before storing. They are capitalized in the examples below only for visual effect.

Missing values default to yes.

Values can continue on following lines if they are indented more than the key or if the last character of the line is a backslash character (\).

Values can be <<word in which case the value becomes the following lines read verbatim until a line consisting of only word (where word is an arbitrary string). This is similar to Perl's ``here'' document. It is useful for entering JavaScript code.

Multi-word settings can be enclosed by either single (') or double quotes (``).

Anything from the # character to the end of the line is ignored unless it is escaped by the backslash character (\) or it in a value enclosed with quotes (' or ``).

White space at the beginning of the line is ignored unless the line above ended with a backslash character (\) which makes the current line a continuation of the line above.

The - character at the beginning of a line introduces a Form Builder directive. Everything between - and a semi-colon ; or the end of the line is passed to the Form Builder when constructing a form. (See Form Builder Directives)

The < character at the beginning of a line introduces a Form Builder directive, a Form Filler directive, or regular HTML code to be inserted into a form by the Form Builder. (See HTML Codes) Be careful of # characters in the HTML code as they are interpreted as the beginning of comments unless escaped with \.

Lines consisting of <<word begin a ``here'' document which the following lines up to the line consisting of only word (where word is an arbitrary string) is read as HTML and inserted into a form verbatim. The # character is not interpreted as a comment character in the ``here'' document.

A line matching __END__ marks the end of the file. Any remaining lines in the file are ignored.

Form Files

Form files are the same as schema files, but they are stored in a different directory. Form files provide alternate views for entering and editing database information. Security settings in form files are ignored. Security settings are always read from the schema files.

There is a property setting that is specific to form files, the cache (or nocache) property. Schema files are normally read into main memory and cached there for future use. In a mod_perl environment, this can greatly speed up the time it takes to render a form. However, it might not be desirable to have a seldom used form file locked into main memory. To avoid that, specify Nocache at the beginning of a form file before any other settings.

        Cache                   # default setting
        Cache   no              # don't cache the form
        Nocache                 # don't cache the form

Names

In AcmeBase, names are used to refer to information items (the data) in three different realms, or names spaces: 1) SQL, 2) Perl, and 3) in JavaScript references to HTML form fields.

Data is stored in databases accessed by SQL. The data is organized in tables that have a fixed number of columns and an unlimited number of rows. The data in a column of a table is referenced using a fully qualified name of table_name.column_name. A period is used to separate a table's name form its column name.

Database tables are read a row at a time, and the column name for a row becomes a field name which is referenced in Perl and JavaScript as a variable. Periods in variable names are illegal in both Perl and JavaScript, so a double underline (__) is substituted for the period. For example, the SQL name of table_name.column_name is referenced in JavaScript by the field name of table_name__column_name and in Perl with the variable name of $table_name__column_name.

Names of Display fields -- fields on forms that do not have corresponding database columns -- must begin with the characters D_. Where possible, use database field names prefixed with D_ for display-only fields because their value will be automatically filled in if possible by the Form Filler. For example, you can use D_customers__address1 as a form field name to refer to a field that holds a customer's address on an order form.

AcmeBase examples do NOT use the naming convention that has become to be known as the RVBA Conventions (Reddick Visual Basic Conventions), introduced by Leszynski & Reddick in 1992, where a table of customers is named ``tblCustomer'' and a column for the customer's name is named something like ``strCustomerName''. Table and column names in AcmeBase programs are used as nouns in information messages for the user, in error messages, as form field labels, and they are used as column headers in lists. There are property settings below like Title, Row_name, and Label that can be used to explicitly set those terms, but all those properties can be derived from the table and column names if a name like ``customers'' is used to refer to the customers table and a name like ``customer_name'' is used to refer to the customer's name column. The underscore character (_) represents a space in a multi-word name and words are capitalized as needed for titles. Because a table's name refers to a collection of rows or data items, its name should be plural. These are conventions, not rules. AcmeBase does not place any restrictions on the names used for tables and their columns. (The exception being that table names for audited tables need to be three characters less than the maximum length allowed for column names and 2 characters less than the maximum length allowed for table names.)

Variables

Variables in Perl begin with the dollar sign ($). Perl variables can be used in the snippets of SQL code below to refer to the current value of the column in the current record being processed. For example, a SQL where clause could be written like:

        customers.customer_name = '$customers__customer_name'

Notice that the quotes are needed to identify strings to SQL. The resultant clauses must pass SQL syntax rules after the substitutions are made.

All the JavaScript code entered into the AcmeBase schema files is passed through a translator that converts Perl'esque variables -- variable names beginning with $ -- into a form field value references. This is necessary to accommodate having more than one record of a table rendered on a form. It is also convenient -- you use the names as they are defined in the schema files. As with Perl, the variable $var can also be written as ${var}.

At times the JavaScript code needs to refer to a form field object, not its value. Changing the $ to a % on an AcmeBase variable causes it to refer to the form field object, not its value. Thus %table__field.value is the same as $table__field.

Conventions

Keys and IDs

The AcmeBase software requires every record in the database tables to be identified with a unique code or number. The term Key refers to the field to use to access a particular record. The term ID refers to the Key field with a unique number generated by the database software. An ID field is a Key field, but a Key field is not necessarily an ID field. Several tables are used to define codes which are to appear in dropdown lists when entering information into other tables. Those codes are defined by the business and are used as Keys for the table records.

Delete

AcmeBase views the deletion of data records to be primarily an administrative task. Cross links with other database table records could be broken and consequent searches compromised because normal SQL queries eliminates an entire row of information if only one of the links is missing. Deleting records from a SQL database is usually an computationally expensive operation too as the database needs to revise its indexes, so deletions should ideally be scheduled for a slow time. History is lost too.

The Lister program does allow users to delete entire lists of records at one time, but not without creating an audit trail -- that is, if the table is being audited. (See Audit Settings)

Normally, extra column(s) are defined in the database table to indicate the record is not actively used anymore and could be deleted.

Tables which define codes and their descriptions -- code tables -- usually have a one character field named retired which if set to 'Y', signifies that the code is no longer in use. Records in the code tables should not be deleted as they are usually referred to by records in other tables.

Another convention is to use fields named start_date and terminate_date to signify when a record is valid, such as in employee tables.

Table Properties

Table General Settings

Table is a key word which causes the following key/value settings to be attributed to the named table.

        Table   table_name      ./Schema/file_name      # name of file where
                                                        # table def is located

        Table   table_name                              # start table definition

        Title   Table Title                             # for table headings

        Description     Table description.              # for users' help text
                        Can be shortened to Desc.       # synonym

        Note    Notes pertaining to the table. Newlines are not preserved
                in the notes as they are for the description.

To allow for the dynamic loading of settings, a file name can be specified after the table name which contains the actual key/value settings for a table. That allows the initial file opened for a schema to be a table of contents listing the actual files to use for Table definitions.

Another method (now the preferred method) of dynamically loading table settings is to place the settings in a file, one table per file, with the file name being same name as the table name. File name extensions are not considered, therefore, the file customers.txt can hold the settings for the customers table. Place all the files in one directory having the same name as the database -- and specify that directory to be the schema file to initially open. That allows all Table settings to be dynamically loaded as needed.

The initial file/directory to open for the schema files is specified in the site configuration file etc/site.conf with the configuration variable $DBSCHEMA.

Key Settings

All tables have a key, not all tables have an ID key. A unique key for each row of a table is required if the table is to be edited with HTML forms. A unique key value can be entered by users into a Key field, or the database can generate a unique number to use for a key value in the ID field. Either a Key or ID field should be specified for each table. If it is not specified, the first field listed will be used.

        Key     field_name_1                            # key if non-serial

        ID      field_name_1                            # serial number key
        ID_title        Row Name ID                     # title for ID field
        ID_description  ID field description            # for user's help text

        ID_style        font-weight:bold                # CSS style settings
        ID_label        Label Text                      # field label on form
        ID_label_style  font-weight:bold                # CSS style settings

        Noid_label                                      # same as 'ID_label no'

Table Classes

Table classes are advisory. They are inferred by the field naming convention and used for grouping tables. They are intended to eventually be used to setup initial setting and fields when designing databases.

        Class   master                                  # primary table
        Class   detail                                  # items of master
        Class   join                                    # master's extra info
        Class   code                                    # lookup codes
        Class   user                                    # company user info
        Class   log                                     # user's log of actions
        Class   contact                                 # list of contacts addr
        Class   ledger                                  # a ledger of entries
        Class   work                                    # non-user info table

The current classes being experimented with are:

master
Primary data table of company information. Each record is uniquely identified by a system assigned numeric ID number.
detail
Detail information of master tables. They also have unique system assigned ID numbers but they are also related to the master table's key field, and usually have an ``item number'' field. A many to one relation.
join
Ancillary information about master table records. Usually used to hold large fields of optional information so that master table records can be small to speed searches. A one to one relation.
log
Log tables contain user comments about another ``primary'' table. Log tables must have a Master clause to identify which table the log messages belong too.
code
Tables of codes and their definitions. Used primarily as lookup tables. They are characterized by having a user assign unique key and a flag field (retired) used to determine if the code can still be used when entering information.
people
User tables identify company people, not customers or suppliers. They are primarily used to identify authorized users. They are a mix of a master table and a code table. They have a unique system assigned ID number and a user assigned unique code name. They also have fields for a start date and a stop date, dates between which the records are valid (authorized) for entering into other tables. Examples uses are for authorized users, salespeople, and managers.
audit
Audit information tables. These tables are copies of other tables, usually invisible, and contain a history of changes to a table.
work
Work tables are application support tables. They are treated as invisible tables that do not require any user interaction. A possible use of a work table is to contain many to many relationships between two other tables.

Security Settings

Access to tables and fields can be controlled by assigning a security code to be checked with the user's name before access or update of tables.

User security settings are defined in a separate site access configuration file, usually .../etc/access.conf. It's a file that specifies whether a user is allowed (or denied) access, update, insert, or delete of any table, field, program, or any of these codes defined by Secure or Restrict.

Each field definition can have a security code, however, for performance reasons, those codes are not checked unless the table has a security code.

More than one security code can be specified if separated with commas, semi-colons, or spaces.

        Secure                                          # use table name as code
        Secure          sysadmin                        # specify security code
        Secure          admin sysadmin                  # multiple codes
        Security_code   sysadmin                        # used internally

Security codes can be defined which will restrict queries on the table to rows that are not restricted by the user's security code. Security codes above restrict access to a table and it's columns, where as these Restrict codes restrict access to table rows.

If the user is not allowed to ``Access'' the Restrict code, then the where clause is applied to queries on the table.

If the user is inserting new data and they are denied ``Insert'', then the Restrict code acts just like Secure: the insert action is denied.

Always use fully-qualified names (e.g. customers.customer_name) in the where clauses.

        Restrict        code1 code2 code3               # define all codes
        Restrict_codes  code1 code2 code3               # used internally

        Restrict_code1  sql_where_clause                # where clause for code1
        Restrict_code2  sql_where_clause                # where clause for code2
        Restrict_code3  sql_where_clause                # where clause for code3

Specifying restrict_where applies a where clause to all users. It overrides all restrict codes and their corresponding where clauses above. Because the restrict_where clause is added on to a where clause, it should begin with ``and''. The above Restrict_code statements should NOT begin with ``and''.

        Restrict_where  and sql_where_clause            # used internally

Security codes can be defined for export or email operations. If a table has a Export_secure code(s) defined and the user does not have ``Access'' privilages for any of those codes, then the export operation is restricted. (See Export Lists)

        Export_secure           staff                   # export security code
        Export_security_code    staff                   # used internally

The same is true for the email operation. If a table has a has a Email_secure code(s) defined and the user does not have ``Access'' privilages for any of those codes, then the email operation is restricted.

        Email_secure            staff                   # email list security code
        Email_security_code     staff                   # used internally

Limits

Lists are not ``chunked'' when rendered. If a user asks for a list of ten thousand names, the system will try to render ten thousand names. Usually, users will not knowingly do so. If they do, it usually means: 1) they didn't realize the search query would result in such a large list and they could use more training, 2) they exposed a program error resulting in a super large query result, 3) they are actually trying to steal the ``master'' list. All cases command the attention of an administrator, so a set of ``Limit'' properties are defined which can be used to set realistic limit numbers and to set an email address to send alerts to when limits are exceeded.

Limit defines the record number to trigger a limit event if reached. If the number of records rendered in a list reaches the Limit number, then the list is terminated and an email message is sent to the Webmaster and any other email address listed in Limit_alert. If you want to warn the administrators before aborting the list, use Limit_warn to set a limit which when reached will email an alert message but will not terminate the list.

Most SQL engines now support setting limits to the size of resulting queries. The limit settings here are passed along to the SQL engine.

The Nolimit and Nolimit_alert turns off limit checking and sending email alerts respectfully.

        Limit           2000                            # number of records
        Limit_alert     someone@somewhere.com           # send email upon limit
        Limit_warn      1000                            # number of records
        Limit_abort     2000                            # used internally

        Nolimit                                         # no limit checks
        Nolimit_alert                                   # do not email alerts

Audit Settings

Columns to track who and when data records change are automatically added to data tables if Audit is specified. Indexes on those fields are created and a history table is cloned.

        Audit           yes                             # "yes" is optional

It is possible to exclude changes to data table columns from being saved in the audit history table.

        Audit_exclude   field1,field2,field3            # don't audit fields

The following settings allow control over field names holding audit info. Default names for the column names can be specified in the site configuration file etc/site.conf.

        Audit_entry     entry_date                      # entry date audit field
        Audit_update    last_update                     # timestamp audit field
        Audit_user      last_update_by                  # user name audit field

The name of the table holding the history of changes can be specified by Audit_table. Usually the name of the history table is derived from the data table's name by adding to it the prefix specified by the $audit_table_prefix variable in the site configuration file etc/site.conf.

        Audit_table     table_name                      # default: y_table_name

The audit functions allow a user to double-click a field label on an edit form and pop up a window showing who changed the field's value and when they changed it. A report of all the changes made to all the columns of a record can also be produced.

Auditing a table ensures the serialization of edits to the table. If someone updates the database record while you are currently editing it, the system will display a reconciliation screen to allow you to choose which edits to apply.

Dispatch

Following programs are primarily used to produce links when listing records. Values are script URLs relative to the site's root directory. Database fields can be inserted into the strings by using corresponding Perl variable names (e.g. $table__field).

The following links have the table's key field and value automatically appended to all the calls except for Save_prog, New_prog, and Import_prog for which only the table's name is appended.

        View_prog       /cgi-bin/view.cgi               # view of record
        Edit_prog       /cgi-bin/form.cgi?template=/templates/customer.htm;request=edit
        New_prog        /cgi-bin/form.cgi?template=/templates/customer.htm
        Copy_prog       /cgi-bin/form.cgi?table=codes;code_key=mycode
        Log_prog        /cgi-bin/logprog.cgi            # user log
        Print_prog      /cgi-bin/form.cgi?template=/templates/customer.htm;print=yes
        Import_prog     /cgi-bin/import.cgi             # import program
        Save_prog       /cgi-bin/save.cgi               # save program

The following links should accept the entire set of list control variables. The links are not adjusted in any way like those above.

        List_prog       /cgi-bin/list.cgi               # list program
        Count_prog      /cgi-bin/list.cgi               # count list program

        Export_prog     /cgi-bin/export.cgi             # list export
        Emaillist_prog  /cgi-bin/list/email.cgi         # send email to list items
        Loglist_prog    /cgi-bin/list/log.cgi           # add log to list items
        Editlist_prog   /cgi-bin/list/edit.cgi          # edit list items
        Savelist_prog   /cgi-bin/list/save.cgi          # save list items edits

Help/Error Message Hints

The following settings specify the phrases to use to refer to the database row in help and error messages displayed to the user.

        Row_name name                                   # for user messages
        Row_title title                                 # for user messages

Help messages and error messages refer to table rows and fields. The actual names used can be specified instead of the system trying to infer them from the table name. A Table name is usually plural, Row_name is singular. Row_title is a capitalized row_name.

Search Hints

The following are defaults for the search engine searching this table. They cause indexes to be automatically created on their fields.

Specifying a Code_field allows searches consisting of all lowercase or all uppercase to be interpreted as a code and to search the Code_field, not the Name_field.

More than one field can be specified in the following properties except Last_name_field and First_name_field by separating them with commas. It produces a query like: (field1 = 'search' or field2 = 'search') or in the case of name fields, (field1 matches 'search' or field2 matches 'search'). Note that it is difficult for database engines to optimize these muliple field searches even though there are indexes built on each of the fields being searched.

        ID_field        field_name                      # unique ID number field
        Code_field      field_name                      # unique code field
        Name_field      field_name                      # name field for search

Searches that look like phone numbers use the Phone_field if it is specified. Phone numbers must match exactly unless the Phone_field_match property is specified (see below).

        Phone_field     field_name                      # phone number

Specifying an Email_field tells the Searcher to use the email field for searches that look like an email address (e.g. Name@some.company.com). It also adds an option to the Lister buttons allowing you to send email to an entire list of addresses in the email field (see Email List Hints), and it causes an index to be created on the field_name. Another subtlety is that if more than one email address is listed in Email_field, the corresponding name listed in Name_field is used as the ``friendly name'' for the email address.

        Email_field     field_name                      # email address field

If first names and last names are stored separately in the database table, use the Last_name_field and First_name_field properties.

        Last_name_field field_name                      # last name field
        First_name_field field_name                     # first name field

Searches on the Code_field or Phone_field cannot contain any magic text matching characters (e.g. * ? . ). The Searcher will search the Name_field instead. However, the search needs to only match the beginning part or ending part of the code (or phone number) if the following is set to one of the constant values 'begin', or 'end'. The default setting is 'equal' which causes the Searcher to require the search string to match the code field (or phone field) exactly.

        Code_field_match        begin
        Code_field_match        end

        Phone_field_match       begin
        Phone_field_match       end

Select List Hints

The following properties are the default settings for another table's field properties of Choose or Search. The first value in Select is the value returned by HTML <select>. If there are more fields listed in Select than listed in Select_format, information is available to the program but not displayed to the user. Note that the fields listed in Select_order should also appear in the Select setting.

        Select          key, key, description           # fields in SELECT

        Select_fields   table.key, table.key, ...       # used internally
        Select_join     table.field = table2.field2     # optional join clause
        Select_where    retired <> 'Y'                  # with where clause
        Select_where    terminate_date is null or current_date < terminate_date
        Select_order    field desc                      # list sort order clause
        Select_order_by_clause t.f1,t.f2                # used internally

The Select_sql is the property that is used internally, and if not defined, assembles the SQL query script from the Select_fields, Select_join, Select_where, and Select_order properties.

        Select_sql      select t.f1, t.f2, ...          # used internally

Choose lists can get a default value and formatting options from the following properties. Search lists get their default formatting options from the Pick properties. Select_format cannot contain any embedded HTML tags, as the display is formatted for the HTML <select> tag.

The format string used in Select_format is very similar to Perl's sprintf() format strings but can have embedded functions. (See List Formats)

        Select_default  value                           # default value of key
        Select_format   "format string"                 # display format of list
        Select_rows     number                          # number of display rows

Query List Hints

Query lists are special versions of Select lists that are rendered in query forms, the forms that are used to enter the parameters of database queries. Lists of values suitable for querying the database are quite often supersets of the values available for data entry, therefore Query forms refer to the Query_where and Query_all parameters instead of the Select_where property. If Query_where or Query_all is not defined, then the Select_where property is used instead.

The Query_where clause is used instead of the Select_where clause when building lists for query forms. Setting Query_all to ``yes'' builds the list without using the Select_where clause, essentially listing all rows.

        Query_where     terminate_date < current_date   # override Select_where

        Query_all                                       # "yes" is optional

Pick List Hints

Pick lists are used to select a record from the results of a search. Pick lists are very similar in function to select lists. Pick lists are better formatted, but the user needs to use the mouse to select a record, whereas a record in the select lists can chosen with the keyboard arrow keys and the enter key.

The Select List Hints are used if these are not defined. Like Select, the first field listed in Pick is the value used as the ``Pick'' value.

        Pick            key, key, description, ...      # fields to list

        Pick_fields     table.key, table.key, ...       # fully-qualified fields
        Pick_join       table.field = table2.field2     # optional join clause
        Pick_where      retired <> 'Y'                  # with where clause
        Pick_order      field desc                      # list sort order clause
        Pick_order_by_clause t.f1,t.f2                  # used internally

The Pick_sql is the property that is used internally, and if not defined, assembles the SQL query script from the Pick_fields, Pick_join, Pick_where, and Pick_order properties.

        Pick_sql        select t.f1, t.f2, ...          # used internally

Search lists can get default value and formatting options from the following properties. Pick_format can contain embedded HTML tags, unlike Select_format.

The format string used in Pick_format, Pick_header, and Pick_footer is very similar to Perl's sprintf() format strings but can have embedded functions. (See List Formats) The default header is the table column names. There is no default footer.

        Pick_format     "format string"                 # display format of list

        Pick_title      "Table List Title"              # optional list title

        Pick_header     "format string"                 # default is `no'
        Pick_header                                     # display default header

        Pick_footer     "format string"                 # default is `no'
        Pick_footer                                     # display default footer

Pick columns totals can be computed by specifying Pick_total. It is a comma separated list of fields. The totals of the columns listed can be displayed in the Pick_footer format by using the &total('qty') function. (See List Formats)

        Pick_total              qty,amount              # compute totals
        Pick_total_fields       table.field1, ...       # used internally

List Hints

Following are default setting for creating a list display of records. They are used as defaults for the Select List Hints. Note that List_format can contain HTML tags, but Select_format cannot (Select list is displayed inside a HTML <select> tag). The format string is very similar to Perl's sprintf() format strings but can have embedded functions. (See List Formats)

        List            field, description, ...         # fields to list

        List_fields     table.field, table.field2, ...  # used internally
        List_join       table.field = table2.field2     # optional join clause
        List_where      retired <> 'Y'                  # with where clause
        List_order      field desc                      # list sort order clause
        List_order_by_clause t.f1,t.f2                  # used internally

The List_sql is the property that is used internally, and if not defined, assembles the SQL query script from the List_fields, List_join, List_where, and List_order properties.

        List_sql        select t.f1, t.f2, ...          # used internally

The format string used in List_format, List_header and List_footer is very similar to Perl's sprintf() format strings but can have embedded functions. (See List Formats) The default header is the table column names. There is no default footer.

        List_format     "format string"                 # display format of list

        List_title      "Table List Title"              # optional list title

        List_header     "format string"                 # default is `no'
        List_header                                     # display default header

        List_footer     "format string"                 # default is `no'
        List_footer                                     # display default footer

List Totals

List columns totals can be computed by specifying List_total. It is a comma separated list of fields. The totals of the columns listed can be displayed in the List_footer format by using the &total('qty') function. (See List Formats)

        List_total              qty,amount              # compute totals
        List_total_fields       table.field1, ...       # used internally

Subtotals can be grouped together by specifying both List_total above and List_group. The list of fields should be sorted fields. The order that the fields are listed in the List_group is important as all fields listed to the right of the field triggering the after group totals also get totaled.

 ##########
 # TODO - The following still needs to be implemented.
 #      List_group              qty,amount              # compute totals
 #      List_group_fields       table.field1, ...       # used internally
 ##########

List Extensions

Lists have a button bar that holds buttons which perform actions on the list, such as save and export. Custom buttons can be added to the button bar by including the HTML code. Buttons are simple A tags surrounded by <td> and </td>, with an href attribute and a title attribute (for pop-up help messages). There are no class attributes nor are there any onmouseover or onmouseout attributes needed, although you can certainly use them.

        List_buttons_html       <td><a href="url" title="help">Custom</a></td>

List Filters

Lists can be filtered by the contents of a field. For each group of the field's values, the number of records is counted and a special form is displayed at the end of a list allowing the list to be regenerated with only the groups of interest. Several filters can be specified by listing the fields separated by commas.

Special filters that lists only two groups of values: empty (null) and not empty, are referred to as ``null'' filters. They are identified by adding the keyword ``null'' after the column name in List_filter.

The List_filter_desc property lists columns that contain the description for the List_filter_fields names. This is still experimental, but the intention is to be able to use a description in a lookup table for a field value used as a filter.

        List_filter             field1,field2 null      # filter list by field
        List_filter_fields      table.field1, ...       # used internally

        List_filter_desc        table.field1, ...       # description column

The List_selector is a special filter. If the column named in List_selector passes the condition that is defined in List_selector_cond (and in List_selector_param1, 2, and 3), a ``checked'' image is displayed next to the record. Checked records are records that are selected to be processed. Individual records can be checked or unchecked by the user before exporting or processing the list.

        List_selector           table.field             # select if not empty

        List_selector_cond      not null                # default value

        List_selector_param1    value
        List_selector_param2    ending_value
        List_selector_param3    value1, value2, value3, ...

        List_selector_param     value   # synonym for List_selector_param1

        List_selector_desc      Optional description of selector filter.

The List_selector_cond can be set to one of the following conditions. These are the same options that appear in the list program's customization menu for the ``Selector Filter''. The second column is the number of parameters the condition takes, with the exception of in and not in, which take only one parameter that is a list of comma separated values explained below.

        null                    0
        not null                0
        eq                      1
        ne                      1
        lt                      1
        le                      1
        gt                      1
        ge                      1
        between                 2
        not between             2
        in                      3
        not in                  3
        matches                 1
        not matches             1

The List_selector_param1, List_selector_param2, and List_selector_param3 are the parameters to use with the List_selector_cond. Some conditions don't use any parameters, most use 1, and a couple use 2 parameters. The in and not in conditions use the List_selector_param3 parameter which is a list of comma separated values. The matches and not matches identify text pattern matches, otherwise known as regular expressions. List_selector_param is a synonym for List_selector_param1.

By using List_filter and List_selector and their options, lists of information can be extracted from the database, reviewed and revised by the user, and then acted upon. The resultant lists can be saved, exported, or edited. See Export Lists for options on exporting a list to a text file and see Edit List Hints for options controlling the updating of a list.

Other properties that pertain to lists are List_include (see Computations) and List_stylesheet (see Appearance).

Form List Hints

Form lists are lists on a data entry form. An example is the list of order items that are on an order entry form.

        Form_list        key, description, ...          # fields in list window

        Form_list_fields table.field, table.field2, ... # used internally
        Form_list_join  table.field = table2.field2     # optional join clause
        Form_list_where retired <> 'Y'                  # with where clause
        Form_list_order field desc                      # list sort order clause
        Form_list_order_by_clause t.f1,t.f2             # used internally

The Form_list_sql is the property that is used internally, and if not defined, assembles the SQL query script from the Form_list_fields, Form_list_join, Form_list_where, and Form_list_order properties.

        Form_list_sql   select t.f1, t.f2, ...          # used internally

The format string used in Form_list_format, Form_list_header and Form_list_footer is very similar to Perl's sprintf() format strings but can have embedded functions. (See List Formats)

        Form_list_format "format string"                # display format of list

        Form_list_title  "List Title"                   # optional list title

        Form_list_header "sprintf string"               # default is `no'
        Form_list_header                                # display default header
                                                        # use form_list_format
        Form_list_footer "sprintf string"               # default is `no'
        Form_list_footer                                # display default footer
                                                        # use form_list_format

Form list columns totals can be computed by specifying Form_list_total. It is a comma separated list of fields. The totals of the columns listed can be displayed in the Form_list_footer format by using the &total('qty') function. (See List Formats)

        Form_list_total         qty,amount              # compute totals
        Form_list_total_fields  table.field1, ...       # used internally

Forms were originally intended to have input fields for both master and detail tables. However, that made the Form Saver very complex and it created some very long form field names. Currently forms can only save fields from one table and its joining tables. All tables must have a one to one relationship.

To enter detail records such as an order items, pop-up screens are used and when they are saved, a list is built on the master form.

The detail item's fields can be formated in an HTML <input> tag by using the Form_list_format property. Even though a list of several items would have several <input> fields with the same name, you can use the TOTAL(), SUM(), or AVG() functions to sum or average those fields. (see Aggregate Functions)

Edit List Hints

Group edits on a list can be performed. Some fields are personal to each record and can be excluded from group edit changes if listed in the Editlist_exclude clause. Fields listed in Personal_fields or those that have the Personal property set to ``yes'' are also excluded. (See Personal Hints and That's Personal). For example, a form for editing a list of customers should exclude the customer_name field which is usually different for each customer. An ``Edit'' button appears on the list's button bar unless Editlist is set to ``no''.

        Editlist        no                              # default is 'yes'
        Editlist_exclude        field1,field2, ...      # exclude fields

Email List Hints

Email can be sent to a list. The field(s) to use to for email addresses is set by Email_field_list. It is a comma delimited list of field names. If the field names are not listed, the field(s) in the Email_field parameter described above is used. The difference between Email_field_list and Email_field is that Email_field influences the Searcher's queries, whereas the Email_field_list fields do not. Setting Email_field_list or Email_field causes an ``Email'' button to appear on the list's button bar.

        Email_field_list        field1,field2           # email fields
        Email_field_list_fields field1,field2, ...      # used internally

Names for each email address listed in Email_field_list can be listed in Name_field_list. A name should be listed for each corresponding email address, otherwise the first name listed is used. Like Email_field_list, the default value for Name_field_list comes from the searcher hint Name_field.

        Name_field_list         field1,field2           # fields with names
        Name_field_list_fields  field1,field2, ...      # used internally

The columns queried from the database for merging into email can be set with Email_list. Normally, all the table's fields (those that the user can access) are exported to the email server.

        Email_list              field1,field2           # exported fields

        Email_list_fields table.field, table.field2, ...# used internally
        Email_list_sql          select t.f1, t.f2, ...  # used internally

A separate security code for email can be specified, different from the table's or field's security codes. If a user cannot email, the ``Email'' button does not appear on the list's button bar.

        Email_secure            staff                   # email list security code
        Email_security_code     staff                   # used internally

The email feature can be turned off by setting Email to ``no'', or by setting Noemail to ``yes''. The default value for Email is ``yes'' if Email_field is defined.

        Email                   yes/no                  # can email to a list
        Noemail                 yes/no                  # cannot email to a list

Personal Hints

A list of fields that are ``personal'' to the table can be listed using the Personal property. It is a shortcut in lieu of setting Personal for each field. See That's Personal.

Personal fields are those fields that ususally have different values for each record in the table. Labeling fields as personal assist the system generating forms for importing or editing lists of records -- the personal fields should not have data entry fields on those forms.

        Personal                yes                     # all fields are personal
        Personal                field1, field2, ...     # list of personal fields
        Personal_fields         field1, field2, ...     # used internally

Export Lists

Lists can be exported to text files. The feature can be turned off by setting Export to ``no'', or by setting Noexport to ``yes''.

A security code separate from viewing the information can be used to restrict its export.

A log file is kept of ``who exported what when'' by default, which can be turned off by setting Export_log to ``no''. Export_log can be set to a different log file than the general log file defined in the site configuration file (.../etc/site.conf).

An email message is sent to the Webadministrator or the email address specified by Email_alert when tables are exported. That feature can be turned off by setting Export_alert to ``no''.

        Export                  yes/no                  # can export list
        Export_log              yes/no/logfile          # keep log of exports
        Export_alert            yes/no/email address    # send email upon export

        Export_secure           staff                   # export security code
        Export_security_code    staff                   # used internally

Exporting a list is quite often viewed as an ``action'' on the list which means that (aside from needing to be recorded in a security log) database tables need to be simultaneously updated and/or new information needs to be entered into the database for each record exported. For example, making an entry in the customer log file for each customer receiving a mailing.

To accommodate such, event statements onExport, beforeExport, and afterExport can be defined. The onExport statements are very similar to the onSave statements described below, they both can contain Perl code or SQL statements depending on whether the first word is recognized as a SQL statement insert, update, delete, or the key word set. The beforeExport statement, the export SQL query statement, and the onExport statement are issued to the database engine as one transaction. The afterExport statements are sent to the database engine as a different transaction if the first didn't fail, just like the Save Events described below.

A temporary table of the key values of the rows to export is created by the system according to the SQL and according to the List_filter and List_selector filters. The name of the only column in the temporary table is the same name as the table's key column. The temporary table's name is ``t_keyval'' (unless it is changed in a program's code). You need to know the temporary table's name for referring to the export variable values in the ``update'' statement in the onExport clause.

The set keyword in onExport automatically builds a SQL update statement joining the temporary table to update each record that was exported.

See Save Events below for a more complete explanation of the event statements.

        onExport        set export_flag = 'Y'           # update exported records
        onExport        update table set field = ...    # SQL done when exported
        onExport        insert into table ...           # SQL done when exported
        onExport        delete from table ...           # SQL done when exported

        beforeExport    set export_flag = 'Y'           # update exported records
        beforeExport    insert into table ...           # SQL done when exported

        afterExport     set export_flag = 'Y'           # update exported records
        afterExport     insert into table ...           # SQL done when exported

There can be different types of exports defined. The Export_types lists out the options to the user as a drop-down list. Then for each code value listed in Export_types, a specific set of onExport, beforeExport, and/or afterExport events can be defined, each of which override the general event statements if they are also defined.

        Export_types    code1 "description 1"
                        code2 "description 2"
                        code3 "description 3"


        onExport_code1  set ...                         # override onExport
        onExport_code2  update table set field = ...    # override onExport

        beforeExport_code1 set ...                      # override onExport
        beforeExport_code2 update table set field = ... # override onExport

        afterExport_code1 set ...                       # override onExport
        afterExport_code2 update table set field = ...  # override onExport

The Export_instructions defines HTML text that is displayed on the export confirmation screen. It can be used to explain the different Export_types.

        Export_instructions <<End_Of_Instructions       # HTML text

Table Relationships

Join Tables

The following Join parameters use to be inserted directly into SQL statements verbatim. Now they are parsed and reassembled into SQL92 join expressions. All field names should be fully-qualified with their table names -- even the fields of the current table.

        Join            table.field1 = table1.field     # one to one link
                    and table.field2 = table2.field     #  "and" separated
                    and table.field3 = table3.field     #  multiple joins

Master Tables

Like the Join property above, the Master property values are inserted into the SQL statements verbatim, so use fully-qualified table names.

        Master          table.field1 = table1.field     # many to one link
                    and table.field2 = table2.field     #  "and" separated
                    and table.field3 = table3.field     #  multiple masters

The Master properties have changed some since originally conceptualized. The Master clause is now used primarily for filling in display-only fields on detail forms that refer to the master table fields. It is no longer used to maintain table relationships so that a master table is updated when it's detail table is modified. Use Save Events for that. It is now preferred to just use forms and Master_assign statements to update the master form when filling in detail forms, and then repost the master form when done.

Master forms that open detail forms pass a parameter to the detail form (named master) that identifies the master table and field controlling the detail form. Detail forms that have been opened by another master form (e.g. an order_items form opened by an orders form) can initialize their fields from the master form. Although a table can have multiple master tables in the Master clause, the Master_assign properties are executed for all the master table forms. Therefore, it is safest to have only one master table. You can check the existence of a form field before posting it as in the example below, however this technique only works for Master_assign, and not for Master_assign_options or Master_assign_input as those clauses have to be re-arranged into JavaScript function calls and the if statements break that process. (see Assignment Functions)

Refer to Search Button for more explanation on assigning options or input selections.

Note the use of % to refer to the form field object and the use of $ to refer to the form field value. The %M and $M refers to the field in the master form window, the other window, not the current (detail) form.

        Master_assign           if (%M.table__field) $field = $M.table__field

        Master_assign_options   $field1 = table_name.field1
                                $field2 = table_name.field2

        Master_assign_input     $field1 = table_name.field1

Detail Tables

A detail property can be specified for a table or for a field. Defining a Detail table as a Table property causes the Form Builder to place the table after the table form, although its actual placement can be specified by the <!--%Detail--> form builder directive (see Form Builder Directives. Defining a Detail table as Field property (see Detail Records Input) causes the detail table to be placed after that field. Note that the syntax allows only one detail table to be linked to any one field, including the key field.

        Detail                  table.field             # one to many link
        Detail_list             field1,field2,field3    # detail list fields
        Detail_list_fields      table.field1, ...       # used internally

        Detail_join             table.field = table2.field2
        Detail_where            table.field = 'Bogus'   # filter retrieved rows
        Detail_order            field1,field2           # detail sort order

The Detail_sql is the property used internally. If it is not defined, then the SQL query is assembled from the Detail_join, Detail_where, and Detail_order properties, which get their default values from the detail table's List properties.

        Detail_sql              select t.f1, t.f2, ...  # used internally

The Detail_format property is used to format the list of detail records in the master form. The fields can be put into table columns by separating them with ``<td></td>'' HTML tags. The format strings used in Detail_format, Detail_header and Detail_footer are very similar to Perl's sprintf() format strings but can have embedded functions. (See List Formats)

        Detail_format           "format string"         # display format
        Detail_header           "format string"         # header display format
        Detail_header                                   # display default header
                                                        # use detail_format
        Detail_footer           "format string"         # footer display format
        Detail_footer                                   # display default footer
                                                        # use detail_format

Detail columns totals can be computed by specifying Detail_total. It is a comma separated list of fields. The totals of the columns listed can be displayed in the Detail_footer format by using the &total('qty') function. (See List Formats)

        Detail_total            qty,amount              # compute totals
        Detail_total_fields     table.field1, ...       # used internally

The title over a list of detail records is specified by Detail_title. This is above the Detail_header above which is just above the columns. It can contain HTML tags. The text of the button used to enter new detail records can be specified with Detail_button_title. It cannot contain HTML tags, as the text becomes the value attribute of a button tag.

        Detail_title            Detail Items            # title of detail list
        Detail_button_title     New Item                # text on detail button

Forms that open detail forms -- the master form -- can update their fields after each detail form is saved with the Detail_assign property. Form fields in the detail form window are identified by %D. or by $D. prefixes. Each statement is separated with a semicolon ; or a newline.

        Detail_assign           $mastertable__field = $D.detailtable__field

        Detail_assign_options   $field1 = table_name.field1

The <input> fields that are displayed to enter a text array can be restricted to those options that are specified in a text array value. (See Input Arrays)

        Detail_assign_input     $field1 = table_name.field1

The Detail_next specifies the form field to focus after a successful entry of a detail record. Identify the form field with the % character, not $.

        Detail_next             %field

Details records of master records usually have a number field used for sorting. Specifying this implies this table has a master. It causes a compound field index to be created with the table's key field. This property is still experimental.

        Number_field            field_name      # item number field

Log Tables

Log tables are tables holding user comments about records in another ``primary'' table. The Log property is used to identify the related table of log messages. That table's schema must have a Master clause (see Master Tables) identifying the ``primary'' table and field which the log records are attached to.

        Log             table_name              # table of log messages

Computations

The compute statements are executed whenever ANY field is changed. Form field variable names are denoted by the % character. They are converted into the actual form name at runtime. Form field variable's values are denoted by the $ character. That is, %field.value is the same as $field. Beware of JavaScript trying to always interpret the + sign to be a concatenation operator instead of an addition operator.

Fields also have a Compute property. They are accumulated into one script with the table's Compute scripts appended after them.

        Compute         $field1 = $field2
                        $field3 = $field1 + $field2
                        $field3 = parseFloat($field1) + parseFloat($field2)

The Initialize statements are just like the Compute statements except they are executed only when the form initializes before entering new information.

Fields also have an Initialize property. They are accumulated into one script and combined with the table's Initialize script, but unlike the Compute properties, the field scripts are appended after table's script.

        Initialize      $field1 = $field2
                        $field3 = parseFloat($field1) + parseFloat($field2)

Inclusions

The Include property lists file names of script files that are to be included in the HTML page header of a form. Separate the file names with commas or spaces. The files are assumed to be located relative to the directory specified in the system configuration variable $Scripts. If they cannot be found there, then $Script_Local configuration variable is used.

        Include         file.js file2.js

If the first character of the value is a < character, the value is inserted into the HTML page header as is.

        Include         <script src="/scripts/file.js"></script>

Scripts can be included in forms, lists, pick lists, or views with the following properties. Like the Include property, their values can be either a list of file names, or the actual HTML tag inserted into the form, list, or view. Form_include is a synonym for Include.

        Form_include    file.js
        List_include    file.js
        Pick_include    file.js
        View_include    file.js
        Query_include   file.js

Scripts

The Script property is essentially a synonym for Include with one small difference. The Include files/HTML are inserted into the document before the Script files/HTML.

        Script          file.js file2.js
        Script          <script src="/scripts/file.js"></script>

Scripts can be included in only the forms, lists, or views with the following properties.

        Form_script     file.js
        List_script     file.js
        Pick_script     file.js
        View_script     file.js
        Query_script    file.js

Do Do's

There are three phases to processing a form: building it, filling it with database values, and saving the form values to the database: the Builder, the Filler, and the Saver. For each phase: there is a Do property consisting of Perl code that is executed in each of the phases.

        Do              Perl code               # executed in every phase
        Do_builder      Perl code               # executed in Builder phase
        Do_filler       Perl code               # executed in Filler phase
        Do_saver        Perl code               # executed in Saver phase

The Do_builder Perl code is executed before a form is built. It is useful for defining routines that can be invoked with the <!--% Do ... --> form builder directive. (See Form Builder Directives)

The Do_filler Perl code is executed before a form is filled but after the database has been queried. It can define form field values simply by assigning values to variables that are named after the form fields, which are named after database table columns. (See Names and Variables)

The Do_filler Perl code can also define routines that can be invoked with the <!--$ Do .. --> form filler directives, just like Do_builder. (See Form Filler Directives)

The Do_saver Perl code is executed after CGI form field values are read from the browser but before they are saved to the database. The code can add or change values posted to the database simply by assigning values to variables whose names correspond to database column names. (See Names and Variables) SQL statements can be executed by using the pre-defined Perl function sql().

The Do_saver code is also a good place to define functions that are invoked in the beforeSave, onSave, and afterSave properties.

There is a Do_query property that is independent of the above Dos. It is Perl code that is executed before filling Query forms. Like Do_filler, it can also define routines to be invoked with the <!--$ Do .. --> form filler directives.

        Do_query        Perl code               # executed in Query Filler

Save Events

onSave

The onSave clause is executed for every table record saved. It is often used with tables that have a Master property to simultaneously update the master table when changes occur. There can only be one onSave property but it can have multiple statements if separated with semicolons (;). The contents can be Perl code or SQL statements. SQL statements are interpreted if the first word is a keyword set, update, insert or delete. The set ... value cannot be combined with the other keywords.

If you include a where clause, use fully-qualified Perl-type variable names to refer to database field values. For example, use $customers__customer_id to refer to the current value of customers.customer_id column.

        onSave  set order_total = (
                        select sum(item_total)
                        from order_items
                        where order_items.order_id = $orders__order_id
                        )

The set clause is a shorthand for update this_table clause. It has the where clause of where this_table.key = $this_table__key appended to it automatically. In other words, it is for updating the current record. It follows SQL syntax for the set clause, i.e, use comma delimited assignment clauses.

        onSave  set field1 = field2 + field3, sfield1 = sfield3 - sfield2

The keyword values update, insert, and delete introduce actual SQL statements that are submitted to the database ``as is'' with the variables substituted into the statements first.

        onSave  update table set field = ...            # SQL done when saved
        onSave  insert into table ...                   # SQL done when saved
        onSave  delete from table ...                   # SQL done when saved

If the onSave property does not start with any of the keywords set, update, insert, or delete, then it is assumed to be Perl code. SQL statements can be added to the SQL transaction by using the sql() function. The values of the fields about to be posted to the database can be accessed using the convention $table__field as described above in Variables.

Other functions are available for saving database records.

        onSave update("orders",12345,{ total_amt => $order_items__total_amt });
        onSave insert("ad_response",{ ad_source => $customers__ad_source });
        onSave delete("calendar_event",54321);

The advantage of using these functions instead of the above SQL statements is that audited tables are handled automatically.

The save() function not only accommodates audited tables like update(), but it also applies the target table's beforeSave and onSave triggers. It can also update several records at once. Multiple records are updated by entering a where clause -- starting with the word ``where'' -- as the second argument instead of the table record's key value.

        onSave save("orders",12345,{ total_amt => $order_items__total_amt });
        onSave save("oitems","where oitems.oid = $orders__oid",{ cancel => "yes" });

The save() function is the prefered way to update related tables when data is saved.

Perl code in the onSave property is executed before the database is updated. The SQL statements in the onSave property are appended to the SQL statements updating the database and then they are all submitted to the database engine as one transaction.

beforeSave

The beforeSave clause is executed before any SQL statements generated by the system to update the database, but after the Do and Do_saver clauses. As in the onSave clause above, if it does not begin with any of the keywords set, update, insert, or delete, then it is assumed to be Perl code. When executing Perl code, SQL statements can be specified to execute as part of the update transaction by using the sql() function.

        beforeSave      system("myprog");

        beforeSave      sql("insert into mylog (savetime) values (current_timestamp)");

afterSave

The afterSave clause is executed after the SQL transaction has been submitted to the database engine.

        afterSave       system("cleanup.pl $customers__customer_id")

Appearance

The way a form is rendered can be changed by specifying a CSS file for the Stylesheet property. More than one CSS file name can be specified if separated with commas or spaces. The files are located relative to the directory specified in the system configuration variable $Styles. The styles in the stylesheet files augment the standard styles for forms. The standard stylesheets used are $Styles/common.css and either $Styles/form.css, $Styles/list.css, $Styles/pick.css, or $Styles/view.css depending upon whether a form, list, pick list, or a view is being rendered.

        Stylesheet      skin.css file2.css

Like the Include property, if the first character of the value is a < character, the value is inserted into the HTML page header section ``as is''.

        Stylesheet      <link rel="stylesheet" href="/styles/list.css" type="text/css">

Stylesheets can be specified for lists, pick lists, and views. The above Stylesheet is just a default stylesheet used for the following. They all have the ability to list several files, or to insert the actual HTML text.

        Form_stylesheet skin.css form_file2.css
        List_stylesheet skin.css list_file2.css
        Pick_stylesheet skin.css pick_file2.css
        View_stylesheet skin.css view_file2.css

Topping it all off, the Print_stylesheet property specifies a stylesheet that gets included after all the other stylesheets when printing the form/list/view. This allows variable font sizes for screen displays so users can customize their view, but sets fixed font sizes so formatting is correct when printing. The specified stylesheets are included after a standard form_print.css, list_print.css, or view_print.css stylesheets are included.

        Print_stylesheet        aux_print.css

As with Stylesheet, Print_stylesheet is used as a default stylesheet for the following.

        Form_print_stylesheet   aux_form_print.css
        List_print_stylesheet   aux_list_print.css
        Pick_print_stylesheet   aux_pick_print.css
        View_print_stylesheet   aux_view_print.css

Index Properties

Index General Settings

The Index properties identify extra foreign indexes. Key indexes, search indexes, join indexes, and audit indexes are automatically generated without being specified in the Index section. The names of indexes are generated automatically. They are simply the table name with an appended number. The desc keyword can be used in the comma separated list of fields to create an index in descending order.

        Index   field_name_2 desc,field_name_3          # foreign index fields

        Type    unique                                  # index type

Field Properties

Field General Settings

Field is a key word which causes the following key/value settings to be attributed to the named field.

The field Type is a database type as defined by the database, such as: char, varchar, integer, decimal, float, .... It is the only field property that is required.

        Field   field_name_1                    # start field definition

        Type    varchar(7)                      # required database type

        Title   Field Name 1                    # column header

        Description     Description of field and its use.
                        Can be shortened to Desc.

        Note    Notes pertaining to the field. Newlines are not
                preserved in the notes as they are for the description.

Field Labels

Field labels can be specified for input fields. They also can be ``turned off'' if an unlabeled input field is desired. A custom CSS style can be specified using the Label_style property.

        Label   Field Label Text                # specify field label
        Label   no                              # do not produce label for field

        Label_style     font-size:24pt;font-weight:bold

Field Default Values

Default values for fields can be specified. There are two types of defaults, static values, and dynamic values. Static values are inserted into the form when the form is created by the Form Builder. Dynamic default values, such as dates, are inserted into the form when filled by the Form Filler.

Note: as of 6/6/2001, ALL default values are filled in by the Form Filler.

        Default value                           # default value
        Default "value"                         # can be quoted strings

        Default user                            # user's code name for security

        Default today                           # today's date
        Default now                             # current date/time
        Default yesterday                       # yesterday's date
        Default tomorrow                        # tomorrow's date
        Default dayadd(n)                       # add/subtract n days

The above Default settings are referred to internally as:

        Default_static  value                   # used internally in form builder
        Default_dynamic function                # used internally in form filler

Field Display Settings

The following class settings are used as CSS styles. For example, the text class uses the CSS class of ``text'' (or ``text-readonly'' for readonly fields) to format the field contents. The class settings (``text'',``date'',``number'',...) should be all lowercase letters.

Class settings are used as hints to validate user input before transmitting to the server.

The date and color classes have buttons to display a calendar or color wheel appended to the input field unless the Nocalendar or Nocolorbutton properties are specified (set to yes).

This first group of classes are usually inferred by the Type value and do not need to be specified.

        Class   text                            # inferred by Type property
        Class   date                            # inferred by Type property
        Class   number                          # inferred by Type property
        Class   decimal                         # inferred by Type property

Integer fields are often used to cross reference an ID field in another table. In other words, they are used as foreign keys. Integers used as ID numbers can be formatted different than integers, so there is a class ID.

        Class   id                              # ID number

The following class corresponds to the HTML 'file' field type and is used enter files names. Most browsers attaches a button to the field for the user to use to look for files on the local computer.

        Class   file                            # sets up HTML file input field

The following classes are also used to re-format user input.

        Class   money                           # display type
        Class   phone                           # display type
        Class   url                             # display type
        Class   email                           # display type

        Class   color                           # formatted like #FFCC33

The display of individual form fields can be altered with their own CSS style settings.

        Style   font-weight:bold                # CSS style settings

Form input fields and any HTML text up until the next Field, <!--%BR-->, or <!--% IN --> directives are enclosed in a HTML <span> container. A CSS class name or a CSS style can be specified for the container. The CSS class name must be programmed in one of the style sheets used for the form. (see Appearance)

Note that each field container -- the <span> block -- has an Id assigned to it that is a concatenation of the strings 'f_', table name, '__' (two underlines), and the field name. For example, the container Id for the ``name'' field of the ``customer'' table is ``f_customer__name''. The CSS standard allows the setting of styles to any element that has an Id attribute, which offers a different way to change the display of field containers than by setting its style or class name as below.

        Container_style font-weight:bold        # CSS style settings
        Container_class blue-background         # CSS class name

The display of fields in lists or views can be specified with a sprintf() format string. Format has no effect in forms as JavaScript does not have a sprintf() method. (See List Formats)

        Format "$%0.2f"                         # display format
        Format &commafy(%s)                     # format function

Form field length and the number of digits appearing after the decimal point can be specified.

        Length  7                               # field display length
        Decimal 2                               # digits after decimal

The number of rows (lines) that appear in a HTML <textarea> tag, which is used to enter long text fields greater than 100 characters, can be specified with rows. Set rows equal to 1 to force fields whose length is greater than 100 characters to be entered with <input> tags instead of <textarea> tags.

        Rows    3                               # number of lines in <textarea>

Field Requirements

Fields which users must enter before the form can be posted can be flagged as required.

        Require                                 # field value is required

Users can be restricted from entering or viewing field values by specifying a security code. For performance reasons, field security codes are not checked unless the table also has a security code (see above).

        Secure                                  # use fq field name as code
        Secure          sysadmin                # specify security code
        Secure          admin sysadmin          # multiple codes
        Security_code   sysadmin                # used internally

Readonly, Hidden, Private, and Not_used

The Readonly property restricts the user from entering or changing its value. Readonly fields differ from Display fields in that Readonly fields are stored in the database, whereas Display fields appear only on forms and are not stored in the database.

        Readonly                                # user cannot change

The Hidden setting hides form fields on forms whereas the Private setting does not even create a form field on forms. Private fields are denoted as private on schema listings with the reason if specified. The reason should be a full sentence to conform with normal grammar in the schema documentation. Not_used is a synonym for Private. I use Not_used for those database columns that still exist in the database table but are not to be used anymore. Note that private fields are listed in schema documentation, but fields for which the user does not have security access to, are not listed.

        Hidden                                  # hide from user view

        Private         Reason for privacy.     # hide and don't use
        Not_used        Reason field not used.  # hide and don't use

Arrays of Values

The Array setting identifies text fields used to hold lists of values, a ``text array''. A separator character is used to delimit the values and is used to quote the string. For example: :one:two:three:

Any punctuation character can be used as a text array separator. A standard system wide (configurable) default character is used unless Array_separator is specified.

        Array
        Array_separator :                       # delimiter

Specifying Array and Choose (see Choose Lists) together for a field alters the selection list made by Choose to return multiple values.

Array values can also be used in a Search_assign_options property (see Search Button) to dynamically assign the options of a Choose list to the contents of an array (which should be a subset of the original list).

Individual values of an array can be entered using an input array (see Input Arrays).

Field Compute and Validate

Following are client-side JavaScript directives performed when field contents change and before saving the form. In function definitions, this refers to the input field.

Use the notation of %var to refer to form field names corresponding to database column names. They will be translated to the actual form field name at run-time.

Note that to be secure and ``really'' valid, these calculations and assertions should be repeated on the server before saving to the database, but they're not.

        Valid           $field > 0              # JavaScript condition
        Valid           daydiff('1/1/2002',$field) > 0  # after a date

        Valid_error     error message           # custom error message

        Translate       myfunction(this)        # JavaScript function

        Translate       toUpperCase()           # JavaScript String method
        Translate       replace(/.+/,'bogus')   # JavaScript String method

Scripts can included in the form field's onfocus and onchange event attributes. The JavaScript in the onchange property is combined with the Translate and Valid properties above. The order of execution is: first the Translate script, then the standard check done on all fields to see if the input is acceptable to the type and/or class of the field being entered, then the Valid script, and finally the onchange script.

        onFocus         JavaScript using %var fields or $var values
        onChange        JavaScript using %var fields or $var values

The Compute property defines JavaScript code that is executed before the Compute property of the table (see Computations). It is allowed as a Field property so JavaScript code can be organized by fields instead of it all being defined elsewhere.

        Compute         JavaScript using %var fields or $var values

The Initialize property is used to define JavaScript code that is to execute just before the form accepts any user input. Unlike the Compute property above, the Initialize property is appended to the table's Initialize property.

        Initialize      JavaScript using %var fields or $var values

Radio Buttons

Input fields can constructed as radio buttons.

        Radio   value1  label1                  # value of radio buttons
                value2  label2                  #  space delimited
                value3  "multi-word label3"     #  quotes ("') optional

Check boxes

Input fields can be constructed as check boxes. To initialize the checkbox to an ``unchecked'' state, use the Default property to initialize the field to a value different than the value used for Checkbox. (see Field Default Values)

        Checkbox        value                   # use checkbox input

Choose Lists

Fields can be drop-down lists of values. A pre-programmed function with the name specified is looked for first.

If a pre-programmed list is not found, a list is constructed from the Choose_table, Choose_join, Choose_where, and Choose_order properties which get their default values from the named database table's Select properties. (See Select List Hints)

If the field is an Array, the list is displayed in a scrolling box, not a drop-down list, and multiple values can be chosen from the list. (see Arrays of Values)

        Choose          list_name               # custom pre-programmed list

The following gets translated into Choose_menu.

        Choose          menu N Y                # choose from given menu
        Choose_menu     N Y                     # used internally

The following gets translated into Choose_list Multi-word codes or descriptions must be quoted.

        Choose          list                    # choose from following list
                        code1   description1    #  of tab and newline
                        code2   description2    #  delimited codes and
                        code3   description3    #  their descriptive text
        Choose_list     "code 1" "description 1"# used internally
                        "code 2" "description 2"
                        "code 3" "description 3"

The following Choose gets translated into Choose_table.

        Choose          table_name              # use table rows as selections

        Choose_table    table_name              # used internally
        Choose_tables   table_name, table_name2 # used internally

        Choose_fields   tab.fld1, tab.fld2, ... # used internally
        Choose_join     table.field = table2.field2
        Choose_where    table.field = 'Bogus'
        Choose_order    2, 3

The following Choose property gets used for the Choose_sql property. Choose_sql is used internally, and if the property is not specified, the Choose property is looked at to see if the first word is ``select''. If so, the fields in the Choose property are converted to fully qualified SQL field names before using them for the Choose_sql property. If the Choose_sql property is specified, it must use fully-qualified column names.

The first column in the query is the column that is used for the selection's value. Select the first column twice to have it appear in the user's selection list.

        Choose          select f1, f2 from ...  # sql query
        Choose_sql      select t.f1, t.f2 ...   # used internally

Query forms, the forms used to build database queries, use the Choose properties to list the values to use to query the database table. There are times where the values listed for Edit and New forms are different from the values listed for queries. For example, if a field retired is used to filter out old records, the Choose_where clause would be ``retired is null or retired != 'Y'''. But in Query forms, you would want to list the old values too as possible values to use in the query. Two ways to do this: Set Choose_query_all or specify a different where clause in Choose_query_where. See Query List Hints for a description of the corresponding table properties.

        Choose_query_all                        # "yes" is optional

        Choose_query_where      table.field is null or table.field = 'Bogus'
        Choose_query_sql        select t.f, ... # used internally

Values can be assigned to other form fields when different selections are chosen by defining the following assignment statements. Each statement is separated by a semi-colon or a newline. If more than one table is listed in the Choose or Choose_table property, then the fields used to fill in the current table fields must be fully-qualified field names. In other words, the table_name must be part of the field name like the assignments listed below.

The fields to the left of the equal sign are form fields identified by the $ character (or % like %field.value). The names to the right of the equal sign are database names from the SQL query. They can be identified two ways. The first way is to think of them as database fields in which case they must not have a $ or % character, and look like fully-qualified SQL column names like: customers.customer_name. The other way is to think of them coming from the pop up search window's form fields in which case the names to the right of the equal sign must start with $S. (or %S.), and the delimiter between the table and column name is a double underline ``__'' instead of a period, like: $S.customers__customer_name.

        Choose_assign   $field1 = table_name.field1
                        $field2 = table_name.field2
                        $field3 = $S.table_name__field3
                        $field4 = %S.table_name__field4.value

Options which appear in a Choose dropdown list can be restricted to those options which are specified in a text array value (see Arrays of Values). More than one dropdown list can be initialized in one assignment. For example, a table of color codes for inventory items can be built into a form, and depending upon which inventory item is being entered, the list of colors can be restricted to showing only those colors which the inventory item comes in.

        Choose_assign_options   $field1 = table_name.field1
                                $field2 = table_name.field2

The <input> fields that are displayed to enter a text array can be restricted to those options that are specified in a text array value. (See Input Arrays) For example, similar to Choose_assign_options, an input array can be defined to enter quantities for all inventory sizes, and depending upon which inventory item is being entered, the input array fields can be restricted to showing only those size fields that apply to that particular inventory item.

        Choose_assign_input     $field1 = table_name.field1

The Choose_next property specifies the form field to focus after choosing a new selection. Identify the form field with the % character, not $, as $ translates to a field value, and it's the field reference that is necessary to focus.

        Choose_next     %field

The Choose_format property is handy for formatting Choose_sql lists. The format string used in Choose_format is very similar to Perl's sprintf() format strings but can have embedded functions. (See List Formats) HTML codes are not allowed in the choose_format string as choose lists are rendered in HTML <select> tags.

        Choose_format   "format string"         # display format

The Choose_rows property determines the number of rows in the HTML select list. By setting this equal to 1 forces the lists to be a drop down list.

        Choose_rows     5                       # number of display rows

The Choose_size and Choose_length are synonyms for each other. They set the maximum length of the displayed options, essentially restricting the width of the dropdown list on the form.

        Choose_size     50                      # number of characters displayed
        Choose_length   50                      # number of characters displayed

Several Choose options have sister Search options. For example Choose_assign is similar to Search_assign. Choose lists can be used to lookup a limited set of values from the database, whereas the Search option presents an initial search screen so it can be used to look up values from a large database table.

Combo Lists

The Combo property enhances a Choose list with an extra button that hides the list and displays an input field for the user to use to enter a value not on the list.

        Combo                                   # "yes" setting is optional

Search Button

Field values can be chosen from other tables with Search. A button is appended to the <input> field which pops up a search window used to look up a record in another database table. This facility is especially powerful. It can be used in conjunction with the Restrict property to require a value entered by the user to already exist in the other database table, but more conveniently, it can be used to fill in form fields with values selected from the other table.

The following Search property gets used for the Search_table property. Search_table is used internally, and if the property is not specified, the Search property is looked at to see if the first word IS NOT ``select''. If not, a query is constructed from the Search_join, Search_where, and Search_order properties whose default values comes from the search table's Select properties. (see Select List Hints)

More than one table_name can be listed if separated by commas and if the other tables are listed in the search table's Join clause.

        Search          table_name

        Search_table    table_name                      # used internally
        Search_tables   table_name, table_name2, ...    # used internally

        Search_list     field1, field2, ...     # fields to list
        Search_fields   tab.fld1, tab.fld2, ... # used internally
        Search_join     table.field = table2.field2
        Search_where    table.field = 'Bogus'
        Search_order    2, 3                    # columns or field names

The following Search property gets used for the Search_sql property. Search_sql is used internally, and if the property is not specified, the Search property is looked at to see if the first word IS ``select''. If so, the fields in the Search property are converted to fully qualified SQL field names before using them for the Search_sql property. If the Search_sql property is specified, it must be specified using fully-qualified column names.

The first column in the query is the column that is assigned to the search field. Select the first column twice to have it appear in the user's selection list.

Note: When specifying Search_sql explicitly, either Search_table needs to be specified or search hints need to be specified (see below).

        Search          select f1, f2 from ...  # sql query
        Search_sql      select t.f1, t.f2 ...   # used internally

Hints to the search engine can be specified for each search. They correspond to the search hints described in Search Hints above.

Search hints are usually inferred from the Search_table properties.

        Search_id       field                   # search parameter
        Search_code     field                   # search parameter
        Search_name     field                   # search parameter
        Search_first_name field                 # search parameter
        Search_last_name  field                 # search parameter
        Search_phone    field                   # search parameter
        Search_email    field                   # search parameter

The pop-up search window's appearance and the search parameters are specified with the following parameters. They are optional as default values are used if they are not specified.

        Search_title    Title of Dialog Window  # search window title
        Search_label    Label for search box    # search field label

Search results are displayed in an HTML <select> tag so the keyboard can be used to select the search results. The number of rows displayed in the <select> tag can be set by Search_rows.

        Search_rows     number                  # number of display rows
                                                # (will become depreciated)

The format of the lines displayed can be specified in Search_format The format string used in Search_format is very similar to Perl's sprintf() format strings but can have embedded functions. (See List Formats)

        Search_format   "format string"         # display format

 ##########
 # TODO - The following still needs to be implemented. (if ever)
 #      Search_header   "format string"         # default is `no'
 #      Search_header                           # display default header
 #      Search_footer   "format string"         # default is `no'
 #      Search_footer                           # display default footer
 ##########

By default, when the user changes the field contents, the search window automatically pops up and the text the user just entered is used as the search parameter. That behavior can be changed by setting Search_autosearch to ``no'', which forces the user to click on the ``Search'' button to initiate a search. Note that setting Search_autosearch to ``yes'' (the default value) is equivalent to requiring the user's entry to match a pre-existing entry in the search table. Setting Search_autosearch to ``no'' allows the user to enter a value that does not exist in the Search_sql (i.e. the search lookup table).

        Search_autosearch no                    # operation style flag

By default, when a search returns only one record, that record is automatically selected and the search window closed without any action from the user. That behavior can be turned off by setting Search_autoselect to ``no''.

        Search_autoselect no                    # operation style flag

One of the features of the system is to track searches for the user so that they can refer to a previous search simply by picking it from a dropdown list. The Search_history is the name of the ``history list'' to use to find and display previous searches. The Search_history_save parameter instructs the Searcher to save the search in the history list.

        Search_history  name                    # history list name
        Search_history_save yes                 # save search string

Values can be assigned to form fields after a search. Each statement is separated by a semi-colon or a newline. If more than one table is listed in the Search or Search_table property, then the fields used to fill in the current table fields must be fully-qualified field names like those demonstrated in the assignment statements below.

        Search_assign   $field1 = table_name.field1
                        $field2 = table_name.field2
                        $field3 = $S.table_name__field3
                        $field4 = %S.table_name__field4.value

        Search_assign_options   $field1 = table_name.field1
                                $field2 = table_name.field2

The <input> fields that are displayed to enter a text array can be restricted to those options that are specified in a text array value. (See Input Arrays) For example, similar to Search_assign_options, an input array can be defined to enter quantities for all inventory sizes, and depending upon which inventory item is being entered, the input array fields can be restricted to showing only those size fields that apply to that particular inventory item.

        Search_assign_input     $field1 = table_name.field1

The text on the search button can be changed from ``Search'' by setting the Search_button_title property.

        Search_button_title     Search Customers

The Search_next property specifies the form field to focus after a successful search. Identify the form field with the % character, not $, as $ translates to a field value, and it's the field reference that is necessary to focus.

        Search_next     %field

Same Button

The Same option appends a button to the <input> field which executes the assignment statements. It is useful for copying field values from one place on a form to another.

The assignment statements use form variables ($field_name) like Search_assign and Choose_assign. (See Assignment Statements) The assignment statements can be separated by new-lines or semi-colons (;).

        Same    $shipped_date  = $picked_date
                $invoiced_date = $picked_date

The text on the button can be changed from the default of ``Same'' by setting the Same_button_title property.

        Same_button_title       "Same as Billing"

The Same_next specifies the form field to focus after a successful assignment. Like Search_next, the field name must be identified with the % character.

        Same_next %invoiced_by

Browse Button

File names on the server can be looked up with the Browse button. If a file name is to be looked up resides on the client computer, use the Class file option instead. (see Field Display Settings)

The parameter to the Browse option is actually part of the url passed to the browse.cgi program. The left side of = is the name of a root directory defined in the site configuration file (.../etc/site.conf). The right side of the = is the path name of a sub-directory under the root directory.

        Browse  lists=customers
        Browse  images=pic_of_day

The text on the browse button can be changed from ``Browse'' by setting the Browse_button_title property.

        Browse_button_title     Lookup File Name

The Browse_next specifies the form field to focus after a successful assignment. Like Search_next, the field name must be identified with the % character.

        Browse_next %file_description

Input Arrays

A text array (see Arrays of Values) can be entered with a collection of <input> tags, one for each item in the Input_sql or Input_array.

The following Input property gets used for the Input_table property. Input_table is used internally, and if the property is not specified, the Input property is looked at to see if the first word IS NOT ``select''. If not, a query is constructed from the table's Select property in the schema. (see Select List Hints)

        Input           table_name
        Input_table     table_name

The following Input property gets translated into the Input_sql if the first word is ``select''.

        Input           select code, label from codes
        Input_sql       select code, label from codes

The following is an alternate way to specify codes and labels used for <input> tags used to enter an array.

        Input           :code1:code2:code3:

        Input_array     :code1:code2:code3:
        Input_labels    :label1:label2:label3:

The actual <input> tags displayed and entered can be determined at run-time by the contents of the field specified by Input_filter.

        Input_filter    field_name

The input fields for the text array can have their own attributes. Their meanings are the same as those properties for other fields. That is, the settings for Input_class are the same as Class. (see Field Display Settings)

        Input_description       Input field pop-up description

        Input_class     number                  # class of array input fields
        Input_length    10                      # field display length
        Input_decimal   2
        Input_attributes class=input-array      # extra field attributes

        Input_translate myfunction(this)        # JavaScript function
        Input_translate toUpperCase()           # JavaScript String method
        Input_translate replace(/.+/,'bogus')   # JavaScript String method

        Input_valid             condition       # JavaScript condition
        Input_valid_error       error message   # custom error message

Scripts can be included in the input field's onFocus and onChange events. They should return a true/false value.

        Input_onFocus           JavaScript statements
        Input_onChange          JavaScript statements

Detail Records Input

Detail records from another table linked to the current field's contents can be specified for a field other than the table's ID field.

The Detail options are the same as those defined above in Detail Tables. They are only listed here briefly.

        Detail                  table.field             # one to many link
        Detail_list             field1,field2,field3    # detail list fields
        Detail_join             table.field = table2.field2
        Detail_where            table.field is not null # query where clause
        Detail_order            field1,field2           # detail sort order

        Detail_format           "format string"         # display format
        Detail_header           "format string"         # default is `no'
        Detail_footer           "format string"         # default is `no'

        Detail_total            qty,amount              # compute totals
        Detail_total_fields     table.field1, ...       # used internally

        Detail_title            Detail Items            # title of detail list
        Detail_button_title     New Item                # text on detail button

As with the table Detail options, Detail_list_fields and Detail_sql are the internal property names used to retrieve list of fully qualified fields and the SQL query.

        Detail_list_fields      table.field1, ...       # fully-qualified names
        Detail_sql              sql                     # SQL script

Form fields can be assigned from the detail window form, and a field to go to when assignments are successful.

        Detail_assign           $mastertable__field = $D.detailtable__field

        Detail_next             %field

Associated HTML

The HTML_before and HTML_after properties define HTML code that is rendered when the field is displayed. Fields might not be displayed if they have security restrictions or if they are Personal fields that should not be rendered on Edit List forms. In such cases, it is better to use these properties than general HTML codes as explained in HTML Codes.

        HTML_before             <p>Enter your name below.</p>
        HTML_after              <p>The name above is used for salutations.</p>

A Bunch of Questions

The AcmeBase programs need to know what type of field they are working with. Usually that information is obtained with the class and type properties, however, there are some classifications that are aggregates of class or type. These properties answer those questions. For example, they tell the Form Builder what kind of a parameter input button to put on a query field. They allow new classes to be defined. These are internal properties that usually are not set in the schema files, and they are Yes/No type properties.

        is_text                                 # is a text type field
        is_number                               # is a number (int, decimal,...)
        is_date                                 # is a date type field
        is_time                                 # is a time type field

The is_personal property is true if a field is an ID, a key field, a field with a unique index on it, or a field with the Personal property set. (See That's Personal below.)

        is_personal                             # is field's data personal

A Bunch of No's

Several of the above properties have default actions or side effects that can be canceled by setting the following ``No'' settings to ``Yes''. These settings only apply to the current field or table definition. They are not global settings.

        Nocalendar                              # no calendar button displayed
        Nocolorbutton                           # no color-wheel button displayed

        Noreadonly                              # same as setting `Readonly no'
        Nolabel                                 # same as setting `Label no'

        Noeditlist                              # same as setting `Editlist no'

        Noemail                                 # same as setting `Email no'

        Noexport                                # same as setting `Export no'
        Noexport_log                            # same as setting `Export_log no'

        Nolimit                                 # same as setting 'Limit no'
        Nolimit_alert                           # do not email alerts

That's Personal

Observing the types of data held in a table, one can say some columns hold data that is common with other records while some columns hold data that is different for each record. A primary key column or a record ID column are examples of data that are different for each record and are automatically considered ``personal''. Other columns such as customer_name are also considered personal. Classifying a column as ``personal'' is not as strict as placing a unique index on the column. A customer's street address can be considered personal even though there are other customers that might share the same address.

        Personal                                # column contents are not shared

Classifying a field/column as ``personal'' is useful for constructing forms to change column values for a list of records. It is also useful for importing external data into the database: required fields that are also ``personal'' need to have a corresponding import data column.

Display Field Properties

Display Field General Settings

Display fields are fields not in the database. They are forced to be read-only (unless Readonly is set to ``no'' or Noreadonly is set to ``yes'') and the field names are checked to make sure they begin with D_ so that they are ignored when saving a form.

Most of the Field options apply to Display. A useful option is Compute, which is executed after the user enters the form field but before the table's Compute script.

A label must be specified if one is to appear on a form for it is not automatically generated from the field's name (or title) as it is for normal fields.

        Display D_field_name_1                          # start display field

        Type    varchar(5)                              # required like Field

        Style   font-weight:bold                        # CSS style settings

        Label   Display Field                           # label for field input

        Label_style font-size:24pt;font-weight:bold     # CSS style settings

        Compute $D_field_name_1 = $field1 + $field2     # optional formula

        Noreadonly                                      # allow user entry

        Hidden                                          # hide field contents

HTML Codes

General Codes

HTML code can inserted into a form in between field definitions. Any line beginning with a < is interpreted to be HTML code. The line and any following indented lines are inserted directly into a form when being built.

        <p>You can insert commentary into the form.</p>

These HTML codes always appear in the form. The HTML_before and HTML_after field properties define HTML that appears on the form only when the field appears on the form. Fields might not appear on a form if security restricts their access or if the field is a personal field not to be rendered on Edit List forms (see Associated HTML and Edit List Hints).

Be aware that HTML codes cancels the ``current'' Field or Display. Property settings that follow HTML codes are Table properties until another key word. It has yet to be determined whether that is a bug or a feature.

Here Documents

Lines consisting of <<word begin a ``here'' document. The following lines up to the line consisting of only word (where word is an arbitrary string) is read as HTML and inserted into a form verbatim. The # character is not interpreted as a comment character.

Form Builder Directives

Form directives are HTML code with the form of <!--% ... -->. These form directives talk to the form builder. They are gobbled and do not appear in generated forms. They are recognized only if they appear at the beginning of a line.

There is an alternate syntax for specifying form builder directives. They begin with a dash - character and end with a semi-colon ; or at the end of the line. They can be uppercase or lowercase and they too must occur at the beginning of a line in order to be recognized.

        -br                     # break table
        -br;                    # break table
        -nl;                    # next line (across table width)
        -in;                    # next line in same column under field input.

        <!--% BR -->            # break table
        <!--% NL -->            # next line (across table width)
        <!--% IN -->            # next line in same column under field input.

The Nav(igate) directive is a shorthand for entering a break (-br;) and a form filler directive <!--$ Navigate Buttons -->. Using the form builder directive allows the user to specify if navigation buttons are to be used on the form or not when making a form template.

        -nav                    # insert navigation buttons
        <!--% NAVIGATE -->      # alternate command style

The Begin and End directives are convenience directives which insert DIV tags with their class attribute set to the specified class. For example, the following directives insert a <div class="block"> tag into the form. Any extra parameters are inserted into the <div> tag verbatim.

        <!--% Begin block -->   # same as <!--%BR--><div class="block">

        <!--% End block -->     # same as <!--%BR--></div>

        <!--% Begin block id="myblock" style="background-color:#aabbcc" -->
        <!--% End -->           # same as above

Begin directives immediately following <!--% IN --> directive causes the block to be ``inserted'' in the column under the field input, which causes it to appear indented.

        <!--% IN --><!--% Begin block -->       # indented block
        <!--% End -->                           # terminate block

The Hide directive begins hiding the form parts and pieces. This is different from the field's Hidden property which constructs the form with a field that is hidden from view but can be computed and saved to the database. The Hide form directive gobbles all the other form properties up until the matching End directive. Begin blocks can be contained within Hide blocks (and vice versa) but they must be totally contained within the block -- no crossing borders is allowed.

        <!--% Hide -->          # begin hiding form parts
        <!--% End -->           # end hiding of form parts

The words following the Hide keyword are compared with the CGI parameter named ``request''. If one of the words matches the request, hiding of the form commences, otherwise the Hide directive is ignored. For example, if a form is being built for entering new data -- request is ``New'' -- then <!--% Hide New --> starts hiding the form parts, whereas if the form is being built for changing existing information in the database -- request is ``Edit'' -- the Hide directive is ignored.

        <!--% Hide New Edit --> # begin hiding if request is New or Edit
        <!--% End Hiding-->     # terminates the hiding of form elements

The JoinField and JoinField directives inserts all the fields or just the specified field into the form from the other table. The specifiecations for the joined fields comes from their table's schema files.

Normal forms built from the schema files includes the fields from the tables that are joined to the table at the end of the form. (See Join Tables) The fields inserted into the form with the JoinField directive are excluded from that list. Form fields are built only once.

        <!--% JoinTable table -->               # insert fields from the table

        <!--% JoinField table.column -->        # insert a field from the table

To stop the Form Builder from building form fields, use the EOF directive. This is different from the __END__ statement discussed above in Schema Files. __END__ marks the end of the schema file, everything is ignored after it. The EOF directive marks the end of the built form. Other fields can follow it but they are not rendered on the data entry forms. Fields from other joined tables are also not automatically included. They should have been inserted previously with the JoinTable or JoinField directives.

        -eof                    # end of form
        <!--% EOF -->           # end of form

The Detail directive controls the placement of detail table records in the form for the detail table joining with the key of the current table. The form builder converts these directive into the proper form filler directive to insert records from the detail table. (See Form Filler Directives) Like the Begin and Print directives, any text after the ``Detail'' word is used as the title and attributes to the button.

        <!--% Detail -->        # insert detail info and button

The Print directive places a button on the form that prints the form. The script used to print the form makes sure that the form is saved first, then opens up a new browser window using the url in Print_prog property (see Dispatch) or using the current url with the string ``;print=yes'' appended to it. The optional text after ``Print'' is used as the title and attributes to the <input> button tag.

        <!--% Print "Save & Print" -->
        <!--% Print "Save & Print" style="background-color:green" -->

The Do directive calls a Perl function previously defined in a Do and/or a Do_builder properties. (See Do Do's)

        <!--% Do function(arg1,arg2) -->

Form Filler Directives

Form filler directives (commands) have the form

        <!--$ command -->       # substitute command's results

The directives (commands) are recognized by pattern matches. They usually produce HTML code which is inserted into the form right after the directive terminator (-->). Custom commands can be programmed and placed in the Local::Acme::Form::Extensions module. Currently, the only ``standard'' commands are those used for placing navigation buttons on the form and the header and footer. They and the Do directive described next.

The Do directive executes Perl functions previously defined in the Do and/or Do_filler properties. (See Do Do's)

        <!--$ Do function(arg1,arg2) -->

One $ is removed from the form directive and then it and the inserted HTML code is scanned again for more form directives. Therefore, to insert the results of a command three times, use:

        <!--$$$ command -->     # substitute 3 times

HTML code, Form Builder directives, and Form Filler directives can all be combined together as long as the Form Builder directives are at the beginning of the line. For example:

        <br>&nbsp;                                      # insert a blank line

        <br>Insert a comment about above fields.        # insert a comment

        <!--% NL --><!--$ Small Navigation Button -->   # insert a blank line
                                                        # and navigation button
        <!--% BR --><!--$ Navigate Buttons -->          # insert separator
                                                        # navigation buttons
                                                        # across whole window

Detail records are inserted into the form where the following directive occur. The left part of the equal sign is a database table and field name, the master table. The right side of the equal sign is the detail table and field name that joins with the master table and field specified in the left part of the equation. The form builder generates these directives in place of the <!--%Detail--> directive to specify where detail records should appear in the final filled form.

        <!--$ Detail table.field = detail_table.detail_field -->

Functions

Form Control Functions

The following functions set the keyboard focus to the first <input> field, or to the first field that is empty. They are usually used in the form_initialize() or form_reinitialize() functions that are included in every form built by the Form Builder.

        focus_first_field(f)
        focus_first_blank_field(f)

The following functions show or hide <input> field(s) and their labels.

        show_field(input_field)
        show_fields(input_field1, input_field2, ... )

        hide_field(input_field)
        hide_fields(input_field1, input_field2, ... )

The revert() function is attached to a button which the user can use to unset any changes to a preceding <select ... multiple> tag.

        revert(select_field)

Array Functions

The split_array() function returns a JavaScript array of the elements in text_array.

        split_array(text_array)

Use is_array_filled() to determine if text_array has any empty elements.

        is_array_filled(text_array)

The array_count() function returns the number of elements in the text_array. The array_total() function returns the total of the text_array elements.

        array_count(text_array)
        array_total(text_array)

The array_total_remainder() function returns the total minus sum of the elements in the text array, or 0 if the result is negative. The total argument can be a number, a string, or a form field (%field_name).

        array_total_remainder(total, text_array)

Assignment Functions

The assign_options() function changes the options of a <select> tag to be those listed in the arguments. The new settings should be a sub-set of the original settings. The arguments can be single value arguments or they can be text arrays.

        assign_options(select_field, ... )

Similar in concept to assign_options(), the assign_input() function works with an input array (a collection of <input> fields) instead of the <select ... multiple> tag. It shows the input fields whose entries are for the values in the text_array, and hides the others.

        assign_input(input_field, text_array)

The assign_multiple() function sets the options of the select_field to those listed in the text_array. It is used to initialize HTML <select ... multiple> tags.

        assign_multiple(select_field, text_array)

Aggregate Functions

The following aggregate functions compute the sum, average or count of their arguments. The arguments can be values, form fields, or text strings of form field names. All the elements in the form are searched for fields with the same name as the argument's form field or a name matching the text string arguments' name. In other words, if there are 10 input fields with the same name in the form, all 10 are totaled.

The difference between SUM() and TOTAL() is that TOTAL() is used for summing fixed decimal numbers such as money. SUM() returns a floating point number with an unknown number of digits after the decimal point.

        SUM( ... )
        TOTAL( ... )
        AVG( ... )
        COUNT( ... )

Radio Button Functions

The radio_value() function scans the radio buttons and returns the value of the radio button currently checked. The radio_set() function sets the radio button which whose value equals val and clears all the rest.

        radio_value(radio_field)
        radio_set(radio_field,val)

Checkbox Functions

The checkbox_value() function returns the value of the checkbox field if it is checked, otherwise it returns null. The checkbox_set() function ``checks'' the checkbox if the value of val equals the value of the checkbox field.

        checkbox_value(checkbox_field)
        checkbox_set(checkbox_field,val)

Date Functions

Date arithmetic is possible with dayadd() and daydiff() functions. The dayadd() function adds the specified number of days to the startdate, which if not specified, defaults to today's date. The daydiff() function returns the number of days (nights) between two dates. The number is positive or negative depending if the enddate is later than startdate or not.

        dayadd(days, startdate)

        daydiff(startdate, enddate)

Date arithmetic requires that dates be converted from Gregorian dates to Julian dates, which is what julian() does. Its always useful to know which years are leap years too.

        isLeapYear(y)           # return true if y is a leap year

        julian(m,d,y)           # convert Gregorian date to Julian date

Dates in forms generated by JavaScript should look the same as database dates. The dbdate() function converts a JavaScript date string to a suitable database date string. The today() function returns today's date in the same format as dbdate().

        julianDate(j,y)         # convert Julian date to Gregorian database date

        dbdate(ds)              # convert JavaScript date to database date

        today()                 # return today's date (mm-dd-yyyy)

Formatting Functions

JavaScript does not have very good formatting for numbers. The roundValue() function rounds a floating point number to a fixed number of decimal places. It is useful for formatting money.

        roundValue(val, precision)

Notes

Opposites

Several properties are pairs ``opposite'' of each other, one as a field property in one table and the other as a table property in the ``opposite'' table. When rendering a field, if a property is not specified as a field property, the target table is looked at for the ``opposite'' property to use as a default value for the field property.

Choose, a field property, is opposite of Select, a table property. Unless all the Choose properties are specified in the field section, needed values are obtained from the table's Select properties. For example, to determine which fields in a table to list, the Choose_list property is looked for first and if that is not specified, the table's Select_list property is used.

Detail, a field property, is opposite of Form_list, a table property. Although there is a table property named Detail, it is really a field property for the table's key field.

Search is opposite of Pick.

Search is also opposite of ID_field, Code_field, Name_field, and First_name_field in that if the Search_id, Search_code, Search_name, or Search_first_name is not specified in the field description where the Search directive is located, the ID_field, Name_field, Code_field, or First_name_field properties are used from the target table. The same is true for Search_email and Email_field and for Search_phone and Phone_field.

Join can be a reference in both tables, but is usually only in the master table. It is a table property.

Master properties have diminished from their original intent. They currently identify default values on detail forms. Originally, it was intended to identify SQL updates when detail records were posted. That can be done now with the onSave property, however better synchronization occurs when the master form is always used to enter detail records, which computes on screen values, and then posted again after the detail forms.

Join clauses are simplistic and are not intended to convey the full power of SQL joins. Specifically, synonyms for joined tables are not allowed and therefore a table can be joined to another table only once on one field. Also, the joins are ``left'' or ``outer'' joins -- a row does not have to exist in the joined table.

Assignment Statements

The assignment statements in the Search_assign, Choose_assign, Detail_assign, Master_assign, and Same properties are more restrictive than the full range of JavaScript statements allowed in the Compute and Initialize properties.

The assignment statements are parsed on the equal sign (=) into the left side and the right side, and sometimes they are rearranged into a JavaScript function calls. The translation is done to set radio buttons, checkboxes, and select-multiple lists.

The Master_assign and Detail_assign statements define assignments between two displayed forms. The Master_assign assignments affect the detail form when it is displayed, and the Detail_assign assignments affect the master form when the detail form is saved.

The Search_assign and Choose_assign statements are processed by the Form Filler when filling in a form if the value for the search field is known -- either the value comes from the database when editing a record or the value comes from the CGI arguments when a new form is rendered. This allows display-only fields to be automatically filled in when a form is displayed to edit a record, and more importantly, it fills in default values for form fields when adding a new record to the database.

Lists

Information in database tables is displayed in four different types of lists.

Pure text in a drop down list or scrolling box on a form. Referred to as a select list or a choose list.
HTML text in a separate window. Used to select a record for editing or viewing. Referred to as a pick list.
HTML text in a window. Used to manipulate lists as a whole, such as exporting a list of records. Referred to as just a list.
HTML text in a form. Used to display detail records on a form. For example, to list order items on an order form. Referred to as a form list or a detail list.

The first is the most basic of all lists. They are controlled by the Select table properties. The second type of lists are controlled by the Pick table properties. The third by List properties, and the fourth by Form_list properties.

The fourth type of list is also usually similar to the second and third, but is controlled by its own set of properties so that <input> tags can be embedded in the list. Being displayed in a form, the <input> tags allow the use of a trick to keep running totals of detail records on a form using the functions TOTAL() and SUM(). (see Aggregate Functions)

Default values used to construct lists are determined as follows:

Choose ---> Select
A field's Choose properties looks to the table's Select property settings.
Search ---> Pick ---> List ---> Select
A field's Search properties looks to the table's Pick properties which looks to the tables List properties which looks to the table's Select properties for a default value.
Detail ---> Form_list ---> List ---> Select
A field's or table's Detail property settings looks to the table's Form_list properties which looks to the table's List properties which looks to the table's Select properties for a default value.

All lists eventually inherit properties from the Select properties. The Select property defaults are simple constants or assumptions, like listing fields in the order they are listed in the schema file. The Select_where property is ONLY inherited by Choose, it is not used as a List property.

Lists can have computed fields. The List_fields, Pick_fields, Form_list_fields, and Select_fields properties are passed to the SQL engine intact so database SQL functions can be used. The corresponding format specifications List_format, Pick_format, Form_list_format, and Select_format can also call functions after the fields are formatted to further modify the data retrieved from the database before displaying it.

The field names in SQL clauses, such as those used for Choose_sql, Search_sql, and onSave, should use fully-qualified names (table.field) to be safe. Constructed SQL statements can refer to several tables, and if two tables have a field with the same name, the SQL engine will fail.

List Formats

Positional Substitutions

The format of fields in lists are specified by the format properties: List_format, Pick_format, Form_list_format, Select_format, Detail_format, Choose_format, and Search_format.

The property settings are character strings that are processed by Perl's sprintf() function. Examples are:

        "%s -- %s"
        "%3.2d -- %s"

The values substituted into the output line are grabbed from the input record in the order they are specified in the sql's list of columns.

Complete documentation of the format codes recognized by sprintf() is found in ``Programming Perl'' by Wall, Christiansen and Orwant. Briefly, they are:

        %%      percent sign
        %c      character
        %s      string
        %d      signed decimal integer
        %u      unsigned decimal integer
        %o      octal number
        %x      hexadecimal number
        %e      number in scientific notation
        %f      number in fixed decimal notation
        %g      number in %e or %f depending on number

The following flag characters can appear between the % and the conversion character.

        space   prefix positive numbers with a space
        +       prefix positive numbers with a plus sign
        -       left justify field
        0       right justify field using 0's instead of spaces
        #       prefix octal with "0", prefix hexadecimal with "0x"
        number  minimum field width
        .number number of digits after a decimal point or maximum string length
        l       interpret integer as a C type long integer
        h       interpret integer as a C type short integer

Function Substitutions

The resultant formatted string is then searched for embedded functions to execute. The functions look like: &func(arg1,arg2), they start with the & character and end with parenthesis.

The functions that are recognized and executed in format strings is restricted. They must first be listed in the Local::Acme::Format module by an administrator.

Function substitution was set up so that the mingle() function could be used to format text arrays. For example, if there are two related text arrays, one for sizes: :small:medium:large: and one with quantities: :2:4:3:, then the format string: "&mingle([': ',', '],'%s','%s')" produces the output:

        small: 2, medium: 4, large: 3

The list of functions that can be used in format strings are:

        &user()                                 # user's login id
        &user_name()                            # user's full name
        &user_email()                           # user's email address

        &split_array(text_array_string)         # split textarray into an array
        &mingle([':',','], array1, array2, ...) # mix elements of arrays

        &dollars(amount,precision)              # format US money with $ and ,
        &dollars_us(amount,precision)           # US money ($1,234.56US)
        &dollars_can(amount,precision)          # Canadian money ($1,234.56CAN)
        &commafy(amount,precision)              # place commas, not $ sign

        &total('table.column')                  # total of the column's values.
                                                # Column must be named in List_total.

        &today()                                # today's date (mm-dd-yyyy)

        &mdate($date)                           # Jul 10, 1998
        &meddate($date)                         # Wed Jul 10, 1998
        &ldate($date)                           # July 10, 1998
        &longdate($date)                        # Wednesday July 10, 1998
        &short_date_range($date1,$date2)        # 7/10-28
        &long_date_range($date1,$date2)         # July 10 - 28, 2002

        &if($cond,$true_value,$false_value)     # if $cond condition is true
                                                # then return $true_value
                                                # otherwise return $false_value

        &sprintf($format,arg1,arg2,...)         # format arguments 
        &sprintf_cmd($format,arg1,arg2,...)     # format arguments (recursive call)

        &upc_img(upc_code)                      # image tag using upc_src()
        &upc_src(upc_code)                      # href of UPC code image

Variable Substitutions

Before the format string is process by the sprintf() function and before functions are substituted into the format, Perl'ish variables like $table_name__column_name are substituted into the format. The values come from the list and change for each row. The variables substituted into the header's format are the first record's values and the values substituted into the footer are the last record's values.

Security Note

Key fields should never have a security code more restrictive than that of the whole table.

Examples

Table of Code Settings

This is an example of a code table. It provides values for use in dropdown lists.

 TABLE address_usages
 Description    Codes signifying if address can receive mail or not.
 Key    address_usage

 Select address_usage,address_usage,description
 Select_where   retired is null or retired <> 'Y'
 Select_order   sort_index, address_usage

 FIELD  address_usage
 Type   varchar(7)
 Description    Address type code.
 Translate      toLowerCase()

 FIELD  description
 Type   varchar(50)
 Description    Description for the address_usage code.

 FIELD  notes
 Type   varchar(127)
 Description    Extra notes describing the address_usage code.

 FIELD  usage
 Type   char(1)
 Description    A code describing how the address is to be used.
                A "Y" signifies that the address can be used for a mailing.
                A "N" signifies that the address is NOT to be used.
                A "C" signifies that the address can possibly used with caution.
 Choose menu N Y C      # No, Yes, Caution
 <br><small>The usage determines if the address can be used for a mailing (Y),
  cannot be used for a mailing (N), or if the address can be used with
  caution (C).
 </small>

 FIELD  sort_index
 Type   smallint
 Description    Codes are sorted by these numbers so that you can determine
                the order codes appear in a list.

 FIELD  retired
 Type   char(1)
 Description    Set to "Y" when code is not to be used anymore.
 Choose menu N Y

Audited Table of Customers

The following is an example of a customer table. It is audited and keeps track of changes to the customers table. It also demonstrates a join to a customer_info table and has detail records in the customer_contacts table.

 TABLE customers


 Desc   Customer information, including addresses, phones, ...
 ID     customer_id
 Audit


 List   customer_name, customer_id, customer_type, city, state, province, country
 List_format    "<big>%s</big> (%s, %s) &nbsp; %s, %s %s %s"
 List_order     customer_name, customer_id


 Join   customers.customer_id = customer_info.customer_id
 Detail customer_contacts.customer_id


 Log            customer_log


 View_prog      /cgi-bin/customers/view.cgi
 Edit_prog      /cgi-bin/customers/edit.pl
 List_prog      /cgi-bin/customers/list.pl
 Count_prog     /cgi-bin/customers/list.pl
 New_prog       /cgi-bin/customers/new.pl
 Print_prog     /cgi-bin/customers/view.cgi?print=yes


 Name_field     customer_name
 Email_field    email
 Phone_field    phone, company_phone


 Include address.js


 FIELD  name
 Type   varchar(50)
 Desc   Customer's full name as it is to appear on addresses (if applicable).


 FIELD  company
 Type   varchar(50)
 Desc   Company name as it is to appear on addresses (if applicable).


 FIELD  customer_name
 Type   varchar(50)
 Desc   Customer's name as it is to be filed. People names are like
        "Last_name, First_name" whereas company names are like
        "Company Name, The".
 Required


 FIELD  last_name
 Type   varchar(28)
 Desc   The name to use to compare with other lists.
 Translate      toUpperCase()


 FIELD  first_name
 Type   varchar(22)
 Desc   The name to use when addressing a letter: Dear ....


 <!--%Begin block--><h3>Address</h3>


 FIELD  address_locale
 Type   char(1)
 Radio  A American
        I International
 Label  no
 Default        A
 Compute <<End_Of_JavaScript
 if (radio_value(%address_locale) == 'I') {
        // International address
        show_fields(%province,%postal_code,%country);
        hide_fields(%state,%zip);
 } else {
        // American address
        hide_fields(%province,%postal_code,%country);
        show_fields(%state,%zip);
 }
 End_Of_JavaScript


 FIELD  address1
 Type   varchar(50)
 Title  Address
 Desc   Customer's first line of address.


 FIELD  address2
 Type   varchar(50)
 Label  no
 Title  Address
 Desc   Customer's second line of address.


 FIELD  city
 Type   varchar(24)
 Desc   Customer's city of residence.


 FIELD  zip
 Type   varchar(10)
 Desc   USPS zip code for customer's address.
 onChange       $state = lookup_state($zip);


 FIELD  state
 Type   char(2)
 Desc   Customer's state of residence.
 Choose states


 FIELD  postal_code
 Type   varchar(12)
 Desc   International postal code.


 FIELD  province
 Type   varchar(24)
 Desc   Name of province or state for international addresses.


 FIELD  country
 Type   varchar(50)
 Desc   Customer's country of residence.
 Choose countries


 FIELD  address_usage
 Type   varchar(7)
 Desc   Type of mailing address. It determines if an address is confidential or not.
 Title  Usage
 Default ok
 Choose address_usages


 <!--%End block-->


 <!--%Begin block--><h3>Shipping Address</h3>


 FIELD  ship_name
 Type   varchar(50)
 Desc   Customer's name of shipping address (if applicable).
 Same 
        $ship_address_locale = $address_locale
        $ship_name           = $name
        $ship_company        = $company
        $ship_address1       = $address1
        $ship_address2       = $address2
        $ship_city           = $city
        $ship_state          = $state
        $ship_zip            = $zip
        $ship_postal_code    = $postal_code
        $ship_province       = $province
        $ship_country        = $country
        $ship_address_usage  = $address_usage


 FIELD  ship_company
 Type   varchar(50)
 Desc   Company name of shipping address (if applicable).


 FIELD  ship_address_locale
 Type   char(1)
 Radio  A American
        I International
 Default        A
 Label  no
 Compute <<End_Of_JavaScript
 if (radio_value(%ship_address_locale) == 'I') {
        // International address
        show_fields(%ship_province,%ship_postal_code,%ship_country);
        hide_fields(%ship_state,%ship_zip);
 } else {
        // American address
        hide_fields(%ship_province,%ship_postal_code,%ship_country);
        show_fields(%ship_state,%ship_zip);
 }
 End_Of_JavaScript


 FIELD  ship_address1
 Type   varchar(50)
 Desc   Customer's first line of shipping address.
 Title  Address


 FIELD  ship_address2
 Type   varchar(50)
 Desc   Customer's second line of shipping address.
 Label  no


 FIELD  ship_city
 Type   varchar(24)
 Desc   Customer's shipping address city.
 Label  City


 FIELD  ship_zip
 Type   varchar(10)
 Desc   USPS zip code for customer's address.
 Title  Zip
 onChange       $ship_state = lookup_state($ship_zip);


 FIELD  ship_state
 Type   char(2)
 Desc   Customer's shipping address state.
 Title  State
 Choose states


 FIELD  ship_postal_code
 Type   varchar(12)
 Desc   International postal code.
 Title  Postal Code


 FIELD  ship_province
 Type   varchar(24)
 Desc   Name of province or state for international addresses.
 Title  Province


 FIELD  ship_country
 Type   varchar(50)
 Desc   Customer's shipping address country.
 Title  Country
 Choose countries


 FIELD  ship_address_usage
 Type   varchar(7)
 Desc   Type of mailing address. It determines if an address is confidential or not.
 Title  Usage
 Default ok
 Choose address_usages


 FIELD  ship_via
 Type   varchar(15)
 Desc   Customer's preferred method of shipping.
 Choose ship_via_codes


 <!--%End block-->


 FIELD  phone
 Type   varchar(18)
 Desc   Customer's home phone number.
 Class  phone


 FIELD  fax
 Type   varchar(18)
 Desc   Customer's fax number.
 Class  phone


 FIELD  company_phone
 Type   varchar(18)
 Title  Business Phone
 Desc   Company's phone number.
 Class  phone


 FIELD  url
 Type   varchar(70)
 Desc   Customer's web URL address.
 Class  url


 FIELD  email
 Type   varchar(70)
 Desc   Customer's primary email address.
 Class  email


 <!--% BR --><!--$ Navigate Buttons -->


 FIELD  customer_type
 Type   varchar(7)
 Choose customer_types
 Desc   Code identifying the customer type.
 Required


 FIELD  customer_status
 Type   varchar(8)
 Desc   Customer status code.
 Choose cust_status_codes
 Required
 onChange       $customer_status_date = today()


 FIELD  customer_status_date
 Type   date
 Desc   Date the customer status last changed.
 Nocalendar


 FIELD  ad_source
 Type   varchar(8)
 Desc   Ad source code of advertisement that attracted this customer.
 Choose ad_sources
 onChange       $ad_source_date = today()


 FIELD  ad_source_date
 Type   date
 Desc   Date the customer responded to the ad source.
 Nocalendar


 FIELD  original_ad_source
 Type   varchar(8)
 Desc   Original ad source code of advertisement that attracted this customer.


 FIELD  referral_id
 Type   integer
 Desc   Customer ID of customer who referred this customer to us.
 Search customers
 Search_assign  $referral_id = $S.customer_id
                $referral = $S.customer_name


 FIELD  referral
 Type   varchar(50)
 Desc   Name of customer who referred this customer to us.


 FIELD  parent_id
 Type   integer
 Desc   Customer ID number of parent customer / business.
 Search customers


 FIELD  reference
 Type   varchar(12)
 Desc   Accounting reference number.


 FIELD  terms
 Type   varchar(20)
 Desc   Default terms for customer's orders.
 Choose terms


 FIELD  credit_limit
 Type   decimal(8,2)
 Class  money
 Desc   Customer's credit limit.


 FIELD  credit_status
 Type   varchar(8)
 Desc   Customer's credit status code.
 Choose credit_status_codes
 onChange       $credit_status_date = today()


 FIELD  credit_status_date
 Type   date
 Desc   Date the customer credit status last changed.
 Nocalendar


 FIELD  salesrep
 Type   varchar(32)
 Desc   Sales representative code name.
 Choose salesreps


 FIELD  mailing_lists
 Type   varchar(127)
 Desc   A list of mailing codes used to register the customer with one or more
        mailing lists.
 Array
 Choose mailing_list_codes


 FIELD  select_code
 Type   char(1)
 Desc   A temporary (working) code which can be used to mark and select customers
        for mailings and such.


 <!--% BR --><!--$ Navigate Buttons -->
 <!--% Detail -->
 <!--% BR --><!--$ Navigate Buttons -->


 FIELD  comments
 Type   varchar(255)
 Desc   Comments about the customer, special interests and needs.

Cheat Sheet

This is simply an extraction of the AcmeBase properties defined above. The headers are links to the section where they are defined.