Import Templates

What are import templates?

Import Templates empower you to regulate and expedite your importing process when you need to import data on a recurring basis. With Import Templates, you can match columns in your CSV file to fields within your data table just once, and save the template for recurring importing from the same CSV file so you do not have to re-match columns and fields the next time(s) around. 

For instance, if you would like to import data from your Quickbooks CSV file into Tadabase on a weekly basis, you can create an Import Template to expedite your weekly imports down to just a click.

Creating Import Templates

To create an Import Template, navigate to the Automations and click on the “Import Templates” option.

A new window will pop up where you can customize your new Import Template using four setting tabs.

1. Detail
2. Field Mapping
3. Conditions
4. Import Rules

We will define each of these setting tabs within this article.

Detail

The Detail settings tab is where you can name your Import Template. For instance, you name your Import Template "Weekly Quickbooks Import". This setting is required.

Field Mapping

The Field Mapping settings tab is is where you will align the columns in your CSV file with the corresponding fields in your data table. 

Note: CSV's without a header column will result in an error and the import will not be completed successfully. 

When importing without at Import Template, this process has to be done manually each time. The benefit of creating an Import Template is that you can define your field mapping process once and not have to remap your fields with every recurring import. The import template will automate your saved column/field mapping for all recurring imports using this template.

To map fields within your data table to columns within your CSV file, you can either click on the Add New Field button to add a field from your data table one at a time or you can click on the Add All Fields button to add all fields from your data table at once.

Every time you add a new field from your data table you can map it to its corresponding column within your CSV file. 

Within the Mapping CSV Field section, you must enter the exact spelling (case sensitive) of your CSV columns for correct mapping. 

When importing fields that can contain multiple values, you will be able to match each part of the data with the appropriate column in your spreadsheet. 

 

Note: Read-only fields can not be imported. Read-only fields include fields that are calculated in the database, such as Complex Formula fields, Date Formula fields, Auto Increment fields, Equation fields, Text Formula fields, and Basic Formula fields. 

Field Mapping Criteria

When mapping fields/columns for import, please take note of several field types that require specific criteria for correct field/column mapping. Let's explore each of these field types that require specific mapping criteria.

Multi-section fields 

There are three field types that contain multiple sections within their fields, and when mapping these fields/columns for import, each section within these fields must be divided into separate columns within the Mapping CSV Field section. These three multi-selection field types are Name fields, Address fields, and Date/Time fields.

As you can see in the image below, when you add either of these three multi-section fields to map to CSV columns, these fields will be divided into multiple sections and each section require mapping into a separate column within the Mapping CSV section.

As each name field, address field, and date/time field is separated into individual fields by their sections, you can choose to map all field sections into CSV columns or you can choose to map only some of the field sections into CSV columns. For instance, you can choose to map only the Expense Date field to a CSV column for import and exclude the Expense Time field from importing.

Date fields

When mapping date fields/columns for import, you can choose the date format to be displayed for imported date field values. Currently, there are three date format options: 

• MM/dd/yyyy
• MMM dd, yyyy
• dd/MM/yyyy

Time fields

When mapping time fields/columns for import, you can choose the time format to be displayed for imported time field values. Currently, there are two time format options: 

• "hh:mm: a" (example: "07:45 PM")
• "HH:mm" (example: "19:45")

Select and Radio Fields

To successfully import values into Select fields and Radio fields, confirm that the option values are available within the spreadsheet prior to import.

Checkbox and Multi-select Fields

To successfully import values into checkbox fields and multi-select fields, confirm that the option values are available within the spreadsheet prior to import. Use a comma to separate each value. 

Generally, saving a file in Excel or Google Sheets as a CSV will format multiple values in a cell correctly, even if the cell contains a comma. However, you can open the CSV in a notepad and make sure that when you have a comma inside a cell it is wrapped in quotation marks. 

Decision Field

To import into Decision fields, set the value to either 1 (checked) or 0 or blank for (unchecked).  

Importing Connected Records

You can import connected records by using any field (preferably a unique field) from the parent data table to map the field/column. 

For instance, say you have a Students data table and an Enrollments data table with a connection field to the Students data table with each enrollment belonging to one student. Say you would like to import enrollment records from your Enrollments spreadsheet file into your Enrollments data table. 

Your Enrollments spreadsheet may look something like this:

To successfully import connected records of each student's enrollments from your spreadsheet, you can use any field from the parent data table- the Students data table, in this case- to map the field/column for import. As a reminder, the child data table is the data table that belongs to the parent data table. As each enrollment belongs to one student, the Students data table is the parent data table and you would therefore use a Students field to map to the column for import. 

It is preferable to use a unique field for field/column mapping, such as an Email field or a Student ID field.

The following image illustrates using an Email field from your Students data table for mapping to the spreadsheet column:

Or, the following image illustrates using a Student ID field from your Students data table for mapping to the spreadsheet column:

To import multiple connections for each student, use a comma or two pipe characters (||) as a separator, as illustrated in the Class column below. 

