Mapping Tab

The Mapping tab enables users to link fields from the data source to fields in Lucity.

Source Grid

The Source grid lists all of the fields found in the source data and displays examples of the data in those fields.

#

Supplies an identifier for each field. This number is used to identify a field during the mapping.

Field Name

Specifies the name used by the data source for the field.

Value (A)

Provides an example of the field contents. The data displayed comes from the first record in the data source.

Refresh

Refreshes the Source grid. Click this button if the data source has been changed in any way since the last time the Settings record was saved.

Destination Grid

This grid displays a list of all the properties found in the Lucity module and enables users to link those properties to a field in the Source grid.

Lucity properties often indicate a specific field; however, this isn't always true. Sometimes, a property represents multiple fields in Lucity. For example, the Street property links to the XX_ADR_STR field, however the Street property actually maps the entire street value. The import automatically splits the data between the different address fields.

Property

Indicates the alias name for the Lucity field.

Table

Indicates the name of the Lucity table into which this information will be imported. Not every part of the import data will be imported into the same table.

Example: You are importing a selection of Equipment data. One column of that data stores a path to the location of a pdf copy of the user manual for the equipment. When the data is imported the equipment data is stored in the equipment table but the path to the document is stored in the document table and linked back to the equipment record.

Field(s)

Specifies the name of the field within the destination table.

Caption

Displays the caption set in the software for the field. This makes it easier to identify fields.

Mask

Shows what type and how much data the field will hold.

Value

Displays the value that will be imported for the first record in the source data based on the formula.

Source Field *

Shows the name of the Source data field you mapped to if you mapped directly to the data.

Formula *

Enables users to enter a formula for what should appear in the Lucity field. Additional details are provided below.

Test Formula

Enables users to enter a formula and see a preview of the results. This allows users to see exactly what would be inserted into the Lucity database if that formula was used. If the formula includes data from a data source, the system will pull the test data from the first record in that data source.

* Hidden by default.

Show/Hide Columns

Formulas

Formulas tell the Import program exactly what to import into Lucity. A formula could be as simple as "This source data goes in to that Lucity field." Or, it could outline more complex operations like formatting, data conversion, etc.

Note that if the first character is a plus sign (+), the system expects a function, and strings containing =A and a number are interpreted as column mappings.

When a formula contains a plus sign (+) as the first character, the Import and Update tool generates a SQL statement and passes it to the underlying Lucity database platform to resolve the formula. First, the system replaces the mapped values in the formula with the data from the import file, removing the +; then, it issues a SELECT statement with the resulting value.

For example, if a user specifies a formula of +CAST('=A1' AS VARCHAR), and the mapped data for A1 is Test, the system issues the following SQL statement: SELECT (CAST('TEST' AS VARCHAR)) AS TEXTVAL

Simple Functions

Map Source Data to Lucity

 

Click on a value in the Source grid and drag it to a related Formula field in the Destination grid. Or, in the Formula field in the Destination grid, type =A+#. (The # refers to the first column in the Source grid.)

  • The end result will look like =A4.
  • All data from the fourth row of the Source grid will be imported into the related Lucity field.

Insert a value into a Lucity Field
  • Click in a Formula field in the Destination grid. Type "2" or "1/1/2013" or "Bill".
  • The Lucity field will be populated with anything between the " ".

Perform a simple calculation
  • Click in a Formula field in the Destination grid. Type +(=A3-=A2) or +(=A3*=A2) etc.
  • The results are populated in the Lucity field.

Advanced Functions

Force the Database to Insert the Date
  • SQL Server: +GETDATE()
  • Oracle: +SYSDATE

Convert Data Types

To convert the text data ‘00066’ into the numerical value ‘66’:

  • SQL Server: +CAST('=A4' AS INT)
  • Oracle: +CAST('=A4' AS NUMBER(5,0))