Transform map Script so keep a category value

[Rafael Batistot]

Hi Team, 

I have a process in that i use the transform map to rise automatic  category and subcategory
This is the template that i use in my Data Source

For this, some rules need to be consider: 

1. If the category value has been exist, the category and your sequence no need to be raised
2. Subcategory value cannot be created duplicated  in the same category sequency. Only one value .

Knowing this... I have this code that i need to pick the value from category and case exist in the sys_choice table do not need create, the category and the category sequency 

The situation here is with this code i could the avoid that the category raised, but the category sequency not
i've tried with onStart and onBefore. The result is the same

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
   
    if (source.u_element == 'category') {
        var categoryValue = [];
        categoryValue.push(source.u_value);
    }

    if (source.u_element == 'subcategory' && categoryValue.length > 0) {
        gs.log("Category Value: " + categoryValue[0]);
    }

    categorias = [];
    var categoryValueUsed = getCategoryValue(source.u_value);

    if (categoryValueUsed) {
        ignore = true;

    }

    function getCategoryValue(value) {
        var choiceGr = new GlideRecord('sys_choice');
        choiceGr.addQuery('name', 'incident'); // Tabela alvo
        choiceGr.addQuery('element', 'category'); // Category
        choiceGr.addQuery('value', value); // Category value (10)
        choiceGr.query();

        while (choiceGr.next()) {
            categorias.push(choiceGr.label + ' - ' + choiceGr.value);
        }

        return categorias.length > 0 ? categorias[0] : null;
    }


})(source, map, log, target);

 
Let me know if may you help me
Thank's in advanced

[Community Alums]

Hi @Rafael Batistot   ,

Hey! Thanks for sharing the details — you're really close! Let me help you improve this logic step by step to ensure that:

1. Duplicate Categories are not created, including duplicate value entries in sys_choice.

2. Category sequence (value) is only created when it doesn’t already exist.

3. Subcategories are only created once per category.

try this

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // Only proceed if the element is 'category' or 'subcategory'
    var isCategory = source.u_element == 'category';
    var isSubcategory = source.u_element == 'subcategory';

    // Reusable values
    var categoryLabel = source.u_element == 'category' ? source.u_label : '';
    var categoryValue = source.u_value;

    // Check for existing category
    if (isCategory) {
        if (categoryExists(categoryValue)) {
            gs.info("Category already exists: " + categoryValue);
            ignore = true; // Skip inserting this row
        }
    }

    // Check for duplicate subcategory in same category sequence
    if (isSubcategory) {
        var parentCategoryValue = source.u_parent_value; // You might need to map this field
        if (subcategoryExists(parentCategoryValue, categoryValue)) {
            gs.info("Subcategory already exists under this category: " + categoryValue);
            ignore = true;
        }
    }

    // Function: check if category (value) already exists
    function categoryExists(value) {
        var gr = new GlideRecord('sys_choice');
        gr.addQuery('name', 'incident');
        gr.addQuery('element', 'category');
        gr.addQuery('value', value);
        gr.query();
        return gr.hasNext();
    }

    // Function: check if subcategory (value) already exists for this category
    function subcategoryExists(parentValue, subValue) {
        var gr = new GlideRecord('sys_choice');
        gr.addQuery('name', 'incident');
        gr.addQuery('element', 'subcategory');
        gr.addQuery('dependent_value', parentValue); // This links subcategory to category
        gr.addQuery('value', subValue);
        gr.query();
        return gr.hasNext();
    }

})(source, map, log, target);

[Rafael Batistot]

Hi @Community Alums, thank you for your assistance 

What you did, it’s work, but, in this case you’re comparing the category and sub from sys_choice table and this is good but in parts 

Let’s imagine a scenario, using the template that I’ve sent, that the value 45 already exists in the table as 45 - Catalog

in this case your script will not allow to create the category 45 from my template, because it’s already exists, but the subcategories are new values and this sequence will create with the category catalog as dependent. And this is will be wrong. Got you the point here?