Non-importable fields

The following fields can not be imported: 

• File
• Image 
• Signature
• Text Formula
• Basic Formula
• Count
• Sum
• Min
• Max
• Average
• Equation
• Date Range
• Auto Increment

Conditions

The Conditions settings tab of your Import Template is where you can add conditions to only import values from the CSV when records match pre-defined criteria. Each record in the CSV will be evaluated based on the condition you define. If the condition is met, the record will be imported, otherwise it will be skipped. 

When adding conditions, you can add multiple conditions and evaluate if any or all conditions should be matched. 

Setting conditions for your Import Template is optional.

Import Rules

The Import Rules settings tab of your Import Template is where you can set the values of specific fields regardless of what values are in the CSV columns. 

You can create rules to set values to:

1. Custom Values
2. A logged-in user's field value

Custom values

You can create a rule to set the value of a specific field to any custom-defined value of your choosing. This custom value will be applied to all records using the selected field regardless of what values are in the records using the corresponding column.

Logged-in user's field value

You can create a rule to set the value of a specific field to a value belonging to the logged-in user. The logged-in user refers to the user who is currently logged in.

Creating rules to set specific field values to values belonging to the logged-in user can be highly useful in tracking user-related details such as who used the Import Template to import records or when did this user import records.

Creating rules to set values to the logged-in user's field values is only applicable when the import template is used from the live app/ front-end of your application. 

Setting import rules for your Import Template is optional. 

Using Import Templates

Once you have defined all your desired settings to create your Import Template, you can use your Import Template to import records either in the Data Builder or on the live app.  

In the Data Builder

To use your Import Template to import records within the Data Builder, click on the Import button within the Records Tab of the selected data table within which you wish to import records.

A new window will pop up prompting you to select the import template you wish to use for this import along with several other settings you can define to utilize your import template exactly as you need. 

1. Import Type- To import records using an Import Template, choose Template as the import type. To import records without using an Import Template, you would select a Regular import type. Click here for instructions on how to import records as a regular import type/ without using an Import Template. 
   
   
2. Import Template- If you chose to import records using an Import Template, select the Import Template you wish to use for this import.
   
   
3. Select CSV File- Browse for your CSV file from which you would like to import records using your Import Template.
   
   If you wish to keep the remaining two settings at their default settings, you can continue with your import at this point. As you are importing records using an Import Template, you can skip all the field mapping, rules, and condition settings that are typically required when importing records without a template. All field mapping, rules, and conditions originally defined when you created your Import Template will be automatically applied to this import and all future imports using this template. 
   
   
4. Delimiter- Select the type of delimiter your file contains. By definition, CSV (Comma Separated Values) files use commas as delimiters, and "comma" will generally be the response to this question. However, if your CSV file is configured with a different character delimiter you can define the correct delimiter here. 
   
   
5. Select a field to match records- This is where you can enable Record Matching to occur during your import by selecting a field to match to existing records. Record Matching empowers you to use your CSV file to update existing records within your data table by selecting a field within your data table that can be matched to a column within your CSV. 
   
   The selected field must match to a column within your CSV and it must be a field that requires unique values, such as an ID field or an Email field with the unique setting enabled to require unique email values.
   
   Once you select a field, the import will comb through all existing records within the data table and CSV that use the selected field/column. If it finds a value within the selected column of your CSV that matches to a value within the selected field of your data table, it will use that row in the CSV to update that record in your data table. If it does not find a value within the selected column of your CSV that matches to a value within the selected field of your data table, it will insert that row in the CSV as a new record.
   
   
   For instance, say you are importing records from your Expenses CSV file into your existing Expenses data table and you would like to update records that match for values within the Expenses ID field/column. For instance, say you would like to update records within your Expenses data table to an Expense Status of "Paid" when they have already been marked as "Paid" within your Quickbooks CSV file.
   
   

   

   
   By selecting the Expenses ID field to match records, the import will comb through all existing records within your data table and CSV that use the Expenses ID column/field. If it finds a matching Expense ID within this column/field, it will update that record in the data table with that row's record values in the CSV. If it does not find a matching Expense ID within this column/field, it will insert that row in the CSV as a new record within your data table. 
   
   As illustrated in the image below, matching Expense IDs will result in those records being updated with record values from those rows within the CSV. 
   
   

   

   
   Non-matching Expense IDs will result in those rows being inserted as new records within your data table.
   
   

   At default, Record Matching is disabled and this setting is set to "Don't Match, add all imported" at default. 

It's best to use a unique field for record matching. Something like an Auto Increment or other unique values.  When doing record matching, only the first record that matches will be updated. For example, if you have in your CSV a record with ID 1 and in your table, you have five records with ID 1 - only the first record will be matched. 

As you are importing records using an Import Template, you can skip all the field mapping, rules, and condition settings that are typically required when importing records without a template. All field mapping, rules, and conditions originally defined when you created your Import Template will be automatically applied to this import and all future imports using this template. 

On the live app