Bulk Importing Records
Tobi McLennan avatar
Written by Tobi McLennan
Updated over a week ago

NOTE: This functionality is currently only available for use on configurable modules. There are no plans to introduce this functionality to classic modules.

Description

The record importer is designed to be used as both

  1. A data migration importer, and

  2. A bulk record uploader.

The ‘Importer’ allows a user with technical admin access to download a CSV template for a record, and using the provided instructions populate that CSV and upload it to Donesafe, either creating new records or replacing fields within existing records.

Imported records will not trigger automations until new automation conditions have been met. When a record is imported, any automations that would have run are treated as if they already have.

What can be imported?

Records of any type can be imported to configurable modules. Donesafe’s record level data exists in two primary tiers, Main Records and Subforms.

  • A main record is the parent record and contains all of the fields from the main form (the first form you fill in when creating a new record) as well as other values such as the workflow state, creation times and creators of a record.

  • Subforms are the forms that can be accessed via the various tabs within a record. In order to import a subform, you will need the external ID of the main record in order to upload subforms.

Accessing the Importer Tab

Go to Admin Settings > Modules > then select the desired module. Once there, click on the ‘Importer’ tab.

The Importer Tab

Within the Importer Tab, you’ll see a set of instructions, divided into three steps.

These three steps will guide you through the process of uploading new records for that specific module.

  • Step 1: “Download the CSV template files”.
    Download a zipped file containing upload templates for the main record and all of its subforms.

  • Step 2: “Read the CSV template manuals for explanations of all the fields”
    This step, contains a link the instruction manuals for the Main Form and all of the Subforms present in the module. It is important to review all of the documentation for each form you wish to import.

  • Step 3: “Select the type of records you will be importing, upload the file and click on the import button.”
    Each set of records will need to be imported individually, so for example, if you’re uploading a set of main record records, you’ll need to select ‘Main Form’ in the list, upload the populated CSV for the main form, then click ‘Import’.

Finally below the import button, a history table of all historical imports, whether or not they failed.

Importing New Records Process

For the following walkthrough, we’re going to use the example of uploading a set of Plant & Equipment records as well as some maintenance records attached to each plant item.

Keep in mind that whether you’re performing a data migration or simply uploading a simple list, that the process is relatively the same, though it will take longer depending on the complexity.

Step 1: Downloading the CSV template/s.

From the importer tab, click on the link in the first link of the instructions. You can see it underlined in the screenshot below.

Clicking this link will download a .zip file containing templates for all uploadable elements. In the above example, you can see that it’s a fairly simple module, containing just the Main Form and one Subform. Therefore, the zip file will contain just two CSVs. Depending on your system, these will either unzip automatically or require an zipping tool to unzip.

If you have more than one subform available on this module, an additional file will be present for each possible subform.

Opening up one of these files will reveal a table with a column for each of the primary record detail as well as a column for each field.

Do not start filling in this detail until you have proceeded to step two and read the instructions.

Step 2: Read the relevant CSV Template Manuals and populate your CSVs.

a) Accessing instruction manuals.

The most important part of this process is to follow the instructions in the CSV import manuals. Each module is different and these manuals will auto populate with the available import fields for each one.

To access the CSV Template Manual for the relevant form you want to import, click on the link below step 2 (pictured below). Each Main Form/Subform has their own set of instructions.

Using the main form of our example, Plant & Equipment register, you’ll see the instructions cut into two sections; the system columns (“Linking Columns”) and the field columns.

Ensure that all follow the instructions for populating each question as the required formats will change between items such as ‘Date’ fields vs ‘Text’ fields.

b) How to populate CSVs.

When populating the CSVs downloaded in Step 1, as stated each field may require a different format so ensure that all instructions are followed. Some are much clearer than others though, so let’s take a moment to talk through some of the more complicated items.

Firstly though, here are some pointers:

  1. Always ensure that your Main Form records are present before uploading your subforms, otherwise your subforms won’t be able to link to a main form.

  2. ‘Main Form’ records only need an ‘external_uuid’. Subforms require both ‘external_uuid’ as well as ‘parent_external_uuid’ (explained in detail below).

  3. Not all fields are mandatory, however some system fields definitely are (such as external_uuid, created_by, workflow). You can happily leave most other fields blank if it makes sense for this particular upload.

  4. Look out for date field format. Many spreadsheet editors will automatically recognise the format and convert it to a date field- it needs to be retained as a text field.

How to use external_uuid and parent_external_uuid

These two fields are some of the most important and also the most confusing; they’re also the first fields to populate.

Why are they needed?

Though Donesafe will assign these records system IDs when they’re imported, it’s very important that these IDs also be provided by the user. The system will either match a UUID (unique Identifier) with an existing record’s OR create a new record with this UUID when you’re uploading. The reason for this is twofold:

  1. If you make an error in your CSV and only notice it after your upload has been completed, then reusing the same UUIDs will allow you to overwrite previous entries, meaning that your can ‘edit’ your mistakes rather than just re-creating new records from scratch.

  2. If you want to edit existing records in bulk, then obtaining the UUID of those records (via the report builder), will allow you to do this.

How to use external_uuid.

