Mapping configuration¶

The external values can be matched to values from an existing TYPO3 CMS table, using the "mapping" property, which has its own set of properties. They are described below.

Properties¶

Property	Data type
default	mixed
matchMethod	string
matchSymmetric	boolean
multipleValuesSeparator	string
referenceField	string
table	string
valueField	string
valueMap	array
whereClause	string

table¶

Type: string
Description: Name of the table to read the mapping data from.
Scope: Transform data

referenceField¶

Type: string
Description: Name of the field against which external values must be matched.

Note

SQL functions may be used here. Example: 'CONCAT(first_name, \' \', last_name)'.
Scope: Transform data

valueField¶

Type: string
Description: Name of the field to take the mapped value from. If not defined, this will default to "uid".

Note

SQL functions may be used here. Example: 'CONCAT(first_name, \' \', last_name)'.
Scope: Transform data

whereClause¶

Type

string

Description

SQL condition (without the "WHERE" keyword) to apply to the referenced table. This is typically meant to be a mirror of the foreign_table_where property of select-type fields.

However only one marker is supported in this case: ###PID_IN_USE### which will be replaced by the current storage pid. So if you have something like:

'foreign_table_where' => 'AND pid = ###PAGE_TSCONFIG_ID###'

in the TCA for your column, you should replace the marker by a hard- coded value instead for external import, e.g.

'whereClause' => 'pid = 42'

Important

The clause must start with neither the "WHERE", nor the "AND" keyword.

Scope

Transform data

default¶

Type

mixed

Description

Default value that will be used when a value cannot be mapped. Otherwise the field is unset for the record.

Note

This is quite important when mapping MM relations. If an existing item has currently relations in the TYPO3 database, but not any longer in the data to be imported, the existing MM relations will not be removed if the field is unset. In such a case, make sure to use an empty string for the default value, as this will tell the DataHandler that it has to remove the existing MM relations.

Example

$GLOBALS['TCA']['tx_externalimporttest_product']['columns']['categories']['external']['base'] = [
     'xpath' => './self::*[@type="current"]/category',
     'transformations' => [
          10 => [
               'mapping' => [
                    'table' => 'sys_category',
                    'referenceField' => 'external_key',
                    'default' => ''
               ]
          ]
     ]
];

Scope

Transform data

valueMap¶

Type: array
Description: Fixed hash table for mapping. Instead of using a database table to match external values to internal values, this property makes it possible to use a simple list of key-value pairs. The keys correspond to the external values.
Scope: Transform data

multipleValuesSeparator¶

Type

string

Description

Set this property if the field to map contains several values, separated by some symbol (for example, a comma). The values will be split using the symbol defined in this property and each resulting value will go through the mapping process.

This makes it possible to handle 1:n or m:n relations, where the incoming values are all stored in the same field.

Note

This property does nothing when used in combination with the MM property, because we expect normalized data with one and denormalized data with the other. The chapter about mapping data hopefully helps understand this.

Scope

Transform data

matchMethod¶

Type

array

Description

Value can be "strpos" or "stripos".

Normally mapping values are matched based on a strict equality. This property can be used to match in a "softer" way. It will match if the external value is found inside the values pointed to by the referenceField property. "strpos" will perform a case-sensitive matching, while "stripos" is case-unsensitive.

Caution should be exercised when this property is used. Since the matching is less strict it may lead to false positives. You should review the data after such an import.

Note

It is important to understand how the matchMethod property influences the matching process. Consider trying to map freely input country names to the static_countries table inside TYPO3 CMS. This may not be so easy depending on how names were input in the external data. For example, "Australia" will not strictly match the official name, which is "Commonwealth of Australia". However setting matchMethod to "strpos" will generate a match, since "Australia" can be found inside "Commonwealth of Australia"

Scope

Transform data

matchSymmetric¶

Type: boolean
Description: This property complements matchMethod. If set to true, the import process will not only try to match the external value inside the mapping values, but also the reverse, i.e. the mapping values inside the external value.
Scope: Transform data

Examples¶

Simple mapping¶

Here's an example TCA configuration.

$GLOBALS['TCA']['fe_users']['columns']['tx_externalimporttut_department']['external'] = [
    0 => [
        'field' => 'department',
        'mapping' => [
            'table' => 'tx_externalimporttut_departments',
            'referenceField' => 'code'
        ]
    ]
];

The value found in the "department" field of the external data will be matched to the "code" field of the "tx_externalimporttut_departments" table, and thus create a relation between the "fe_users" and the "tx_externalimporttut_departments" table.

Mapping multiple values¶

This second example demonstrates usage of the multipleValuesSeparator property.

The incoming data looks like:

<catalogue>
    <products type="current">
        <item sku="000001">Long sword</item>
        <tags>attack,metal</tags>
    </products>
    <products type="obsolete">
        <item index="000002">Solar cream</item>
    </products>
    <products type="current">
        <item sku="000005">Chain mail</item>
        <tags>defense,metal</tags>
    </products>
    <item sku="000014" type="current">Out of structure</item>
</catalogue>

and the external import configuration like:

$GLOBALS['TCA']['tx_externalimporttest_product']['columns']['tags']['external'] = [
  'base' => [
      'xpath' => './self::*[@type="current"]/tags',
      'transformations' => [
           10 => [
                'mapping' => [
                     'table' => 'tx_externalimporttest_tag',
                     'referenceField' => 'code',
                     'multipleValuesSeparator' => ','
                ]
           ]
      ]
  ]
];

The values in the <tags> nodes will be split on the comma and each will be matched to a tag from "tx_externalimporttest_tag" table, using the "code" field for matching.

This example is taken from the "externalimport_test" extension.