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

Siyou Li · ‎02-27-2023

Hi,

We need more information to investigate this issue. Please feel free to create a case task with support team.

Thanks

Siyou

blakemolmen · ‎02-27-2023

Thanks for the reply @Siyou Li . Is there any documentation (outside of this post). To show that this is a feature and how to use it? I cant find any mention of this outside of this post. I want to ensure that If I open a case that I can provide evidence that this is not working as the documentation describes.

Siyou Li · ‎02-27-2023

I cannot find any documentation for this. We will log this documentation improvement request. But I do see this item logged in customer support inside document. Thanks

blakemolmen · ‎05-22-2023

Documentation can be found here:

KB1122071:

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB1122071

VaranAwesomenow · ‎05-23-2023

we were able to successfully implement the fix and it worked, but it requires adding <EMPTY_STRING> for each column and generate a secondary column so its very tedious to do in Integration Hub ETL, a hack is to apply this in robust transform directly which worked with minimal effort.

Checkout Varan awesomenow youtube channel for more learning videos!