Viewing Form Submissions

From the Submissions page you can see who has been using your form and what data they submitted. You can see exactly when the form was submitted, the user’s IP address, and all data the user entered. You can view each submission individually by double clicking a submission in the table. 

To access submissions for a particular form, navigate to the [[V4_Forms_Home_Page | Forms Home Page]] and click the submission button for that form.

The various items on the Submissions page are explained below.

==== Submission Data ==== <div style="background-color:#ffffcc"> [[Image:18px-Attention_niels_epting.svg.png]] 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]].</div>

==== Submission From Date and To Date ==== The two date controls specify the time period for the submissions you see. The dates default to the current date and one month earlier, so you see one month of submission results when the Submissions page first displays.  Adjust the dates if you want a shorter or longer time period. 

==== Submissions Graph ====   The graph shows you how many submissions you’ve received over a given time period. The green bars provide a breakdown of exactly when you received the submissions. Hover over any green bar to display a tooltip submission count.

[[Image:submissionGraph.png]]       Other important details about the graph: * The graph reflects the time period you see in the two date controls. * The time increments along the bottom are calculated automatically to make sense for the time period you view--seven days for the weekly view, for example. * The green bars (period) reflect the number of submissions in a given period. * The <<day>>, <<week>>, <<month>> or <<year>> links above the graph are shortcuts to quickly change the date range. The graph will show you the most recent period—for example, if you click <<week>>, you’ll see a week’s worth of submissions, starting from a week ago up to the current date. The dates you see in the To Date and From Date controls also will be adjusted automatically. * The unlabeled << link to the far left moves the date range back. The exact behavior depends on the dates in the date controls before you click the link. If the date range is May 11th to May 15th, for example, clicking the << link will move the range back to May 7th to May 11th. Similarly, clicking the >> link slides the date range forward.

==== Submissions Table ====

The Submissions table at the bottom of the page lets you see the key field values for each submission in the selected date range. The table displays the most recent 20 submissions in the selected range. Use the scroll controls at the bottom of the table to see the next 20 submissions or to jump to the first of last batch of 20 in the selected date range.


By default the submission table also displays columns for status, submission date, submitter name and form version. The submitter name is the frevvo 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 increments the version each time you revise your form.)

A yellow icon in the Err column indicates the data was captured but there was an issue with the request.  For example, if you set the form action property to go to a URL after the user clicks Submit but the URL is invalid, you would see a yellow icon. Or if the form action was set to send an email but the email address was missing, this will also cause a yellow icon to appear on the submission. 

