Skip to main content

Workbooks
  • Knowledge Base
  • Customer Forum
  • Training
  • Support Portal
  • Support Processes
Login
  1. Home
  2. Support
  3. Knowledge Base
  4. Knowledge base
  5. Importing Data
  6. Workbooks Import Wizard
  7. Defining Field Mappings
Support

Knowledge Base

Browse our knowledge base articles to quickly solve your issue.

  1. Home
  2. Support
  3. Knowledge Base
  4. Knowledge base
  5. Importing Data
  6. Workbooks Import Wizard
  7. Defining Field Mappings
  • Welcome to the Knowledge Base
  • Introduction
    • Using the Knowledge Base
    • Editions & Licensing
      • Renewing Licences
    • Workflow
      • Queues
    • Logging In
      • Configuring Security Policies
      • Google Authentication
      • Login Protection
      • Microsoft Azure Active Directory
        • Link Workbooks to Microsoft Azure
        • Setting up User Templates
        • Locking down your Workbooks
    • Support Processes
  • Training
    • Training Videos
    • Training Courses
  • Getting Started
    • Supported browsers
    • Introduction
    • Desktop
      • Tips & Tricks on the Desktop
      • Welcome Messages & Bulletins
    • Records
      • External Links to Workbooks records
      • Tabs
        • Default Tab Views
        • Summary Timeline View
        • Overview Tab
      • Watching Records
      • Notes
    • Landing Pages
      • Bulk Actions
        • Scheduled Bulk Actions
      • Customising Views
        • Customising Columns
        • Filtering
          • Custom Filters
        • Calendar Views
        • Card View
          • Report Card View
          • Worked Examples of Card Views
      • Deleted Items
      • Edit Grids
      • Exporting Data
      • Exporting to MS Excel
    • Workbooks on Mobiles and Tablets
  • Preferences
    • Introduction to Preferences
    • Setting your Preferences
      • Email Accounts
      • Notifications & Reminders
  • Activities
    • Introduction to Activities
    • Using Activities
      • Meetings
        • Integrating Workbooks and Google Calendar
          • Installing and Managing Workbooks and Google Calendar Integration
          • Synchronising your Google Calendar with Workbooks
      • Follow-on Activities
  • Cases
    • Introduction to Cases
    • Displaying & Adding Cases
    • Managing Cases
    • Case Portal
      • Installation
        • Custom Fields
        • Case Portal Configuration
          • Company Details
          • General
            • Case Portal Reports
          • New Case
          • Help Text
          • Emails
          • Advanced Features
          • Field Mappings
          • Help Dialog
          • Product Area Questions
        • Case Portal Additional Features
          • Setting up Public/Private Activity creation
          • Setup Registration Emails from a Person
          • Automatic Emails on Case Status Change
      • Using the Case Portal
        • Case Updates - Public & Private Activities
        • Inviting a Person to the Case Portal
  • Email
    • Introduction to Email
    • Working With Emails
    • Email Signatures
    • Sending Bulk Email
    • Common Email Errors
    • Email Integrations
    • Mailing Lists
  • Forecasts & Quotas
    • Introduction to Forecasts & Quotas
    • Sales Forecasts
      • Adjusting Forecasts
      • Included Forecasts
    • Sales Quotas
  • Importing Data
    • Introduction to Importing
    • Preparing your Import Data
      • Import Templates
      • Compulsory fields for Import
    • Workbooks Import Wizard
      • Uploading your data
      • Defining Field Mappings
      • Extending your Import to other Record types
      • Checking for Duplicates
      • Running and Reviewing your Import
    • Managing your Imports
      • Undoing an Import
  • Leads
    • Introduction to Leads
    • Displaying & Adding Leads
    • Converting Leads
  • Marketing
    • Introduction to Marketing
    • Marketing Campaigns
      • Displaying & Adding Campaigns
      • Campaign Management
      • Campaign Membership
      • Tracking URLs on Campaigns
      • Integrating DotDigital and Workbooks
        • Configuring Workbooks and Dotmailer
      • Integrating MailChimp and Workbooks
        • Configuring Workbooks and MailChimp
    • Mailing Lists
      • Creating Mailing Lists
    • Products
      • Pricing Schemes
      • Supply Scheme
      • Product Bundles
    • Mailshots
      • Creating a Mailshot
    • Upload Library
      • Using the Upload Library
    • Templates
      • Creating Templates
      • Creating a Template to show multiple lines
      • Extra Template Placeholders
    • Using HubSpot with Workbooks
    • Event Management
      • Introduction to Event Management
      • Enabling the Event Management Module
      • Using Event Records
        • Event Records
        • Event Sessions
        • Event Speakers
        • Event Sponsors
        • Event Products
        • Event Tickets
        • Event Attendees
      • The Event Management Portal
        • Introduction to the Event Management Portal
        • Prerequisites for installation
        • Accessing the Plugin
        • Installing the Event Management Portal
        • Amending Event Portal Parameters
        • Adding Custom CSS to the Event Portal
      • On The Day Registration App
      • Setup On the Day Registration App
    • Compliance Records
      • Introduction to Compliance Records
      • Enabling Compliance Records
      • Creating Compliance Records
      • Using Compliance Records
    • Spotler Integration
      • What is Spotler?
      • Navigating your Spotler homepage
      • GatorMail
        • Introduction to GatorMail
        • Managing your GatorMail Account
          • Domain Setup
        • GatorMail Configuration
          • GatorMail Field Mappings
            • GatorMail Hard Bounces
          • Preference Centres & Landing Pages
            • Preference Centre Setup
        • Creating Emails in GatorMail
          • Imbedding Images and Videos
        • Sending Emails in GatorMail
          • Sending GatorMail Campaigns
            • Campaign Action Types
            • Ramp-up
            • Testing Campaigns and Emails
          • Using SendForensics
          • Understanding your Campaign results
        • Advanced Features
          • Gator Smart Forms
          • GatorMail - Dynamic Content
      • GatorLeads / Web Insights
        • Tracking Code
          • Restricting Page Tracking
        • Setting up the Plugin
        • Viewing Web Insights Data on your Form Layouts
        • Domain Names and Online Activities
        • Reporting incorrect Leads created through Web Insights
        • Reporting on Web Insights data
        • Using UTM Values
        • Why aren’t Online Activities being created in the database?
        • Why is GatorLeads recording online activities in a foreign language?
      • GatorSurvey
      • GatorWorkflow
        • Spotler Data Sync
        • Triggered Mailshots
      • GatorPopup
  • Opportunities
    • Introduction to Opportunities
    • Displaying & Adding Opportunities
    • Opportunity Line Items
    • Opportunity Relationships
  • Integrations
    • Mapping
      • Introduction to the Mapping Module
      • Using the Mapping Module
      • Using Mapping in Reports
        • Changing the size/colour of Map Pins
    • Electronic Signing Tools
      • Docusign Integration
        • DocuSign Functionality
          • Copy Document on Signature
          • Update Related Records
          • Sending Multiple Documents
          • People, Organisations & Cases with DocuSign
          • Invoice Payments with DocuSign
          • Managing DocuSign Documents
        • Setting up the DocuSign Integration
          • Authentication with DocuSign
          • Production vs Sandbox
          • Creating a Document with a DocuSign Template
          • Creating a Document with a Workbooks PDF
          • DocuSign Fields
          • DocuSign Settings
          • Form Layout Configuration
        • DocuSign - Worked Examples
          • Taking Payments
          • Updating Related Records
          • Multiple Document Sends
          • Creating a Template for Cases
          • Creating a Template for Organisations
          • Creating a Template for People
          • Unified PDF for Transaction Documents
        • DocuSign - Reporting
          • Multiple Send Report Grid
          • Document Review Report
          • Monitoring Integration Errors
      • Adobe Sign Integration
        • Introduction to Adobe Sign
        • Authentication with Adobe Sign
        • Installing the Integration
        • Integration Monitoring & Error Handling
        • Setup of Adobe Sign
        • Adobe Sign Fields & Form Layout Configuration
        • Optional Custom Fields for Adobe Sign
        • Adobe Sign Button Process
        • Using the Adobe Sign Integration
    • Creditsafe Integration
      • Installing the Creditsafe Plugin
      • Configuring Organisations to use the Creditsafe Integration
      • An example of how to use the Creditsafe Integration
    • Zapier
      • Introduction to Zapier
      • Available Triggers and Actions
      • Linking your Workbooks Account to Zapier
      • Setting up Zaps
        • Posted Invoices to Xero Invoices
        • Xero payments to Workbooks Tasks
        • New Case to Google Drive folder
        • New Case to Basecamp Project
        • New Workbooks Case to JIRA Ticket
        • Jira Issue to new Case
        • 123FormBuilder Form Entry to Case
        • Eventbrite Attendee to Sales Lead and Task
        • Facebook Ad Leads to Sales Leads
        • Wufoo Form Entry to Sales Lead
        • Posted Credit Note to Task
        • QuickBooks Online
          • Initial Setup
            • Authentication with QuickBooks Online
          • People & Organisations to QuickBooks Customers
          • POSTED Workbooks Invoice to QuickBooks Online
          • QuickBooks Online Payment to Workbooks Invoice & Task
          • QuickBooks Sales Tax Code Lookup
        • Survey Monkey responses to Tasks
      • Multistep Zaps
    • Email Integrations
      • Email Dropbox
        • Setting up your Dropbox
        • Using your Dropbox
      • Workbooks Exchange Server Sync
        • Supported email clients
        • Set Up
          • Setting up the WESS
          • Setting up the Outlook Add-In
          • Customising the WESS Add-In
          • How to change Authentication setup for WESS
            • O365 Exchange authentication Troubleshooting
        • What gets synchronised?
          • Synchronising with the WESS
          • Synchronising with the WESS Add-in
          • How to force a sync
          • Best practice
        • WESS Troubleshooting
      • Workbooks Outlook Connector
        • Introduction to the Outlook Connector
        • Before downloading the Outlook Connector
          • System Requirements
          • Enabling the Outlook Connector
        • Download/update the Outlook Connector
        • Installing the Outlook Connector
          • Uninstalling the Outlook Connector
        • Using the Outlook Connector
          • Configuring the Outlook Connector's Settings
          • Identifying Records in Workbooks created in Outlook
        • Outlook Connector Troubleshooting
    • Event & Webinar Integration Tools
      • GoToWebinar
        • Installation
          • GoToWebinar Fields & Processes
          • Setting up Process Buttons for GoToWebinar
        • Authentication
          • Multiple GoToWebinar Accounts
          • Removing GoToWebinar Accounts
        • Create/Update & Cancel Webinars from Events
        • Event Attendees as Webinar Registrants
        • Event Speakers as Webinar Panelists
        • Retrieve Webinar Data
      • ON24
        • Installation
          • ON24 Fields & Processes
          • Setting Up Process Buttons for ON24
        • Authentication with ON24
          • Removing ON24 Accounts
        • Create Webcasts from Events
        • Event Attendees as Webcast Registrants
        • Retrieve Webcast Data
          • Reporting: Survey Responses
          • Reporting: Poll Responses
        • Retrieve Survey Data
        • Engagement Hub Interactions
    • Microsoft Office
      • Azure Active Directory
      • Excel
      • Outlook
      • Power BI
      • SharePoint
        • Authentication
        • Installation
          • Custom Fields & Processes
          • SharePoint Integration Settings
        • Using the SharePoint Integration
      • Word
        • Introduction to the Word Add-in
        • Deploying the Word Add-in
        • Creating Word Add-in Templates
          • Example Word Add-in Templates
        • Using Word Add-in Templates
        • Word Add-in Tips and Tricks
    • Outreach
      • Installation
        • Outreach Fields & Processes
        • Create Outreach Custom Field Mappings
          • Configure Outreach Field Dynamic Picklist
        • Sync Outreach Mappings
      • Outreach Authentication
        • Remove Outreach Account
      • Sync People to Outreach Prospects
        • People Field Mappings
      • Sync Organisations to Outreach Accounts
        • Organisation Field Mappings
      • Sync Workbooks Opportunities to Outreach
        • Opportunity Field Mappings
      • Sync Tasks/Activities from Workbooks to Outreach
        • Follow-Up Activities created via Scheduled Process
      • Sync Outreach Sequences to Workbooks
      • Sync Outreach Sequence States to Workbooks
      • Sync Outreach Sequence Step Numbers to Workbooks
      • Sync Prospects/Accounts/Opportunities from Outreach to Workbooks
      • Sync Outreach Tasks/Calls/Meetings to Workbooks
    • Scribe/Workbooks Connector
      • Connection Setup
        • Microsoft SQL Server Connection Requirements
      • Connector Control Blocks
      • Worked Example - Create, Read, Update & Delete
    • RingCentral
      • Installation
        • RingCentral Integration Fields and Processes
      • RingCentral Authentication
        • Remove RingCentral Account
      • Creating Phone Call Activities
      • Click to Dial
  • People & Organisations
    • Introduction to People & Organisations
    • Organisations
      • Displaying & Adding Organisations
      • Customers & Suppliers
    • People
      • Displaying & Adding People
      • Adding People to Outlook
    • Relationships
      • Change Employer Wizard
      • Direct Relationships
      • Third Party Relationships
    • De-duplication and Merge
      • Workbooks Merging
      • Workbooks DQ
      • Data Quality
    • Using Postcode Lookup
    • Data Enrichment
      • Introduction to Data Enrichment
      • Setting up Data Enrichment
  • Snippets
  • Reporting
    • Introduction to Reporting
      • Creating Reports
        • Standard Workbooks Reports
        • Creating a Pre-populated Report
        • Creating a Report from a Template
          • Report Templates
        • Creating a Blank Report
        • Auto-refreshing Report Views
      • Sharing Reports
    • Using Reports
      • Reporting Explained
        • Adding Targets, Conditional Formatting and CSS Styling
          • Calculated Targets
          • Target Sets
            • Creating a Target set for Closed Cases per Customer Support Agent
            • Creating a Target set for Closed Sales amount per Sales Person by quarter
        • Reporting with multiple currencies
        • Drilling into Summary Views
        • Improving Report Performance & Run Time
      • Displaying reports within Record Views
      • Displaying Reports
        • Reporting Views (Reports on Landing Pages)
      • Adding a Report view to a Dashboard
    • Charts
      • Creating & Using Charts
        • Bar Charts
          • Targets on Bar Charts
        • Line Charts
        • Pie Charts
        • Dial Charts
    • Exporting Reports
      • Emailing Scheduled Reports
    • Advanced Reporting
      • Types of Split Columns
      • Using Calculated Columns
        • Introduction to the IF Statement
        • Introduction to the CASE Statement
        • Applying Search filters in Calculated Columns
        • Comments in Calculated Columns
        • Financial Year Reporting
        • Introduction to Date Functions in Reports
        • Using Calculated Criteria
        • Reporting on empty fields
      • Audit Reporting
  • Dashboards
    • Adding Elements to Dashboards
      • Configuring Dashboard Elements
    • Dashboard Views
    • Dashboard Fields
    • Creating a Sales Dashboard
    • Springboard
    • Adding a Dashboard as a Record Tab
  • Okta Authentication
  • Transaction Documents
    • Introduction to Transaction Documents
      • Displaying & Adding Transaction Documents
      • Copying Transaction Documents
      • Transaction Documents Fields Help
      • Transaction Documents Line Items Help
      • Printing & Sending Transaction Documents
      • Managing Transaction Document Currencies
      • Managing Transaction Document Statuses
      • Setting a Blank Default Currency on Transaction Documents
    • Credit Notes
    • Customer Orders
    • Invoices
    • Quotations
    • Supplier Orders
    • Contract Management
      • Displaying & Adding Contracts
      • Using Contracts with Cases
    • Sagelink
  • Auditing
    • Auditing
  • Configuration
    • Introduction to System Administration
      • Quick Start Wizard
    • Users & Security
      • Users
        • Setting up your Users
        • Disabling Users
        • Profile Pictures
      • User Groups
        • Capabilities
        • Creating & Modifying User Groups
      • Permissions
        • Sharing Policies & Mandatory Permissions
          • Configuring the Advanced Security Module
            • Configuring the Advanced Security Module: Segmenting Users by function
            • Configuring the Advanced Security Module: Segmenting Users by location
      • Account Settings
      • Licences & Modules
        • Multi Language
    • Database
      • Databases
      • Storage Usage
      • Prefixes & Numbering
        • Modifying Object References
      • Export Database
      • Database Settings
    • Accounting
      • Accounting Periods
      • Exchange Rates
        • Setting Exchange Rates
      • Tax Regimes
        • Setting Tax Rates
        • Sales Tax Rulesets
          • Using Sales Tax Rulesets
      • Own Organisations
        • Setting up your Own Organisation
    • Email & Integration
      • Email Settings
        • Setting up your Email Server
        • Using Workbooks Server to send Emails
      • API Keys
      • Web to Case and Web to Lead
        • Setting up a Web2Case Form
        • Setting up a Web2Lead Form
        • Using Web Forms in Workbooks
        • Editing a Web Key Form
      • Preference Centre
      • MailChimp & dotMailer
      • Loqate
      • Bank Account Validation
    • Customisation
      • Creating & Modifying Picklists
      • Desktop Preferences
      • Theme
      • Record Types
        • Creating Custom Fields
          • Dynamic Linked Items & Picklists
          • Constrained Dynamic Linked Items (DLI) & Picklists
          • Creating relationships with Dynamic Linked Items (DLIs)
          • Linking Custom Field Values
          • Showing Linked Records in Tabs
        • Report-based Custom Fields
          • Improving the Performance of Report Cells/Grids
        • Linked Fields & Reference Fields
        • Record Templates
        • Form Layouts
          • Customising an Organisation form
          • Displaying a Tab as a Side Panel
          • Card View
          • Read-only Assigned to Fields
        • Customising relationships between parties
          • Configuring Custom Party Relationships
        • Opportunity Stages
      • Custom Records
        • Enabling Custom Records
        • Creating Custom Record Types
          • Editing Custom Record Types
        • Using Custom Records
    • Automation
      • Plugins
        • Field Updater Plugin
        • File Storage Plugin
          • Using File Storage
          • Using SharePoint File Storage
        • Cognism Enrich Plugin
          • Using Cognism Enrich
        • Email to Case Plugin and Process
      • Processes
        • Process Logging
      • Workbooks Scripts
        • Script Library
        • How Workbooks Runs Scripts
        • Workbooks Script External Access
      • API Data
      • Triggers and Webhooks
        • Configuring Triggers and Webhooks
    • PDF Configuration
      • Modifying Document Templates
        • Adding Terms & Conditions to your Documents
        • Multi-line Value Tags
      • Modifying Default Colours & Images
      • Creating Custom Document Templates
  • Contact Support
    • Contacting Workbooks Support
    • Workbooks CRM Advisory Packs
    • Introduction to the Administrator Service
  • Releases & Roadmap
    • Roadmap
    • September 2022 Release
    • June 2022 Release
    • March 2022 Release
    • October 2021 Release
    • June 2021 Release
    • March 2021 Release
    • 2020 Releases
      • December 2020 Release
      • September 2020 Release
      • April 2020 Release
      • January 2020 Release
    • 2019 Releases
      • September 2019 Release
      • February 2019 - Event Management Release
      • January 2019 Release
    • 2018 Releases
      • October 2018 Release
      • May 2018 Release
      • February 2018 Release
      • January 2018 Release
    • 2017 Releases
      • November 2017 Release
      • September 2017 Release
      • June 2017 Release
      • March 2017 Release
    • 2016 Releases
      • December 2016 Release
      • August 2016 Release
      • January 2016 Release
  • Workbooks Glossary

