15 Replies · Latest reply on May 24, 2017 8:16 AM by Andrew Pilachowski

    unable to lookup ( call field) from another table

      i want to call 'name' field in another table where ip add  is 192.168.167.124  .... name should come as 'test2'  ,

       

      Am having requirement to lookup ( call the field ) from another table , i have written a business rule to update same , but some how its not working .

       

      kindly assist /guide me to achieve my requierment .

       

       

      I have created a column as 'name' in 'dscy_credentials_affinity' table  , i need to get the name of credential (label name) compared 'credential_id /sys_id' from 'discovery_credentials ' table.

       

      I am using below business rule to achieve this requirement but not getting

       

      var gr=new GlideRecord('dscy_credentials_affinity');

      gr.addQuery('credential_id',current.credential_id);

      gr.query();

       

      where(gr.next())

      {

      //gr.setValue('u_credential_name','discovery_credentials.name');

      gr.setValue('u_credential_name',name);

      gr.update()

      }

       

       

      kindly help me on this please

        • Re: unable to lookup ( call field) from another table
          Michael Fry

          The results from your query should give you a sys_id which is associated to this table: discovery_credentials

          You'll have to take the sys_id from the first GlideRecord and do another GlideRecord on discovery_credentials to get the name.

            • Re: unable to lookup ( call field) from another table
              shoban mamidi

              Hi Michael,

               

              Thank you for suggestion ,  here am little confuse to update the script , kindly help me to write possible script please.

              parent table name  -    discovery_credentials               ; child table name  -   dscy_credentials_affinity

               

              Thanks

              Shoban

                • Re: unable to lookup ( call field) from another table
                  Shishir Srivastava

                  Please try with below code if this helps or gives you further clue to proceed.

                   

                  (function executeRule(current, previous /*null when async*/) {

                    // Add your code here

                    var cred_sys_id = current.getUniqueValue();

                    var gdca = new GlideRecord('dscy_credentials_affinity');

                    gdca.addQuery('sys_id', cred_sys_id);

                    gdca.query();

                       if(gdca.next())

                         {

                              var nameField = gdca.getValue('name');

                              var gdc = new GlideRecord('discovery_credentials');

                              gdc.addQuery('credential_id', nameField);

                              gdc.query();

                   

                              if(gdc.next())

                              {

                                   gs.addInfoMessage('Field Name :    ' + gdc.getDisplayName('name'));

                                   gdc.setValue('u_credential_name', gdc.getDisplayName('name'));

                                   gdc.update();

                              }

                         }

                  })(current, previous);

                  Thank you!

                  Regards,
                  Shishir Srivastava

                    • Re: unable to lookup ( call field) from another table
                      shoban mamidi

                      Thank you shishir , i could not call the actual field with above code .

                       

                      its displaying the field name as name , but actual requirement to update the credential_name field need to be update..

                       

                       

                        • Re: unable to lookup ( call field) from another table
                          Shishir Srivastava

                          Can you please try to debug the code by capturing some logs, that will help you to understand what value are you getting at what stage.

                           

                          For example:

                           

                          (function executeRule(current, previous /*null when async*/) {

                            // Add your code here

                            var cred_sys_id = current.getUniqueValue();

                            var gdca = new GlideRecord('dscy_credentials_affinity');

                            gdca.addQuery('sys_id', cred_sys_id);

                            gdca.query();

                           

                               if(gdca.next())

                                 {

                                      var nameField = gdca.getValue('name');

                                      gs.log('nameField' + gdca.getValue('name'));

                                      var gdc = new GlideRecord('discovery_credentials');

                                      gdc.addQuery('credential_id', nameField);

                                      gdc.query();

                           

                                      if(gdc.next())

                                      {

                                           gs.addInfoMessage('Field Name :    ' + gdc.getDisplayName('name'));

                                           gdc.setValue('u_credential_name', gdc.getDisplayName('name'));

                                           gdc.update();

                                      }

                                 }

                          })(current, previous);

                          Thank you!

                          Regards,
                          Shishir Srivastava

                          • Re: unable to lookup ( call field) from another table
                            Andrew Pilachowski

                            Add some logging.

                             

                            Are you sure you are getting a defined current.credential_id value?

                             

                            On the commented line gr.setValue('u_credential_name','discovery_credentials.name'), you are trying to set the value to the literal string "discovery_credentials.name" (though it is commented out)

                            Also where is the object variable discovery_credentials coming from?

                             

                            On this line gr.setValue('u_credential_name',name), where is the variable "name" coming from?  If its not set anywhere in the script, you are trying to set the value to an undefined variable.

                              • Re: unable to lookup ( call field) from another table
                                shoban mamidi

                                credential_id from  dscy_credentials_afinity  table ( where i want call the field)  required field name is credential_name.

                                 

                                 

                                 

                                source table (discovery_credentials) from where i need to get the value is :

                                 

                                  • Re: unable to lookup ( call field) from another table
                                    Andrew Pilachowski

                                    You want to know that credential ID a490.......e89 is named test2?  It seems like you are over-engineering a design.  Query the first record on Credential Affinities, dot walk to the Discovery Credential name (untested)

                                    var gr = new GlideRecord("dscy_credentials_affinity");

                                    gr.addQuery("ip_address", <variable_storing_ip>);

                                    gr.query();

                                    if (gr.next())

                                    var credentials_name = gr.discovery_credentials.name;

                                     

                                    Edited, guessing at this

                                      • Re: unable to lookup ( call field) from another table
                                        Andrew Pilachowski

                                        Point of my example is, let table relationships work for you. Don't try to re-store the same information unless you have an absolute business need.  This will relieve you of a lot of headaches later on.

                                          • Re: unable to lookup ( call field) from another table
                                            Andrew Pilachowski

                                            If you want to continue with this:

                                             

                                            If this is a business rule on the dscy_credentials_affinity table then

                                             

                                            current.u_credential_name = current.discovery_credentials.name;

                                            current.update();

                                             

                                            This will set the inserted/updated credentials affinity record with the associated discovery credentials name to the u_credential_name field.  If this is a business rule on another table, then please add a screenshot of the business rule you are trying to work with.

                                              • Re: unable to lookup ( call field) from another table
                                                shoban mamidi

                                                Hi Andrew,

                                                 

                                                I have write this code in business rule .

                                                 

                                                var gr = new GlideRecord("dscy_credentials_affinity");

                                                gr.addQuery("ip_address", ip_address);

                                                gr.query();

                                                if (gr.next())

                                                gr.u_credentials_name = gr.discovery_credentials.name;

                                                gr.update();

                                                 

                                                 

                                                i could not see any update in table.

                                                 

                                                  • Re: unable to lookup ( call field) from another table
                                                    Andrew Pilachowski

                                                    Please show screenshots of When to run and Actions tab on business rule.  Business rules will run before or after insert, update, or delete depending on what you have selected.  If you need to run a batch to copy the name down to all existing records, or to a specific single existing record, you need to create a job, fix script, or such, something you can run on demand.  If you want to copy down the name value only when the credential affinity record is inserted or updated, you can keep the business rule.

                                                     

                                                    I also have to apologize, I've been going blind on this only relying on your screenshots and making assumptions.  There is no reference field between the two tables which is what I was relying on being there in my code.  I went into my own dev instance and mocked up a demo.

                                                     

                                                     

                                                    Here's the script.  We need to query the discovery credentials table because a relationship does not exist between credential affinity.  We can also just use GlideRecord get because we want a single record where the sys_id is equal to the credential_id of the current record.

                                                     

                                                    (function executeRule(current, previous /*null when async*/) {
                                                    
                                                      var cred_id = current.credential_id;
                                                    
                                                      var dscy = new GlideRecord('discovery_credentials');
                                                    
                                                      if (dscy.get(cred_id)) {
                                                        current.u_credential_name = dscy.name;
                                                      }
                                                    
                                                    
                                                    })(current, previous);
                                                    

                                                     

                                                    When I manually add an affinity record:

                                                     

                                                    Other option is to create a job to update all/one existing affinity records in one batch.  You could actually use the two in conjunction if you want existing and new records to show u_credential_name.

                                                     

                                                    (function() {
                                                      //uncomment below line if you only want to update one record
                                                      //var ip_address = '127.0.0.1';
                                                    
                                                      var aff = new GlideRecord('dscy_credentials_affinity');
                                                      //uncomment below line if you only want to update one record
                                                      //aff.addQuery('ip_address', ip_address);
                                                      aff.query();
                                                    
                                                      while (aff.next()) {
                                                      var cred_id = aff.credential_id;
                                                    
                                                      var dscy = new GlideRecord('discovery_credentials');
                                                    
                                                      if (dscy.get(cred_id)) {
                                                      aff.u_credential_name = dscy.name;
                                                      aff.setWorkflow(false);  //Disable business rules from running
                                                      aff.update();
                                                      }
                                                      }
                                                    })();
                                                    

                                                     

                                                    Before job run:

                                                     

                                                    After job run:

                                • Re: unable to lookup ( call field) from another table
                                  Andrew Pilachowski

                                  Unfamiliar with MID servers and the tables referenced, but hopefully I can help you with your scripting.

                                   

                                  It looks like your "name" record in the screen shot is the parent ("one" of the relationship) and the IP affinity are the children ("many" of the relationship).

                                   

                                  If you query the child table (var gr = new GlideRecord("<child_table>"); gr.addQuery("<field_on_child_table>", "<field_value>")), you could dot walk to the parent to access the name:

                                  gr.<parent_reference_field>.name

                                   

                                  As an extension of this:

                                  gr.<parent_reference_field>.name = "new value";

                                  gr.updateWithReferences();

                                  Inserting/Updating GlideRecord with References - ServiceNow Wiki

                                   

                                  If you query the parent, you need the parent ID or a query to select the parent that contains the child with the field value, I dont know how to do this offhand but pseudo (var gr = new GlideRecord("<parent_table>"); gr.addQuery("<figure_out_child_contains>", "<field_value>")), then you have the record you want the name of:

                                  gr.name

                                   

                                  And to finish off:

                                  gr.name = "new value";

                                  gr.update();