Transform Map "Copy empty fields" does not work with reference value fields

[Andreas B]

Hello,

I have a problem, that a referenced value field, in my case customer_account.account_parent does not get cleared if it is set in the Account record, but the source has NULL as the new value. How can I tweak the Transform Map to unset the field on the corresponding record?

I have tried using this Script but unfortunately it does not delete the reference field value:

answer = (function transformEntry(source) {
	if (source.u_parent_account) {
		return source.u_parent_account;
	}
	else
	{
		var gr = new GlideRecord("customer_account");
		gr.addQuery("identification_number", source.u_identification_number);
		gr.query();
		if (gr.next()) {
			gr.setValue("account_parent", null);
			gr.update();
		}
		return;
	}
})(source);

Thanks and regards,
Andreas

[Mark Manders]

The source gives 'NULL' as value in the u_parent_account field? In that case it will never go to your 'else', because there always is a 'u_parent_account'. 

How about: 

answer = (function transformEntry(source) {
    if (source.u_parent_account != 'NULL' && source.u_parent_account != '') {
        return source.u_parent_account;
    } else {
        var grCustomerAccount = new GlideRecord("customer_account");
        grCustomerAccount.addQuery("identification_number", source.u_identification_number);
        grCustomerAccount.query();
        if (grCustomerAccount.next()) {
            grCustomerAccount.setValue("account_parent", ''); // Clear the field instead of setting it to null
            grCustomerAccount.update();
        }
        return ''; // Return an empty string as a default
    }
})(source);

[Andreas B]

Hi Mark,
Thank you for your reply. That is correct, if the value of u_parent_account is NULL then the target field account_parent has to be cleared. In my understanding java script returns false at "if(fieldname)" if the value of the fieldname is empty, undefined or null. 

I tried your script, but unfortunately the ref field 'account_parent' still has the old value after running the transform. 

[Mark Manders · Accepted solution]

Just looking at it again, after I had coffee, I think this should work:

answer = (function transformEntry(source) {
    if (source.u_parent_account != 'NULL' && source.u_parent_account != '') {
        return source.u_parent_account;
    } else {
        return ''; // Simply return an empty string if the condition is not met
    }
})(source);

It's a transform script, so it needs to return the value for this field on this record, matched to the import set record. It's either the account, or empty, you don't need to do a GlideRecord in there, because the script isn't setting the value, it is returning the value it needs to set. And that's either the account or nothing.

[Andreas B]

Thank you, most helpful was the general information, that empty ('') is supposed to be used to clear the reference field and NULL does not work. I just went ahead and used COALESCE(u_parent_account, '') in my SQL query in the data source and it did the trick. Your script would also work even though the second if condition is uneccessary, so I will paste the corrected script here. May be you can correct it in your reply, I will Accept it as Solution:

answer = (function transformEntry(source) {
    if (source.u_parent_account != 'NULL') {
        return source.u_parent_account;
    } else {
        return ''; // Simply return an empty string if the condition is not met
    }
})(source);