Submission Data Export Guide
- Ben Worley
- Logan Korn
Overview
The nFORM system supports the ability form internal staff to download submission data using varying formats, including Excel, JSON, and XML. This feature allows for high-level submission data to be exported based on the columns selected in the Submission Dashboard, as well as allows for detailed submission data to be exported.
Additionally, online payment transactions can be printed or exported into an excel format.
This document provides the details of how to access and make use of these export features.
Included in this article
Submission Data Exports
The following section provides instructions on how to access the submission data export features. Users will only be able to download submissions that belong to the organization they are associated with (i.e., have Submission Processor/Viewer role for).
Export Dashboard Data
The Export Dashboard Data feature allows a Submission Processor to download the data that is presented in the dashboard (high-level submission data). The end result will be an Excel file containing each submission in rows, with the columns matching the columns that were selected in the dashboard.
Prerequisites: User account must have one of the following roles: Administrator, Submission Viewer, Submission Processor
Navigate to the Dashboard by clicking on the Dashboard menu item.
Add/Select columns that you would like to be included in the export.
Use the various filters to obtain the desired result list.
Click “Advanced Search”.
In Advanced Search modal, click “Add Filter”.
Scroll down to “Form” and select the plus icon, this will add the Form select list to the search screen.
Select the desired form. If you have many forms in your list, you can start typing the form name to find the desired form.
Once selected, click “Search”.
Once the submissions are filtered, you can click on the download icon next to the results found count.
This will present a modal window providing various options. Click “Excel” under the Export Dashboard Data header.
This will immediately download the Excel file to your computer.
Example of Dashboard Export in Excel
Export Submission Data
The Export Submission Data feature allows Submission Processors to download submissions with all submission data on their dashboard. This includes the columns presented in the dashboard, data provided by the applicant in the form itself, and any internal data controls. There are various Export formats that a user can choose from, included Excel, JSON and XML. The most useful/flexible format for data integration purposes is JSON.
Prerequisites: User account must have one of the following roles: Administrator, Submission Viewer, Submission Processor
Navigate to the Dashboard and filter your list to the desired results. See steps 1 – 3 in the Export Dashboard data for details.
Once the submissions are filtered, you can click on the download icon next to the results found count.
This will present a modal window providing various options. Under the Export Submission Data header, you can click JSON, XML, or Excel.
If the submission result count is higher than the threshold set in the configuration, then a new modal will appear indicating that Extract will be worked on and will be emailed to you once completed. The default is 150 records.
Click “Yes/Proceed”. An email with a link will be provided. Clicking the link will download the export file to your machine. The link to the file can only be accessed by the user that requested the export. Once downloaded it can be shared with others.
If the result count is lower than the threshold set in the configuration, then the file will be downloaded immediately to your computer.
Excel Formatting/Pivoting
The Excel file contains 3 tabs:
Submissions: This contains a distinct list of submission high-level data. The data displayed will depending on the columns that were selected in the Dashboard prior to downloading.
Submission Data: This contains all of the data supplied by the applicant while filling out the form, for all submissions downloaded.
Internal Data: This contains a list of all internal data control values for all submissions downloaded.
Due to the flexibility in how forms can be designed, and the complex nature of the data structures, such as repeating sections and tables (advanced tables and data grid), the submission data tab is structured in an unpivoted fashion, which lists all controls and values in rows. For individuals that would rather view the data with control labels (questions) across the top in columns, steps can be taken to pivot the Submission Data.
Pivot – Simple Form
Steps for pivoting an export Excel file that contains submissions for forms that do not contain repeating sections or tables.
Open the exported SubmissionData.xlsx file. Select the “Submission Data” tab. | |
Click the “Data” menu item at the top of excel sheet, then click the “From Table/Range” option. | |
A small Create Table modal will appear. The entire sheet will automatically be selected. “My table has headers” will also be checked by default. Click “OK”. A PowerQuery Editor window will appear. You will create two steps to pivot the data. 1) Remove Unneeded Columns, 2) Select Pivot column details. | |
Remove Unneeded Columns: Highlight all of the columns EXCEPT: Form Name, Submission ID, Value and Control Pivot (note: the Form Name is optional). If you have submissions associated to different forms in your download, it makes to include the form name, otherwise you could eliminate it). Then click “Remove Columns” menu. | |
Select Pivot Column: Highlight “Control Pivot” column (likely already selected), select “Transfer” then “Pivot Column” in the menu.
| |
A Pivot Column modal will appear. For Values Column, select “Value”. Under Advanced Options, select “Don’t Aggregate”. Click OK. This will pivot the data, putting the questions across the top in columns, listing the distinct submissions in rows. How nice! | |
To save the results into the original download file, click “Home” menu and then “Close & Load”. |
Pivot – Complex Forms (With Repeaters and/or Tables)
For pivoting an export Excel file that contains repeating sections and/or advanced table controls, the steps are very similar to the Simple Form approach, but with two minor differences. In this process, we will not delete the Repeater Pivot column, since we will need to include the repeating section data. We will also eliminate table data, as this data cannot be pivoted.
Open the exported SubmissionData.xlsx file. Select the “Submission Data” tab. | |
Click the “Data” menu item at the top of excel sheet, then click the “From Table/Range” option. | |
A small modal Create Table modal will appear. The entire sheet will automatically be selected. My table headers will also be checked by default. Click “OK”. A PowerQuery Editor window will appear. You will create three steps to pivot the data: 1) Remove Unneeded Columns, 2) Remove Null Values from Control Pivot Column, 3) Select Pivot column details. | |
Remove Unneeded Columns: Highlight all of the columns EXCEPT: Form Name, Submission ID, Value “Repeater Pivot”, and Control Pivot (note: the Form Name is optional. If you have submissions associated to different forms in your download, it makes you include the form name (otherwise you could eliminate it). Then click “Remove Columns” menu. | |
Remove Null Values from Control Pivot column: Go to the “Control Pivot” column and click on the down arror icon. Uncheck the null option and click “OK”. This will remove all table data. [Note: This step is only necessary if there are tables in your submission results set] | |
Select Pivot Column: Highlight Control Pivot column (likely already selected), select “Transfer” then “Pivot Column” in the menu.
| |
A Pivot Column modal will appear. For Values Column, select “Value”. Under Advanced Options, select “Don’t Aggregate”. Click “OK”. This will pivot the data, putting the questions across the top in columns, listing the distinct submissions in rows. How nice! | |
To save the results into the original download file, click the “Home” menu and then “Close & Load”. |
Repeating Section will be listed in the pivoted display as separate rows. So, the first submission row will contain all data not in a repeating section. Then rows 2 – N will contain each repeating section’s data set. In the example below, the first submission (HPR-APV0QCBS7) has one outfall, while the second submission (HPR-AQ79-QZD8P) has two outfalls.
Online Payment Transaction Export
Users that have the Payment Report Viewer role can print or export the online payment transactions for desired forms. Submissions are automatically filtered by the user’s organization affiliation.
Prerequisites: User account must have one of the following roles: Administrator, Payment Report Viewer
Navigate to the Online Payment Transaction Report by clicking on the “Reports” menu item.
Use the various filters to obtain the payment list desired.
The options include organization, payment start and end dates, submission number and payer name.
Once the payment transactions are filtered, you can click on the “Download/Export” icon in the top right of the screen.
This will present a modal window providing the option to download the report or to print the results.
Clicking “Print Results” will open a print friendly version of the results in a print modal. This can be printed or saved as a PDF as desired.
Clicking “Download Report” will download an Excel file to your computer.