TUESDAY, FEBRUARY 07, 2012

Promote a Field from an Extended Table in Service-now

Tuesday, 19 January, 2010 06:13 Written by Mark Stanger
12 Comments
This customization DOES NOT come across in update sets! If you make this change you need to make it across all of your instances manually as soon as you verify the result in a dev or test instance.

S
ervice-now allows you to use and create extended tables to help organize your database structure.  Out-of-box, there are 2 really important places that you’ll need to understand this setup.  The first is the ‘task’ table (which includes incident, problem, change_request, etc.) and the second is the ‘cmdb_ci’ table (which contains every table in the Service-now CMDB).
One of the nice things about this type of arrangement is that it allows you to set up fields or columns on these tables that can be unique to a single table (‘caller_id’ on the Incident table) or available to all extensions of a parent table (‘short_description’ on the Task table).  You can see if a table extends or is extended by another table by using the table schema map.

If you’re not careful when working with tables that are set up this way, you can get yourself into a little bit of trouble.  Fortunately it’s not that difficult to work your way out of it most of the time.  The biggest thing to be aware of when you’re creating fields on an extended table is to know what table you’re working on and what table(s) your new field should be available to.

If you do make a mistake, the easiest thing to do is just to catch it early and re-create the field.  If that’s not possible then there is a command you can run that promotes a field from an extended table to a parent table.  The function should be run from ‘System Definition -> Scripts – Background’ and works like this…

Packages.com.glide.db.DBUtil.promoteColumn('table_to_move_from', 'table_to_move_to', 'field_to_move', true);

For example, if you wanted to move the ‘category’ field from the Incident table to the parent Task table you could use this…

Packages.com.glide.db.DBUtil.promoteColumn('incident', 'task', 'category', true);

Or if you wanted to move the ‘u_yourfield’ field from the ‘cmdb_ci_service’ table to the ‘cmdb_ci’ table you could use this…

Packages.com.glide.db.DBUtil.promoteColumn('cmdb_ci_service', 'cmdb_ci', 'u_yourfield', true);

Of course, you’ll want to use this command only when necessary. It defeats the purpose of having extended tables if you promote all of the columns in a table to the parent table, but it can be extremely useful if you need it. The best part about this command is that it preserves the data in the field in question so you don’t have to worry about breaking any existing reports, forms, or filters!

Moving a column from a parent table to an extended table

It is also possible to move a column from a parent table down to an extended table. The 4th parameter in the promoteColumn method specifies whether the system will verify first that the source table is a child of the parent. Setting it to false bypasses this check and will let you move a field down from cmdb_ci to cmdb_ci_service or from task to incident.

For example, if you wanted to move the ‘u_yourfield’ field from the Task table to the child Incident table you could use this…

Packages.com.glide.db.DBUtil.promoteColumn('task', 'incident', 'u_yourfield', false);

Or if you wanted to move the ‘u_yourfield’ field from the ‘cmdb_ci’ table to the ‘cmdb_ci_service’ table you could use this…

Packages.com.glide.db.DBUtil.promoteColumn('cmdb_ci', 'cmdb_ci_service', 'u_yourfield', false);
Please note that although I have not seen any major issues with this function, I do not recommend running it directly in a production system without prior testing. Depending on the Service-now build your instance is on, you may need to go to the dictionary entry for the field you’ve promoted and check the ‘Active’ checkbox after promoting (although I haven’t seen this issue in recent builds). You may also have to re-define the label entry for the field you’ve promoted.
Tags:  , , ,
This entry was posted on Tuesday, January 19th, 2010 at 6:13 am and is filed under Scripting. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Comments

Posted On
May 04, 2010
Posted By
Brian Broadhurst

Hi Mark, I have used this facility many times since you first posted this entry, and it has saved me loads of time and effort. The one drawback (apart from the labels) that I have found is that the promotion of the field doesn’t get written to the current update set, so if you do this on a development instance, you also have to do it on your production instance. This can be a problem if the first part of your update set assumes a field is on an extension table and the second part assumes it is on the base table. Is there any chance the function might be enhanced to write to the update set?

Brian

Reply
Posted On
May 05, 2010
Posted By
Mark Stanger

Thanks for this Brian. This is actually an excellent point. Currently, there is no way to include these promotions in an update set. The best thing to do is probably to make this request on the Service-now /hi server. Until that point, if it were me, I’d probably consider these types of promotions as a standalone change to be pushed across all of your instances before any development work was done on top of the promoted fields. Either that, or you make the promotion a manual step before the application of any update set built on the promoted field.

