Importing Data

TBS has a built in full featured import function that is accessible typically by administrator logins

Because importing data can be quite a complex function where knowledge of file associations can be required it is usual to keep access to the import function typically to selected users only

Go to System Administration -> Import Data :

In this screen - the top list is for your named import formats - or the 'layouts' of the text files you may want to import.

The lower list contains all of the table columns required to complete this import split into the 'visible columns' (left hand list) and any other columns on the right hand list - which may include columns not referenced in the actual import but which may be required such as foreign key fields etc

Note : if the mention of foreign keys has no meaning to you this is why it is an administrators function only !!

Selecting any line from either list will display information that we are holding about how to deal with that column in the import routine :

In the example shown here, the first visible column we are expecting to see in our import spreadsheet is the column called 'Stock Description' which we have said is to be used as a 'Match Field' i.e. we will use the contents of this column to 'match' to an existing entry in the Stock Descriptions table to find the correct entry to update (if we are updating data in this import as opposed to adding new records)

The second entry in the Import File Columns list therefore becomes the column which will be updated in this example

Note : the first column showing in the right hand list (fsdesc_ID) will show as the 'Primary Key' of the stock descriptions table, and we will always need to have a primary key column available for any table to be updated successfully

Creating a new import entry

To create a new import entry use the New button of the top list to create an import header entry :

After saving the name you wish to use for the new format, you are shown an empty lower entry area.

Although you can add each column individually, the 'easiest' way to populate your lists here is to click the checkbox to the left of the Table Name field, this indicates to TBS that whichever table you then select should simply add ALL columns of that table to the Selectable columns list.

If your import is for columns which are all from one table then you simply need to move the required column titles from the right hand list to the left hand list by highlighting each column in the order they are saved in your spreadsheet one column at a time and using the

Once your Import File Columns list matches the spreadsheet you wish to import (and in this case we are importing a file with 3 columns : Client Name, Address and Zip Code - we wish to match existing records by Client Name (as shown) and therefore to update the contents of the Address and Zip Code columns of those found records....) you can import the file by clicking the Import File button of the top list - this asks for information about your import file :

Here we tell TBS what we are trying to do : to update found existing entries or to add new ones - the default being to add new entries. We also tell TBS if the first record it will find in the file is a row of column titles or real data - the default being to start with raw data. Then we have a choice of three input file formats : Tab Delimited text, Comma Delimited text (also known as CSV 'Comma Separated Value'), or Omnis Data transfer Format. And finally, we can tell the importer to start importing from any given line number of the file (useful if you hit an error midway through an import !)

Once you have told it information about your file, click on OK to go ahead and select your import file - then TBS will open the file and display the first few lines for you so you can verify that the columns line up (in case this was a pre-existing layout and the current import file has a different/changed column order for example) :

If all columns appear to be matching up, then clicking on OK will go ahead and run the import for you

Importing with multiple tables

If the import includes data from more than one single table, this is where life gets trickier ! You have to supply the relevant information to allow TBS to link the related tables together and this is done by using lookup fields of the foreign table allied to the foreign key - this information does not necessarily have to be from the visible columns of the import however in practice it usually is

Typically you will find information about related tables in the final few columns of your main chosen table. Looking at the example we have here, going to the bottom of the 'Selectable Columns' list you will see various 'ID' fields which are all foreign keys which link a clients entry to various other tables such as : Client Type, Client Sub Type, Credit Terms, Tax rates etc

So if we wanted to update the Tax Rate Description of selected clients information, we would have a column in our import spreadsheet for the Tax Rate Description. To add this to our import list, we would want to add a 'New' column entry in to our lower screen, but without the need to add the entire Tax Rate table entries, we should not click the checkbox to the left of the table name field, but merely choose the relevant table from the dropdown list (in this case 'Tax Rates') and with the table selected, now the Column Name list will contain all the columns of our selected table for us to select from :

Here we would then want to add the column as a 'Match Field' where you will see that this then also adds the known information that this is a link to the taxrates table (i.e. this allows any looked up record of the taxrates table to be set as the appropriate foreign key column in our main import record.)

Once we have saved our new column entry, then we simply highlight it and move it to the left hand list to indicate that it is the fourth column in our import spreadsheet

Now following the same principle as above for importing the file, this time it would include updating the client entries with any related tax rate dsecription links which may match the given information

Note : if you have columns in your import file which you wish to be ignored for this import, then create a New column entry but with no table name selected and just a hand written column name - any such columns are ignored by the importer

Note : You can only have up to a maximum of 4 additional / linked tables in addition to your main table being updated in any one import in TBS. If you have more than 4 related tables you would need to split your import into 2 (or more) separate imports with each import using different related tables (and by making the columns which relate to unwanted columns for this import set as mentioned above as ignored columns)