For administrators and project managers. How to export data from memos/forms/actions to CSV and PDF files.
This document is for administrators and project managers. It describes how to:
- Export data from memo, form, and/or action fields to a CSV file.
- Bulk-download PDFs of forms & memos.
- Export a register.
NOTE: The user interface has changed so navigation to some of the functionality has changed, especially to Custom Exports (formerly known as Reporting Tables).
Exports are used to extract data contained in memo, form, or action fields into a CSV file. This is then usually imported into Excel for further analysis. However, CSV is a widely supported format that can be imported into many other applications as well (e.g. report generation systems).
Users who can view forms can also export the forms register (similarly for memos and actions registers), however specific permissions are required for doing the other kinds of exports.
There are two options in the exports section of the menu:
This is used to do data extraction.
The menu option only appears for users who have the “Export” user privilege.
Note: previously users needed permission to “Access all projects” in order to be able to do exports, so that they could export records from any/all projects. That is no-longer necessary. Now, if a user tries to export records from all projects, then they will get records from just the projects that have access to.
This is an easy, powerful and recommended way to customise your data exports.
All users with “Export” user privilege can export using Custom Exports.
However, if they need to create or edit Custom Exports (formerly called Reporting Tables), then they’ll also need permission to “Manage templates and organisation lists”.
Template exports are the simplest type of export. No setup is required, you just specify the template you are interested in, and you can extract all the data for the memos, forms or actions created using that template.
Custom exports are potentially more useful, but they need more work in order to define the fields that you want to extract. A Custom Export would typically be used for one or more of the following reasons:
- To filter out unwanted fields (so that you are only exporting data that is relevant for a particular type of analysis).
- To combine data from different templates. For example, you may have timesheet information on more than one template. In this case Custom Exports can be used to combine data from multiple templates into a single timesheet export.
- To restructure how the data is presented. For example, a checklist template would typically get exported in columns as follows:
Question1, Answer1, Question2, Answer2, Question3, Answer 3, etc…
However using Custom Exports, you could export the data using one row per question like this:
Question 1, Answer 1
Question 2, Answer 2
Question 3, Answer 3
Here is an example of a simple CVI (Confirmation of Verbal Instruction) memo template with a small number of fields and no tables:
Let’s say that we want to export all data for these CVI memos raised in the first half on 2014 for our Site Expansion - Zone D project. Here’s what we would do:
- Open the Exports register
- Click on the Export button and select Using Template
- Enter the details in the Export using Template page.
- The fields in this page are:
- Template – the Template of the memos, forms, or actions that we want to export.
- Project – The project we are interested in (or All Projects).
- Raised from – The earliest Date Raised date that we want. If blank, it will start from the earliest one found.
- To – The latest Date Raised date that we want. If blank, it will include everything after Raised from.
- Repeat non-table fields for each table row – There are no tables in the CVI template, so it won’t matter whether this is selected or not. I’ll explain this further in the next section.
- Once all fields are filled in, click Submit. If successful, we see the following:
- This tells us that the request was successfully submitted. We now need to wait for the export to be processed. Once it has finished, we should receive a notification email that will look something like this:
- You can also periodically refresh the export request Details page or the Export Register to see if the CSV has been generated. If it has, these pages will have links to download the CSV file. Here is what the Details page would look like:
- And here is what the Exports page would look like:
- Clicking on the link in the email, in the Details page, or in the Export Register should all enable you to download the generated CSV file. Note that if you click on the link on the email, you will need to log into the browser if you are not already logged in.
- If you then open the downloaded CSV file in Excel, it looks like the following:
- In this case (a memo template with no tables), there is one line for each memo. There are standard metadata fields at the beginning, which are common to all memos, forms, and actions (Id through Status - see the Reference section for more details). After these standard fields are the fields that have been defined for the Template using the Template Editor (Sequence Number through Signature in the case of the CVI example).
The CVI example is a simple one. What happens when we have a template with tables? Here we’ll look at an export of Toolbox Meeting forms. Note that the template has two tables: one for Attendees and one for Minutes.
The process of creating the export is the same. However, there are differences with the output. The two cases are discussed below.
Here is the exported data:
Note that there are now gaps between each form. This is because table data is causing the export to created additional rows for each form. Table fields always appear at the end, so we need to scroll right to see the table data as follows – the highlighted areas contain the data for the first form.
All non-table fields appear first. Then the rows of the first table appear followed by the rows of the second table. If there were additional tables, they would follow the same pattern.
If we tick Repeat non-table fields for each table row then the export gives us the following (scrolled to the right).
The difference here is that the non-table fields appear on every row.
Why the difference? The un-ticked version is likely easier for a human to read. The ticked version may be easier to analyse or import into other systems (e.g. you would probably want to repeat non-table rows if you were doing a pivot table analysis in Excel).
Template exports have the following limitations:
- They are verbose: you get all metadata and template fields whether you want them or not.
- The order of fields is fixed.
- You can only export data from one type of memo, form, or action at a time.
In order to let users customise their exports, we have introduced Custom Exports. These allow you to exactly specify what fields you want to export, and where the data for each field comes from (potentially from different types of memos, forms, and/or actions).
In this section, we’ll create a Custom Export that will export the minutes from each Toolbox Meeting form.
- Click on Custom Exports in the navigator or menu.
- That opens the Custom Exports Register
- Click the green Create button.
- That is the first stage in creating a Custom Export. Here we define the columns that we want in the export, and the template(s) that contain the fields that are the source of the data we want to export. Later on, we will actually map the desired template fields to the columns we define here. For this example, we enter the following:
- Note that we can name the columns whatever we like – they do not have to be the same as the field name in the template. Also, the Sources are Templates. In this case, we are only using one. When finished, we click Save. These take us to an Edit Custom Export Mappings page.
- The Columns defined in the previous page are along the top, and the Sources are along the left. In each box in this table, we map the field from the Source to the Column that we want to export. (The fields are maintained by the system based on the contents of each template). Note that this page can be very wide, and you usually need to scroll right to get to all of the Columns. In this example, we enter the mappings as follows:
- And here is the rest of the content when we scroll to the right:
- We then hit Save.
- The new Custom Export is now ready for use. To use it, go to Exports, click Export, and select Custom Data.
- This is the same as the Template Export except you specify Custom Data rather than Template Data.
- When we click Submit and wait for the export to finish, we get a CSV file that looks like the following:
- You can see that the export behaves much like the Template export, except that it is using our customisations.
Note that by specifying multiple Sources, it is possible combine data from different types of memos, forms, or actions. This can be used to do things like combine timesheets from different sources into one export.
Also note that you can specify the same Source multiple times. For example, the following Custom Export takes the first three questions and answers of a checklist, and outputs them in tabular form.
You can download PDFs of forms & memos in bulk (instead of individually one at a time).
Using the Web App, go into the Forms or Memos register and click Export => PDFs.
To narrow-down the list, first apply a filter, for example to set a range for Date Raised.
That submits an export request to the server. The PDFs are created and put into a ZIP file. When that is ready for download, you will receive an email like:
Your APE Mobile Form PDFs Export is ready to download. You can get it at:
You can download a register to a CSV file, for forms, memos and actions.
Using the Web App, go into the register and click Export => Register.
You often want to resize columns after you’ve opened a CSV file in Excel. An easy way to do this for one column is to double-click the right border of the column.
You can also resize multiple columns by selecting the columns to resize, and then double-clicking the right border of one of the columns in the header. Preferably, first select the header row and make it bold.
Where possible, only reference one type of template table within a Custom Export. In most cases this will make it easier to analyse the data. For example, if you are pulling in timesheet data from one set of template tables and order data from another, then consider whether it might be easier to work with two separate exports rather than combining them into one.
These are standard memo/form/action fields that are provided in addition to all of the fields defined in the template.
|Metadata Field Name||Contents|
|Template Id||A unique identifier for the published template associated with the memo/form/action.|
|Template Type||The type of template (RFI, Issue, General Memo, Form, or Action)|
|Internal Template Version||The internal version of the published template (a new version is created every time the draft template is published).|
|Creator User Id||The unique Id of the user who created the memo, form, or action.|
|Creator Name||The name of the user who created the memo, form, or action.|
|Created||The date and time that the memo/form/action was created on the server in the Coordinated Universal Time (UTC) zone (effectively the same as GMT).|
|Updated||The UTC date and time that the memo/form/action was last updated on the server.|
|Created On Client||The UTC date and time that the memo/form/action was created on the client device (e.g. iPad).|
|Updated On Client||The UTC date and time that the memo/form/action was last updated on the client device.|
|Status||The status of the memo, form, or action.|