Removing a referenced field value in a Transform Map.

[labandar]

Hello, 
I am having a Transform Map for a Medical Department Table it has a field  "head_doctor" which is of a reference type to User table. Now, my scenario is we have a record in Medical Department Table with head_doctor as "Francis" which is a referenced record of User. Now for the same record I am trying to load data with an empty head_doctor field and I want the "Francis" reference of User table to be removed for this record and not have any value.

I have already tried to return the value with empty string and I see that is not helpful and the reference is not being removed.

[Tai Vu]

Hi @labandar 

If I grasp it correctly, the head_doctor field will be populated only when the source is not "Francis".

You can try my approach below.

answer = (function transformEntry(source) {

    var grUser = new GlideRecord('sys_user');
    grUser.addQuery('name', '!=', 'Francis'); //replace your own query to filter out Francis user.
    grUser.addQuery('name', source.head_doctor); //replace your source field
    grUser.setLimit(1);
    grUser.query();
    if(grUser.next()){
        return grUser.getUniqueValue();
    }

})(source);

Or just simply check if the source head doctor is Francis.

answer = (function transformEntry(source) {
    //replace your head doctor field name in source table
    if(source.getValue('head_doctor') != 'Francis'){ 
       return source.getValue('head_doctor');
    }

})(source);

Make sure the choice action is set Ignore to avoid creating new user when the reference value doesn't match any User records.

Cheers

Tai Vu

[labandar]

Hello @Tai Vu 

I don't think you got it right, I don't want to replace my field with any other user, but I completely want to remove it from the record. For example in the load data the 'head_doctor' field is empty and so after loading the data into the Medical Department table. I want the 'head_doctor' field empty without being replace by any other record.

[Tai Vu]

Hi @labandar 

If you'd like to leave the head_doctor field empty from the load, just remove it out from the Field Map in your Transform Map.

Like below, ensure the 'head_doctor' field is not included in the mapping.

Alternatively, you may consider to remove the 'head_doctor' column from your Excel file before initiating the data load.

Not sure if I grasp your use case correctly. If possible, can you help to provide screenshots for better understanding what you're trying to achieve?

Cheers,

Tai Vu

[labandar]

Sure @Tai Vu ,

If you see in the attached screen shot I have a Business Unit (business_unit) as "ICT-CORF" which is a reference field for another table Business Unit, for the Application "VAM - Visual Asset Management-S"

Now if you see my load data below I have the Business Unit as empty, when I load the I want the record in the table above to be replaced by null or rather removed from the Business Unit (business_unit) field in my Table.