Question: transformation for substructureFields field with child records

[bh-teufels]

is it possible to make a transformation for a substructureFields field with child records?

my data:

<equipments>
   <equipment>
      <code>airbag</code>
      <value>FRONT_AND_SIDE_AND_MORE_AIRBAGS</value>
   </equipment>
   <equipment>
      <code>alloyWheels</code>
      <value>true</value>
   </equipment>
   <equipment>
      <code>radio</code>
      <value>DAB</value>
   </equipment>   
[...]

my approach:

'xpath' => 'equipments/equipment',
        'substructureFields' => [
            'code' => [
                'field' => 'code'
            ],
            'value' => [
                'field' => 'value',
            ],
            'title' => [
                'field' => 'value',
                'transformations' => [
                    10 => [
                        'mapping' => [
                            'valueMap' => [
                                'DRIVER_AIRBAG' => 'Driver-Airbag',
                                'FRONT_AIRBAGS' => 'Front-Airbags',
                                'FRONT_AND_SIDE_AIRBAGS' => 'Front- and Site-Airbags',
                                'FRONT_AND_SIDE_AND_MORE_AIRBAGS' => 'Front-, Side- and additional Airbags',
                            ],
                        ],
                        'default' => ''
                    ]
                ],
            ],
        ],
        'children' => [
           [...]

i need to distinguish entries by code & value so for on record i have e.g.

<equipment>
      <code>airbag</code>
      <value>FRONT_AND_SIDE_AND_MORE_AIRBAGS</value>
</equipment>

for the other

   <equipment>
      <code>airbag</code>
      <value>FRONT_AND_SIDE_AIRBAGS</value>
   </equipment>

in my case i need to create / assign two different child records in my table "tx_myext_domain_model_equipment" how can i achieve that?

[fsuter]

It is currently not possible to apply transformations to substructureFields columns. I agree that it would be very nice and - reading your message - I'm starting to see how that could be made possible. But it won't happen right now, mostly for lack of time to start such a development.

Now in your case, I think there's something that should work:

1. declare an additional field (see https://docs.typo3.org/p/cobweb/external_import/7.2/en-us/Administration/AdditionalFields/Index.html), calling it "title" (unless you have another fields named "title", in which case you need to change the name)
2. every field needs a (initial) value, otherwise External Import will raise an error. In this case, we need to set some dummy value, that will be changed later on. So use, for example, field => 'equipment'.
3. apply the value map transformation directly in the additional field configuration instead of inside the substructureFields

Normally what should happen is that the "title" column will be filled first with a dummy value, then with the value from the <value> node when the substructureFields property is evaluated (this happens in the HandleDataStep). Then comes the TransformDataStep that applies the transformation to each value in the "title" column. Finally the StoreDataStep again refers to the substructureFields property for preparing the records to store in the DB, with the transformed value.

[bh-teufels]

First of all, a huge thank you for the always quick and competent help

this is what my configuration looks like now

$GLOBALS['TCA']['tx_tt3vehicles_domain_model_vehicle']['external']['additionalFields'] = [
    'mobilede_xml' => [
        'equipment_title' => [
            'field' => 'equipment',
            'transformations' => [
                10 => [
                    'mapping' => [
                        'valueMap' => [
                            'DRIVER_AIRBAG' => 'Driver-Airbag',
                            'FRONT_AIRBAGS' => 'Front-Airbags',
                            'FRONT_AND_SIDE_AIRBAGS' => 'Front- and Site-Airbags',
                            'FRONT_AND_SIDE_AND_MORE_AIRBAGS' => 'Front-, Side- and additional Airbags',
                        ],
                    ],
                    'default' => ''
                ]
            ],
        ],
    ]
];
$GLOBALS['TCA']['tx_tt3vehicles_domain_model_vehicle']['columns']['equipment']['external'] = [
    'mobilede_xml' => [
        'xpath' => 'equipments/equipment',
        'substructureFields' => [
            'code' => [
                'field' => 'code'
            ],
            'value' => [
                'field' => 'value',
            ],
            'equipment_title' => [
                'field' => 'value',
            ],
        ],
        'transformations' => [
            10 => [
                'mapping' => [
                    'table' => 'tx_tt3vehicles_domain_model_equipment',
                    'referenceField' => 'code'
                ]
            ]
        ],
        'children' => [
            'table' => 'tx_tt3vehicles_domain_model_equipment',
            'columns' => [
                'code' => [
                    'field' => 'code'
                ],
                'value' => [
                    'field' => 'value'
                ],
                'title' => [
                    'field' => 'equipment_title'
                ],
            ],
            'controlColumnsForUpdate' => 'code,value',
            'controlColumnsForDelete' => 'code,value'
        ]
    ]
];

I don't get any errors but no data records are created and therefore no assignment takes place

[fsuter]

It's hard to say what's wrong. Have you used the Preview feature to check what happens at each step if whether it corresponds to expectations or not?

[bh-teufels]

I have adjusted the names a little to make a distinction here and have left out the titles for now

$GLOBALS['TCA']['tx_tt3vehicles_domain_model_vehicle']['columns']['equipment']['external'] = [
    'mobilede_xml' => [
        'xpath' => 'equipments/equipment',
        'substructureFields' => [
            'codes' => [
                'field' => 'code'
            ],
            'values' => [
                'field' => 'value',
            ],
        ],
        'transformations' => [
            10 => [
                'mapping' => [
                    'table' => 'tx_tt3vehicles_domain_model_equipment',
                    'referenceField' => 'code'
                ]
            ]
        ],
        'children' => [
            'table' => 'tx_tt3vehicles_domain_model_equipment',
            'columns' => [
                'code' => [
                    'field' => 'codes'
                ],
                'value' => [
                    'field' => 'values'
                ],
                'type' => [
                    'value' => 'DEFAULT'
                ],
            ],
            'controlColumnsForUpdate' => 'code,value',
            'controlColumnsForDelete' => 'code,value'
        ]
    ]
];

in HandleDataStep and TransformDataStep i have the fields

 0 => array
 [...]
codes => 'abs' (3 chars)
values => 'true' (4 chars)

1 => array
 [...]
codes => 'airbag' (6 chars)
values => 'FRONT_AND_SIDE_AND_MORE_AIRBAGS' (31 chars) 

but in StoreDataStep they are gone I don't have an 'equipment' field in any of the steps

in StoreDataStep i have a tx_tt3vehicles_domain_model_equipment => array(empty)

[fsuter]

Since I don't have the overview of your whole code, I'm going to ask what may be a stupid question. I'm guessing that the import of equipment is separate from some other, main import. Is that right?

If that is the case, have you thought about importing the external primary key in your equipment import too (the column defined in "referenceUid", and which needs a column configuration too)? Otherwise, External Import cannot relate the external data to existing records.

[bh-teufels]

no, I thought that importing using 'children' records would also create them. So I don't currently have any 'equipment' records.

my next approach would be not to use a child record but to import it separately and then just do a mapping

[fsuter]

Reading your latest answer made me realize that you are missing one important element in your children configuration. You need to have a field which points to the parent record somehow. If you're using IRRE, you will have a parent field. This parent field needs to be mapped in the children configuration and have the special value "parent.id". See the example in the docs: https://docs.typo3.org/p/cobweb/external_import/7.2/en-us/Administration/Children/Index.html#administration-children

[bh-teufels]

okay, tanks so it works only for inline IRRE since many equipment records would be duplicated per data set, I don't have an IREE relationship but a selectMultipleSideBySide in this case, importing equipment first is probably the right way

I'll test that out

[fsuter]

Indeed yes. Children is essentially for IRRE, otherwise - as you guessed - you will have many duplicates.

I guess this could be better explained in the documentation.