Get Active Data Dictionary

If you want to generate a report for a table that lists all fields being used (i.e., containing data in at least one record), you need a query or logic that scans through each field in the table and checks for non-null or non-empty values. Fields that do not contain data in any record should be excluded from the report.

This approach has been tested successfully on other tables like incident, sn_hr_core_case, cmdb_ci, and problem, where similar logic was applied, and it worked as expected.

USE CASE:

It exports all the fields utilized, even if they appear in only a single record, while adding a few basic fields as columns. Additionally, it pulls reports from the selected table.

Here’s a demo outlining how to export active fields (i.e., fields with data) for the Incident table:

Steps to configure:

1. Create a Kb article, where we will store the downloaded attachments, copy the sys id and keep it handy.

2. Create a client callable script include

Name: dictionarytableFields

Code:

var dictionarytableFields = Class.create();
dictionarytableFields.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getFieldsValue: function() {
        var nonEmptyFields = [];
        var fieldCounts = {};
        var tableNames = this.getParameter('sysparm_selected_rec');
        var dictionaryInfo = {};

        // Initialize field counts
        var gr = new GlideRecord(tableNames);
        gr.query();
        if (gr.next()) {
            var fields = gr.getFields();
            for (var i = 0; i < fields.size(); i++) {
                var fieldName = fields.get(i).getName();
                fieldCounts[fieldName] = 0;
            }
        }

        // Iterate through each record and count non-empty fields
        gr = new GlideRecord(tableNames);
        gr.query();
        while (gr.next()) {
            var fields = gr.getFields();
            for (var i = 0; i < fields.size(); i++) {
                var fieldName = fields.get(i).getName();
                var value = gr.getValue(fieldName);
                if (value !== '' && value !== null) {
                    fieldCounts[fieldName]++;
                }
            }
        }

        // Identify fields that are non-empty for any records
        for (var field in fieldCounts) {
            if (fieldCounts[field] > 0) {
                nonEmptyFields.push(field);
            }
        }
        var hierarchyList = new TableUtils(tableNames).getTables();
        var arr = hierarchyList.toArray();
        var resultString = arr.join(', ');
        // Fetch additional dictionary information
        var dictGr = new GlideRecord('sys_dictionary');
        // Query for dictionary entries related to specific tables
        dictGr.addEncodedQuery('nameIN'+resultString);
        dictGr.query();
        while (dictGr.next()) {
            var fieldName = dictGr.getDisplayValue('element');
            dictionaryInfo[fieldName] = {
                type: dictGr.getDisplayValue('internal_type') || 'undefined',
                readOnly: dictGr.getDisplayValue('read_only') == 'true' ? 'Yes' : 'No',
                mandatory: dictGr.getDisplayValue('mandatory') == 'true' ? 'Yes' : 'No',
                fieldTableName: dictGr.getDisplayValue('name') || 'undefined',
    referenceTableName: dictGr.getDisplayValue('reference') || '',
    maxLength: dictGr.getValue('max_length') || '',
    columnLabel: dictGr.getDisplayValue('column_label') || 'empty'
            };
        }

        var csvRows = [];

        // Add header row
        csvRows.push('Table Name,Field Name,Field Label,Type,Reference,Max Length,Read Only,Mandatory\r\n');

        // Add fields and their dictionary info as rows
        for (var i = 0; i < nonEmptyFields.length; i++) {
            var fieldName = nonEmptyFields[i];
            var dictInfo = dictionaryInfo[fieldName] || {
                type: 'undefined',
                readOnly: 'undefined',
                mandatory: 'undefined',
                fieldTableName: 'undefined',
    referenceTableName:'',
    maxLength:'',
    columnLabel:'undefined'
            };
            csvRows.push(dictInfo.fieldTableName + ',' + fieldName + ','+ dictInfo.columnLabel  + ',' + dictInfo.type + ',' + dictInfo.referenceTableName + ',' + dictInfo.maxLength + ',' + dictInfo.readOnly + ',' + dictInfo.mandatory + '\r\n');
        }

        var gdt = new GlideDateTime();
        var fileName = tableNames + ' Non Empty Fields ' + gdt.getDisplayValue() + '.csv';

        // Write the CSV to an attachment
        var attachment = new Attachment();
        var attachmentRec = attachment.write('kb_knowledge', 'sys id to your newly created kb article', fileName, 'text/csv', csvRows.join(''));

        var att = new GlideRecord('sys_attachment');
        att.addQuery('file_name', fileName);
        att.addQuery('table_sys_id', 'sys id to your newly created kb article');//update the sys id to your newly created kb article
        att.orderByDesc('sys_created_on');
        att.query();
        if (att.next()) {
            return att.sys_id.toString(); // Return the sys_id of the attachment
        }
        return '';
    },

    type: 'dictionarytableFields'
});

Note: Replace the copied sys id from step to line 86 and 90 in script include code.

3. Create a Client Callable ui action shown as below.

Code:

function onclickofButton() {
    var gm = new GlideModal("glide_prompt", true, 600);
    gm.setTitle("Get active data from below selected table");
    gm.setPreference("title", "Enter Table Name");
    gm.setPreference("onPromptComplete", function(tablevalue) {
        var ga = new GlideAjax('global.dictionarytableFields');
        ga.addParam('sysparm_name', 'getFieldsValue');
        ga.addParam('sysparm_selected_rec', tablevalue);
        ga.getXMLAnswer(callback);

        function callback(response) {
            try {
                var attachmentSysId = response;
                if (attachmentSysId) {
                    var downloadURL = "/sys_attachment.do?sysparm_referring_url=tear_off&view=true&sys_id=" + attachmentSysId;
                    g_navigation.openPopup(downloadURL);
                } else {
                    alert("No attachment generated.");
                }
            } catch (error) {
                alert("An error occurred while processing the download. Please check the console for more details.");
            }
        }
    });
    gm.setPreference("onPromptCancel", function() {
        return false;
    });
    gm.render();
}

Note: For other scoped application like, human resource you might need to create cross scope record in order to access data from different application.

Happy Learning,
Nisha