Transform map scripting for a field with choices

[Lakshmi Prasann]

We have a requirement,while migrating data from source to target(Servicenow),Using data source and sql query,The source field have choices around 30 with dropdown,and the target field also have choices around 15..If the source field sends a value other than the choices in the target field ,we should return empty and that should reflect in the target field of the target table...Any help in this ??

We have 3 such type of fields and another two fields have only 3 choices,so we simply used If and else if conditions but for the 3rd field it have 30 choices

[Pavankumar_1 · Accepted solution]

Hi,

1. first try this you can select source and target choices as below and choice action select ignore so it will not create new values on target table.

If above not got expected then you can use 2 option.

2. on filed maps can use source script to complete this requirement.

check Use source script and select target choice field

Source Script:

answer = (function transformEntry(source) {
    
    var an = source.u_sourcechoice ;  //provide your source choice field
    var grch = new GlideRecord('sys_choice'); // go to the sys choice table
    // on choice table filter records with target table and choice filed and inactive false.
  grch.addEncodedQuery('name=incident^element=state^inactive=false');  //use your table and choice field
    grch.addQuery('value',an);  // compare value with source choice
    grch.query();
    if(grch.next()){
        an = grch.label ; // if choices available on choices table return label
    }

})(source);

To get Encodedquery refer below. copy query after you filter with your table choice field.

Hope it helps!!

Please Mark ✅ Correct if applicable, Thanks!! 

Regards

Pavankumar

[Pavankumar_1]

Hi,

If it resolves your issue please Mark Correct so it will be on the top search and will help to other users.

Regards

Pavankumar

[Pavankumar_1]

Hi,

Please close the thread by Mark it  ✅ Correct/helpful, if this solves your issue.

Regards

Pavankumar

[Lakshmi Prasann]

Thanks Pavan