facing Issue while copying records from parent table A to child table B (extends A)

[shreyanshs07]

hello community, 

Recently I faced this issue while copying records from parent table 'u_table_a' (created by me) to child table 'u_table_b' (also created by me). I created a script include to copy those records from parent table to child table and when running that script in background script, the records are getting copied to the parent table itself and not in the child table.

above is the screenshot of the script include which I created.

above are the parent table records which I want to copy.

after running the script include, the records are getting copied to the parent table and not the child table.

[Bert_c1 · Accepted solution]

This may expected due to the proprietary database schema in servicenow, see:

table-administration

if 'u_table_a' is not extended from any table, then the check you have for a valid column in 'u_table_b' is not needed. As 'u_table_b' contains all of the columns form 'u_table_a'. You can leave out the 'isValidField()' check.

Look at each table definition in sys_db_object, see that all fields on u_table_a are present on u_table_b.  Also, post script as text and then others here can copy and test. I don't see any problems by eye with the script logic.

And I just confirmed, that when creating a record in table b, the parent record is created in table a. so no need for your script.

Try the following in scripts background:

var sda = new GlideRecord('sys_dictionary');
sda.addQuery('name', 'u_table_a');
sda.query();
while (sda.next()) {
	gs.info('Table: ' + sda.name + ', element: ' + sda.element);
}

var sdb = new GlideRecord('sys_dictionary');
sdb.addQuery('name', 'u_table_b');
sdb.query();
while (sdb.next()) {
	gs.info('Table: ' + sdb.name + ', element: ' + sdb.element);
}

the results in the database are:

*** Script: Table: u_table_a, element: sys_updated_on
*** Script: Table: u_table_a, element: sys_id
*** Script: Table: u_table_a, element: sys_created_by
*** Script: Table: u_table_a, element: u_field_1
*** Script: Table: u_table_a, element: sys_created_on
*** Script: Table: u_table_a, element: 
*** Script: Table: u_table_a, element: sys_updated_by
*** Script: Table: u_table_a, element: sys_mod_count
*** Script: Table: u_table_a, element: sys_class_name
*** Script: Table: u_table_b, element: 
*** Script: Table: u_table_b, element: sys_id
*** Script: Table: u_table_b, element: u_table_field

The UI shows Table A's fields when viewing a record in Table B. But the database need not duplicate the table column names.

[Bert_c1 · Accepted solution]

This may expected due to the proprietary database schema in servicenow, see:

table-administration

if 'u_table_a' is not extended from any table, then the check you have for a valid column in 'u_table_b' is not needed. As 'u_table_b' contains all of the columns form 'u_table_a'. You can leave out the 'isValidField()' check.

Look at each table definition in sys_db_object, see that all fields on u_table_a are present on u_table_b.  Also, post script as text and then others here can copy and test. I don't see any problems by eye with the script logic.

And I just confirmed, that when creating a record in table b, the parent record is created in table a. so no need for your script.

Try the following in scripts background:

var sda = new GlideRecord('sys_dictionary');
sda.addQuery('name', 'u_table_a');
sda.query();
while (sda.next()) {
	gs.info('Table: ' + sda.name + ', element: ' + sda.element);
}

var sdb = new GlideRecord('sys_dictionary');
sdb.addQuery('name', 'u_table_b');
sdb.query();
while (sdb.next()) {
	gs.info('Table: ' + sdb.name + ', element: ' + sdb.element);
}

the results in the database are:

*** Script: Table: u_table_a, element: sys_updated_on
*** Script: Table: u_table_a, element: sys_id
*** Script: Table: u_table_a, element: sys_created_by
*** Script: Table: u_table_a, element: u_field_1
*** Script: Table: u_table_a, element: sys_created_on
*** Script: Table: u_table_a, element: 
*** Script: Table: u_table_a, element: sys_updated_by
*** Script: Table: u_table_a, element: sys_mod_count
*** Script: Table: u_table_a, element: sys_class_name
*** Script: Table: u_table_b, element: 
*** Script: Table: u_table_b, element: sys_id
*** Script: Table: u_table_b, element: u_table_field

The UI shows Table A's fields when viewing a record in Table B. But the database need not duplicate the table column names.

[shreyanshs07]

hi @Bert_c1 , thank you for for the provided help.
here is the code which I was using from my side, and it would be helpful if you could tell what's exactly going wrong with my script.

var parent_record_to_child = Class.create();
parent_record_to_child.prototype = {
    initialize: function() {
		var parentgr= new GlideRecord('u_table_a');
		parentgr.query();

		while(parentgr.next()){
			var childgr= new GlideRecord('u_table_b');
			childgr.initialize();

			var fields= parentgr.getFields();
			var size= fields.size();

			for( var i=0; i<size;i++){
				var fieldname=fields.get(i).getName();
				var fielvalue= parentgr.getValue(fieldname);

				if(childgr.isValidField(fieldname)){
					childgr.setValue(fieldname,fielvalue);
			}
			}
			
			childgr.insert();
		}
    },

    type: 'parent_record_to_child'
};

[Bert_c1]

It worked for me. I think what you are doing has little value. I had 4 records in table a, and three in table b, I now have 8 records in table a and 6 records in table b, after running the logic you have.

[Bert_c1]

@shreyanshs07 ,

Use the following script logic:

		var parentgr= new GlideRecord('u_table_a');
		parentgr.query();

		while(parentgr.next()){
			var childgr= new GlideRecord('u_table_b');
			childgr.addQuery('sys_id', parentgr.sys_id.toString());
			childgr.query();
			if (childgr.next()) {
				gs.info("Found child record in Table B for Table A record with sys_id = " + parentgr.sys_id);
			}
			else {
				gs.info("Did not find child record in Table B for Table A record with sys_id = " + parentgr.sys_id);
				// Add logic to create a record in Table B
			}
		}

The sys_id value of the child record will match that of the corresponding parent record. The platform uses a SQL Join on sys_id to link the two. Study Relational Databases, Servicnow scripting and the Servicenow GlideRecord API.