Defining Field Mappings

Import - Stage 3 - Define Field Mappings

Stage 3 is where you control which fields within Workbooks will be populated with information from your source data.  This is done by 'mapping' the fields from your source data against the fields within Workbooks.  All mandatory fields within Workbooks (marked with a star )must be mapped.  If you left the checkbox next to Auto-Map file Headings ticked at Stage 1 Workbooks will suggest likely mappings from your source file to Workbooks fields.  

Make sure you check that Workbooks has mapped the fields correctly.  If required, you can amend these mappings by clicking on the row you want to amend.

More information about field mappings can be found below.

Import Field Mappings

Workbooks can create different types of mappings for you, as listed below:

  • Field from Source File
    A direct mapping of the information in your source data into a specified Workbooks field.  This is the simplest option and will take the value from the specified column in your source data and put that into the Workbooks field that you've identified in section 1 of the screenshot below.
  • Concatenated Fields from Source File
    This option allows you to concatenate (ie, combine) the values from two or more fields in your source data and put them all into the Workbooks field that you've identified in section 1 of the screenshot below.  Alternatively you can use a program like MS Excel to concatenate your data before starting your import.
  • Fixed Value
    This option means that you don't use values from your source file but instead you can enter a value that will appear in the Workbooks field that you've identified in section 1 of the screenshot below.  This will populate every record you import with exactly the same data for that field.
  • Blank Value 
    Similar to the one above except that the Workbooks field you've identified in section 1 of the screenshot below will be blank on every record you import.  Of course, this option cannot be used for mandatory Workbooks fields.

