Fixing corrupted custom fields

If the problem occurs on a Product, you can look up the specific product manually in the database to fix the problem. You need to be aware of the following, and pay close attention to every detail:

1. Custom fields are stored in the product_translation table, because they are translatable.
2. If a given registred custom field is either missing or define as "null", it might mean that it is inheriting its properties from a parent product.
3. You cannot safely delete the fields from a product, because some fields might be unregistered, and something might depend on those "secret" fields.
4. You can delete individual fields that are known to Shopware, and then simply fill them out again from the administration – this might be useful if you are not comfortable editing the database directly.

I would personally prefer custom fields are registred with Shopware, because then you can actually define their types and control their content from the administration. But, it is seemingly also possible to save entities (E.g. Products or Categories) with fields that Shopware does not know about – such fields will still be available when the specific entities are returned through the DAL or API, but they might not be usable inside the rule builder.

Why custom fields are corrupted

Custom fields will be corrupted when someone tries to manually change them within the database and they get the format wrong, but more likely, they will be corrupted doing import and update processes.

When entities (E.g. Products and Categories) are imported from .csv, xls, or other spreadsheet file formats, the files themselves might contain errors or missing important escapes for special characters. Creating these "backup" or "update" files might require special knowledge about escaping and compatibility with Shopware and formatting inside the specific file format that is being used. I don't personally have much knowledge in this area – outside of the most obvious things like escaping in .csv files – and in addition to this, the code that does the importing might also contain errors that messes up the formatting.

So, it can sometimes be difficult to say exactly where the problem occurs without thoroughly debugging it, and that process can take multiple days doing which you might make wrong assumptions, form wrong theories. Etc.

However, I got a tip for you; if the problematic fields are registered with Shopware in a field set, they can probably be changed from the administration. So, there is a way to get an idea about the right JSON formatting. Follow these steps:

1. Go to the database and make a backup of the corrupted custom fields on the *_translation table. E.g. On a specific product, category, or other type of entity that has a problem.

2. Remove the fields from the relevant *_translation table.
3. Go to your administration on example.com/admin and manually change the custom field data on the specific entity.
4. Finally, go back to the database and look how the data is now formatted (hopefully correctly).

After doing this, you might get an idea about how the formatting is supposed to be, and then you "just" need to find out what actually does the malforming of your custom fields so you can correct it!

Specific to the rule builder

Another problem I'd like to mention that is also related to custom fields in Shopware 6.5+, is a custom field with a "multi select" field might not to work properly with the rule builder when used with the "Is One Of" rule.

I had a scenario where the rule would simply not be triggered if multiple options was selected – I think this might actually be a problem or bug in the "is one of" rule itself, but I have not done the investigation to verify this.

The exact definition of a custom field is actually stored inside the custom_field table in the database, and this definition also needs to be right for it to work!

If you have a problem with multi select rules, then I suggest instead registering the options as separate fields rather than keeping them in a single multi select field.

The structure of a single field is of a "key": "stringValue" nature, while that of a multi select results in an array. E.g:

{
 "someFieldName": "SomeValue",
 "multiSelectFieldName": ["option1", "option2", "option3"]
}

In my case I verified that the correct data type (array) was outputted in the Storefront twig, but still the "Is One Of" rule was not triggered. That is, unless I have misunderstood how it is supposed to work!

Presumably, and intuitively, I would expect this rule to trigger an iteration over the options selected in the multi select field, and comparing with whatever string value specified. For some reason, this only worked if a single option was selected. E.g:

{
 "stockLocation": ["location1"]
}

If another "stock location" was selected, the rule would stop triggering, even when the specified location existed in the array.

Tell us what you think: