Tranform Map - Field Map Reference to multiple fields

[wadeb]

Hello,

I am setting up a Transform map to map user fields.   One field is a reference to the Locations table.   My problem is that the field I need to use in the reference field is not unique in the locations table.   I need to have it reference locations where the reference field matches and when another field is true.     I see I can reference to one field and that I can use script on the source side.   But I cannot see how to do any calculations on the Target side.   Any help will be greatly appreciated.   Thanks.

What I want to do in T-SQL pseudo code:

select * from Locations where LocationName = User.Location and LocationActive = true

[nitin_kumar]

Hi Wade,

Try using a script like

if(source.location == target.location.name && target.location.state == true){

ignore = false;

}

else{

ignore = true;

}

Thanks,

Nitin.

Mark Correct, Helpful or Like based on the impact of the response

[wadeb · Accepted solution]

@Nitin thanks for you reply.   It got me thinking about Field Map Source Script differently.   Your suggestion didn't really work in my situation.   I worked with it more and got a version to work as I wanted to.   I post it here in case its useful to others.

Field Map - "Source Script"

// Returns only a matching Location's Sys_id where the Location is of the correct type

answer = (function transformEntry(source) {

      var locRecord = new GlideRecord('cmn_location'); //Table

      locRecord.addQuery('u_code', '=', source.u_office); //Where Clause

      locRecord.addQuery('u_custom_type', '=', 'True'); //Where Clause

      locRecord.query();   // Issue the query to the database to get relevant records

      answer = -1;

      if (locRecord.getRowCount() > 0)

      {

              while (locRecord.next())

              {

                      answer = locRecord.sys_id;

              }

      }

      // Add your code here

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

})(source);