In addition Workbooks gives you the option to replace any blank rows in your source data with a fixed value, by ticking the checkbox next to Use fixed value in place of blanks.  This opens a text box where you can enter the fixed value you'd like to use.  More information on the different mapping types is given below.

Indexing Fields for Import

If you are importing to Custom Fields that are not indexed, this can significantly slow down your import if you are using these to uniquely identify your record. It is recommended that if you do this frequently, you should index the field. More information on Custom Fields, can be found here.

Ignore Blank Fields during Import

When importing data, looking for matching records based on blank values can slow the import down considerably. Our import wizard no longer defaults to matching blank values, although a user can make a conscious decision to enable it if required. By not looking up blank values for matching purposes your imports will run much more quickly.

Field from Source File Mapping

This is the simplest type of mapping and is likely to be the type you use most frequently so it is the default setting.  All that's required is:

  • Use the dropdown picklist next to Field in section 1 of the above screenshot to choose the Workbooks field where you want the data to appear.
  • Leave the Mapping Type field in section 2 as Field from Source File.
  • Use the dropdown picklist next to File Field in section 2 to select the field in your source data that you want to import into the Workbooks field you've already specified.

NOTE:  There is also a checkbox called Use fixed value in place of blanks.  Ticking this allows you to import the values in your source data when a value has been entered and import a fixed value for the records if your source data field is blank.

