IH-ETL field mapping with empty string value does not update corresponding record column value to empty

Siyou Li · ‎05-03-2022

Scenario:

1. In IntegrationHub ETL field mapping, we have a user input field X which contains empty string value

2. Currently, "cmdb_ci_linux_server" table exists linux server Y record that has field "short_description" with value "test description" and field "manufacturer" which has value references "core_company" record "IRIS"

3. Now inside IH-ETL field mapping, map user input field X with empty string value to linux server class' "short_description" field and "manufacturer" field. Also, make sure this field mapping will target updating linux server Y

Expected Result:

After running transform process, linux server record Y's "short_description" and "manufacturer" fields should be updated to empty because both fields were mapped to user input field X which contains empty string value

Actual Result:

Both "short_description" and "manufacturer" fields are not updated

Siyou Li · ‎05-03-2022

Root Cause:

Before San Diego release, IRE does not support robust transform mapping using blank or empty string mapping to update a record's column value to empty. Below are some common scenarios:

1. Update a non-reference column value to empty using blank/empty string value -> IRE will skip this update.

2. Update a reference column value to empty using blank/empty string value -> IRE will skip this update

3. Update a reference column value using invalid value or value that does not have corresponding referenced record -> IRE will update reference column to be empty

Solution:

Starting from San Diego release, IRE has a new feature to address this issue. When user wants to update a field to blank/empty, we only need to map corresponding field with keyword "<EMPTY_STRING>".

For example, in IH-ETL "prepare data" step, we can create a script operation to generate a column with "<EMPTY_STRING>" value there are fields that need to be update to empty. Or user can prepare a column with "<EMPTY_STRING>" keyword in their data source. Then if this user input field with "<EMPTY_STRING>" keyword is mapped to target class columns, corresponding class columns will be updated to blank/empty if IRE successfully identified the record.

Please note that if the class being updated has IRE reconciliation rule associated, then we need to add the columns to the "update with null" list in reconciliation rule config, so that IRE will allow these columns' value to be cleared by above key word.

If value is still not updated, please check other common error scenarios listed in this post.

View solution in original post

VaranAwesomenow · ‎02-22-2023

@Siyou Li thanks for the solution.

Any plans to fix this issue in Tokyo or Utah to have a more cleaner solution. Also can you please confirm if it works in Tokyo / Utah versions

Checkout Varan awesomenow youtube channel for more learning videos!

Siyou Li · ‎02-27-2023

Hi Varan,

This feature will work starting from San Diego release onwards.

Thanks,

Siyou

scottl · ‎05-13-2025

Sorry but I'm not accepting that, the root cause is actually a poor implementation by SN, as a blank value in a payload is a valid value. Just look at the transform script to return a User, it returns "" when a user is not found. It should return NULL or boolean false, not blank. Imagine having to do this each time, for any blank values. Unreal.

This really does sum up the state of SN with their "new approach" at locking down and centralising integrations in-order to nickle and dime customers, and this "solution" is not even documented, and has a KB article hidden behind HI support that most cannot see, after implementing a poor technical approach.

And today, 3 years later has this even been addressed in any of the recent updates?