The submissions repository can store your form data in three ways -- as data values; as an [[#XML_Document | xml document]]; as a [[#Viewing_Attachments_.26_PDF_Image | pdf form image]]. By default for efficiency, data is only stored as xml. If you plan to [[#Viewing_Submissions_in_Excel | export your form data to an Excel spreadsheet]] or plan to view your form data in the [[#Submissions_Detail | submission detail view]] you must configure your form to save the data field values. See the documentation for [[V4_Designing_Forms#Setup_Key.2FSaved_Fields | setting up key/saved fields]].
By default the submission table also displays columns for status, submission date, submitter name and form version. The submitter name is the frevvo Live Forms user ID of the logged in tenant user submitting the form or blank if the form was submitted by an anonymous user. The version tells you how many times you have edited the form. This is helpful if you have updated your form and need to track how many users submitted the newest version. (When you create a form the version number starts at 1 but frevvo Live Forms increments the version each time you revise your form.)
- The Data Tab shows the user’s view of the data. This means for money controls you’ll see dollar signs, decimals and commas, for example. The Label column corresponds to the labels on your form, and the Value column shows you what the user entered in each control. This tab is view-only; you cannot edit any data shown here. This tab display only form fields setup as [[V4_Designing_Forms#Setup_Key.2FSaved_Fields | saved fields]] in the form designer. If you do not see a specific form field then go back and edit this form to added the field to the [[V4_Designing_Forms#Setup_Key.2FSaved_Fields | saved fields]].
- The Signature Tab shows you all the users that signed the form using frevvoLive Forms's digital signature feature.
- The Documents Tab shows you the exact XML document that was generated when the form was submitted.
Click the links on the Data Tab to view the PDF or attachments.
Viewing XML Documents
frevvo Live Forms always stores your form data as XML documents. To view the XML data, click the Documents tab in the Submission Details panel.
This will reload the current version of the form with the data from this old submission. You can then edit the form data in the frevvo Live Forms form. When you click submit on the form the data in frevvoLive Forms's submission repository with be updated with the new data. If the form properties are set to save a Pdf then the Pdf will also be replaced.
Currently only the form designer has access to edit the form in frevvoLive Forms's submission UI page. Using the [[V4_frevvo_API | frevvo API]] it is possible to build your own application that enables editable access to non-designers. A future release of frevvo Live Forms will enable out-of-the-box editable access to non-designer via access control granted by the designer to other users.
Live Forms Online users have this feature enabled by default. Live Forms In-house users can disable/enable this feature using the frevvo Live Forms configuration property in frevvo/WEB-INF/web.xml:
<context-param> <param-name>frevvo.submission.edit.link</param-name> <param-value>true</param-value> <description>Show a link to edit the submission.</description> </context-param>
Sorting Submission Results
- Top-down: drag and drop controls from the palette and frevvo 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 frevvo Live Forms and frevvo 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.
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, frevvo 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> <Personal> <FN>John</FN> <LN>Doe</LN> <EmailAddress>firstname.lastname@example.org</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> <Incident> <Date>2010-08-08</Date> <Description>Speeding ticket</Description> </Incident> </ns</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.
<ns:form xmlns:ns="http://www.frevvo.com/schemas/_5PqoILXnEeCqU4w2X0ufGw" name="FNLN2"> <Info> <Personal> <FN>John</FN> ... </Personal> ... </Info> " 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 frevvo 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.
An upcoming version of frevvo Live Forms (currently estimated for late Q1 or Q2 2012) will permit versioning of forms/flows so existing submissions can be edited using older versions of the form/flow.
Submissions can be accessed directly via database SQL queries. By default frevvoLive Forms's submissions are stored in the built-in HSQLDB. You can easily [[V4_Installation_Instructions#Changing_from_HSQLDB_to_another_database | configure frevvo 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 frevvo Live Forms submissions repository. Several examples are described below. [http://www.frevvo.com/frevvo/web/static/contactUs Contact frevvo] for further assistance.
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 frevvo Live Forms submissions repository. In this example your frevvo Live Forms designer account name is 'JoeSmith', your application name is 'Economic Impact' and your pledge form is named 'pledge2009'.