Staging Table choice field is not getting mapped to Target table choice field when blank space is provided.

[vivek dhasmana]

Hello All Members,

I just started learning ServiceNow recently and trying hard to understand a problem that I am facing.

I have a staging table setup using import set option(xml) and I am trying to update a choice field type in target table(sys_user)  via staging table as its not a good practice to update target table directly.

So I am using a onbefore script to update a choice field type to --NONE-- in staging table by below script when there is a blank space provided by the user in order to update existing choice field.The script is working fine i guess and I am able to see that staging table is getting updated to --NONE--, The real issue is this --NONE-- is not getting reflected in the target table field and the previous choice type is remain unchanged.

I am using custom field and defined the field in both target as well as staging table and configured the mapping using transform map too. I just want to know why the staging table choice field is not getting mapped to target table(Other fields are working fine and getting updated successfully,Its only the choice field).

Could somebody please guide what approach should be followed in this,Thanks in advance.I have attached all screenshots for reference.

var gr = new GlideRecord('sys_dictionary');
gr.addQuery('name','my staging table name');
gr.query();
while (gr.next()) {
var ele = gr.element;
var val = source.getValue(ele);
if (!gs.nil(ele)) {

if (val !== " ") {

source.setValue(ele,val);

}
else {

source.setValue(ele,"NULL");
}
}
}

[Tom Blanchard]

I'm not sure I fully understand what you are trying to achieve here, but you don't necessarily need to modify choice values on a staging table - generally I'd leave these all as string values anyway, as the transform map will handle setting the choice value on the target.

If you're trying to create choice values on the target table dynamically, you can set the "choice action" on the field map for that field (https://docs.servicenow.com/bundle/kingston-platform-administration/page/script/server-scripting/task/t_CreatingAFieldMap.html). Set this to "create" to automatically create new choice values.

If you're trying to set the choice field on the target table to -- None --, the underlying value won't be "-- None --", but will be null or "". If you want these to be copied, you need to set the "Copy empty fields" checkbox on the transform map to true. Note that this applies to all field maps however. To do this for a single field, I've seen this in the past achieved with an onAfter transform script

target.field_name = "";
target.update();

[vivek dhasmana]

Thanks for the reply,Actually I posted the wrong script above, I have edited the original script as below which is not working for target to clear a choice field.

var gr = new GlideRecord('sys_dictionary');
gr.addQuery('name','my staging table name');
gr.query();
while (gr.next()) {
var ele = gr.element;
var val = source.getValue(ele);
if (!gs.nil(ele)) {

if (val !== " ") {

source.setValue(ele,val);

}
else {

source.setValue(ele,"NULL");   // If i change it to target.setValue(ele,"NULL") then its working
}
}
}

Yes "Copy empty fields" checkbox is clearing out all the field values on target so it won't work for each field, also  you are suggesting 

target.field_name = "";
target.update();

but won't this be modifying the target table directly? I want to first change the field in staging table so that target is mapped from there because SN won't allow to update target directly as far as I know or its not a good practice atleast , correct me if I am wrong.

Also can you please let me know where can i include this onAfter  script that you are suggesting, I want to clear only custom defined field(u_team) in my case if I pass single space"" accidently or to reset the target field.

[Tom Blanchard]

Yes you are modifying the target directly in the transform map, ultimately this is the purpose of the transform map. If it can be done using field maps it is better practice, but it cannot always be avoided. As far as I know this is the only way to set a single field to null.

I don't think this actually has to be an onAfter script (which can be created in the Transform Scripts section of the transform map). On the transform map, tick "Run Script" and put your script there:

[vivek dhasmana]

Tom,

The approach that you have suggested is working fine I have tried and tested it. However I am looking out for some solution by which we can change the source field and not target field as the above approach is still making changes to the target table (target.u_field="") which is violating the purpose of transform map(which maps staging table fields to target table fields).

Please let me know if you happen to remember any other way of doing this and thanks for the alternate solution.