...

...

With Live Forms, you can create two kinds of forms:

• Top-down: drag and drop controls from the palette and Live Forms will generate an XML document based on the structure of your form.
• Bottom-up: define your data upfront using an XML schema, upload it to Live Forms and Live Forms will generate controls. You can then drag and drop to move the controls, change their appearance etc. However, the generated XML document will always conform to the uploaded XML schema irrespective of where the controls are moved.

You can mix the two approaches, i.e. you can create a form that has, for example, controls generated from two different XML schemas and some from-palette controls. The result will be three documents.

Top-down forms (from palette)

This is the most common type of form where you drag and drop controls from the palette. The form is shown below in the Designer. It contains a Section named Personal. Inside this Section there are two Panels. Each panel has two controls. Below this Section is a Repeating Section called Incident which itself contains two controls.

The form is shown filled in above. We've filled in all the fields and added a second repeating incident. When the form is submitted, Live Forms generates an XML document that looks like the one below:

<ns:form xmlns:ns="http://www.frevvo.com/schemas/_5PqoILXnEeCqU4w2X0ufGw" name="FNLN2"> <Personal> <FN>John</FN> <LN>Doe</LN> <EmailAddress>johndoe@yahoo.com</EmailAddress> <Phone>202.202.2020</Phone> </Personal> <Incident> <Date>2010-08-01</Date> <Description>Parking ticket</Description> </Incident> <Incident> <Date>2010-08-08</Date> <Description>Speeding ticket</Description> </Incident> </ns:form>

Note that the structure of the generated XML document matches the structure of the form. You can download an XML schema that describes the document that the form will generate by clicking on the Schema button for the form. See the [[V4_Forms_Home_Page|Forms Home Page]] documentation for details.

If the structure of the form is changed, the generated XML document (and associated schema will change). For example, if you add a new Section - say it's called Info - and nest both the existing Personal Section and the Incident Repeat inside this new Section, the XML document will change to reflect this change as shown below (snipped for brevity):

<ns:form xmlns:ns="http://www.frevvo.com/schemas/_5PqoILXnEeCqU4w2X0ufGw" name="FNLN2"> <Info> <Personal> <FN>John</FN> ... </Personal> ... </Info> </ns:form>

Why is this important? It's only important if you are using the form to edit existing submissions, i.e. to edit the XML documents they have generated. To do so, the form must first be initialized using the XML document. If the structure of the XML document does not match the structure of the form (or more accurately, if the XML schemas are incompatible), the initialization will fail and you cannot edit the submission using the form.

This can happen if you are using the Live Forms API in your application to edit submissions or if you are using the Create/Update Confluence pages functionality. In these cases, you must be very careful that you maintain the structure of the form. You can add new fields or delete existing ones. But you cannot make changes that change the schema of the generated XML.

If you don't want to worry about making sure that form versions remain compatible, you can always use the from-schema bottom-up path to generate forms from XML schemas. In this case, changes to the form will not change the document since it must conform to the schema. The downside is that you must first define the XML schema and then use it in the form, which requires some technical knowledge of XML and XML schemas.

Submissions Database

Submissions can be accessed directly via database SQL queries. By default Live Forms's submissions are stored in the built-in HSQLDB. You can easily [[V4_Installation_Instructions#Changing_from_HSQLDB_to_another_database | configure Live Forms to use a different database]] such as mySql, MS SQL Server, etc.. Doing so can make direct access to the submission database simpler.

There are many possibilities when using direct SQL queries against the Live Forms submissions repository. Several examples are described below. [http://www.frevvo.com/frevvo/web/static/contactUs Contact frevvo] for further assistance.

Count

You may have a pledge or survey form and you would like to track the number of people that have submitted your form. Here is the SQL query to retrieve the count from the Live Forms submissions repository. In this example your Live Forms designer account name is 'JoeSmith', your application name is 'Economic Impact' and your pledge form is named 'pledge2009'.

To retrieve the count of ALL submission for a specific form:

select count(*) from formsubmission fs, formsubmissiontype fst where formsubmissiontype_formtype_id=fst.id 
and fst.ownerid='JoeSmith' and fst.deleted is null and fs.state='SUBMITTED' and 
fst.applicationname='Economic Impact' and fst.name='pledge2009'

To retrieve the count of ALL submission for forms in a specific application:

select count(*) from formsubmission fs, formsubmissiontype fst where formsubmissiontype_formtype_id=fst.id 
and fst.ownerid='JoeSmith' and fst.deleted is null and fs.state='SUBMITTED' 
and fst.applicationname='Economic Impact'; 

To retrieve the count of ALL submission for a given form designer:

select count(*) from formsubmission fs, formsubmissiontype fst where formsubmissiontype_formtype_id=fst.id 
and fst.ownerid='JoeSmith' and fst.deleted is null and fs.state='SUBMITTED';