How to set an invalid choice value to (empty) during an import set?

[Phong Lo]

I have a field named Contract Status that contains the following choice list: Active, Completed, Cancelled, Terminated. There are instances where there are invalid values added to the spreadsheet (e.g. Canceled (with one L)). I tried two (2) scenarios but it's not the outcome I'm looking for:

1. In the Field Maps, the Choice action field is set to Ignore. When I load the data with an invalid value, two things are happening: if there is no value in that existing record, the invalid value will update to (empty), which is great! But my problem lies here... when an existing record has an existing value (e.g. Active) and the field in the spreadsheet has an invalid value (e.g. XYZ), the field isn't updated to (empty). It keeps the existing value (e.g. Active). I want the record to update to be (empty) instead. 

2. In the Field Maps, the Choice action field is set to Reject, but I want the record to be updated so this route doesn't work for me.

I'm not sure if I need to run an onBefore Transform Script? Sorry, very new to this. Any feedback would be appreciated. Can provide more context as well if this is confusing. Thank you!

Screenshot for reference:

[James Chun]

Hi @Phong Lo,

You can use onBefore Transform Script or Field Map with 'Use source script'.

Both would work but I would prefer using the Field Map's script.

Within the script, you can add your logic of using the source value if it's a valid choice, otherwise clear the target value.

Cheers

[Phong Lo]

I wrote the following code in the 'Use source script' but it's clearing all the fields to empty when I upload data. Am I missing something?

answer = (function transformEntry(source) {

    if (source.getValue() == "Active" || source.getValue() == "Completed" || source.getValue() == "Cancelled" || source.getValue() == "Terminated"){

        target.setValue(source.getValue());

    } else {

        target.clearValue();

    }

})(source);

[James Chun · Accepted solution]

Try the following:

answer = (function transformEntry(source) {

	var sourceStatus = source.getValue('your_source_field_name');
	sourceStatus = sourceStatus.toLowerCase();

    if ( sourceStatus == "active" || sourceStatus == "completed" || sourceStatus == "cancelled" || sourceStatus == "terminated") {
        return sourceStatus;
    } else {
        return '';
    }

})(source);

[Phong Lo]

That worked. Thank you!