Destination Table

This grid displays the fields that the import data can be mapped to.  The fields are described in the table below:

Field Name

Description

Name

This column indicates the field name in the appropriate Lucity table.  The fields highlighted in yellow indicate fields that are required and must have data mapped to them.

Caption

This column indicates the associated Caption that a user would see when in the related Lucity modules.

Mask

This column indicates the field’s mask (25x means that a field can support 25 characters, nnn means that a field will display only the 1st 3 digits of numeric data, etc.).

Field Type

This column indicates the data type of the field (Text, Date/Time, Long Integer, Double, Short Integer, or Boolean).

Formula/Value

This column indicates the data to be mapped to the associated field in the desktop application.

In order to map the data to the appropriate field in Lucity, the user must populate the Formula/Value column for the appropriate fields. There are four ways to accomplish this:

  1. Indicate a specific value that will be inserted for each record imported.  For example, indicating a specific date for all of your records by entering “1/1/2006” for the Date field.
  2. Map data from the Source File grid. This is done by typing “=A” and then the appropriate column number from the # field in the Source File grid.  For example, mapping a date for the records by entering “=A8” for the Date field.  This way, the date value for each record is inserted into Date field.    
  3. Use a formula/equation that your database platform can recognize.  This is done by typing “+” and then the formula/equation.  For example, indicating the current date for all of your records by entering “+Now()” for the Date field.  Now() is a formula specific to Access (Jet) databases, so if your database platform is Oracle, you would use “+SYSDATE” and for SQL Server you would use “+GETDATE()”    
  4. Use a combination of a formula and data mapping.  This is done by typing “+” and then the formula with the appropriate data mapping.  For example, if your import data contains data similar to “00066” but the data needs to be imported into a Text field as “66”, this could be accomplished by entering “+CINT(=A4)”.  CINT is specific to Access (Jet), so for Oracle you would use “+CAST(=A4 AS NUMBER(5,0))” and for SQL Server you would use “+CAST(=A4 AS INT)”  

    Note:  When specifying a field mapping into a formula it may be necessary to include formatting syntax.  For example, if your import data contains data similar to "01234", and you only want the first two characters, you'd need to include single quotes around the field mapping designation.  For Access, your statement may look like "+LEFT('=A4', 2)".

The Test and Close buttons are used in the following manner:

Button

Purpose

Test Equation

You can test Equations/Formulas/Mappings by entering them into this field and clicking the Test button.  The value entered should be identical to a value you would indicate in the Destination Table grid for the Formula/Value column. Please note that if testing data that will be mapped to a date field, the Test may be inaccurate as date data goes through several formatting checks during the import.

Close

Clicking this button will take you back to the Lucity Import screen.  If you wish to save any modifications to the Field Mapping form, either press Save or Save As when you are returned to the Lucity Import screen.  If you do not save your changes, your changes will be used for the current import, but will be lost for future imports.

See Also

Field Mapping Button

Source File