Now you need to create your form that integrates with your database SQL queries.
There are two ways of creating frevvo forms:
- dragging and dropping controls from the designer palette
- creating a form from an XML schema. In this case, the controls will be automatically created for you based on the XML schema definitions.
When working with the database connector, you will always used the latter and create the controls from the schema. In the future we intend to relax this requirement.
Generating the XML schema
You can use the database connector to generate the schemas you will need to use in the frevvo form.
To fetch the schema, type a URI in the browser that looks like the example below. This is almost identical to the query we used to test the connector, but it tells the connector to GET the schema instead.
http://<host>:<port>/database/<queryset name>/<query name>/schema?<template name>=<template value>
If your SQL Retrieve statement includes template variables, you’ll need to add the variable names as shown below. The schema itself does not require variables but the connector fetches the schema the same way it fetches data for the '''retrieve''' statement, so the connector expects the template names and values in the URI.
For the example we've been using so far, point your browser to the url: http://localhost:8082/database/myStore/customers/schema?customerId=1234
You should get an XML schema similar to the following:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns="http://www.frevvo.com/database/customers" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.frevvo.com/database/customers">
<xsd:element name="customers">
<xsd:complexType>
<xsd:sequence>
<xsd:element maxOccurs="unbounded" name="row">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="customerId">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="11"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element minOccurs="0" name="firstname">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element minOccurs="0" name="lastname">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Save the schema as an .xsd file in your file system. You can then [[V4_Data_Sources_and_Schemas#Uploading_a_Schema | upload the schema]] to frevvo and generate controls from the schema elements that map to your database tables. If you have multiple queries in your configuration file, you’ll need to generate a schema for each query.
Document URI Properties
After you’ve generated controls from the elements in your database schema, set your form’s Document URI to the database connector query Url. To do:
- Click on the Doc Action button in the toolbar at the top of the form.
- In the dialog box that appears click on 'Manually set document URIs'.
[[image:DocActionDocURItab.png]]
The first screen in the wizard is for the form's default document. This is indicated by the Document Name of "default" displayed in the wizard. The default document maps to all of the controls you added to this form by dragging a dropping from the palette. This is typically not the document you want to configure.
- Click the Next button to advance from the default document to the next schema document
- You should see the Document name change your schema's document name
- Enter the database connector Url to your query in the wizard's URL input. If the database connector is running in the same host and port as the frevvo form server, then you can omit http://<host>:<port>/ from the URL. See this example below.
[[image:DocActionDocURItabSetDoc.png]]
You also must specify which of the four SQL statements to run—you do this by choosing the appropriate values in the Document URI Read Method and Document URI Write Method dropdowns according to the table below. (You may notice in the Forms Designer that the Document URI Read Method drop down box includes a POST option, but it is not shown below because it is not used by the database connector.)
<table> <table cellpadding="3" cellspacing="0" border="1"> <tr> <th>SQL Statement Type</th> <th>Document URI Read Method</th> <th>Document URI Write Method</th></tr> <tr> <td>Create</td> <td>Leave blank</td> <td>POST</td></tr> <tr> <td>Retrieve</td> <td>GET (or leave blank if you are not retrieving data) </td> <td>Leave blank if your form is read-only</td></tr> <tr> <td>Update</td> <td>Leave Blank</td> <td>PUT</td></tr> <tr> <td>Delete</td> <td>Leave Blank</td> <td>DELETE</td></tr> </table>
You’ll see one set of Document URI properties '''for each schema''' in your form. Type the Document URI next to the schema name—make sure '''not''' to type it next to the Default schema. To specify which of the four SQL statements to run, choose the appropriate values in the '''Read Method''' and '''Write Method''' dropdowns, based on the information below.
- Read Method - Choose GET if your form will be initialize with data from your database; leave the dropdown blank if it won’t. (Don’t set the Read Method to POST; this is used for integrating forms with different back ends.)
- Write Method - If your form is read-only, leave this blank. Otherwise, use the chart above chose either PUT or POST. PUT maps to the '''update''' method while POST maps to '''create'''.
Note that you may choose only one Write Method in the Forms Designer.
Still using the customers example, assume the Document URI for the schema customers is:
http://localhost:8082/database/myStore/customers?customerId={customerId}
If you choose the Write method to be ''POST'', the SQL statement that will be executed is:
<create>
<statement>
INSERT into customers (customerId,firstname,lastname)
VALUES ({customerId},'{firstname}','{lastname}')
</statement>
</create>
At run time, the values of {customerId}, {firstname} and {lastname} will be replaced by the values of the respective form controls.
What happens at run Time ?
Let's assume that you defined a Document URI for a schema in the form, a GET Read Method and a PUT Write Method.
When a user loads your form, a GET request for that URI will be sent to the database connector. That will cause the database connector to execute the '''retrieve''' operation for the query specified in the URI, take the result of the SQL execution and transform it to XML. The database connector will than return the resulting XML back to the form which will be initialized with the contents of the XML file.
When the user submits the form, the same URI will be executed but now with the PUT (or POST) method. The database connector will than execute the '''updade''' operation identified by that URI.
Customizing the XML Schema
The schema you retrieve from your database will be relatively generic. As a result the controls you generate from this database schema might need some tweaking to suit the specific needs of your form. You can modify a control’s behavior by making changes to the schema after you’ve retrieved it.
If you have already uploaded the schema to frevvo, you can still make the changes and update the schema. The process of updating the schema is described [[V4_Data_Sources_and_Schemas#Updating_a_Schema | here]];
Below are common customizations:
Adding Dropdown/Radio Options
Suppose you want users to select a specific manager’s name from a dropdown list. Your database has a 50-character limit but naturally does not include specific manager name restrictions, so the schema you retrieve initially might look something like this:
<xsd:element minOccurs="0" name="Manager">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
The control you generate from the initial schema will be formatted as a text control. You can make the control a dropdown by changing the control’s '''Format As''' property but you must add the actual restrictions to the schema itself, as shown below.
<xsd:element minOccurs="0" name="Manager" type="ManagerType"/>
<xsd:simpleType name="ManagerType">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="John Doe"/>
<xsd:enumeration value="Pat Johnson"/>
<xsd:enumeration value="Mary Smith"/>
</xsd:restriction>
</xsd:simpleType>
You also can populate dropdown boxes dynamically from your database by writing a [[V4_Designing_Forms#Triggers_.26_Dynamic_Options | rule.]]
Adding Checkbox Options
You may also need a control to allow multi-select options. Like dropdowns and radios controls described above, the control you generate from the initial schema will be formatted as a text control. You can make the control a checkbox by changing the control’s Format As property to Checkbox and changing the actual schema typeto xsd:list as shown below:
[[Image:text-toCheckbox-Example-1.png]]
<xsd:element minOccurs="0" name="colorChoice">
<xsd:simpleType>
<xsd:list itemType="xsd:string"/>
</xsd:simpleType>
</xsd:element>
Once the Color Choice control is formatted as a checkbox, a [[ V4_Designing_Forms#Labels |labels property]] will display in the properties settings tab for this control. Enter your checkbox options here.
[[Image:text-toCheckbox-Example-2.png]]
Limitations
[[Image:18px-Symbol_OK.svg.png]] Currently it is not possible to list the options directly in the schema.
[[Image:18px-Symbol_OK.svg.png]] Currently it is not possible to have a checkbox option containing a space. So a label of "dark blue" will appear in the database as "dark_blue".
Changing text controls to date Controls
Controls you generate based on SQL Server date columns initially will be text controls, because all SQL Server dates are in datetime format and therefore have a type of xsd:datetime in the schema you retrieve from the database.
In the schema, change the XSD type from xsd:string to xsd:date If in your you want a date control instead of a text control.
Limiting potentially repeating Data
Often the schema from your database will allow any number of elements even though you know your form will never use more than one at a time. Every schema includes the “rows” element as shown in the partial schema below—note that based on the maxOccurs value of “unbounded” in the schema, there can be an unlimited number of customer elements.
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.frevvo.com/database/Customer"
targetNamespace="http://www.frevvo.com/database/Customer">
<xsd:element name="Customers">
<xsd:complexType>
<xsd:sequence>
<xsd:element maxOccurs="unbounded" name="row">
<xsd:complexType>
<xsd:sequence
Since the schema says the element is unbounded, the control you generate initially will be a repeat control. You’ll see the Repeat Row heading in the Forms Designer and if you don’t make any changes, users will see the + sign that comes with repeat controls.
[[Image:Customers_repeat_control.png]]
In this case, change the schema to indicate you only want to work with one customer at a time, as shown below.
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.frevvo.com/database/Customer"
targetNamespace="http://www.frevvo.com/database/Customer">
<xsd:element name="Customer">
<xsd:complexType>
<xsd:sequence>
<xsd:element maxOccurs="1" name="row">
<xsd:complexType>
<xsd:sequence>
Note that now maxOccurs="1" and that will force the + sign to disappear.
Type Restrictions
Sometimes the database field will let you enter any string such as a VarChar field. However if you field is a phone number you may which to have the form restrict the input to only valid phone numbers. See the section on [[V4_Using_Forms#Patterns | patterns]] for more common type restrictions. In this example you still want the form input to be a text box. So we do not change the Format As.
Edit your xsd:
<xsd:element minOccurs="0" name="Home Phone">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="12"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
Add the following simpleType restriction to your xsd.
<xsd:simpleType name="phoneType">
<xsd:restriction base="xsd:string">
<xsd:pattern value="\d{2}-\d{4}-\d{4}"/>
<xsd:pattern value="\d{4}-\d{3}-\d{3}"/>
</xsd:restriction>
</xsd:simpleType>
Finally change the existing type for the Home Phone element to:
<xsd:element minOccurs="0" name="Home Phone" type="phoneType"/>
Now the frevvo form will only accept valid phone numbers. If you enter an invalid phone the form will flag the field as invalid.
Dynamic Dropdown Options
Individual controls also may trigger SQL statements, if you’ve customized your form with rules. See [[Rules_Examples#Database_Connector_REST_Services | Database Connector Rest Service]] for an example of using the ResultSet returned from the Database Connector to dynamically populate the options in a dropdown control.
Show/Hide Required Fields
There are times when you have several fields that you want hidden and then only want to make visible depending on the value entered into another field. Thus only when the fields are visible do you want them to be required. Currently hidden required fields are still required and will causes the submit button to remain disabled. Automatically making hidden fields not-required may be added in a future release of Live Forms.
See the documentation for Data Sources and Schemas a solution for implementing show/hide with [[V4_Data_Sources_and_Schemas#Required_Controls | required controls]].
[[Image:18px-Attention_niels_epting.svg.png]] '''NOTE:''' Avoid using message control inside of a section that contains other controls that you may want to set to required/not required (either via the Forms Designer or using [[V4_Business_Rules | business rules]]). Since a message control always contains a value, it can cause a section, or other controls in a section, to become required. If you must include a message control, place it outside the section. Another alternative is to write rules for the individual controls within a section to set them to visibile/invisibile or required/not required.