How to get all fields of extended table and filter out inactive field

[Igor Kozlov]

Hello,

can someone help me with the following problem?

I need a way to get names and values of all active columns for a table, for example alm_harware

but i can either filter out inactive field for alm_harware losing field of parent table

//also need to collect data from parent table
var dict = new GlideRecord('sys_dictionary')
dict.addQuery('name', table)
dict.addActiveQuery()
dict.addEncodedQuery('element!=sys_id')
dict.query()

while(dict.next()){

gs.print(dict.column_label)
gs.print(dict.element)
}

or get all field for both current and parent table without a way to filter out inactive

var gr = new GlideRecord(table);
gr.initialize();
var flds = gr.getFields();
for(var i = 0;i<flds.size();i++){

 var field = flds.get(i);
 gs.print(field.getLabel());
 gs.print(field.getED().getName());
}

[Shishir Srivast · Accepted solution]

Can we try with below script:

var grCheck = new GlideRecord('alm_hardware');
grCheck.query();
if(!grCheck.next() || grCheck.next()){
	var fields = new GlideRecordUtil().getFields(grCheck);
	var gr = new GlideRecord('sys_db_object');
	gr.get('name', grCheck.sys_class_name);
	for (var j = 0; j < fields.length; j++){
		var dict = new GlideRecord('sys_dictionary');
		dict.addQuery('name', gr.super_class.name);
		dict.addQuery('element', fields[j]);
		dict.addActiveQuery();
		dict.query();
		if(dict.next()){
			gs.print('Parent Table Column Label:   ' + dict.column_label);
			gs.print('Parent Table Column Name:    ' + dict.element);
		}
		else{
			var dict1 = new GlideRecord('sys_dictionary');
			dict1.addQuery('name', grCheck.sys_class_name);
			dict1.addQuery('element', fields[j]);
			dict1.addActiveQuery();
			dict1.query();
			if(dict1.next()){
				gs.print('Base Table Column Label:     ' + dict1.column_label);
				gs.print('Base Table Column Name:     ' + dict1.element);
			}
		}
	}
}

[Shishir Srivast]

OR,

var grCheck = new GlideRecord('alm_hardware');
grCheck.query();
if(!grCheck.next() || grCheck.next()){ // just trying to check whether record is there or not so that we can use grCheck.sys_class_name in sys_db_object gliderecord query.
	var fields = new GlideRecordUtil().getFields(grCheck);
	for (var j = 0; j < fields.length; j++){
		var gr = new GlideRecord('sys_db_object');
		gr.get('name', grCheck.sys_class_name);
		var dict = new GlideRecord('sys_dictionary');
		dict.addQuery('name', gr.super_class.name);
		dict.addQuery('element', fields[j]);
		dict.addActiveQuery();
		dict.query();
		while(dict.next()){
			gs.print(dict.column_label);
			gs.print(dict.element);
		}
	}
}

[Igor Kozlov]

Hi  Shishir,

Thank you for super_class  field i haven't heard about it before.

However while working with some table extending cmdb_ci everything become more complex as you have a parent of parent, etc.

But search for details about super_class i have found a way to get all parents of current table. Now i can use the list of all tables and just one query

var u = new TableUtils('alm_hardware')
var tables =  u.getTables().toString() .replace('[','').replace(']', '')

var dict1 = new GlideRecord('sys_dictionary');

dict1.addActiveQuery();

dict1.addEncodedQuery('nameIN' + tables.toString() );
dict1.query();

while(dict1.next()){
	gs.print(' Table Column Label:     ' + dict1.column_label);
	gs.print('Table Column Name:     ' + dict1.element);
}