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

[Siyou Li]

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 · Accepted solution]

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.

[DH7]

Hi @Siyou Li,

 We are also facing the same issue, we did try the implementation approach for the San Deigo that you provided, but when we try to do the same in Quebec and Rome, we are getting "<EMPTY_STRING>" as a value instead of empty.

As you can see in the below image, for Quebec we are getting "<EMPTY_STRING>" instead of actually getting empty.

do you know any alternative approach for the same which can work on all the Quebec, Rome, and San Deigo versions of ServiceNow?

Thanks

[Siyou Li]

Hi,

As mentioned in the solution, this feature is only supported starting from San Deigo, so it is expected that it's not working in Quebec or Rome release.

For Quebec and Rome release, IRE does not support updating records to empty values. Users need to use methods other than IRE+IH-ETL. Alternative could be custom scripts updating.

Thanks

[blakemolmen]

Hello @Siyou Li 
We recently upgraded to San Diego (patch 7) and I am trying to take advantage of the change regarding the <EMPTY_STRING> value to clear out fields with our SCCM Service Graph Connector Import. I am setting the <EMPTY_STRING> in the "Last Logged In User" field which is a reference field when the import value is empty or when we can not locate a matching user record in servicenow. Passing <EMPTY_STRING> does not seem to be clearing out the value, it seems to be ignoring it just like a blank value. I am not able to locate any other documentation on this to make sure I am using it correctly. Any advice? This does not seem to be working for me even though I can see the <EMPTY_STRING> value being passed via the transform into the field that gets mapped to the computer table.