Data Import

What is it?

Data import allows creating many records at once by importing from an Excel file. This is much faster than manual data entry for large volumes. Instead of re-entering each record one by one, users can use the data import function to migrate at most 10,000 past records each time with a few simple steps. 

How and When to use it?

Table Configuration

1. Create a page that contains 1 Form and 1 Form Table. 

2.Connect the Form Table to the Form in the Data Source Settings as usual.

3.Then in Widget Properties > Functions, allow import permissions for relevant user roles.

Set Up the Import Template

An import template is an excel file that guides users to copy and paste the existing excel data on it and will be uploaded to the system for data import. Users are highly recommended to use the template to keep away from import errors. The following steps illustrate how to set up an import template to fit the existing Excel file.

1.Click on the Form Table and click Record Import Settings .

2.In step 1 ‘Column Settings’, check the box next to ‘Show’ and ‘Manual Import’ to select all column headers.

3.Rearrange the order of column headers based on your preference. It is suggested to rearrange the order according to your existing excel order for an easier copy and paste.

4.Input the ‘Display Name’ to customise the headers in the template excel

5.Customize the ‘Width’ if you want to view more content of a particular column in the import data step.

6.Click ‘Continue’.

7.In step 2 ‘Action Buttons’, check the actions you want to appear when importing the data and click ‘Finish’.

Data Import

After finishing the above preparation steps, you can now see an ‘Import’ button next to the ‘Add’ button on the Form Table in the Run Time Preview.

1.Click the ‘Import’ button.

2.Download the template Excel file in step 1.

3.Open the template. Copy and paste your data to the file. Remember to paste the data into the right columns. 

4.Click ‘Choose File’, select the template excel file in Step 2. Beware that the uploaded excel file must be smaller than 2MB and should not exceed 10,000 records.

5.Click ‘Start’.

6.If there is no error in the uploaded file, the system will appear as follows. Click ‘Submit’ or ‘Save’ to proceed.

7.Click ‘Done’ and import all the successful data to the Form Table.

Import Error

There are a few cases that trigger the system to prompt an error message ‘To continue, please remove all the records that have errors’. The following are some common errors that user may make:

1. An empty value for a required field

2. Format error for Date & Time Widget or ‘Creation Time’

3. An invalid email address for ‘User and Group’ widget or ‘On behalf of’

4. Non-existent option for ‘Dropdown Menu’ and ‘Selection Box’ widgets.

For a more detailed widget format, please refer to the ‘Example - Must Follow’ tab in the template excel file.

The related cell will be shown as ‘#Value!’. You can choose ‘Abort’ to cancel the upload, no entry will be added to the result Form Table. Another option is ‘Delete the Above Records to Continue’ which system lists out all the other uploaded records for your action.

Form Validation

Form validation rules set on the Form will apply to the imported data as well and it will be checked in the ‘File Uploaded’ state, except for the uniqueness rule. Following are some examples for your reference. 

Uniqueness Rule

For the keys that should not be repeated like Staff ID, you can add a Uniqueness Rule to the Form in Form Validation Settings. Please note that validations will be checked after the action button is clicked. Those violated entries will be shown in the ‘Failed’ Tab under Import Completed. For records with repeated keys, only the first processed record will be imported successfully.

Following widgets should not be empty

For the fields that users do not want to leave empty, they can specify them under ‘Following widgets should not be empty’ to the Form in Form Validation Settings. As mentioned in section 1.4 Import Error, entries that violate this rule will be prompted as an error.

However, it is not a must to include a required key in the source file every time. There are some exceptional cases. 

1. Default Value: If a widget is given a default value, it will be optional to include it in the template (i.e. Users can uncheck the ‘Manual Import’ option in Column Settings for that particular widget). The system will automatically assign the default value to each imported entry. However, if the user checks the ‘Manual Import’ option for that widget, he must include a value for the corresponding column of each entry. The imported value will override the default value.

2. Calculation: Widget can be derived from Calculation. In this case, users are free to import manually or leave the calculation to the system. Once ‘Manual Import’ is chosen for the computed widget, values must be assigned for all entries in the source file. The imported value will be used in the Form Table. As the system will not calculate for those cells and therefore there is a chance that the imported value is different from the calculation result.

Workflow Edge Validation

Edge Validations are rules and conditions that a record has to fulfil in order to move on to the next state in a workflow. They can be General Rules, Conditional Rules or Required Field Widgets. The data import process checked for these validations when users take a workflow action (e.g. Submit). Records that violate the Edge Validations are listed in the ‘Failed’ Tab in ‘Import Completed’ state.

Note that ‘Save’ is not an action created in the workflow, therefore no error is prompted even if the record violates the edge validations when ‘Save’ is clicked.

FAQ

What is the difference between ‘Show’ and ‘Manual Import’ in Template Column Settings?

The 2 options serve for different purposes. Remember when we import the data, there are 2 states: File Uploaded and Import Completed. The ‘Show’ option is designed for users to examine the imported data first before taking any actions in the ‘Import Completed’ state. While the ‘Manual Import’ option provides flexibility for users to import old records without being affected by the latest default value or calculation for certain widgets.

If the imported field has calculation/Page Data Source settings in the form, what would be the behaviour if the cell (1) has value (2) is empty?

The answer of this question depends on whether the widget is an optional or a required field.

For optional fields, when users import manually for widgets that have calculation/Page Data Source settings in the form, the system ignores the original settings and copies exactly from the source file. Therefore, the values shown on the result table can be different from the calculated result or the page data source value. Users can import a blank value for the imported field, the system will leave them blank on the result table.

For required fields, users who choose to import manually must make sure the imported field contains value or they will come across an upload error in the ‘Upload File’ state. 

If the widget is (1) Visibility Looks Read-Only or/ and (2) Permission Read Only, can the field be imported successfully?

Yes, those fields can be imported successfully. Although users cannot edit the input box for widgets with the above settings when they try to add a new record, they can still import them manually. Editability is not a restriction for data import.

What is the behavior if ‘Unique Key’ is used in the calculation of a widget?

‘Unique Key’ is commonly used in calculation of record ID widget for identification purpose. It is a special widget type that triggers a system to calculate based on the formula set in the widget and replace the data from the source file regardless whether ‘Manual Import’ is checked or not.