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).

Form properties

Database

Select the database containing the data that must be managed by the form.

SELECT query

SQL query that provides the data to populate the form's fields. All fields returned by this query can be used to configure form components and also within the other queries defined in the form using the fv() function.

The query must have a condition to link a layer attribute with a database field.

Example: select * from assets where asset_id = ev(asset_id);

In this example, all the fields of the assets table are obtained and available to configure form components. The asset_id field is used to link towards the layer element using the ev(attrib) 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 used to insert new data in the database. You should only configure this query 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: $id = ev(asset_id);

insert into assets (asset_id, date_inst, asset_type) values ($id, fv(assets.date_inst), fv(assets.asset_type));

In this example, a $id variable is created and initialized with the map element's asset_id attribute value using the ev(attrib) function. The form data is inserted in the assets table when the insert SQL query is executed. The values of the assets.date_inst and assets.asset_type fields are provided by the values the user has entered in the form using the fv(field) function. The value of the asset_id field is provided by the $id variable.

Example: $id = nti(inspections, id);

insert into inspections (id, date_insp, insp_by, notes) values ($id, fv(inspections.date_insp), fv(inspections.insp_by), fv(inspections.notes));

In this example, a $id variable is created and initialized with the next value (max + 1) of the id field from the inspections table. The form data is inserted in the inspections table when the INSERT SQL query is executed. The values of the date_insp, insp_by and notes fields are provided by the values the user has entered in the form using the fv(field) function. The value of the 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.

UPDATE query

This SQL query updates the data in the database. You should only configure this query if the form must allow data to be updated.

Example: update assets set date_inst = fv(assets.date_inst), asset_type = fv(assets.asset_type) where asset_id = ei();

In this example, the form data is used to update the assets table when the UPDATE SQL query is executed. The values of the assets.date_inst and assets.asset_type fields are provided by the values the user enters in the form using the fv(field) function. The WHERE clause allows you to update the record containing the element's unique identifier using the ei() function.

Example: update inspections set notes = fv(inspections.notes) where inspection_id = fv(inspections.inspection_id);

In this example, the form data is used to update the inspections table when the UPDATE SQL query is launched. Only the value of the notes field is updated and it is provided by the value the user enters 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. Note that in this case, the inspection_id field is not associated with a form component, however, its value is still accessible through the fv(field) function.

DELETE query

This SQL query deletes the data from the database. You should only configure this query if the form must allow data to be deleted.

Example: delete from assets where asset_id = ev(asset_id);

In this example, the record corresponding to the element's asset_id attribute value is deleted from the table using the ev(attrib) function to obtain the value of the element identifier.

Example: delete from inspections where inspection_id = fv(inspections.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 get the value of the identifier.

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.