Reply
Posted On
Oct 29, 2010
Posted By
Fred van der Schaar

Hi Mark,

First I want to thank you for the very useful addition your site is to my Service-now knowledge!

We noticed an element being renamed using the

Packages.com.glide.db.DBUtil.promoteColumn("cmdb_ci_service", "cmdb_ci", "u_yourfield", true);

method.

The element u_requested_for has been renamed to z_u_requested_for and the script stopped working.

Any idea what we did wrong, and/or how to recover this?

Thanks for your help!

Fred

Reply
Posted On
Oct 29, 2010
Posted By
Mark Stanger

That’s really strange. I’ve never seen that happen before. I just tried to reproduce that on https://demo.service-now.com and didn’t have any issue. I would try to reproduce it on your own system if I were you and see if you can get it to happen again. It sounds like there’s something else going on because I’ve never seen that issue with the promote script.

Reply
Posted On
Dec 03, 2010
Posted By
Tony Fugere

Mark, yet another awesome script. This just saved me a boatload of time with saving a client. On top of that I discovered that you can actually perform this on many extended tables where the field name is the same. My client had “u_yourfield” on about 8 different CMDB classes. We were able to roll them all up to the CMDB CI base class and save the day! You rock!

Reply
Posted On
Dec 03, 2010
Posted By
Mark Stanger

Thanks! I felt the same way when somebody first showed this to me. It’s not something that you’ll use every day, but when you do need it, it’s great to know about.

Reply
Posted On
Feb 26, 2011
Posted By
Tony Fugere

Mark, maybe you know about this already.

It appears that things have changed with the call to Packages.com.glide.db.DBUtil.promoteColumn for the good. Labels and forms automatically pick up on the new field on the parent table. For example, “U yourfield” would be the default label. Now the code seems to take care of that for us! Am I crazy or is this new?

Reply
Posted On
Feb 26, 2011
Posted By
Mark Stanger

Yeah, that’s worked for a while now, but I’m not sure exactly which release that issue was fixed in. If you’re running Fall 2010 or later (and maybe even earlier) you shouldn’t have any issues with re-activating the dictionary entry or fixing field labels.

Reply
Posted On
Jul 06, 2011
Posted By
Tim

Hi Mark – this will be extremely helpful in my case, too! I do have one question for you about how best to proceed here: I would like to promote the caller_id field to the task table… but it exists individually in Incident, Facilities, HR, etc.

Would I run the script from each of those tables? I can picture that working fine, but concerned it might cause problems.

Thanks, as always!
Tim

Reply
Posted On
Jul 06, 2011
Posted By
Mark Stanger

@Tim. I think that would work as well but you would probably only have to promote from one table and then just delete the dictionary entries on the other tables. If I were you I would probably promote the ‘Requested for’ field from the ‘sc_request’ table instead though. That way, you get the same field across all tasks. The ‘Requested for’ field is tied into the service catalog in so many places that it’s going to be hard to replace with anything else so I think it’s the best one to promote if you want a single ‘Caller/Requestor’ field at the task level. Then you could just de-activate the dictionary entries of all of the ‘caller_id’ fields.

Reply
Posted On
Jul 06, 2011
Posted By
Tim

Aaaah, great idea. We don’t use Service Catalog now, but I can see where promoting the ‘Requested For’ field now would be an excellent idea for when we do.

Thanks again! I always love reading your articles, and appreciate you being so responsive to questions too!

Tim

Reply
Posted On
Jan 13, 2012
Posted By
Martijn

If I create a field on an extended table and then on the base table, the field on the extended table form will actually point to the one on the base table (right mouse: show – “u_xxx”).
This way the new field will at least be in the updateset. After this you have two options:
1) Delete the field from the extended table (be aware: all data in that column is lost)
2) Run the promote script: all data from the extended column will be migrated, and the column on the extended table will be removed. (with regards to the z_u_xxx remarks: you get them, but no such columns are created. I think they are temporary ones).

Reply

Leave a Reply


Notify me of followup comments via e-mail. You can also subscribe without commenting.

Latest Comments

  • Mark Stanger: This linkage all happens for you if you use the task survey plugin. You can look on the wiki for more...
  • Vineeth: I want a way in which if a survey is filled in by the user the response are stored in the survey response...
  • Mark Stanger: This functionality doesn’t connect to an FTP server. See this line in the post above…...
  • Mark Stanger: The report page is back-end XML so there’s no way to directly manipulate the behavior of that...