The Connector Element is a powerful database object. From the web or mobile it is used to map data and between fields, forms, tables, or even data sources.
It can be used for the purpose of populating data into the current form or writing the information contained within it to another source.
Connectors map the fields on the form using their references to the references in the mapping form or database. Therefore it is a good idea to ensure that all of the form fields are properly referenced with meaningful names to make this task easier.
Connector Type: There are two types of connectors, a form connector and a data source connector. The form connector maps data between two forms and is typically the lesser used of the two types since it is strictly a read and relies on a specific known submission. The data source connector however is a link between the form and the underlying database, it supports both read and write and is more flexible in its execution. If a data source connector is selected you will need to specify the data source by selecting it from the list. See ‘Data sources’ under the admin section of this guide for information on creating data sources.
Event: Allows you to specify when and how the connector will be used.
Manual: The connector is executed by a manual action, typically the click of a button or on the selection of a value within a pulldown menu. If the button is not clicked or a value not selected, then the connector will not be executed. The manual event is often used with the button element to call the functions doDBConnector and doDBExport as described under 7.4 Button and the chapter X on Reporting and Exracts
On Load: The connector is executed on load of the form, or in MobiTask on refresh of the form (including after a lookup is called)
Before Submit: The connector is executed at the time the form is submitted but before the actual submit action is executed. This is a pre-submit action commonly used for validating data prior to executing the actual submission procedures
On Submit: The connector is executed at the time the submission is created as part of the submission transaction.
Operation: Specifies how the connector will operate as it relates to the data source connection.
Raise all exceptions: The connector will be interrupted by all exceptions.
All except empty result exception: The connector will be interrupted by all exceptions except for those relating to an empty result.
Suppress all exceptions: The connector will not be interrupted.
Fire Connector on every version: A connector by default will only execute the first time it has the opportunity to do so, typically the first time the form is loaded or submitted. It is then ignored for each subsequent load/submission of that particular instance. In some cases however it may be necessary to re-apply the connector, for instance in a workflow where additional important information may be added down the processing line. In this case we can force the system to execute the specified connector for every matching action. The onus is then on the designer to ensure the correct information is being updated/retrieved at each revision.
Take a Time-Off Request as an example. Typically in this situation the user is requesting time-off, perhaps a vacation or sick day. The request then goes to the manager for approval and then to HR to validate before the employee’s vacation bank gets docked. We can’t reduce the hours when the employee submits the request because it is not yet approved so the connector needs to be executed at each step, but must include a check to see if the form was approved before it makes the reductions.
Server and Client Side SQL: As mentioned at various points throughout this guide eXFORMA and MobiTask both use different databases. eXFORMA can be connected to multiple data sources outside of its own, while MobiTask uses only its own internal SQLite database. There are a few options for providing SQL for the system to use based on the end-user interface.
Web-based User: If the user is web-based then the application will read from the Server Side SQL. The syntax should match that of the data source you’re connecting to. If the data source is Oracle then use Oracle syntax. The system will add the ending ‘;’ automatically when it parses the SQL statement, so it should be omitted from your query.
MobiTask User: If Client Side SQL exists then MobiTask will execute the query against its internal database, so the syntax provided must be in SQLite. The query will be executed offline using the data as it exists from the last synchronization of the device application.
If there is NO client side SQL entered, then MobiTask will treat the connector as a LIVE ONLINE call and it will make a call to the server to execute the server side SQL and return the results back to the form. If no connection is available then connector will not be executed and any dependent field data will be empty.
It also worth noting that OnSubmit connectors do not need Client Side SQL since they rely on the server to complete the submission transaction, so only server side SQL would need to be entered. All data being returned or mapped to the form must be in a varchar format so fields must be converted in your select statement to properly map to the form. The same goes for output – data leaving the form is always in varchar format, so you will need to convert it to map to numeric or date fields within the datasource.
Mapping Table:The mapping table is used to define the relationship between the data returned in the SQL query and the fields on the form. It is used primarily for OnLoad or Manual connectors but can also be used during an onSubmit connector to return a value back into the form submission.
The mapping table has three parameters; the table, the column and the XML reference. Typically the table can be omitted as usually defined by the query itself so only the column and the reference need to be mapped. The mapping fields are comma separated and each mapping is separated by a carriage return. In the example below you can see a mapping table for some Consumer information. The first value represents the column names as retrieved by the server side SQL query and the second value is the XML reference on the form. So the data from the column FIRSTNAME is being mapped on the form in the field with the XML reference firstName. XML references are case sensitive, while the column names are not, but the column name entered in the mapping table must match the case of the values entered within the query.
The mapping table can also be used to map returning data from an onSubmit connector in the same way. The output parameter name of your procedure can be entered in the place of the column name and the value mapped back to the form submission in the field specified.
USING REPEAT WITH CONNECTOR
eXFORMA and MobiTask both support the use of a repeat object, which is used to dynamically add rows of data to the form as needed. Repeats can be used with connectors for both read and write. In the example below we are reading an unknown number of goals and there relative properties into the form based on the user that has been selected. Each user can have any variable number of goals. You can statically add fields to the form (hoping you cover the maximum) and then try to map the data manually, or you can use the repeat object and then map the full dataset back to the repeat. In this case we’re reading GOAL_ID, GOAL, CREATEDATE, TARGETDATE, COMPLETEDATE and STATUS back to fields goalId, goal, goalCreateDate, goalTargetDate, goalCompletionDate and goalStatus. The fields are inside of a repeat object with the reference repeat_bigGoal. As you can see in the screenshot, the correct syntax is: COLUMNNAME, referenceForRepeat`referenceForField. As you can see it is possible to now return multiple rows of data to a form.
There are a number of ways to use the connector to insert/update data within a data source. The simplest way is to simply code the insert/update statement right into the connector and execute it on submit. The code example for something like this is below, you can also use values within a repeat, and the system will insert as many records as there are repeated lines. Also note it is only necessary to provide server side SQL for these inserts.
You can also choose to execute a procedure to insert/update records, passing the references directly into the procedures parameters. There are two options with this method as well, you can pass the references as varchar to the parameters as show in the image below, or you can convert them within the procedure call and pass them to the parameters in correct format. You can also pass values back from a procedure to the form. For instance you can pass the customer id, which is unknown for a new customer back to the form when you create the customer. The same could be done for a work order number or an inspection Id. In the example below we’re inserting a new contact or updating an existing one from the form.
Our procedure has two output parameters which are going to be passed back into the fields contactId and action. Contact Id will represent the primary key assigned to the contact in the database (from a sequence) and the action field will tell us whether the form was used to insert or update a record.
create or replace
Finally the third option is to pass the entire XML and let the procedure do the work. This is the best option for forms that undergo regular changes or that work with different tables or have multiple repeat objects.
eXFORMA has a few generic parameters for this purpose; :submit_data, :submit_id and :submit_version, which can be used to pass form data to your database procedure.