Advanced Scenarios

The Import API supports a few advanced Import scenarios.

Replace Existing Codes

As codes are usually used to uniquely identify records, a special mechanism to update and replace codes on existing records is required. To support this scenario, a fallbackInput field can be added to an Import Schema.

Consider the following SectionRename custom Import Schema that allows updating and changing Section records, including their codes:

entities:
- entity: Section
  input:
  - code
  - newcode
  - description
  mappings:
  - id:
      converter:
        input: code
        type: CodeReferenceLookup
  - code: 
      input: newcode
      fallbackInput: code
  - description:

Notice that the mapping for the code property now includes a new fallbackInput property as well as a definition for a input property.

When no value is specified for the input property, the Import API uses the value of the fallbackInput as the actual input value. In this specific example, it indicates that if the newcode property is not provided for a Section record, the code property should be used. This allows us to change the code of existing Section records using the newcode property and to update the values of any other Section record without affecting its code by simply leaving their newcode properties empty.

Given the schema above, the following Import File illustrates how to rename and update Section records in the same file:

!SectionRename,,,,
#Entity,Action,*code,newcode,*description
Section,Update,Sec1,,Sec1_0
Section,Update,Sec2,XSec2,XSec2_0
Section,Update,Sec3,,Sec3_0
Section,Update,Sec4,,Sec4_0
Section,Update,Sec5,XSec5,XSec5_0
Section,Update,Sec6,,Sec6_0
Section,Update,Sec7,,Sec7_0
Section,Update,Sec8,XSec8,XSec8_0

Notice that the code property for the Section records on lines 2, 5 and 8 will be updated using their newcode properties. For all the other Section records the code stays unchanged, but the values of their other properties are still updated.

Identify Records Using Id's

In some Import scenarios it is very difficult/impossible to uniquely identify records using Code or Custom Reference Lookup Converters. Some examples include identifying Asset and detail related records within a complex Asset hierarchy. To support these more complex scenarios, an Id fallback mechanism can be used.

When an Id fallback is added to a schema, the Import API will check to see whether a value has been specified for the Id fallback property. If a value has been specified, it will use this value instead of trying to resolve the Id using the Schema Converters specified.

To illustrate, consider the following extract from the mapping section for the system generated schema of a Regular Asset Task:

entities:
- entity: RegularAssetTask
  input:
  - id
  - code
  - componentCode
  - assetCode
  - assetNearestRegularAssetCode 
  ...
  mappings:
    - id:
        input: id
        converter:
          properties:
            - RegularAssetTask->Code:
                input: code
                nullIndicator: true
            - RegularAssetTask->RecordType:
                input: Regular
                inputType: Constant
            - RegularAssetTask->Component_Code:
                input: componentCode
            - RegularAssetTask->Asset_Code:
                input: assetCode
            - RegularAssetTask->Asset_NearestRegularAsset_Code:
                input: assetNearestRegularAssetCode
          type: CustomReferenceLookup
    - allowConditions: 
    ...

Notice that in the mapping section, the id mapping now has an input in addition to a converter specified. The id is now also listed in the list of input fields in the schema.

If data within an Import File for a Regular Asset Task contains a value for the id column, the value of the id will be used to identify the record to action. If the id is not specified, the combination of properties required by the converter will be used to try and identify the record to action.

Id fallback's can also be used to resolve any other reference. To illustrate, consider the following Id fallback to resolve the Regular Asset Task for a Regular Asset Task Spare:

entities:
- entity: RegularAssetTaskSpare
  input:
    ... 
    - regularAssetTaskCode
    - regularAssetTaskComponentCode
    - regularAssetTaskAssetCode
    - regularAssetTaskAssetNearestRegularAssetCode
    - regularAssetTaskId    
    ...
  mappings:
    ...
    - regularAssetTaskId:
        input: regularAssetTaskId
        converter:
          properties:
            - RegularAssetTask->Code:
                input: regularAssetTaskCode
                nullIndicator: true
            - RegularAssetTask->RecordType:
                input: Regular
                inputType: Constant
            - RegularAssetTask->Component_Code:
                input: regularAssetTaskComponentCode
            - RegularAssetTask->Asset_Code:
                input: regularAssetTaskAssetCode
            - RegularAssetTask->Asset_NearestRegularAsset_Code:
                input: regularAssetTaskAssetNearestRegularAssetCode
          type: CustomReferenceLookup
    - materialMasterId:
    ...

Notice that in the mapping section, the regularAssetTaskId mapping now has an input in addition to a converter specified. The regularAssetTaskId is now also listed in the list of input fields in the schema.

Note

Id fallbacks are automatically added to some of the more complex system generated Import Schemas

Import Geospatial Data in CSV Format

The different Geometry types supported by On Key can be imported in CSV format using their Well-Known text representation illustrated below:

POINT (30 10)
MULTIPOINT ((10 40), (40 30), (20 20), (30 10))
LINESTRING (30 10, 10 30, 40 40)
MULTILINESTRING ((10 10, 20 20, 10 40),(40 40, 30 30, 40 20, 30 10))
POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))
MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),((15 5, 40 10, 10 20, 5 10, 15 5)))