The Free Form Importer allows you to import from the following types of data files:

• dBase
• Excel
• Foxpro
• Access
• Text

The tool has the following screen:

 

Selecting the data source

The top portion of the screen describes the data source.

Field	Meaning
Type	The type of data file from the list above
Source	Either the directory or file name. Click on the button to navigate to the directory or file
Table	The specific table to use
Skip	Number of rows to skip, 1 if the first row in the table contains field headers
DB ID	The unique number for this table

 

Mapping

Once you have selected a table, the fields for that table will be displayed in the mapping area. You map each column wanted to the equivalent eCandidus™ database field by entering the field definition in the column to the right of each field. Note that you can drag the field definition from the appropriate info button if you display any case.

 

Selecting the ID source

eCandidus™ assigns each case (or work product) a unique identifier which is 32 characters in length. Under normal processing, this identifier is generated by the system as a random unique identifier.

When you are importing, you may need to run the import function more than once or obtain information from more than one table for each case, so a random identifier does not work.

What the import tool does is generate an identifier from a fixed source, either the row number of the table or a specific field in the data. You designate which field to use to generate the identifier by clicking on the ID radio button for the field.

 

Special Processing

The import tool has built-in logic to modify the input data as it comes in. These are described in the Handling box.

Operation	Meaning
Do translate	Translates the values using the translation tables
Remove text in parenthesis	Removes any text inside parenthesis
Trim	Removed leading and trailing spaces
@	Executes a set of formatting options derived from the merge codes

 

Translation

The translation section describes the original text and resulting text. In this example any text in the Type field that reads WC will be converted to Workers Compensation

If no match is found, the text is left as it.

 

Notes

Fields that contain notes can be processed in the following formats:

Option	Meaning
Not a note	Field is not a note field
Single note	Content is treated as a single note, dated 1/1/1980 and created by the System Administrator
Date delimited	Notes are delimited by a date at the start of the row of text
FMP	Treat the field as a FileMaker Pro date or note area. Once both the note and date area are found, the import tool will break the notes accrding to the date field

 

Phones

Fields that contain phone numbers can be processed in the following formats:

Option	Meaning
Not a phone number	Field is not a phone number field
Text after #	Comment text appears after the phone number
Text before #	Comment text appears before the phone number

 

Parsing Addresses

The eCandidus™ address book has fields for the street address, suite, city state and ZIP codes. If youer data source keeps these as a single field, you can use the address merge code as the target.

You can see this in use as any of the address fields in the first screen above.

This will take an address like:

1234 Here St Apt 100 Gotham City 00012

and break apart the text into:

• 1234 Here St - Address line
• Apt 100 - Suite
• Gotham City - City
• 00012 - ZIP code

 

Fixed Values

The import map allows for the entry of fixed values by entering text in the source column:

 

Using a data colum as a data source

If a data column is not impoeted directly but used as a data source for computed fields, you can use a blank target field with the option to save the value:

 

Computed data source

You can use the [#...] merge code in the source column to create a computed source:

 

User defined fields as targets

In the example above, we used a user defined field as the target. You can create any number of fields and import data into them.

 

From many tables...

If you are importing from nay tables, let say you have your open cases in one spreadsheet and the closed in another, make sure that each source has a unique DB ID. This will assure that the unique number assigned for each entry will be different for each table.

 

From many tables into one case...

In those cases where the data for one case comes from many table, make sure that the DB ID is the same for all tables.

In these cases, do not use the Row # as the ID, but use a field that contains the case identifier, like case# in the table.