Transform Map Scripts - Transform a value before its used for coalesce

[Vinicius Luz]

Hey Guys,

So I have a csv file being imported and I want to transform the field u_client before this value its used to find the target record to decide if that exists or not (coalesce).

It happens that some of the values coming on the u_client field on the csv file contains a "." (dot) and I need to strip it out before find values to coalesce on my cmdb_ci_computer.name table 

I tried the Onstart script but it seems like I cannot access the values of souce.u_client on this type of script.

And putting it to an OnBefore Script actually runs good, the problem is that the non-normalized value with the dot are being used to coalesce and then these rows are getting ignored.

Any ideas?

[Appanna M]

Hello @Vinicius Luz ,

Why can't you use source script to return the value for coalesce? Try once writing the script and check. 

 Please Mark My Answer as Helpful and Accept as Solution, if you find this article helpful or resolves your issue.

[Vinicius Luz]

I tried that and It didnt work at the first time, then I god MAD at that no-code-syntax field -  That thing doesnt even provide option to indentation.

Thanks for the help

[Vinicius Luz · Accepted solution]

Hey Yall, I found the mistake I was doing on the OnBefore script...

I was missing a source.update() line...

So when working with business rules, doing .update() on "before" scripts is a sin, dont do it! But when it comes to transform scripts OnBefore you can use it - Just note that I'll never have a register of what originally was sent to the instance on the csv cause I'm grabbing the received value -> changing it -> saving on import set field again...

Lessons learned:
OnBefore Scripts can be used to change a value BEFORE that value is used to COALESCE but you need to use source.update() after you set the new value.

Cons:
No register of the original received value - Sometime we like to have these import sets (stagging tables) to register what the system ingested

[Luu-Ly]

Hi, 

while your statement about having to use "source.update()" in the onBefore transform script is correct (if you intend to change an import set row field value before transformation), my recent debugging (with timed logs in the field map script used for coalesce as well as onBefore transform script) leads me to believe that the field map script on the coalesced field map executes BEFORE the onBefore transform script(s) actually...!

are you sure your use case was actually correctly solved by just using source.update() in the onBefore transform script? did the coalesce part really work with the updated source field value ? 

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