Need to populate the reference field value

[SNOW17]

In the Transform map Target table,

1. Field 'A code' based on that value we need to update A site field value.

 Site Code value available from location table. Based on that site code we need to retrieve the Name field value.

Can you provide me with any code or instructions to obtain this logic?

Thanks.

 

[AshishKM]

Hi @SNOW17 , 

In the transform mapping you can write the script and get the Site display value based on Code via glide query.

the location table must have code for each record, so you search the matched record and set the record sys_id to Site field ( because its reference field ).

write some code and share if you need more help.

-Thanks,
AshishKM

[SNOW17]

(function transformEntry(source) {

    function getSysIdFromLocationTable(codeValue) {

        var locationTable = new GlideRecord('cmn_location');

        locationTable.addQuery('u_site_code', codeValue);

        locationTable.query();

        if (locationTable.next()) {

            return locationTable.sys_id.toString();

        }

    }

    // Map the code field value to the Site reference field in the target table

    var codeValue = source.u_a_code; //  field name in your staging table

    var fieldMappings = getSysIdFromLocationTable(codeValue);

    return fieldMappings; // return the value to be put into the target fields

})(source);

Could you please review the code above and let me know if there are any errors or corrections needed?

[SNOW17]

(function transformEntry(source) {

    function getSysIdFromLocationTable(codeValue) {

        var locationTable = new GlideRecord('cmn_location');

        locationTable.addQuery('u_site_code', codeValue);

        locationTable.query();

        if (locationTable.next()) {

            var name = locationTable.name.toString();

            return {

                'Name': name

            };

        }

    }

    // Map the code field value to the Name field in the target table

    var codeValue = source.u_a_code; // Replace with the actual field name in your staging table

    var fieldMappings = getSysIdFromLocationTable(codeValue);

    return fieldMappings; // Return the value to be put into the target fields

})(source);
Could you please review the code above and let me know if there are any errors or corrections needed?