The ‘External_UUID” field is required for all records uploaded. It is imperative that this ID be unique for the system, and that it has no spaces or unusual characters. If you are migrating from another system, you may use their external IDs. If however you need to generate your own, a good way to ensure that it is unique is to use the module code name and append other detail to the end. The following format for example, is a good one:

[Module Code][Form Name][Date][Record Number]
pandemainform1104201801

So, in the above ‘Plant and Equipment’ example, my module’s system code name is ‘pande’, the form being uploaded is the ‘Main Form’, the date uploaded is the 11/04/2018 and the record number for that day is ‘01’ Therefore,

“pandemainform1104201801”

How to use parent_external_uuid

The “Parent_External_UUID”, is ONLY required when uploading subforms. In short, this ID field is requesting the ID of the main records created so that it can link the records together. For example, if I were uploading a maintenance history list, I would need to identify the parent_external_uuid in order to link that maintenance record to the actual plant and equipment main form record that it is associated with.

For example, If I were uploading a forklift and it’s maintenance record, then the forklift’s UUID fields would look like this:

Forklift ID

Maintenance Record IDs

You can see in the above example, that the forklift upload will create a record with the external ID of “pandemainform1104201801”, whereas the second upload will create a maintenance record with an external ID of “pandemaintenance1104201801” whose parent record is the record with the external ID of pandemainform1104201801, thus linking it with the correct plant and equipment item.

Simple Text fields, VS expected value fields.


Text fields

The simplest fields to populate are always the text fields. These fields have no validations so they’ll accept any field values. An example of text fields is below.

Expected value fields (Eg single select list)

These fields are expecting very specific field values, and these will be listed in the instructions.

So in the above example, if I wanted to set the value to ‘Weekly’, I would need to populate my CSV with the value: Weekly.

Expected value fields (Eg People Selector)

These fields require the system to look up an existing user OR add a new user. Therefore, the format is very specific.

So, if you’d like to lookup a user via their payroll number, you would enter the following value in this field within the CSV: payroll_id:145728.

Expected value fields (Eg Date/Time)

Date or Date ime fields must have their format correctly set in order to work as expected. Below is the ‘created_at’ value which uses the date/time format. Ensure that you follow the upload instructions exactly for obtain the format.

A correct date/time format would therefore be “01-01-2018 10:45:00”.

There are of course other field formats, but instructions for these are also contained within the instructions.

c) Populating CSVs

Fill in all relevant details for your main form records in the CSV. Below is an example of a few rows on a completed main form CSV

Once your main form is ready, prepare your subform records, here is an example of completed rows for the first three main form records.

Ensure that you save these as a CSV and that the spreadsheet software you’re using hasn’t converted the date fields away from the expected formats. Once this is done you can proceed to step 3.

Step 3: Uploading your CSVs

Uploading Main Forms

Once your CSVs are prepared, start by selecting the form type you wish to upload. Below you can see that the ‘Main form is selected. Then, either click to upload or drag and drop your CSV to the upload file area. Once this is done, click [Import].

For example, if you were importing your main form, it would look like this:

On clicking import, you’ll see a record of your import commencing. If you refresh your screen, you’ll see the status update. For larger files, this can take some time, otherwise it is usually completed quite quickly.

Uploading Subforms

The process for uploading subforms is much the same, except, you need to select ‘Sub Form’ from the list and then indicate which subform you’re uploading as per the picture below:

A Successful Upload

It’s not likely that your first upload will be without issues. If it is, you’ll see your uploads with ‘0’ in the ‘Errors’ Column as you can see below.

If however you do receive errors, this is not uncommon. It is recommended that you resolve all errors with each file before you attempt to upload subsequent files. In order to resolve errors, read the following sections;

Dealing with Errors

If you receive one or more errors, this is shown in the summary table as per below.

As you can see, the file uploaded had 1 error. By clicking on the ‘1’ you can see a summary of that error and any others that may have occurred.

For example:

The above error indicates that the first row of the CSV failed on designating the ‘Workflow’ of the record. This could either be because of a spelling error, OR because that workflow state doesn’t exist. In this case, it is because the state has been archived. So, to resolve it, make the adjustments in the CSV and reupload, ensuring that your UUIDs are the same. As long as your external_uuids match, the upload should resolve the issue.

Now that the error has been resolved, you can see that the records no longer have any errors:

Dealing with ‘Total Line Fail’ errors.

A total line fail error will result in that entire record row not importing. This occurs when a value hasn’t imported correctly that the system requires in order to work successfully. This can be resolved by resolving the error/s reported and re-attempting an upload.

One total line error to look out for is illegal characters. If your file has illegal characters you may receive errors like this:

To avoid these types of errors it is recommended that you always use the original CSV downloaded.

Getting External IDs of existing records for bulk updates

Firstly, this is an advanced use of this feature and you should only attempt this if you are already comfortable with the importer system.

If you do need to update existing records in bulk using the importer, then you will need to access the external IDs of the imported records. To do this, you will need to use the report builder.

  1. Go to Settings > Report

  2. Create a new report, selecting the desired module.

  3. In the ‘Table’ section of the report, show the ‘External ID’ field along with any other identifying information required

  4. Export CSV

Below is an image of a report export that includes the External ID value. You may recognise these external IDs from our example, however this will also work for records created within Donesafe.

If updating existing records, you should only be required to include the external ID and details that you would like to update. The importer will not overwrite existing data with blank fields.

Did this answer your question?