Use CVL as mapping when transforming data
In inriver, there is no existing functionality to set data in one field based on another field automatically. This requires an extension that listens to events on the source field and updates the target field.
One normal situation for that is to concatenate two fields into a third. Another scenario is that we get a color name from a source system and import inriver. This color could be called "Midnight blue", but that color often needs a more general color description, a color group.
The process for doing this manually could be to set up workarea with entities that have color name set but not yet a color group. Then a human would read the color name and decide which group or groups it should belong to.
Automate using mapping CVL
Think of the scenario above where we get a color name from a sourcing system. By adding a mapping CVL between the color name and the color group we could automatically set the color group based on any color name.
By creating a CVL which isn't used by any FieldTypes we can perform a secure mapping between source and target value.
CVL Id: ColorNameColorGroupMapping, DataType: String
- Key: Light blue Value: Blue
- Key: Midnight blue Value: Blue
- Key: Dust pink Value: Pink
By doing this we can perform a many-to-one mapping.
In the integration to the source system, or in an EntityListener we can listen to when the field holding the color name is set or updated. If so, we check in the ColorNameColorGroupMapping CVL if there is a key with the color name. If we find a CVL Value with that key, we can take the value and set it on the color group field.
public void EntityUpdated(int entityId, string[] fields)
{
if (fields.Contains("ItemErpColorName"))
{
var itemEntity = Context.ExtensionManager.DataService.GetEntity(entityId, LoadLevel.DataOnly);
var erpColorNameField = itemEntity.GetField("ItemErpColorName");
if (erpColorNameField.IsEmpty()) return;
var erpColorName = erpColorNameField.Data.ToString();
var cvlValue = Context.ExtensionManager.ModelService.GetCVLValueByKey(erpColorName, "ColorNameColorGroupMapping");
if (cvlValue == null) return;
var colorGroupField = itemEntity.GetField("ItemColorGroup");
if (colorGroupField.IsEmpty())
{
colorGroupField.Data = cvlValue.Value?.ToString();
try
{
Context.ExtensionManager.DataService.UpdateFieldsForEntity(new List<Field> { colorGroupField });
}
catch (Exception ex)
{
Context.Log(LogLevel.Error, $"Could not update Color Group on {itemEntity}", ex);
}
}
}
}
Populate non-mapped value
One strength of this method is that you can automatically update empty values by working with your mapping CVL. You can add a CVL Listener that searches for entities with empty color groups but where there is a color name.
public void CVLValueUpdated(string cvlId, string cvlValueKey)
{
if (cvlId == "ColorNameColorGroupMapping")
{
var entitiesMissingColorGroupQuery = new Query
{
Criteria = new List<Criteria>
{
new Criteria
{
FieldTypeId = "ItemErpColorName",
Operator = Operator.Equal,
Value = cvlValueKey
},
new Criteria
{
FieldTypeId = "ItemColorGroup",
Operator = Operator.Empty
}
}
};
var entitiesMissingColorGroup = Context.ExtensionManager.DataService.Search(entitiesMissingColorGroupQuery, LoadLevel.DataOnly);
if (entitiesMissingColorGroup.Any())
{
var cvlValue = Context.ExtensionManager.ModelService.GetCVLValueByKey(cvlValueKey, "ColorNameColorGroupMapping");
if (cvlValue == null) return;
foreach (var itemEntity in entitiesMissingColorGroup)
{
var colorGroupField = itemEntity.GetField("ItemColorGroup");
if (colorGroupField.IsEmpty())
{
colorGroupField.Data = cvlValue.Value?.ToString();
try
{
Context.ExtensionManager.DataService.UpdateFieldsForEntity(new List<Field> { colorGroupField });
}
catch (Exception ex)
{
Context.Log(LogLevel.Error, $"Could not update Color Group on {itemEntity}", ex);
}
}
}
}
}
}
Disclaimer: The code above is just an example and will not work directly.
Discussion
Using this pattern allows the end-user to use Excel export/import to work with mapping.
I my example above I'm using some basic assumptions that the data is stored as strings. In reality, it would be CVL fields, especially for the color group field. But the solution can handle any DataType, even LocaleString if you make the mapping CVL into a LocaleString.
-
Exciting ideas, and my brain can't stop thinking how this could be used even further with support from the platform. For instance: What if the platform could enable support for a field type settings where you point to a mapping CVL + a target field, and let the system automatically set a target value based on the mapping... Guess if that is added to the product backlog ideas internally :-)
Another thing that came up is that some mappings could be awfully large, and currently we do not really like tens of thousands of keys in a CVL (Color mapping could cascade into very large CVLs). So, firstly, to enable CVL type categorization in the system to allow different types of CVL to be collected in each category (the CVLs that are updated by extension or integration in one category, mapping CVLs in another, product attributes in a third and so on), to allow a better user experience in control center as well as different caching logic depending on the nature of the the CVL categories to optimize performance.
A third thing a user probably would appreciate is to use parent-child CVLs to create workarea hierarchies with queries pointing to the entities using the CVLs. In this scenario it would actually be a mapping between a query and a CVL that is automatically generated by the system... Yes, that is also in the backlog ideas, I should be tested through Innovation Labs (yes, Tobias, I know that you have already built this....)
Regards
Ulrik Viebke
Senior Business Solution Architect4 -
It's a great concept and I'm happy you're sharing it Tobias Månsson! We've used the same "CVL mapping" concept multiple times, the most "complex" for a customer getting color codes from their ERP/PLM system where we auto-populate several fields at once.
Example mapping CVL value:
Key: "881" (from the ERP/PLM)
Value: "MakeAWish|Pattern;Blue|PrintedPaper" (one-time mapping work in Excel adding the CVL values)
Which is split up into marketable-friendly the fields on the items:
Color name: Make a Wish
Color group (multivalue): Patter, Blue
Material type: Printed paper1 -
Yes, I can see some uses where we map our CVL field values into ETIM field values. This is currently done by hard coding within an HTML extension but I can build the mappings in a series of CVLs and not need to redeploy to change the mapping.
0
Please sign in to leave a comment.
Comments
3 comments