Oops!... I did it again. While I was testing out data sources, I was tampering some import set tables by setting some "string fields" to "reference fields." However, I noticed some extra data got created before the transformation, when loading data into them. With my raised eyebrows I was not expecting this so I thought I would share my findings. Reference fields are very useful to normalise and organise data. Sometimes reference fields can be too powerful. Bow down before reference fields.
Changing the field 'name' from type String to Reference
In this example I will show you what happens when you change the field 'name' from type String to reference fields to the sys_user table. I wanted to have the sys_id on the field, instead of the full name. If you need to replace ugly sys_id with the actual data "Display" values, or to normalize the data, or better relate your data, you may choose to set the field as a reference field. This is a very unusual case.
Normally, we would expect to import most string values as String types.
However, tables are flexible and you can customize some of those to be reference fields.
Misspellings when importing records
The problem is that if we have a simple spelling mistake like "Boris Catino X", the data load could create a new record or set the reference field to NULL.
This data is inserted on the "Load all data" stage and there is NO transformation map executed yet.
Here is the result of my testings:
Import data | Expected | Match Display valued | Result | Additional notes |
beverly campbel | Beverly Campbel | Yes | Sysid of matching record | Match is no case sensitive |
Billie Cowley | Billie Cowley | Yes | Sysid of matching record | |
Boris Catino X | Boris Catino | No | New record sysid | New record created as display value does not match. On some cases can return false |
You want to avoid the new records created by the reference fields themselves. Those records can cause confusion. If the records are not handled carefully, the loaded data could be set to null if there is no matching of the display value. This also applies if the data is passed for a reference field.
Using reference fields is very useful if you are importing accurate data, or the sys_id of the records directly. If the imported data is flaky, keep the fields as Strings. You can then use the transformation maps to gain control on how to process the data and when the new data is created.
More information here:
4 Comments
