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.