The Form Instances dataset is pivotal for managing form data, linking to several other datasets that enhance its functionality and provide supporting information. Each related dataset provides additional context or details, establishing a comprehensive structure for form data. Below are the datasets directly related to Form Instances, with their connection points highlighted.
FormTemplates
Link: The form_id in Form Instances links to the id in FormTemplates.
Schema Overview: FormTemplates define customizable form structures, including nested arrays for template names, parts, and sections, all of which support localization. This connection allows each form instance to utilize a specific template, dictating the structure and content layout of each form.
FormAnswers
Link: The id in Form Instances corresponds to form_instance_id in FormAnswers.
Schema Overview: FormAnswers contain individual responses to form fields, with fields like form_answer_id, form_field_id, and multilingual value. This relationship provides detailed response data within each form instance, capturing user input and form responses.
Projects
Link: The project_id in Form Instances connects to the id in Projects.
Schema Overview: Projects provide the primary project context for each form instance, including attributes such as id, organization_id, name, description, start_date, and end_date. This link places each form instance within a specific project scope.
Phases
Link: The project_phase_id in Form Instances connects to the id in Phases.
Schema Overview: Phases describe various project stages, with multilingual names and attributes such as updated_at. This relationship indicates the specific project phase associated with each form instance.
Companies
Link: The company_id in Form Instances links to the id field in Companies.
Schema Overview: Companies include details such as name, enabled status and organization_id. This association allows each form instance to be associated with a specific company, facilitating organizational reporting and tracking.
Modules
Link: The module_id in Form Instances connects to module_id in Modules.
Schema Overview: Modules provide data like engine, color, icon, and multilingual names, allowing each form instance to be linked to specific functional areas or project components represented by modules.
Plans
Link: The plan_id in Form Instances links to the id in Plans.
Schema Overview: Plans include attributes like name, parent_id, and organization_id, creating an organized project structure that form instances can be associated with, aiding in planning and execution tracking.
Documents
Link: The project_id in Form Instances associates with project_id in Documents.
Schema Overview: Documents provide metadata such as file_type, name, type, and timestamps (created_at, updated_at). This linkage enables project-related documents to be associated with form instances for richer context and reference.
Statuses
Link: The status_id in Form Instances connects to the id in Statuses.
Schema Overview: Statuses define the state of form instances, including multilingual names, color, and position. This link provides a way to track the current state or progress of each form instance.
Users
Link: The created_by_user_id and updated_by_user_id fields in Form Instances link to user_id in Users.
Schema Overview: Users contain details such as email, first_name, and last_name, identifying the users responsible for creating and updating each form instance, and enabling accountability and tracking of user actions.
The FormTemplates dataset has a complex hierarchical structure designed to support customizable forms. Each form template defines the structure and layout of the form, with nested levels that allow extensive customization. This hierarchy is organized into parts, sections, and fields:
root
|-- id: string (nullable = true)
|-- created_at: timestamp (nullable = true)
|-- updated_at: timestamp (nullable = true)
|-- deleted_at: timestamp (nullable = true)
|-- created_by_id: string (nullable = true)
|-- updated_by_id: string (nullable = true)
|-- deleted_by_id: string (nullable = true)
|-- template: struct (nullable = true)
| |-- names: array (nullable = true)
| | |-- element: struct (containsNull = true)
| | | |-- id: string (nullable = true)
| | | |-- translation: string (nullable = true)
| | | |-- localization: string (nullable = true)
| |-- state: integer (nullable = true)
| |-- version: integer (nullable = true)
| |-- family_id: string (nullable = true)
| |-- parent_id: string (nullable = true)
| |-- visibility: string (nullable = true)
| |-- created_by_id: string (nullable = true)
| |-- deleted_by_id: string (nullable = true)
| |-- form_statuses: array (nullable = true)
| | |-- element: struct (containsNull = true)
| | | |-- id: string (nullable = true)
| | | |-- num: integer (nullable = true)
| | | |-- color: string (nullable = true)
| | | |-- names: array (nullable = true)
| | | | |-- element: struct (containsNull = true)
| | | | | |-- id: string (nullable = true)
| | | | | |-- translation: string (nullable = true)
| | | | | |-- localization: string (nullable = true)
| | | |-- form_id: string (nullable = true)
| | | |-- created_at: string (nullable = true)
| | | |-- deleted_at: string (nullable = true)
| | | |-- updated_at: string (nullable = true)
| | | |-- created_by_id: string (nullable = true)
| | | |-- deleted_by_id: string (nullable = true)
| | | |-- updated_by_id: string (nullable = true)
| | | |-- default_status: boolean (nullable = true)
| | | |-- name_translatable_cell_id: string (nullable = true)
| |-- form_template: struct (nullable = true)
| | |-- id: string (nullable = true)
| | |-- names: array (nullable = true)
| | | |-- element: struct (containsNull = true)
| | | | |-- id: string (nullable = true)
| | | | |-- translation: string (nullable = true)
| | | | |-- localization: string (nullable = true)
| | |-- parts: array (nullable = true)
| | | |-- element: struct (containsNull = true)
| | | | |-- id: string (nullable = true)
| | | | |-- num: integer (nullable = true)
| | | | |-- names: array (nullable = true)
| | | | | |-- element: struct (containsNull = true)
| | | | | | |-- id: string (nullable = true)
| | | | | | |-- translation: string (nullable = true)
| | | | | | |-- localization: string (nullable = true)
| | | | |-- detail: string (nullable = true)
| | | | |-- weight: integer (nullable = true)
| | | | |-- sections: array (nullable = true)
| | | | | |-- element: struct (containsNull = true)
| | | | | | |-- id: string (nullable = true)
| | | | | | |-- num: integer (nullable = true)
| | | | | | |-- names: array (nullable = true)
| | | | | | | |-- element: struct (containsNull = true)
| | | | | | | | |-- id: string (nullable = true)
| | | | | | | | |-- translation: string (nullable = true)
| | | | | | | | |-- localization: string (nullable = true)
| | | | | | |-- detail: string (nullable = true)
| | | | | | |-- weight: integer (nullable = true)
| | | | | | |-- qr_code: boolean (nullable = true)
| | | | | | |-- fields: array (nullable = true)
| | | | | | | |-- element: struct (containsNull = true)
| | | | | | | | |-- id: string (nullable = true)
| | | | | | | | |-- num: integer (nullable = true)
| | | | | | | | |-- names: array (nullable = true)
| | | | | | | | | |-- element: struct (containsNull = true)
| | | | | | | | | | |-- id: string (nullable = true)
| | | | | | | | | | |-- translation: string (nullable = true)
| | | | | | | | | | |-- localization: string (nullable = true)
| | | | | | | | |-- points: integer (nullable = true)
| | | | | | | | |-- prefix: string (nullable = true)
| | | | | | | | |-- suffix: string (nullable = true)
| | | | | | | | |-- weight: integer (nullable = true)
| | | | | | | | |-- choices: string (nullable = true)
| | | | | | | | |-- details: string (nullable = true)
| | | | | | | | |-- required: boolean (nullable = true)
| | | | | | | | |-- data_type: string (nullable = true)
| | | | | | | | |-- form_link: string (nullable = true)
| | | | | | | | |-- created_at: string (nullable = true)
| | | | | | | | |-- created_by: string (nullable = true)
| | | | | | | | |-- deleted_at: string (nullable = true)
| | | | | | | | |-- deleted_by: string (nullable = true)
| | | | | | | | |-- updated_at: string (nullable = true)
| | | | | | | | |-- updated_by: string (nullable = true)
| | | | | | | | |-- internal_code: string (nullable = true)
| | | | | | | | |-- summary_field: boolean (nullable = true)
| | | | | | | | |-- qr_code_mapping_key: string (nullable = true)
| | | | | | | | |-- data_referential_tag: string (nullable = true)
| | | | | | | | |-- type_field_data: integer (nullable = true)
| | | | | | | | |-- presentation_type: string (nullable = true)
| | | | | | | | |-- complementary_text: string (nullable = true)
| | | | | | | | |-- form_field_sa_text: struct (nullable = true)
| | | | | | | | | |-- id: string (nullable = true)
| | | | | | | | | |-- end_with: string (nullable = true)
| | | | | | | | | |-- only_text: boolean (nullable = true)
| | | | | | | | | |-- max_length: integer (nullable = true)
| | | | | | | | | |-- min_length: integer (nullable = true)
| | | | | | | | | |-- start_with: string (nullable = true)
| | | | | | | | | |-- contains_text: string (nullable = true)
| | | | | | | | | |-- default_value: string (nullable = true)
| | | | | | | | | |-- form_field_id: string (nullable = true)
| | | | | | | | | |-- case_sensitive: integer (nullable = true)
| | | | | | | | | |-- case_sensitive_mode: string (nullable = true)
| | | | | | | | |-- form_template_section_id: string (nullable = true)
| | | | | | | | |-- qr_code_mapping_position: string (nullable = true)
| | | | | | | | |-- name_translatable_cell_id: string (nullable = true)
| | | | | | | | |-- description_translatable_cell_id: string (nullable = true)
| | | | | | |-- created_at: string (nullable = true)
| | | | | | |-- created_by: string (nullable = true)
| | | | | | |-- deleted_at: string (nullable = true)
| | | | | | |-- deleted_by: string (nullable = true)
| | | | | | |-- is_enabled: boolean (nullable = true)
| | | | | | |-- updated_at: string (nullable = true)
| | | | | | |-- updated_by: string (nullable = true)
| | | | | | |-- created_by_id: string (nullable = true)
| | | | | | |-- deleted_by_id: string (nullable = true)
| | | | | | |-- internal_code: string (nullable = true)
| | | | | | |-- updated_by_id: string (nullable = true)
| | | | | | |-- qr_code_format: string (nullable = true)
| | | | | | |-- data_referential_id: string (nullable = true)
| | | | | | |-- internal_description: string (nullable = true)
| | | | | | |-- data_referential_tags: string (nullable = true)
| | | | | | |-- form_template_part_id: string (nullable = true)
| | | | | | |-- name_translatable_cell_id: string (nullable = true)
| | | | | | |-- qr_code_separator_key_value: string (nullable = true)
| | | | | | |-- qr_code_separator_information: string (nullable = true)
| | | | | | |-- description_translatable_cell_id: string (nullable = true)
| | | | |-- created_at: string (nullable = true)
| | | | |-- created_by: string (nullable = true)
| | | | |-- deleted_at: string (nullable = true)
| | | | |-- deleted_by: string (nullable = true)
| | | | |-- updated_at: string (nullable = true)
| | | | |-- updated_by: string (nullable = true)
| | | | |-- created_by_id: string (nullable = true)
| | | | |-- deleted_by_id: string (nullable = true)
| | | | |-- internal_code: string (nullable = true)
| | | | |-- updated_by_id: string (nullable = true)
| | | | |-- form_template_id: string (nullable = true)
| | | | |-- internal_description: string (nullable = true)
| | | | |-- name_translatable_cell_id: string (nullable = true)
| | | | |-- description_translatable_cell_id: string (nullable = true)
| | |-- state: integer (nullable = true)
| | |-- weight: integer (nullable = true)
| | |-- version: integer (nullable = true)
| | |-- embedded: boolean (nullable = true)
| | |-- parent_id: string (nullable = true)
| | |-- created_at: string (nullable = true)
| | |-- created_by: string (nullable = true)
| | |-- deleted_at: string (nullable = true)
| | |-- deleted_by: string (nullable = true)
| | |-- updated_at: string (nullable = true)
| | |-- updated_by: string (nullable = true)
| | |-- allow_draft: boolean (nullable = true)
| | |-- presentation: integer (nullable = true)
| | |-- created_by_id: string (nullable = true)
| | |-- deleted_by_id: string (nullable = true)
| | |-- external_code: string (nullable = true)
| | |-- internal_code: string (nullable = true)
| | |-- updated_by_id: string (nullable = true)
| | |-- duplicate_from_id: string (nullable = true)
| | |-- internal_description: string (nullable = true)
| | |-- name_translatable_cell_id: string (nullable = true)
| | |-- description_translatable_cell_id: string (nullable = true)
| |-- finalcad_ready: boolean (nullable = true)
| |-- form_template_id: string (nullable = true)
| |-- duplicate_from_id: string (nullable = true)
| |-- current_organization_id: string (nullable = true)
| |-- name_translatable_cell_id: string (nullable = true)
| |-- parent_in_organization_id: string (nullable = true)
| |-- id_in_current_organization: string (nullable = true)
| |-- current_organization_visibility: string (nullable = true)
| |-- description_translatable_cell_id: string (nullable = true)Key Components:
Parts: Each form template is divided into multiple parts, each representing a major section of the form. Parts help organize sections logically, and each part includes a unique id, names array (for multilingual support), and other metadata
Sections: Inside each part, there are one or more sections. Sections serve as containers for fields and allow further subdivision within each part. Each section includes attributes like id, num (for ordering), and an array of names for localized labels
Fields: Fields are the most granular elements within a form template and represent the individual items users fill out on the form. Each field has a unique id, which is critical as it serves as a reference point in FormAnswers. The form_field_id in FormAnswers links each answer given in a form instance back to the specific field defined in the FormTemplates dataset
Family ID: The family_id is a unique identifier shared across all versions of a given form template, ensuring continuity and linkage between different versions of the same template.
Internal Code: The internal_code serves as a consistent reference point for fields, sections & parts across multiple versions of a form template, allowing fields to be reliably tracked and linked between different template iterations.
type_field_data: Specifies the data type of the field used in the form.
The type_field_data defines the data type for each form field and determines how to display it:
type_field_data equal to Text, Paragraph, Phone, Email, or Formula is of type Text.
type_field_data equal to Number is of type Number.
type_field_data equal to Date, Time, or DateTime is of type Calendar:
type_field_data equal to Radio is of type SingleChoice.
type_field_data equal to Checkboxes is of type MultipleChoice.
type_field_data equal to OkKo or OkKo+NA is of type Control.
type_field_data equal to Grid is of type Resources. It combines several fields within a grid.
The FormAnswers dataset is dedicated to storing the complete history of responses for each form field within a form instance. This structure ensures comprehensive tracking and auditing of all user inputs over time.
Below is the detailed schema of the FormAnswers dataset:
root
|-- form_answer_id: string (nullable = true)
|-- edited_at: timestamp (nullable = true)
|-- form_field_id: string (nullable = true)
|-- form_instance_id: string (nullable = true)
|-- form_template_section_id: string (nullable = true)
|-- is_deleted: boolean (nullable = true)
|-- project_id: string (nullable = true)
|-- user_id: string (nullable = true)
|-- value: array (nullable = true)
| |-- element: struct (containsNull = true)
| | |-- _: string (nullable = true)
| | |-- en: string (nullable = true)
| | |-- fr: string (nullable = true)
| | |-- ... (other locales)
|-- organization_id: string (nullable = true)The value attribute contains a list of values linked to a specific field (form_field_id). There are two possible cases:
Multi-choice fields
The value array contains multiple elements, with each element representing a selected choice.
Other field types
The value array contains only one element, representing the entered or predefined value for the field.
Each element in the value array is stored in one of two possible formats, using a key-value structure:
Multilingual format
If the _ key is empty, the response is stored under locale-specific keys (e.g., en, fr, …).
This format is used for fields with predefined values that need multilingual support.
Single-language format
When the _ key is filled, it contains the raw value entered by the user or set by default.
This format is used for fields that do not require translation, such as free-text fields or numerical values.
