Field needs to be changed from Reference to String

richelle_pivec · ‎07-28-2017

I have a field which was changed (several years ago and not by me) from a string field to a reference field. The field is actually the field that is being referenced by other tables, so, because it is behaving like a reference field, I now have what looks like a circular reference. A reference field on the user table (manager) that points to a "reference" field (manager) on the cost center table.

The field on the cost center table is the one that should be a string. Can I change a reference field to a string field (or does that need to be done by ServiceNow support)? Once I do get it changed to a string field, I'm going to lose all the data, aren't I? So, I'm going to need to do it in Dev first, re-populate the manager names and then do the Change in Test (repopulate) and then Prod. Good times.

thanks,

Richelle

Chuck Tomasi · ‎07-28-2017

Thank you for the clarification. All of this seems to look perfectly fine to me. I think I know where the confusion is coming in Richelle.

A reference field, as noted before contains a sys_id. It's a "pointer" to say "I refer to record 12345ABC from table sys_user" or "I refer to record FF1233 on table cmn_cost_center". The downside of this is that sys_ids are not human readable so we use what's called a "display value". For the sys_user table, the display value is the value in the name field. When any reference field to sys_user needs to display something, the sys_user table says "I'm instructed to tell you that I'm going to display the name." It can be changed, but I don't recommend it since it's a system wide configuration.

If you changed out a reference field to a string field, you wouldn't have any "link" to the record in the other table. You would just have a dumb string. If I put in "Chuck Tomasi", it has nothing to do with the information on the user table. There's no way to get at the other attributes on Chuck's user record.

I invite you to watch the quick tip in episode 39 of TechNow. We just talked about this fairly well.

TechNow Episode List

View solution in original post

Chuck Tomasi · ‎07-28-2017

Hi Richelle,

Changing a field type always carries with it a risk of losing data. In this case, if you COULD, change it from a reference to a string, you would be left with a sys_id (32 hex characters.) Not real good.

I suspect there is a way out of this, but I need to understand your data model.

Are you saying that you have...

Table.FieldName (references) Table

----------------------------------------

sys_user.manager --> sys_user

cmn_cost_center.manager --> sys_user

? I don't see the issue there. Please clarify so I can understand where the issue is.

richelle_pivec · ‎07-28-2017

Here's what I see in the dictionary definitions of the reference fields:

on the Cost Center Form: cmn_cost_center.manager --> sys_user

on the User Form: cmn_cost_center.manager --> sys_user

RIchelle

Chuck Tomasi · ‎07-28-2017

Hi Richelle,

That seems to make sense. I'm not sure I understand the problem.

Shouldn't each cost center have a manager? And wouldn't it make sense for that manager to be a reference back to sys_user? Changing it to a string and making it a dumb string (like Chuck Tomasi) seems to devalue the data model IMHO.

What an I missing?

richelle_pivec · ‎07-28-2017

It seems like the one on the user table is referencing the one on the cost center table and the one on the cost center table is also referencing the one on the cost center table.

For example, the name field on the user record is a string field. It is then referenced throughout the database.

Shouldn't one of those two fields be a string field that is referenced by the other? It seems like there is no "home" or "starting point" where the manager name gets entered and is then referenced throughout the related tables. Currently if I change it in one place, it changes it in the other and vice versa. (It doesn't actually do a look-up of another field, it actually changes the value in both places.)

With the user name field, changing the reference name doesn't change the name field on the User table...it just chooses a different name to put in that reference field.

On User Table - Name is String; Cost Center Manager is Reference

On Cost Center Table; Cost Center Manager is Reference

On SC_Task form. Opened By and Requested By are Reference fields pointing back to Name on the User Table

thanks,

Richelle