Getting an error while creating a custom table via script in Yokohama PDI

[shajiindia]

Dear Experts,

Greetings,

I am trying to create a custom table via script but I am getting the following error, could you please help.

I am using Yokohama PDI

Build name: Yokohama
Build date: 01-22-2025_0439
Build tag: glide-yokohama-12-18-2024__patch0-01-14-2025

[ERROR]

Script execution error: Script Identifier: unknown, Error Description: Can't find method com.glide.sys.cache.CacheManager.get(). (<refname>; line 95), Script ES Level: 0
Javascript compiler exception: Can't find method com.glide.sys.cache.CacheManager.get(). (<refname>; line 95) in:

[/ERROR]

[SCRIPT]

(function() {
    var tableName = 'u_my_test_0001';
    var tableLabel = 'MyTest0001';
    var applicationScopeName = 'global'; // Or your custom scope name

    // Check if the table already exists
    var existingTable = new GlideRecord('sys_db_object');
    existingTable.addQuery('name', tableName);
    existingTable.query();

    if (existingTable.next()) {
        gs.error("Table '" + tableName + "' already exists. Aborting script.");
        return;
    }

    // Create the table
    var glideRecord = new GlideRecord('sys_db_object');
    glideRecord.initialize();
    glideRecord.setWorkflow(false);
    glideRecord.setValue('name', tableName);
    glideRecord.setValue('label', tableLabel);
    glideRecord.setValue('super_class', 'task');
    glideRecord.setValue('is_extendable', true);
    glideRecord.setValue('sys_update_name', 'sys_db_object_' + tableName);

    // Set the scope
    var scopeGR = new GlideRecord('sys_scope');
    scopeGR.addQuery('name', applicationScopeName);
    scopeGR.query();
    if (scopeGR.next()) {
        glideRecord.setValue('sys_scope', scopeGR.getUniqueValue());
    } else {
        gs.error("Scope '" + applicationScopeName + "' not found. Aborting.");
        return;
    }

    // Set the package
    var packageGR = new GlideRecord('sys_package');
    packageGR.addQuery('name', applicationScopeName);
    packageGR.query();
    if (packageGR.next()) {
        glideRecord.setValue('sys_package', packageGR.getUniqueValue());
    } else {
        gs.error("Package '" + applicationScopeName + "' not found. Aborting.");
        return;
    }

    var tableSysId;
    try {
        tableSysId = glideRecord.insert();
    } catch (e) {
        gs.error("Error inserting table record: " + e.message);
        return;
    }

    if (!tableSysId) {
        gs.error("Table creation failed! Check permissions and required fields.");
        return;
    }

    gs.info("Table created successfully: " + tableName);
    gs.sleep(5000); // Wait for table creation to propagate.

    // Register the table in sys_metadata
    var metadata = new GlideRecord('sys_metadata');
    metadata.initialize();
    metadata.setValue('sys_class_name', 'sys_db_object');
    metadata.setValue('name', tableName);
    metadata.setValue('sys_id', tableSysId);
    metadata.insert();

    gs.info("Metadata record created for " + tableName);

    // Create default list view
    var uiList = new GlideRecord('sys_ui_list');
    uiList.initialize();
    uiList.setValue('name', tableName);
    uiList.setValue('view', 'default');
    uiList.insert();

    gs.info("Default list view created for " + tableName);

    // Create fields
    createField(tableName, 'u_short_description', 'Short Description', 'string', 100);
    createField(tableName, 'u_status', 'Status', 'choice', 40);
    createField(tableName, 'u_number', 'Number', 'integer', null);
    createField(tableName, 'u_created_on', 'Created On', 'glide_date_time', null);

    // Add choices
    addChoice(tableName, 'u_status', 'new', 'New', 100);
    addChoice(tableName, 'u_status', 'in_progress', 'In Progress', 200);
    addChoice(tableName, 'u_status', 'closed', 'Closed', 300);

    // Flush cache
    GlideCacheManager.get().flushTable('sys_dictionary');
    gs.info("Cache flushed. Changes should now be visible.");

    function createField(tableName, columnName, columnLabel, columnType, maxLength) {
        var field = new GlideRecord('sys_dictionary');
        field.initialize();
        field.setWorkflow(false);
        field.setValue('name', tableName);
        field.setValue('column_label', columnLabel);
        field.setValue('column_name', columnName);
        field.setValue('internal_type', columnType);
        if (maxLength) {
            field.setValue('max_length', maxLength);
        }
        field.setValue('mandatory', false);
        try{
            field.insert();
            gs.info("Field created: " + columnName);
        } catch(e){
            gs.error("field creation error: " + columnName + e.message);
        }
    }

    function addChoice(tableName, fieldName, value, label, sequence) {
        var choice = new GlideRecord('sys_choice');
        choice.initialize();
        choice.setWorkflow(false);
        choice.setValue('name', tableName);
        choice.setValue('element', fieldName);
        choice.setValue('value', value);
        choice.setValue('label', label);
        choice.setValue('sequence', sequence);
        try{
            choice.insert();
            gs.info("Choice added: " + label);
        }catch(e){
            gs.error("choice creation error: " + label + e.message);
        }
    }
})();

[/SCRIPT]

Any help is much appreciated.

Thank you.

Sincerely,

Shaji Kalidasan

PS: I also tried creating system properties glide.script.use.v3.rhino (set to false) and glide.script.use.es6 (set to true) but in vain.

[Ankur Bawiskar]

@shajiindia 

you are running this script from scoped app?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[shajiindia]

Hi @Ankur Bawiskar,

Currently, I am running this script in the global scope.

Thank you

Shaji Kalidasan

[Bert_c1]

You are going down a rabbit hole with this approach IMHO. This approach is not supported.

1. There is no OOB field named 'column_name' on sys_dictionary, the field is 'element'.

2. There is no need to create records in sys_metadata, as that table is the parent of sys_db_object, sys_dictionary, sys_documentation.

3. An update set that captures a Table definition contains sys_db_object, sys_dictionary, and sys_documentation records.

While the script changes I proposed worked for me at first, they no longer do after deleting that table. I hope this helps.

[shajiindia]

Hi Bert,

Thanks for the heads-up and insights. It is always better to fail fast, reflect, and introspect rather than proceed with the current approach. I shall revisit this approach and explore alternative solutions. Your guidance has indeed saved both time and effort. I am marking your reply as helpful but not as a solution-I trust you shall comprehend the situation. My heartfelt thanks.

Thank you.

Sincerely,

Shaji Kalidasan