Database Forms
Dernière mise à jour
Dernière mise à jour
K2 Geospatial 2022
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, select Forms in the layer parameters menu. Make sure you open the Forms tab, then click on 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.
Syntax for database forms
ev(attrib) or elementValue(attrib)
This function returns the value of the attribute specified as a parameter for an element.
Example:
ev(city);
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:
fv(table.name);
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:
$city = EV(city);
Creates a $city variable initialized with the value of the element’s CITY attribute.
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:
$id = nextTableId(inspections, inspection_id)
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.
Form properties
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 fv()function.
The query must contain a condition to link a layer attribute with a database field.
Example of a query for a database form (1 to 1):
SELECT * FROM ASSET_INFO WHERE ASSET_ID = EI();
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 ei() function.
If the ei() function returns a value in text format, you must place the statement between apostrophes to view the value:
SELECT * FROM ASSET_INFO WHERE ASSET_ID = 'EI()';
Example of a query for a database form (1 to N):
Example with the Inspections subform:
SELECT * FROM INSPECTION WHERE ASSET_ID = EI();
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 ei() function.
If the ei() function returns a value in text format, you must place the statement between apostrophes to view the value:
SELECT * FROM INSPECTION WHERE ASSET_ID = 'EI()';
Example with the Inspectors nested subform:
SELECT * FROM INSPECTOR WHERE INSPECTION_ID = FV(INSPECTION_ID);
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 fv(field) function.
Example with Interventions nested subform:
SELECT * FROM INTERVENTION WHERE INSPECTION_ID = FV(INSPECTION_ID);
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.
Example of a query for a database form (1 to 1): ASSET_INFO.ASSET_ID
Example of a query for a database form (1 to N), Inspections subform: INSPECTION.INSPECTION_ID
Example of a query for a database form (1 to N), Inspectors nested subform: INSPECTOR.INSPECTOR_ID
Example of a query for a database form (1 to N), Interventions nested subform: INTERVENTION.INTERVENTION_ID
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):
$id = ei(); INSERT INTO ASSET_INFO (ASSET_ID, ASSET_YEAR, BRAND, ASSET_HEIGHT) VALUES (EI(), FV(ASSET_INFO.ASSET_YEAR), FV(ASSET_INFO.BRAND), FV(ASSET_INFO.ASSET_HEIGHT));
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 fv(field) function.
The value of the ASSET_INFO.ASSET_ID variable is provided by the ei() function.
Example of a query for a database form (1 to N):
Example with Inspections subform:
$id = nti(INSPECTION, INSPECTION_ID);
INSERT INTO INSPECTION (INSPECTION_ID, ASSET_ID, INSP_DATE, INSP_COMMENT) VALUES ($id, EI(), FV(INSPECTION.INSP_DATE), FV(INSPECTION.INSP_COMMENT));
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 fv(field) function.
The value of the INSPECTION_ID field is provided by the $id variable.
The value of the ASSET_ID field is provided by the ei() function.
Example with Inspectors nested subform:
$id = nti(INSPECTOR, INSPECTOR_ID);
INSERT INTO INSPECTOR (INSPECTOR_ID, INSPECTION_ID, CONTACT_INFO, TEAM) VALUES ($id, FV(INSPECTOR.INSPECTION_ID), FV(INSPECTOR.CONTACT_INFO), FV(INSPECTOR.TEAM));
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 fv(field) function.
The value of the INSPECTOR_ID field is provided by the \$id variable.
Example with Interventions nested subform:
$id = nti(INTERVENTION, INTERVENTION_ID);
INSERT INTO INTERVENTION (INTERVENTION_ID, INSPECTION_ID, WORK_ORDER, FOLLOW_UP) VALUES ($id, FV(INSPECTION_ID), FV(INTERVENTION.WORK_ORDER), FV(INTERVENTION.FOLLOW_UP));
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 fv(field) function.
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.
Example of a query for a database form (1 to 1): $id
Example of a query for a database form (1 to N), Inspections subform: $id
Example of a query for a database form (1 to N), Inspectors nested subform: $id
Example of a query for a database form (1 to N), Interventions nested subform: $id
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):
UPDATE ASSET_INFO SET ASSET_YEAR = FV(ASSET_INFO.ASSET_YEAR), BRAND = FV(ASSET_INFO.BRAND), ASSET_HEIGHT = FV(ASSET_INFO.ASSET_HEIGHT) WHERE ASSET_ID = FV(ASSET_INFO.ASSET_ID);
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 fv(field) function.
The WHERE clause allows you to update the record containing the element’s unique identifier, ASSET_ID, also using the fv(field) function.
Example of a query for a database form (1 to N):
Example with Inspections subform:
UPDATE INSPECTION SET INSP_DATE = FV(INSPECTION.INSP_DATE), INSP_COMMENT = FV(INSPECTION.INSP_COMMENT) WHERE INSPECTION_ID = FV(INSPECTION.INSPECTION_ID);
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 fv(field) function.
The WHERE clause uses the fv(field) function to retrieve the value of the record’s unique identifier.
Example with Inspectors nested subform:
UPDATE INSPECTOR SET CONTACT_INFO = FV(INSPECTOR.CONTACT_INFO), TEAM = FV(INSPECTOR.TEAM) WHERE INSPECTOR_ID = FV(INSPECTOR.INSPECTOR_ID);
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 fv(field) function.
The WHERE clause uses the fv(field) function to obtain the value of the record’s unique identifier.
Example with Interventions nested subform:
UPDATE INTERVENTION SET WORK_ORDER = FV(INTERVENTION.WORK_ORDER), FOLLOW_UP = FV(INTERVENTION.FOLLOW_UP) WHERE INTERVENTION_ID = FV(INTERVENTION.INTERVENTION_ID);
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 fv(field) function.
The WHERE clause uses the fv(field) function to obtain the value of the record’s unique identifier.
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):
DELETE FROM ASSET_INFO WHERE ASSET_ID = FV(ASSET_INFO.ASSET_ID);
In this example, the record corresponding to the element’s ASSET_ID attribute value is deleted from the ASSET_INFO table using the fv(field) function to obtain the value of the element’s identifier.
Example of a query for a database form (1 to N):
Example with Inspections subform:
DELETE FROM INSPECTION WHERE INSPECTION_ID = FV(INSPECTION.INSPECTION_ID); DELETE FROM INTERVENTION WHERE INSPECTION_ID = FV(INSPECTION.INSPECTION_ID); DELETE FROM INSPECTOR WHERE INSPECTION_ID = FV(INSPECTION.INSPECTION_ID);
In this example, the record corresponding to the inspection’s unique identifier is deleted from the table using the fv(field) function to obtain the value of the identifier.
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.
Example with Inspectors nested subform:
DELETE FROM INSPECTOR WHERE INSPECTOR_ID = FV(INSPECTOR.INSPECTOR_ID);
In this example, the record corresponding to the inspector’s unique identifier is deleted from the table using the fv(field) function to obtain the value of the identifier.
Example with Interventions nested subform:
DELETE FROM INTERVENTION WHERE INTERVENTION_ID = FV(INTERVENTION.INTERVENTION_ID);
In this example, the record corresponding to the intervention’s unique identifier is deleted from the table using the fv(field) function to obtain the value of the identifier.
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, select Forms in the layer parameters menu. Make sure you open the Subform tab, 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. Available permissions are described in the table below.
Form permissions
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.
Layer attribute forms and database forms and subforms allow you to define global form validation rules based on multiple fields. You can also define validation rules at the component level.
The rules validate the values of the fields on the client side, that is, the values that the users of JMap applications enter in the forms. The validation takes place when the form is saved, which is not done if the rules are not respected.
To configure the rules of a form or a subform, open the configuration interface of the form or subform that interests you:
Press Create. The window for creating a rule is displayed.
Name
Enter a name for the rule.
Expression
Enter the logical expression that defines the rule. The syntax is that of JSonLogic. You can enter logical and / or mathematical expressions. The expression must be respected for the form to be validated and closed. The user of a JMap application (Pro, Survey, NG) must enter the correct data for the fields concerned by the expression in order the expression to be respected (is true) and the form can be saved and closed.
Exemple :
{“!”:[
{“and”:[
{“==”:[{“var”:“STATUT”},“done”]},
{“or”:[
{“==”:[{“var”:“MATERIAL”},“”]},
{“==”:[{“var”:“INSP_DATE”}, null]},
{“==”:[{“var”: “DIAMETER”}, null]},
{“<=”:[{“var”:“DIAMETER”}, 0]}
]}
]}
]}
This expression indicates that when the value of the STATUS field is “done”, the MATERIAL, INSP_DATE and DIAMETER fields must be initialized (their value cannot be null or 0).
Note: Currently JMap cannot validate expressions that contain date type fields.
Message
Enter the error message that appears when the logical expression is False. You can enter the message in multiple languages.
Exemple :
Fields MATERIAL, INSP_DATE and DIAMETER cannot be null or empty if STATUS is 'Done'
Press Save.
You can create multiple rules for the same form.
Press to open the menu and select the Rules option. The interface that lists the rules is displayed.
