Can we convert field type from String to reference field

[vahini]

Hi All , 

i am working on a scoped app , and there is a field called 'Spoke to' which is a free text field  and logged in user manually enters his name in it . 

I have a requirement  to change the name of field to 'Recorded by' (which is done )  and also  autopopulate this field with logged in user id . 

1st question : can we auto populate a text field  with logged in user id using javascript:gs.getUserID()  ??

As per my knowledge , it has to be a reference field to sys_user table to use   javascript:gs.getUserID() 

2nd question : if above method is not possible , then can we convert a string field to reference field and use  javascript:gs.getUserID()    ???

right now there are 2012 lab records in which  'Recorded by'  field is populated manually by user worked on that lab record  and 1440 records in which this field is empty becos user forgot to enter his name .  .

Please suggest ur advice on this . Thanks for looking into this issue .  

[sachin_namjoshi]

Changing data type is not always recommended for all data types since it may lead to data loss.

You should create new reference field to sys_user table and then run one time fix script to copy data from old text field to new reference field.

AFter copying data to new reference field, you can hide old field from UI.

Regards,

Sachin

[vahini]

Thats a great idea . can you help me with the fix script  please .

[SatheeshKumar]

Use gs.getUserName() to set the default value, modifying field type is not recommended, if needed you can create a new field as per your requirement and deactivate the existing field.

javascript:gs.getUserName();

-satheesh

[sachin_namjoshi]

Please use below fix script and adjust as per your field tables

var a = new GlideRecord('abc');// your table name
a.addEncodedQuery('number=TASK0021501'); // update query to find records with user name populated
a.query();
while(a.next()){

var usr = new GlideRecord('sys_user');
usr.addQuery('name',a.description);// match user name with field on your table
usr.query();
while(usr.next()){

a.assigned_to = usr.sys_id;
a.update();

}

}

Regards,

Sachin