Help with transform field map script

[Snowman15]

Hi all, 

I am struggling with a field map script, and could use some guidance from the community.

Due to limitations, the source spreadsheet columns cannot be modified, so I need to handle everything via scripting.

One of the source fields, source.u_softwaretype, contains values formatted like:
"GH001 - Software Not found"

What I need to do is:

1. Extract just the code portion ("GH001") from the text.

2. Perform a lookup in the x_software_list_code_mapping table where:

   • supplier = 'WILLIAMS'

   • code = 'GH001' (the extracted value)

3. From the matched record, retrieve the serial_number field value. 

4. Populate the target reference field with that serial_number field value.  

This is the script I tried so far but is not working:

(function transformEntry(source) {

var match = source.u_softwaretype.match(/(GH\d{3})/);

if (!match) return "";

var extractedCode = match[1];

var gr = new GlideRecord('x_software_list_code_mapping');

gr.addQuery('supplier', 'WILLIAMS');

gr.addQuery('code', extractedCode);

gr.setLimit(1);

gr.query();

if (gr.next()) {

return gr.serial_number;

}

return "";

})(source);

Thank you

[YaswanthKurre]

Hi @Snowman15 ,

Try adding logs inside your script and where it breaks, if target is a reference field , try returning "

return gr.getUniqueValue(); // returns sys_id".

Thanks,

Yaswanth

[Snowman15]

hi @YaswanthKurre thanks. Tried this but didn't work

[palanikumar]

Hi,

Sometimes you need to convert a field name to string to make it work. Try the below:

(function transformEntry(source) {

var match = source.u_softwaretype.toString().match(/(GH\d{3})/);

if (!match) return "";

var extractedCode = match[1];

var gr = new GlideRecord('x_software_list_code_mapping');

gr.addQuery('supplier', 'WILLIAMS');

gr.addQuery('code', extractedCode);

gr.setLimit(1);

gr.query();

if (gr.next()) {

return gr.serial_number.toString();

}

return "";

})(source);

[Snowman15]

hi @palanikumar thank you, unfortunately this didn't work either.