Concatenate Fields from Source File Mapping

Use this option if the data you want to import into one Workbooks field is split across more than one column in your source data.  A common example of this is a file where a Street Address is split across multiple columns. 

NOTE: 

  • When importing People it's mandatory to import their full name.  Workbooks will then use this data to auto-populate the First Name, Middle Name and Last Name fields.  You may need to concatenate fields in your source data to map to the Name field.
  • You can only concatenate data into Workbooks fields are either text or multi-select fields.

To concatenate fields, follow these steps:

  • Use the dropdown picklist next to Field in section 1 of the above screenshot to choose the Workbooks field where you want your concatenated data to appear.
  • Choose a Mapping Type of Concatenate Fields from Source File, which will cause some more fields to appear as shown below.

You can now choose how you want the concatenated fields to be separated using the dropdown picklist next to Concatenated Field Separator.  You can choose from Comma, Space, New Line or Custom.  If you choose Custom you can enter whatever letter or symbol you want to use in the field next to Custom Field Separator.  You can define which fields you want to concatenate by clicking Add and choosing the fields from your source file.  The order in which you enter the fields is the order in which they'll be combined when concatenated.

NOTE: 

  • If you are concatenating data into a Workbooks multi-select field the only field separator you can use is a comma.
  • There is also a checkbox called Use fixed value in place of blanks.  Ticking this allows you to import the concatenated values from your source data when a value has been entered and import a fixed value for the records if your source data field is blank.

