How can I set Max length and reference value for new field using the GlideColumnAttributes object?

[Todd O]

Dear SN developer community,

I'm successfully leveraging the nice capabilities of the GlideColumnAttributes to create many tables by using a script. That is, I'm able to create the tables along with all the fields programmatically. There is one pitfall, however, that I need to overcome. When creating the fields on my new tables, I also want to set the max length and sometimes make it a reference column to another table. I'm not finding how this can be done with the GlideColumnAttributes object. Can anyone please, please provide some insight into the GlideColumnAttributes methods available? Thank you so much in advance.

Todd

p.s., here is a snippet to the code that is working for me. Perhaps this will help someone else too.

var table = '{"tableName":"custom_table1","tableLabel":"Custom Table1","tableExtension":"","fieldList":[{"name":"name","label":"Name","type":"String","maxSize":40,"refTable":"","defaultValue":""},{"name":"computer_name","label":"Computer Name","type":"String","maxSize":40,"refTable":"cmdb_ci_computer","defaultValue":""}]}';
var jsonPayload = JSON.parse(table);
var tableName = jsonPayload.tableName;
log.info("Table Name: " + tableName);
var tableLabel = jsonPayload.tableLabel;
var tableExtension = jsonPayload.tableExtension;
var fieldObjects = jsonPayload.fieldList;
var attrs = new Packages.java.util.HashMap();
for (var i in fieldObjects) {
       var ca = new GlideColumnAttributes(fieldObjects[i].name);
       ca.setType(fieldObjects[i].type);
       // ca.setMaxLength(50); // This does not work but what does????
       ca.setUsePrefix(true);
       attrs.put(fieldObjects[i].name, ca);
}
var tc = new GlideTableCreator(tableName, tableLabel);
tc.setColumnAttributes(attrs);
if (typeof tableExtension != 'undefined')
       tc.setExtends(tableExtension);
tc.setOverrideUpdate(true);
tc.update();

[Vincenzo4]

Hi,

I need to use the same functionality "setReferenceTable"

Someone have found a solution to use it ?

Regards

Vincenzo

[louis5]

Bonjour Vincenzo!

Unless it was added to the newest version of ServiceNow you have to update the sys_dictionary with a GlideRecord. I build a little function for that.

function setReference( tableName, columnName, refAndQualifier )

{

  var hierarchy=GlideDBObjectManager.get().getTables(tableName).toArray();

  for ( var hierarchyIndex in hierarchy )

  {

    var table=hierarchy[ hierarchyIndex ];

    var gr=newGlideRecord( 'sys_dictionary' );

    gr.addQuery( "name", table );

    gr.addQuery( "element", columnName );

    gr.query();

    if( gr.next() )

    {

      gr.reference=refAndQualifier.reference;

      gr.reference_qual=refAndQualifier.reference_qual;

      gr.update();

      break;

    }

  }

}

Hope it helps.

Louis

[Vincenzo4]

Hi Louis,

In first, thank you to have share with me your script 🙂 .

I'm trying to active it i my dev system.

So, the field "refAndQualifier" visibile like parameter in the function is a json that contains 2 object:

• Table name
• Query 

yes ? or no ?

Regards

Vincenzo 

[louis5]

Bonjour Vincenzo,

Sorry for the delay. Yes, refAndQualifier is expected to be a JSON object.

setReference( 'incident', 'u_my_reference', { reference:'u_my_table', reference_qual:'**some qualifier**' } );

Note that you could omit reference_qual, it would work too.

setReference( 'incident', 'u_my_reference', { reference:'u_my_table' } );

You could also redefine the function signature to use simple variable instead of a JSON object.

function setReference( tableName, columnName, reference, reference_qual )

...

Of course you would have to remove "refAndQualifier." in the function.

Louis