In addition to the default columns, the table contains a column for each [[V4_Designing_Forms#Setup_Key.2FSaved_Fields | key field]] that you setup for this form in the designer. If you are viewing a date range that contains multiple form versions and you changed the key fields from one version to the next, you will see a column for both the new and old key fields.

You can show or hide additional default column data by hovering over any column header and clicking the dropdown arrow that appears.


==== Submissions Detail ====   To view a particular submission results document, double click the submission row of interest in the table. You will see a Data, Signature and Document Tab. Click X to exit the 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 frevvo's digital signature feature. * The Documents Tab shows you the exact XML document that was generated when the form was submitted.


===== Viewing Attachments & PDF Image =====

Scroll to the bottom of the Data Tab to see all attachments uploaded to the form submission. If you selected [[V4_Designing_Forms#Save_PDF | Save PDF]] for this form in the form designer, you will also see an attachment named <form name>.PDF. This is an image of your form exactly how the user saw it when the filled it in and submitted it to you.

Click the links on the Data Tab to view the PDF or attachments.

===== Viewing XML Documents ===== frevvo always stores your form data as XML documents. To view the XML data, click the '''Documents''' tab in the Submission Details panel.


==== Submission Errors ====

If your submission had an error, you'll see an icon [[Image:error.png]] in the ''Err'' column in the submissions list. Hover over the icon to get information about the error.


To get more information, double-click on the submission to view its details, and again hover over the [[Image:error.png]] icon.


==== Editing Submissions ==== (v4.1.4) Submissions can be edited easily by clicking the form name rendered as a clickable URL on the submissions. In this image below click on "Order" to edit the submission.


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 form. When you click submit on the form the data in frevvo'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.

Note: If the current version of the form is different then when the data you're editing was originally submitted then some of the data may not be visible. For example if your form contained a field for "detailed description", and the current version of your form no longer has that field as the designer removed it, then the detailed description data will exist but will not be visible/editable.

Currently only the form designer has access to edit the form in frevvo'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 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 configuration property in frevvo/WEB-INF/web.xml:

<pre> <context-param>  <param-name></param-name>  <param-value>true</param-value>  <description>Show a link to edit the submission.</description> </context-param> </pre>

==== Sorting Submission Results ==== When you mouse over a column in the Submission area you’ll see the [[Image:submission_tbl_dropdown.png]] icon. Click it and choose from several sorting and grouping options:    * Ascending and Descending -- sorts the results in ascending or descending order, based on the column you selected.   * Columns -- this will display checkboxes with each column name; uncheck any columns you don’t want to see. * Show in Groups: Uncheck this box if you are looking a grouped view and want to return to the default (non-grouped) view.    

At any time as a shortcut you also can click the form heading to sort the results in ascending or descending order.


==== Viewing Submissions in Excel ==== <div style="font-weight:bold;background-color:#ffce7b">[[Image:18px-Attention_niels_epting.svg.png]] In order to export data to Excel you must setup your form fields as saved fields as described [[#Submission_Data | here]].</div>

Click the Excel [[Image:excel.gif]] icon at the top of the table to view the submission results in Excel. The spreadsheet reflects the submissions that are displayed in the table (filtered by date).


== XML Documents == <div style="font-weight:bold;background-color:#ffce7b">This section is only relevant if you will be working with XML data either directly or indirectly (e.g. [[V4_Create_Update_ConfluencePages|Updating Confluence pages]]).</div> <div style="font-weight:bold;background-color:#ffce7b">This section is primarily relevant to from-palette controls that are dragged from the palette and dropped into the form. If you are working primarily with XML schemas, please see [[V4_Data_Sources_and_Schemas|Schemas]].</div> With Live Forms, you can create two kinds of forms: * Top-down: drag and drop controls from the palette and frevvo 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 and frevvo 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. [[Image:From_Palette_Form_Edit.png|frame|left|Form with Section and Repeat]] [[Image:From_Palette_Form_Use.png|frame|left|Use Mode form filled with data]] <br style="clear: both" /> 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 generates an XML document that looks like the one below: <pre> <ns:form xmlns:ns="" name="FNLN2">   <Personal>     <FN>John</FN>     <LN>Doe</LN>     <EmailAddress></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> </pre>

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): <pre> <ns:form xmlns:ns="" name="FNLN2">   <Info>     <Personal>       <FN>John</FN>        ...     </Personal>     ...   </Info> </ns:form> </pre>

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

<div style="font-weight:bold;background-color:#63ffc7">[[Image:Lightbulb.png]] An upcoming version of frevvo (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.</div>

== Submissions Database ==

Submissions can be accessed directly via database SQL queries. By default frevvo's submissions are stored in the built-in HSQLDB. You can easily [[V4_Installation_Instructions#Changing_from_HSQLDB_to_another_database | configure frevvo 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 submissions repository. Several examples are described below. [ 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 frevvo submissions repository. In this example your frevvo 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:

<pre> select count(*) from formsubmission fs, formsubmissiontype fst where and fst.ownerid='JoeSmith' and fst.deleted is null and fs.state='SUBMITTED' and fst.applicationname='Economic Impact' and'pledge2009'; </pre>

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

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

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

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

NOTE: Currently, if the user or app no longer exist (was deleted) the submissions will still be counted.