Database Forms
Dernière mise à jour
Dernière mise à jour
K2 Geospatial 2024
Database forms are used to manage data stored in databases that are external to JMap. They allow you to add new data as well as modify or delete existing data.
You can create several database forms for one layer.
Each database form has its own permissions to control which users can make changes to the data.
These forms can be used to manage data using a 1 to 1 relationship with a layer’s data. In this case, a record is added when an element is created, and this record is usually deleted when the element is deleted.
They can also be used to manage data using a 1 to N relationship with the layer data. In this case, a special type of form called a subform is used and is always associated with a table form component. Refer to the subforms section for more information on this topic.
Configuring this type of form is an advanced JMap function that requires writing SQL queries to extract, add, modify and delete data.
To create a database form, in the Form section of the Forms tab of the layer configuration interface, press Create.
You must enter a name for the new form. Afterwards, you must select the type of form to create, Database, then select the database containing the data to be used in the forms.
At the following step, a window allows you to enter the form’s SQL queries. These queries are used to extract, add, modify, and delete data.
The SELECT query is required, but the others are optional.
Only operations for which SQL queries have been provided can be performed.
A special syntax is used for SQL query configuration. In general, each line of this syntax must end with a semicolon (;). The syntax is not case sensitive.
The following table provides a detailed explanation of how this syntax works.
ev(attrib) or elementValue(attrib) | This function returns the value of the attribute specified as a parameter for an element. Example
Returns the value of the element’s CITY attribute. |
fv(field) or formValue(field) | This function returns the value in the form for the field specified as a parameter. The name of the table must be the prefix of the field name. Example
Returns the value entered in the form component associated with the TABLE.NAME field of the database. |
ei() or elementId() | This function returns the id of the element. It can be the jmap_id field, or another field, as configured in the spatial data source. |
$variable | Allows you to create and initialize variables that can be used in the various expressions. For the INSERT query of a form, a variable containing the unique identifier of the inserted record must be used. Example
Creates a |
nti(table, field) or nextTableId(table, field) | This function returns the next value to use as a numeric identifier for the table and field specified as parameters. The specified field must exist in the specified table and must be an integer field. The function determines the maximum value existing in the table and returns this value incremented by 1 (max + 1). This function is useful when adding a new record in the database when the table has a unique identifier. Example
Creates and initializes a $id variable with the value of the next INSPECTION_ID identifier in the INSPECTIONS table. If the maximum value present is 100, the value returned is 101. |
The following table provides a detailed explanation of each parameter in the database form creation window.
Database | Select the database containing the data that must be managed by the form. |
SELECT query | The SQL query that provides the data used to populate the form fields. All the fields that are returned by this query can be used to configure the form’s components and also within other queries defined in the form using the Example of a query for a database form (1 to 1):
In this example, all the fields of the ASSET_INFO table are obtained and available to configure the form components. The ASSET_ID field is used to link towards the element of the ASSETS layer using the
Example of a query for a database form (1 to N):
In this example, all the fields of the INSPECTION table are obtained and available to configure the subform’s components. The ASSET_ID field is used to link towards the element of the ASSETS layer using the If the
In this example, all the fields of the INSPECTOR table are obtained and available to configure the subform’s components. The INSPECTION_ID field is used to link towards the element of the INSPECTION table using the
In this example, all the fields of the INTERVENTION table are obtained and available to configure the subform’s components. The INSPECTION_ID field is used to link towards the element of the INSPECTION table using the fv(field) function. |
Field of the unique identifier | The SELECT query must include a unique identifier field (numeric or alphanumeric). Select the field that must be used as a unique identifier.
|
INSERT query | SQL query that inserts new data in the database. Configure this query only if the form must allow records to be added to the database. You must define a variable that contains the value of the unique identifier for the new record. Example of a query for a database form (1 to 1):
In this example, the data of the form is inserted in the ASSET_INFO table when the INSERT SQL query is executed. The values of the ASSET_INFO.ASSET_YEAR, ASSET_INFO.BRAND and ASSET_INFO.ASSET_HEIGHT fields are provided by the values entered in the form using the The value of the ASSET_INFO.ASSET_ID variable is provided by the Example of a query for a database form (1 to N):
In this example, a $id variable is created and initialized with the next value (max + 1) of the ID field from the INSPECTION table. The form data is inserted in the INSPECTION table when the SQL INSERT query is executed. The values of the INSP_DATE and INSP_COMMENT fields are provided by the values the user entered in the subform using the The value of the INSPECTION_ID field is provided by the $id variable. The value of the ASSET_ID field is provided by the
In this example, a $id variable is created and initialized with the next value (max + 1) of the ID field from the INSPECTOR table. The form data is inserted in the INSPECTOR table when the SQL INSERT query is executed. The values of the INSPECTION_ID, CONTACT_INFO and TEAM fields are provided by the values the user entered in the subform using the The value of the INSPECTOR_ID field is provided by the
In this example, a $id variable is created and initialized with the next value (max + 1) of the ID field from the INTERVENTION table. The form data is inserted in the INTERVENTION table when the SQL INSERT query is executed. The values of the INSPECTION_ID, WORK_ORDER and FOLLOW_UP fields are provided by the values the user entered in the subform using the The value of the INTERVENTION_ID field is provided by the $id variable. |
Variable with the ID of the new element () | Select the variable containing the value of the unique identifier of the new record inserted in the table. It must be defined with the INSERT query. In order for your data to be recorded in the database (the photos, for example), you must define and select this variable.
|
UPDATE query | SQL query that updates the data in the database. Configure this query only if the form must allow data to be updated. Example of a query for a database form (1 to 1):
In this example, the form data is used to update the ASSET_INFO table when the UPDATE SQL query is executed. The values of the ASSET_YEAR, BRAND, and ASSET_HEIGHT fields are provided by the values the user entered in the form using the The WHERE clause allows you to update the record containing the element’s unique identifier, ASSET_ID, also using the Example of a query for a database form (1 to N):
In this example, the subform data is used to update the INSPECTION table when the UPDATE SQL query is executed. The values of the INSP_DATE and INSP_COMMENT fields are updated, and they are provided by the values the user entered in the form using the The WHERE clause uses the
In this example, the subform data is used to update the INSPECTOR table when the UPDATE SQL query is executed. The values of the CONTACT_INFO field are updated, and they are provided by the value the user entered in the form using the The WHERE clause uses the
In this example, the subform data is used to update the INTERVENTION table when the UPDATE SQL query is executed. The values of the WORK_ORDER and FOLLOW_UP fields are updated, and they are provided by the value the user entered in the form using the The WHERE clause uses the |
DELETE query | This SQL query deletes the data from the database. Only configure this query if the form must allow data to be deleted. Example of a query for a database form (1 to 1):
In this example, the record corresponding to the element’s ASSET_ID attribute value is deleted from the ASSET_INFO table using the Example of a query for a database form (1 to N):
In this example, the record corresponding to the inspection’s unique identifier is deleted from the table using the The records of the INTERVENTION and INSPECTOR nested subforms that correspond to the inspection’s unique identifier are also deleted from the table when the inspection is deleted.
In this example, the record corresponding to the inspector’s unique identifier is deleted from the table using the
In this example, the record corresponding to the intervention’s unique identifier is deleted from the table using the |
Subforms are database forms that are opened from a Table component that exists in another form. They are normally used to manage data that has a 1 to N relationship with the layer’s elements. They could be used to manage data for inspections on assets (N inspections can be associated to the same object), for example. They are configured similarly to the other database forms.
To create a subform, open the Subform tab on the Forms tab in the layer parameters menu, then click on Create.
You must enter a name for the new form and select the database containing the data to be used in the forms.
At the following step, you must configure the SQL queries of the subform, as explained above for the creation of a database form.
Several levels of subforms can be nested in a subform. They are used to manage data having a 1 to N relationship with the subform’s records. For example, they can be used to manage the data of interventions arising from an inspection performed on an asset (N interventions can be associated with 1 inspection).
A nested subform is opened from a Table component found in the subform it is associated with.
Creating a nested subform is done by following the same steps to create a subform. Examples of queries are displayed in the table of the Creating a database form section.
The following figure shows a preview of the nested subforms from our example, in JMap Pro.
Database forms have their own permissions. The Permissions button appears in the form configuration interface.
The Permissions section has two tabs: Permissions and Owners. The owners are the only ones who can manage the administrative permissions for the project, manage the list of owners and delete it.
Available users and groups are configured in the JMap Server section of JMap Admin. Refer to the Users and Groups section for more information about managing users in JMap Server.
Available permissions are described in the table below.
Add data | Allows a user or group to use the form to add new data in the database. |
Modify data | Allows a user or group to use the form to modify existing data in the database. |
Delete data | Allows a user or group to use the form to delete data existing in the database. |
A list of users and groups with permissions appears in the Permissions tab. Click on to add new users or groups.