Fixed Value Mapping

Use this option if you want all the records you're importing to have the same value in a specific field.  To use Fixed Value mappings:

  • Use the dropdown picklist
    Next to Field in section 1 of the above screenshot to choose the Workbooks field where you want your data to appear.
     
  • Choose a Mapping Type of Fixed Value 
    Which will cause a new field to appear called Fixed Value.  Use this new field to enter the text that you want to appear in Workbooks.  You can also tick the box next to Capitalise initial letter of each word to help you 'tidy up' your data. 
     

    NOTE:  If you want to use a Fixed Value for Workbooks fields that are checkboxes, enter either 1 or Y if you want the checkbox to be ticked.  If you want the checkbox to remain empty, don't enter anything.

Blank Value Mapping

Use this option if you want a particular field in your imported records to be blank.  Simply:

  • Use the dropdown picklist next to Field in section 1 of the above screenshot to choose the Workbooks field you want to be blank.
  • Choose a Mapping Type of Blank Value.

Of course, this option cannot be used for mandatory Workbooks fields.


Mapping Date Fields 

  • When you map data to a Workbooks field that is a date or date/time field, you'll be prompted to specify the format that's used in your source file for the date using the dropdown picklist next to Data Format.  The standard choices are:
    • %d/%m/%Y, which, for example, corresponds to 30/09/2011;
    • %m/%d/%Y, which corresponds to 09/30/2011; and
    • %Y-%m-%d, which corresponds to 2011-09-30.

