Version 4.18 introduced “custom forms”. Like Standard Forms, custom forms target a single storage table, but aspects such as which fields appear on the form and the sequence of fields can be customized. It is also possible to customize form field labels, display read-only fields, set default values, conditionally hide fields, filter items in foreign key dropdowns, add validation expressions (beyond the rules defined by the data model, which are always enforced) and group fields.

• Using Custom Forms
• Create/Edit Custom Forms
• Using SubForms

Using Custom Forms

Accessing Custom Forms

Custom forms appear under a new top-level menu item called Forms.

Visibility of the Forms menu item requires either the FORM_MANAGE permission (to create and manage forms) or FORM_DATA_ENTRY permission (to use one or more forms).

The mart’s home page also lists the forms the user has permission to use:

“i” Icons

Clicking the “i” information icon above any field reveals details about the field such as the underlying table field code, description and data type. Clicking the “i” icon at the top of the form will reveal this information for all fields.

Record Check

When creating a new record with a form, the user is first prompted to enter the business primary keys of the record before filling out the entirety of the form. This iso that a record check is performed to see if an existing record with matching business primary keys exists.

If a matching record exists, it is retrieved and the form switches to updating the retrieved record (rather than creating a new record). This facilitates editing existing records and to prevent the creation of duplicates before the entire form is filled in. A record check is also performed after the Save button is pressed, in case the user changed the business primary keys while editing the entirety of the form and inadvertently created a duplicate. It is also possible to mark business primary keys as read-only in the form definition. In this case, business primary keys can be entered at the time of the record check but become read-only if an existing record is found. After filling out the form, the user can either just Save, or Save and Add New Record (this button text is configurable and also this button can be hidden).

Save Buttons and Behavior

If the user presses Save, the user is returned to a view of their data with their most recent edit at the top.

This view of the data matches the structure of the form - the columns and their sequence correspond to the form. This facilitates a quality check of the data after submitting and also the ability to quickly view or edit past data records. Access to this form-specific view of data is also available from the Forms page.

Therefore, data entry users might not have access to the Data page but can nevertheless see the data they submitted via the Forms page.

Batches Created Behind the Scenes

Behind the scenes, pressing a Save button results in the creation and committing of 1 new batch consisting of 1 record, visible on the load menu. The origin title is “Submit form data” (code = SYS_FORM_DATA_ENTRY) and the code of the target table is added to the batch comment.

Pre-filled Fields With URL Parameters

URL parameters can be used to pre-fill form field values. Use the field code for the URL parameter name and set it equal to the desired value as shown below.

Create and Edit Custom Forms

Create/Edit a Table

Every custom form requires and is bound to an existing table to receive the form data (the “target” or “storage” table).

If a table does not yet exist, it must be created (for example see create a table using an excel template or add a table using the UI.

A single table can have many custom forms. This facilitates customization of the form for different audiences (ie forms in different languages or forms with different field subsets depending upon the dat entry role/responsibility).

Every field on a custom form must be linked to a field in the data model. By default, custom forms inherit the properties of the table they are attached to. This includes properties such as field labels, description and sequence which can optionally be overridden in the form. This also includes model validation rules such as “is required”, data types and text lengths. Model validation rules cannot be overridden in the form (but additional rules can be added).

TIP: when you want to change the form, it sometimes makes sense to modify the underlying table rather than the form, to keep the things consistent across the table and any forms. For example, to improve a form field description, consider improving the underlying table field description and not specifying a form field description.

Create/Edit a Form

To create or edit a form, fill out and upload an excel template, much like creating or editing the data model. The system asks for the target table of the new form

The template has 2 worksheets, “Forms” and “Form Fields”.

On the Forms worksheet, enter information about the form itself. The “Table to populate” column must refer to an existing table in the data model. On the Form Fields worksheet, enter the fields that should appear on the form.

The Simplest Form - a list of fields

The simplest custom form is simply a list of table field codes.

No other information is mandatory because the underlying data model provides the needed information (by default).

The following image shows very simple but valid form with 4 fields. CASE_COUNTS is the form code and the FIELD_CODE column contains the list of target table field codes

When the form is displayed, because Country is a foreign key field, it is displayed as a dropdown list of countries. By default, the values shown in the dropdown come from the field in the related country table that is marked as “row title” (but values from a different field can be specified, see below).

Because Report Date is a date field, a date picker is displayed. Integer fields only accept integers, the max text length of underlying fields is enforced, and so on.

Customizing Forms

Summary of customizable properties.

Form level

Form field level

For more informatipn about SubForms, go to the Using SubForms Article

Process with custom origin

Optionally, it is possible for submitted form data to be processed by a custom pipeline/origin before being committed. This allows modifications to occur after the user presses the Save button and before the data is committed to the database.

To do this, you need to create a pipeline to perform the processing (see below). The form sends data using a zip file to the pipeline so the Extract section should use the GetZip exactly as it is shown below.

   	<GetZip FileNameToExtractPattern="(\w+)\.csv$">
      <GetText OutputTableName="$1" Delimiter=","/>
    </GetZip>

The zip is consisted of a number of csv files corresponding to the total number of tables the parent form and it’s subforms, if any, are targeting. Each csv file is named after the target table. For example there is the parent FORM_A and the subform FORM_B and each form is targeting the following tables TABLE_A and TABLE_B. In this case inside the zip folder there will be the following csv files, TABLE_A.csv, TABLE_B.csv. This information is useful because the name of the csv file will be used as the SourceTable, in the Load section of the pipeline, as you will see below.

Perform the desired transformation in the Transform section of the LoadTable command.

Use ColumnMappings Auto=”true” to automatically map all columns of the form to the target table but add any new/special column mappings.

Once the pipeline is created, enter the origin code (or create a new origin code) into the ORIGIN_CODE column on the Forms worksheet:

Where to locate the origin code:

The pipeline will now be executed every time the form is saved.