Create columns on table via script

[Utpal Dutta1]

Hey all,

I have a requirement where I need to create a variable for a record producer & map that variable to a newly created field in table via script.

So requirement is that an admin should be able to create variables on record producer and map to a field in table but not via going though the backend. We are creating a portal page, which has some fields like column name and type. When user submits this request, a column should be created in table and then a variable should be created for the record producer after that the variable should be mapped to the table column.

On submit I'm running below script. The script is creating a variable and also creating a column in the table but the column type is not setting up also for variable, map to field is not working.

var grTableField = new GlideRecord('sys_dictionary');
grTableField.setValue('name','x_624157_custom_su_test_table');
grTableField.setValue('internal_type','747127c1bf3320001875647fcf0739e0');
grTableField.setValue('column_label','Example Field 6');
grTableField.setValue('element','example_field_6');
grTableField.setValue('max_length','40');
grTableField.setValue('active', true);
grTableField.insert();

 

var grVariable = new GlideRecord('item_option_new');
grVariable.setValue('type', '6');
grVariable.setValue('cat_item', '9d462e982f263010dfed877cf699b602');
grVariable.setValue('question_text', 'Example Question 6');
grVariable.setValue('name', 'example_question_6');
grVariable.insert();

 

var mapField = new GlideRecord('item_option_new');
mapField.addQuery('name', grVariable.getValue('name'));
mapField.setValue('map_to_field',true);
mapField.setValue('field',grTableField.getValue('element'));
mapField.setValue('record_producer_table','x_624157_custom_su_test_table');
mapField.update();

I also tried a different approach to create table columns via (SncTableEditor.createElement) but it's not working for scoped application.

Please help me out with an approach which best suits my situation.

Thanks:

Utpal Dutta

[Mukesh24]

Hi Change it to below and should work,

var grTableField = new GlideRecord('sys_dictionary');
grTableField.setValue('name','x_624157_custom_su_test_table');
grTableField.setValue('internal_type','string'); //replace the sys_id with 'string'
grTableField.setValue('column_label','Example Field 6');
grTableField.setValue('element','example_field_6');
grTableField.setValue('max_length','40');
grTableField.setValue('active', true);
grTableField.insert();

 

var grVariable = new GlideRecord('item_option_new');
grVariable.setValue('type', '6');
grVariable.setValue('cat_item', '9d462e982f263010dfed877cf699b602');
grVariable.setValue('question_text', 'Example Question 6');
grVariable.setValue('name', 'example_question_6');
grVariable.insert();

 

var mapField = new GlideRecord('item_option_new');
mapField.addQuery('name', grVariable.getValue('name'));
mapField.query(); //perform a query to get the variable created above
while (mapField.next()){
mapField.setValue('map_to_field',true);
mapField.setValue('field',grTableField.getValue('element'));
mapField.setValue('record_producer_table','x_624157_custom_su_test_table');
mapField.update();
}

[Utpal Dutta1 · Accepted solution]

Hi all,

I have figured out how we can use (SncTableEditor.createElement) method to create fields in a table. Now the problem with above Glide method is that it dosen't work until you modify global dictonary table and give access for Can create & Update from all application scope.

However the method (SncTableEditor.createElement) works fine if you want to create fields in a table in global scope but not in costom scope. To resolve this issue, you need a script include in global scope in which this method will be written and then we call this script include in our custom scope.

Below is script include with some parameters in the method:

Note: Create script include & set accessible from "All application scope". 

Now call this script include in any custom app scope like below:

var warraper = new global.wrapperTable();
warapper.createColumns(*pass parameters here*);

The above method last parameter is scope. You need to pass sys_id of your scope.

Thanks,

Utpal Dutta

[Bob Loblaw]

This is a great help, thanks! 😁 A couple things to note:

• The 'dvalue' stands for the default value.
• I've taken the global script includes one tiny step further and allowed the scope to be passed in:

var wrapperTable = Class.create();
wrapperTable.prototype = {
	initialize: function() {
	},
	createCols: function(table,field,field_name,type,length,reference,dvalue,scope){
		SncTableEditor.createElement(table, field, field_name, type, length, reference, dvalue, scope);
	},
	type: 'wrapperTable'
};

• There is a typo in the scoped calling script in the post above: 'warraper' and 'warapper' (in case you copied it like me and got an error).  Another way to do it is in the screenshot below. For testing purposes, you can put the script below in a fix script and run it manually to see it create the table column.

var col = new global.wrapperTable();
//table, display field name, internal field name, type, length, reference, default value, scope
col.createCols("x_table_name","First Name","first_name","string","40","NULL","","84dd56a547312110bf05249bd36d4aa4");