30 Replies · Latest reply on Sep 15, 2017 1:15 AM by Shishir Srivastava

    Retrieving records from a reference table

      Im retrieving two columns fields with values for the Category and subcategory fields from table categories... In a separated process, I find the unique values for the choice field Category

      and I am able to populate the unique values...... Now in the form I will like to populate the subcategory base on the selection of category field value....

      the code

       

      var SubCategoryFields = Class.create();

      SubCategoryFields.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {

          subCategoryFields: function() {

        var array = [];

        var gr = new GlideRecord('u_categories');

        gr.addQuery('u_category',this.getParameter('sysparm_category')); +++++++++++++++++++ this instruction fails..... the parameter value is a integer (0,1,2,3) send by the client script.

                                                                                                                  How can I pass this instruction by filtering out the subcategory of the category selected.

        gr.query();

       

        while(gr.next()){

           var obj = {};

        obj.subCategory = gr.getValue('u_subcategory');

        gs.addInfoMessage(obj.subCategory);

        array.push(obj);

        }

        return JSON.stringify(array);

        },

          type: 'SubCategoryFields'

      });

       

        • Re: Retrieving records from a reference table
          Sanjiv Meher

          Are you sure, you are querying the right table? 'u_categories' should be storing the categories and not sub categories.

           

          var SubCategoryFields = Class.create();

          SubCategoryFields.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {

              subCategoryFields: function() {

            var array = [];

            var gr = new GlideRecord('u_categories');

            gr.addQuery('u_category',this.getParameter('sysparm_category')+''); +++++++++++++++++++ this instruction fails..... the parameter value is a integer (0,1,2,3) send by the client script.

                                                                                                                      How can I pass this instruction by filtering out the subcategory of the category selected.

            gr.query();

           

            while(gr.next()){

               var obj = {};

            obj.subCategory = gr.getValue('u_subcategory');

            gs.addInfoMessage(obj.subCategory);

            array.push(obj);

            }

            return JSON.stringify(array);

            },

              type: 'SubCategoryFields'

          });

          Thanks,
          Sanjiv

          Please Hit like, Helpful or Correct depending on the impact of the response

          • Re: Retrieving records from a reference table
            michael ocasio

            This is a customize table which I populated with two columns fields such as

            Category        subcategory

            Fruit               Orange

            Fruit               Banana

            Vegetables     Potatoes.

             

            the above script, should return a query base on the category, the problem is at  gr.addQuery('u_category',this.getParameter('sysparm_category')); it returning false therefore the query fails... 

            • Re: Retrieving records from a reference table
              michael ocasio

              Well first on loading the the category fields (choice type) gets populate it with unique values.....

              in this case : fruits and Vegestables   ---- - two objects....

               

              this part on change on form category field changes it triggers the above include script call from a client script passing the value of the category field selected by the user.... this is a numeric value (0,1) for this example... it is in the addquery that fails ..... do not know why

              • Re: Retrieving records from a reference table
                michael ocasio

                function onChange(control, oldValue, newValue, isLoading, isTemplate) {

                   if (isLoading || newValue === '') {

                      return;

                   }

                 

                 

                   var getSubcategoryFields = new GlideAjax('SubCategoryFields');

                 

                  getSubcategoryFields.addParam('sysparm_name','subCategoryFields');

                 

                 

                  getSubcategoryFields.addParam('sysparm_category', g_form.getValue('categoryexample'));

                 

                  getSubcategoryFields.getXML(populateFields);

                 

                 

                  function populateFields(response){

                  var answer = response.responseXML.documentElement.getAttribute("answer");

                  answer = answer.evalJSON();

                 

                 

                  alert(answer[0].SubCategory);

                 

                  g_form.clearValue('subcategoryreference');

                 

                  var i;

                  for (i = 0; i < answer.length; i++)

                  {

                   g_form.addOption('subcategoryreference',i,answer[i].SubCategory);

                  }

                  }  

                  • Re: Retrieving records from a reference table
                    Shishir Srivastava

                    Can we try in this way since you need to get subcategory,

                     

                    client script:

                    var getSubcategoryFields = new GlideAjax('SubCategoryFields');

                    getSubcategoryFields.addParam('sysparm_name','subCategoryFields');

                    alert(g_form.getValue('categoryexample')); //Let's see what it alerts

                    getSubcategoryFields.addParam('sysparm_category', g_form.getValue('categoryexample'));

                    getSubcategoryFields.getXML(populateFields);

                    function populateFields(response){

                    var answer = response.responseXML.documentElement.getAttribute("answer");

                    var answers = answer.split(',');

                    alert(answers[0]);

                    g_form.clearValue('subcategoryreference');

                    for (var i = 0; i < answers.length; i++)

                    g_form.addOption('subcategoryreference',i,answers[i].SubCategory);

                     

                    script include:

                    var array = [];

                    var gr = new GlideRecord('u_categories');

                    gs.log('category :' + this.getParameter('sysparm_category')); //Let's check what is logs.

                    gr.addQuery('u_category', this.getParameter('sysparm_category'));

                    gr.query();

                    while(gr.next())

                    array.push(gr.getValue('u_subcategory'));

                    return array;

                    Thank you!

                    Regards,
                    Shishir Srivastava

                    PS: Your feedback (Like, Helpful or Correct) helps community

                  • Re: Retrieving records from a reference table
                    michael ocasio

                    it returns numeric values such as (0,1,2 which correspond to the field value in the choice field...

                      • Re: Retrieving records from a reference table
                        Priyanka R

                        Hi Michael,

                         

                        Good Day

                         

                        I can understand that from the view that back end value of the category is 0,1,2 and the display value is like (fruit, vegetable).

                         

                        Hence while you GlideRecord the table, in the addQuery you can pass the value of category (0,1,2).

                         

                        It should works, as the category on the form is also refers to category table.

                         

                        Please let me know if you have any concerns.

                         

                        Thanks,

                        Priyanka R

                      • Re: Retrieving records from a reference table
                        michael ocasio

                        If I hardcode the parameter that im trying to pass from client such as this

                         

                        gr.addQuery('u_category','Fruits');

                         

                        for gr.addQuery('u_category',this.getParameter,'sysparm_category'));

                        AND i also comment out the parameter that Im passing from client

                        I am able to populate two values in the subcategory..... so the problem is this statement

                        getSubcategoryFields.addParam('sysparm_category',g_form.getValue('categoryexample'));which translate to a numeric value instead of the label of the choice field therefore failing gr.addquery expecting the label.

                        • Re: Retrieving records from a reference table
                          michael ocasio

                          two fields

                               u_category        u_subcategory

                           

                          No I dont used it as reference , it was my originally intention.... what I want is to populate these two choice fields in the form.

                          • Re: Retrieving records from a reference table
                            michael ocasio

                            Thank you all ... I change the value Im passing too when I am populate the category fields from another script....

                            g_form.addOption('categoryexample',i,answer[i].category);

                             

                            g_form.addOption('categoryexample',answer[i].category,answer[i].category);

                             

                            that did it all.....

                            in the back end script

                             

                            gr.addQuery('u_category',this.getParameter('sysparm_category')); this return true and it queries the subcategories base on the category pass from client side as parameter,...

                             

                            One note.... although is a GlideAjax function is takes a hit in performance..... in loading the categories (16 rows) in the Onchange of subcategories (the most are 12).

                            • Re: Retrieving records from a reference table
                              michael ocasio

                              I also noticed sometime the subcategory does not get populate...... I do clear out the control choice field before populating the field. I do not understand while sometimes works and sometimes dont. I being thinking probably these categories and subcategories content could be maintain outside the application,

                               

                              choice table contains all choice fields define....... so when there is a requirements to change a category I could probably update this table instead of the local one I have in the application. Is there a better way to do this....

                              • Re: Retrieving records from a reference table
                                michael ocasio

                                Just learn one side effect of using g_form.addOption it will trigger the onchange event on the client form.... So you need to check at the beginning you event outcome. so I added...

                                if (isLoading) {

                                  return;

                                  }

                                 

                                  if(isLoading)

                                    return;

                                  clearOptions();

                                 

                                  if(newValue == '')

                                    return;

                                  else

                                {

                                    doby of populating the subcategory choice fields..

                                }

                                 

                                I believe GlideAjax is getting fire before the function call clear the options then populating the choice field and then getting clear out by the clear function...

                                Is there a way sync this..?

                                • Re: Retrieving records from a reference table
                                  michael ocasio

                                  I did and loads and sometimes it does not load the subcategory choice fields.

                                  • Re: Retrieving records from a reference table
                                    michael ocasio

                                    I look into this but this is a different user case would you agreed,..... you are defining subcategory as a reference type..... so I wen thru the dictionary and change the type of subcategory as reference and created a new include script and deactivated the other two......this is the script.... the configuration is. I also left subcategory as a dependent field of category. I get the whole list but the is not filtering based on the category field.

                                     

                                    var BackfillSubcategoryChoiceFiled = Class.create();

                                    BackfillSubcategoryChoiceFiled.prototype = {

                                        initialize: function() {

                                        },

                                     

                                      BackfillAssignmentGroup:function() {

                                      var gp = ' ';

                                      var a = current.category;

                                     

                                      //return everything if the subcategory value is empty

                                      if(!a)

                                      return;

                                      //sys_user_grmember has the user to group relationship

                                      var grp = new GlideRecord('u_categories');

                                      grp.addQuery('category',a);

                                      grp.query();

                                      while(grp.next()) {

                                      if (gp.length > 0) {

                                      //build a comma separated string of groups if there is more than one

                                      gp += (',' + grp.subcategory);

                                      }

                                      else {

                                      gp = grp.subcategory;

                                      }

                                      }

                                      // return Groups where assigned to is in those groups we use IN for lists

                                      return 'sys_idIN' + gp;

                                      },

                                        type: 'BackfillSubcategoryChoiceFiled'

                                    };

                                     

                                      • Re: Retrieving records from a reference table
                                        Shishir Srivastava

                                        Can you please with below code:

                                         

                                        var gp = [];

                                        if(current.category.toString() == '')

                                        return;

                                        var grp = new GlideRecord('u_categories');

                                        grp.addQuery('category', current.category);

                                        grp.query();

                                        while(grp.next())

                                        gp.push(grp.sys_id.toString());

                                        return 'sys_idIN' + gp;

                                         

                                        ans also have new in your javascript code, like

                                         

                                        javascript: new BackfillSubcategoryChoiceFiled().BackfillAssignmentGroup();

                                        1 of 1 people found this helpful

                                        Thank you!

                                        Regards,
                                        Shishir Srivastava

                                        PS: Your feedback (Like, Helpful or Correct) helps community

                                      • Re: Retrieving records from a reference table
                                        michael ocasio

                                        It work.....Just that when selecting the subcategory it wont be assign to the reference field. why?

                                        • Re: Retrieving records from a reference table
                                          michael ocasio

                                          So what I need to do.... do I need to re-create the table categories. I was under the impression servicenow takes care of this..... implicitly every row in table categories which is made of category subcategory for values such as vegetables Potatoes. this row has a sysid assigned.

                                          it will defect the purpose if I can select the row from the table and be assign to subcategory field,,,,,, although if i hover the view of the field it shows assign the value selected. thanks for the help

                                          • Re: Retrieving records from a reference table
                                            michael ocasio

                                            What Im saying if I click on the reference button it brings the proper subcategory base on the category field selected but when I select the row if wont get assig to the form field (subcategory),

                                              • Re: Retrieving records from a reference table
                                                Shishir Srivastava

                                                What does it show? and you have told when you hover it then it shows the details. Looks like the display value is not set properly on u_category table. Can you please check if that's correct?

                                                 

                                                Open the table through System Definition -> Table and then open the table and please check what field is set for display?

                                                1 of 1 people found this helpful

                                                Thank you!

                                                Regards,
                                                Shishir Srivastava

                                                PS: Your feedback (Like, Helpful or Correct) helps community

                                              • Re: Retrieving records from a reference table
                                                michael ocasio

                                                Absolutely right. thanks