How to Modify data before inserting them into the target table using Transform Map Script.

Riya25 · ‎06-12-2020

Hello Experts,

I have started an integration with the external database,

When i have loaded the data into my staging table i found some of the data are in different format than the ones present in the Target table.

So,I want to modify the data .

For ex- at present in staging table my data format is "1234567891011....32" that is 32 alphanumerical data ,now i want to include "-" in between after 8th character,after 12th,16th etc.

Is it possible ?

if yes and then how?

Regards,

Riya

Jaspal Singh · ‎06-12-2020

Hi Riya,

Yes, you can. Considering you have created field mapping for the data to be transformed from staging table to target table you need to set 'Use Source Script' to True & use below script.

Ignore the Map, source table, field from below as its just an example. You need to use the script as below & also, map the field correctly.

Script:

answer = (function transformEntry(source) {
	
	
	var str=source.yourfieldname; //replace yourfieldname correctly here
	var addinghyphen= str.substring(0, 8)+'-'+str.substring(8,12)+'-'+str.substring(13,17)+'-'+str.substring(18,32);
	//suppose source is 12345678912345678912345678912345 32 characters target will be 12345678-9123-5678-12345678912345

	return addinghyphen; // return the value to be put into the target field

})(source);

View solution in original post

Ankur Bawiskar · ‎06-12-2020

Hi Riya,

I assume you must be having field map for this

you need to do string manipulation before you set the value in target

Try what Jaspal has shared

If my answer solved your issue, please mark my answer as ✅ Correct & 👍Helpful based on the Impact.

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Riya25 · ‎06-15-2020

Hello all,

Thank you for replying on this post.

I am able to modify the data using the information provided by you all.

Regards,

Riya