If none of the above formats match the format of the data in your source file you can specify the format yourself. For example, if your source file shows 2011/30/09 you should set the format to be %Y/%d/%m or if your source file shows 30/09/11 you should set the format to be %d/%m/%y. 

Mapping Currencies

  • If you choose to map data to a Workbooks field that is a currency field, you'll receive a prompt about whether or not all the values are in a single currency. 

    If they are are, you can tick the box next to All values are in a single currency so used fixed currency code, which will activate a field called Fixed Currency Code from which you can select the appropriate code. If, however, your source data contains values in different currencies, the data must include a column for the currency code and you should use the dropdown picklist next to File Field to select the appropriate column.

Mapping Checkboxes

  • If you choose to map data to a Workbooks field that is a checkbox (for example the Customer checkbox on an Organisation record or the No Email checkbox on a Person record), your source data should contain either a 1 or Y if you want the checkbox to ticked. If you want the checkbox to remain unticked, your source data field should contain 0 or N. 

Update Settings

Regardless of which mapping type you choose, you'll also see a section of the Edit Field Mappings dialogue box headed Update Settings. The fields here only apply if you're using the import functionality to update existing Workbooks records. If you're simply importing new records, you can ignore these fields. 

  • Only overwrite if Workbooks field is blank
    Ticking this checkbox means that if the Workbooks field is already populated with some data, the values in your source data will not be imported.  The source data values will only be imported if the Workbooks field is blank.
  • Do not overwrite if source data is blank
    Ticking this checkbox means that if your source data is not populated with a value, your Workbooks data will not be 'blanked'

