Relationship between forms and database tables

The arschema table holds information about each form, including form name, schema ID and next request ID.

When a regular form is created, three or more of the following tables are created in the database to hold the information (requests) for that form:

• Main data table
• Status history table
• Attachment tables
• Currency table

The main data table for a form

Each form has an associated main data table that holds all the information for that form. The main data table contains a column for each field except Attachments and Status History. Each main data table or view (for join forms) is named with a T followed by the unique ID (schemaID ) for the form (for example, T3 ). To find the ID, search the arschema table by the name column and retrieving the schemaId value. The ID does not change regardless of changes made to the form, so the table name remains the same. In The-AR-System-data-dictionary, the main data table is labeled T n. 

All columns in each table or view are named with a C followed by the unique ID for the field in the form. For example, the Submitter field is C2. The ID for the field does not change; the creator of the field can assign the ID. Every ID is unique within a form, so duplicate name issues do not occur. After an ID is assigned, it cannot be changed, regardless of any changes to the field. For information about reserved and core IDs, see the Developing section. 

If a join form contains an attachment field, a column is added to the Main Data view. The contents of this column are a concatenation of the C, CO, and CC columns of the Attachment Details table. If attachments are added to the base form, the view is updated. See The attachment tables for a form below.

Because AR System must retain the IDs of the requests in the underlying table to form the ID of a join form entry, there are a few extra columns and some special handling for column C1. AR System creates a series of columns for each regular form that is involved in the join tree. The columns are named with an E followed by a zero-based index (three regular tables would be named E0, E1, and E2 ). These columns point to the corresponding entry IDs (column C1 ) of the regular forms. The C1 column for the join form is determined by concatenating the entry IDs of the regular forms (in the E columns) separated by vertical bars (| ).

The status history table for a form

The status history table contains all the information for the Status History field. Each status history table or view (for join forms) is named with an H followed by the unique ID for the form (for example, H3 ). The ID is the same ID that the main data table or view uses, and the name of each also remains unchanged. Every main data table has an associated status history table. In The-AR-System-data-dictionary, the status history table is labeled Hn. 

In AR System 7.0.00 and later, status history tables are optional and are set through form properties, so status history tables are not always available for regular forms.

The most important column in this table is the entryId. It provides a reference to the C1 column of the main data table. (Column C1 is always the Request ID.) This column is followed by a series of one or more column pairs. There is one pair for each state defined for the Status field. The columns are named with a prefix followed by the numeric representation for each state. The prefixes are U for the user name and T for the time the entry was last changed to the corresponding state. The numeric value is zero-indexed. For example, a form with three states for the Status field would yield a table with seven columns: entryId, U0, T0, U1, T1, U2, and T2. 

If status values are added, appropriate columns are added to this table to reflect the new states. If states are deleted, the columns are left in the table, enabling the states to be added again in the future. The data for the status values is stored in the database as an integer that relates to the order of the choices. If you add values at the beginning or in the middle of existing values, other values in the list might change. 

Unlike in regular forms, for join forms, the Status History field is optional. If it is present, the Status and Status History fields must be from the same base table. If there is no Status History field in the form, the Status History table does not exist. If a Status History field is present, it is defined as an exact duplicate view of the status history table or view of the base form to which it is connected. The only difference is the name of the view. For more information about the Status History field, see Setting-form-view-properties.

View and vendor forms do not have corresponding status history tables.

The attachment tables for a form

There are two attachment tables: the attachment details table and the attachment data table.

Attachment details table

The Attachment details table contains information for the properties of Attachment fields. For every Attachment field in the form, a separate table is created to store the attachment value. 

The Attachment details table is named with a B followed by the unique ID for the form (for example, B3 ). In The-AR-System-data-dictionary, the attachment details table is labeled B n . An attachment details table with one column (C1 ) is created with every form. 

For every attachment field added to any attachment pool on the form, three columns are added. Each column is named with C, CO, or CC, followed by the attachment field ID. For example, the three columns added for one attachment might be called C536870920, CO536870920, and CC536870920, where 536870920 is the attachment field ID. 

The C column stores the full path name of the attached file. The CO column stores the original size (in bytes) of the attached file. The CC column stores the compressed size (in bytes) of the attachment file.

Attachment data table

For each attachment field on a form, an attachment data table is created. The attachment data table is named with a B followed by the unique ID for the form, followed by C, followed by the attachment field ID. For example, the attachment data table might be called B7C536870920, where 7 is the schemaID, and 536870920 is the attachment field ID. In The-AR-System-data-dictionary, the attachment data table is labeled BnC fID. 

The Attachment data table has two columns: one that holds the Request ID (entryId ) and one that holds the data from the file. The column holding the data is named with a C followed by the attachment field ID. For example, the data column might be named C536870920, where 536870920 is the attachment field ID.

Limiting attachment size

Attachments can be very large. Large attachments can adversely affect AR System server memory resources. To limit the size of attachments sent to the server and thereby prevent excessive memory growth and reduce transmission times, use the following AR System configuration file option:
AR-Max-Attach-Size — Specifies the maximum size of compressed attachments that can be sent to the AR System database from the AR System server. This option applies to all databases. 

This limit does not apply to attachments retrieved from the database by the AR System server. Hence, if large attachments were added to any database before this limit was specified, the server can still retrieve them. 

For Oracle databases, however, you can also limit the size of retrievable attachments by using the following AR System configuration file option:
Db-Max-Attach-Size — Limits the size of compressed attachments that the AR System server can retrieve from Oracle databases. 

For information about setting configuration file options, see AR System configuration files.

Currency columns for a form

Where a field in a form typically has one corresponding column in the main data table, the currency field has several columns and, therefore, a unique naming convention to distinguish the extra columns. Whereas typical fields follow the naming convention described here (all columns in each table or view are named with a C followed by the unique ID for the field within the form), the currency field is named with a C followed by the unique ID for the currency field and a unique suffix for each additional currency column stored in the database. 

The currency suffixes used to name the additional currency columns are defined in the following table.

For example, the columns for a currency field might be called C536870913V, C536870913C, C536870913D, or C536870913USD.