Unable to update import set table column max length.

[User350291]

Hi,

I have a scoped app where I have a data source that is part of the scoped app that is of type "Custom (Load by Script)" and in the script, I want to change the import set table column max length.  The import set table is also part of the same scoped app.

This is the code for the script of the data source:

(function loadData(import_set_table, data_source, import_log, last_success_import_time) {
	var now_GR = new GlideRecord('sys_dictionary');
	now_GR.addQuery('name', 'x_192652_device42_test');
	now_GR.query();
	gs.info("dictionary info - start");
	i = 0;
	while (now_GR.next()) {
		try {
			gs.info("dictionary info - " + now_GR.getValue('name') + "-" + now_GR.getValue('element') + "-" + now_GR.getValue('max_length') + "-" + now_GR.canWrite());
			if (now_GR.getValue('element') === 'u_col4') {
				now_GR.setValue('max_length', 150);
				now_GR.update();
				gs.info(now_GR.getValue('max_length'));
			}
			i += 1;
			if (i > 5)
				break;
		} catch (ex) {
			gs.info(ex);
		}		
	}
	
	for(var i=0; i<200; i++) {
		var record = {
			'col1': "abc" + i,
			'col2': "test",
			'col3': 'value31231231231239871230987127838123123123123123123131231231231231231231232435345454545235353453354542790123',
			'col4': 'value312312312312312312312381902389712837912798kjfejpofejoifjpoiefijefjoipeoi123123123123jpfoijeofipoijefjoiaojfeabcdefghijklmnopqrstuvwxyz'
		};
		import_set_table.insert(record);
	}
})(import_set_table, data_source, import_log, last_success_import_time);

 Though updating max_length does not generate an error, it also does not update the column max_length as can be seen in the dictionary entry.  It remains at 113 even though I had updated it to 150.

Should I be able to update the dictionary entry to change the column size if the import set table and the script I am trying to update the max_length from are both part of the same scoped application?  If so, how do I go about doing this since what I have now is not working?

Thanks

[sergiu_panaite]

I don't think that would work. 

Theoretically you should get a warning like:

Security restricted: Write operation against 'sys_dictionary' from scope 'xxxxx' has been refused due to the table's cross-scope access policy

Check also this article related: 

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0815411

Why can't you change the length one time only outside of that app scope?

Also, as a guidance when using a script to check something it is better to add as more filters as possible and avoid loops to check for a column name when it can be added directly in the filter, something like this:

var now_GR = new GlideRecord('sys_dictionary');
	now_GR.addQuery('name', 'x_192652_device42_test');
        now_GR.addQuery('element', 'u_col4');
	now_GR.query();
	gs.info("dictionary info - start");
	while (now_GR.next()) {
		try {
			gs.info("dictionary info - " + now_GR.getValue('name') + "-" + now_GR.getValue('element') + "-" + now_GR.getValue('max_length') + "-" + now_GR.canWrite());
			now_GR.setValue('max_length', 150);
			now_GR.update();
			gs.info(now_GR.getValue('max_length'));
			}
		} catch (ex) {
			gs.info(ex);
		}		
	}

[User350291]

Hi @sergiu_panaite,

Thanks

I did not see an error like:

Security restricted: Write operation against 'sys_dictionary' from scope 'xxxxx' has been refused due to the table's cross-scope access policy

in the system log when running the data source script.

Our ServiceNow app will be syncing data from our product to ServiceNow.  We will have hundreds of columns across dozens of import set table.  The columns and data will come from queries run for our product.  In addition, our customers will be able to create their own data sources with their own custom queries which would then result in even more import set tables and columns.  The size of the data in these columns can change over time.  For example, today, the the longest data is one field might be 20 characters, but tomorrow, it might be 40 characters.

We are trying to prevent truncation of this data into the import set table, and reduce/eliminate any manual effort the end user would need to do in order to prevent the data from being truncated.

We wanted to increase the import set table column sizes automatically based on the size of the data like what happens when the system property com.glide.loader.verify_target_field_size is set to true.  Note also that even when com.glide.loader.verify_target_field_size is set to true, it is not increasing the size of the columns for the import set table used by the data source script.

Is there a way for us to be able to increase the column sizes of the import set table?

Our next thought was to try to add the data to a JSON field of the import set table.

Thanks