Linking imported data to existing Workbooks records

If you're importing data that you want to link to an existing Workbooks record, you must define how to identify the correct record to link to.  For example, you may want to import People records and link them to existing Organisation records.  You can specify just one criterion to identify the linked record or apply a multi-field matching rule, to apply more criteria.

Fields marked with a triangle – refer to existing records in Workbooks so you need to consider how you want the linking rule to work.  If you choose to map to a Workbooks field that refers to existing records, the New Field Mapping dialogue box will open a Linking Rule section, as shown below.

In the example above, the Workbooks field to import data into has been set as the Employer field (within a Person record).  The field from the source data has also been set as Employer (simply because the source data column header happens to be the same as the name of the Workbooks field - this may not be the case with your imports). 

Within the Linking Rule section, Workbooks has identified that the Employer field within a Person record references an Organisation record within your database so it knows to try to link your import data to existing Organisation records.  However, you still need to specify how you're going to identify which records to link to. 

This is controlled using the dropdown picklist next to Matching Field.  In this example, the values in the Employer column of our source data are the names of the Organisation records so we're using the Organisation name to match on.  However, if the source data contained other information, such as the Object Reference number, or a custom field that you've created, we could match on that.

It's important to make sure your newly imported data is linked to exactly the right existing record so Workbooks gives you some more options to uniquely identify the record to link to:

  • Case Sensitive Match
    Tick this checkbox if you only want the new data to be linked to records within Workbooks when there is an exact Case Sensitive Match between your source data and the Workbooks record.
  • Reject row if not found 
    Tick this checkbox if you want the import to reject the whole row of your source data if Workbooks can't find an existing record to which to link your imported data. 

    For example, if you're importing a Person called John Smith who works for ABC Company and Workbooks cannot find ABC Company, ticking this box will mean that no record will be created for John Smith.  If you don't tick this box, a Person record will be created for John Smith but it won't be linked to any Organisation.
  • Multi-field Matching Rule
    Tick this checkbox if you want to apply stricter controls on how existing Workbooks records are uniquely identified.  See below for more information.

Multi-field Matching Rule

When you tick the checkbox next to Multi-field Matching Rule and click Save & Close a new dialogue box will appear like the one below where you can specify more fields that Workbooks should reference when looking for a unique match.

In this example, we've specified that the Organisation field within Workbooks should match the Employer field in the source data before the new record can be linked.  By clicking on Add Referenced Field to match on, we can use the dropdown picklist to identify another Workbooks field that needs to match a field in our source data.  You can select as many fields as you like to uniquely identify which record you want your new import data to be linked to.

  • Uploading your data
  • Extending your Import to other Record types

© 2023 Workbooks.com - privacy & terms
  • Linkedin
  • Twitter
  • Youtube
  • Facebook