External Databases: An Overview
    Linking to external databases is an excellent way of drawing together data from different sources and displaying it all in one place.
     
Top   How does it work?
  Once you understand the principle of linking to an external database, it's very easy to set up. Here is a practical example where we are linking to an Excel® worksheet.

Imagine that you are a Secret Agent. Each month MI-5 e-mail you a spreadsheet containing other agents and their codenames, which change each month for security.

Here is a sample from the spreadsheet that is e-mailed to you each month:
     
   
  A B
1 Agent name Code name
2 Eva Piano BLUE BADGER
3 Robin Banks RED FOX
4 Lucy Lastic GREEN WORM
5 Chris Mass ORANGE PARROT
     
  In our database you want the Contact field User-defined 1 to show the codename from this spreadsheet. To do this you would:
   
  Choose the Link to external Databases section
  Choose the Microsoft Excel® page
  Add the Top Secret.XLS workbook to the list
  Choose Column A as the Key Column
     
  Choose the Administrative and Database section
  Choose the Contact tab
  Choose User-defined 1 from the Field / Page drop-list
  Set the Appearance as Excel® Link
  Set the Formatting as Column B
     
    When you now open the Details Screen for one of your contacts, say Lucy Lastic for example, do the following:
     
  Click the User-defined 1 field
  Choose Lucy Lastic from the list of choices
     
    You will now see that the User-defined 1 field says GREEN WORM.

This happens because a search is made on the Key column (Column A) for Lucy Lastic, and then the value from Column B (which we chose as the Formatting property) is displayed.
     
Top   More information about Sage®
    To link to a Sage® database, the Key Column is fixed as the Sage® Account number, such as ABC123. The Formatting property dictates which Sage® field is shown for the ABC123 account.

For example, to display the VAT number in User-defined 1:
     
  Set the Appearance as Sage® Link
  Set the Formatting as VAT number
     
    In the Details screen, enter ABC123 into User-defined 1 and the VAT number will be displayed.
     
    Note Links to Sage® are live, which means that any changes made in Sage® are immediately reflected in our database.
     
Top   More information about Text files
    When linking to a text file (normally a CSV file), the Key Column is fixed as column 1. The first row of the file must contain the column names. The Formatting property dictates which column is shown.

For example, to display the contents of Column B (Code name) in User-defined 1:
     
  Set the Appearance as TextFile Link
  Set the Formatting as Column B
     
    In the Details screen, click on User-defined 1 and choose Lucy Lastic, and GREEN WORM will be displayed.
     
    Note Any changes to a text file must be saved before they will be reflected in our database.
     
Top   More information about Excel®
    To link to an Excel® worksheet, the Key Column can be any column and the Formatting property dictates which column is shown.

For example, to display the contents of Column B (Code name) in User-defined 1:
     
  Set the Appearance as Excel® Link
  Set the Formatting as Column B
     
    In the Details screen, click on User-defined 1 and choose Lucy Lastic, and GREEN WORM will be displayed.
     
    Note Links to Excel® are not live. Even if you make changes to a worksheet and save the changes, they will not be reflected in our database until you restart it.
     
Top   The need for SPEED!
    You've probably noticed that TextFile Link and Excel® Link both produce exactly the same results in the example above. You are right.

In favour of the TextFile Link are the facts that you don't need Excel® to be installed, and it's very fast at reading small files (less than 100k or so).

In favour of the Excel® Link are the facts that you can have any column as the Key column, and it's as fast at reading small files as large files, although it can take several seconds to initiate the link when you first read from a workbook.

Bear in mind:
  If you save a worksheet as a CSV file and use the TextFile Link option, then everyone will have access to the data and not just the people with Excel®.
  If you have a large CSV file and you open it in Excel® and save it as a workbook, it will be much faster and give you greater flexibility with the Key Column.