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.

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. 

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.

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.