business rule to lookup value in another table

[Jeff316]

This should be very simple but I cannot find any example of this online or in the self-paced scripting class.

Yes, I'm new to ServiceNow.

My Scenario:

1. I have a scheduled import set with a transform map that inserts records into the cmdb_ci_printer table.

2. During the transform, I derive a building_code value for each record in the cmdb_ci_printer table.

3. The cmdb_ci_printer table requires a value in the location_name field but I do not receive a location_name field value during the import/transform. I only know the building_code field value in the cmdb_ci_printer table.

4. The   cmn_location table also has a building_code field for each location_name.   *I cannot make building_code a reference field in the cmn_location table because the location_name field is already the display+reference field for that table.

5. I want a business rule that takes the building_code field value in the cmdb_ci_printer table and looks up the location_name field value in the cmn_location table where building_code=building_code

I thought it might be nice if the transform map can do the lookup during the import but I think a business rule would be better so any import is covered by this logic.

If you could point me to an example that would be very helpful. I'm going to need to do other lookups like this and having an example will really help.

[Manoj Kumar16]

is the building code a text field or a reference field in cmn_location table ?

[Jeff316]

building_code is a text string in cmdb_ci_printer and in cmn_location.

building_code is not a reference field in either cmdb_ci_printer or cmn_location.

cmn_location already has a reference field called location_name.

[Jeff316]

I think I just found out how to do it after days of trial and error.

This is how I did it....

In my business rule. I created this script.

var lookup= new GlideRecord('cmn_location');

lookup.addQuery('u_building_code',current.u_building_code);

lookup.query();

if(lookup.next()){

current.setDisplayValue('location',lookup.getDisplayValue());

}

I never specify the field I want to bring back from the cmn_location table since I never mention the field called "name".

However, I'm guessing since the "name" field in cmn_location happens to be the display+reference field, that is what comes back into the "location" field on the cmdb_ci_printer form which is where this business rule fires. I'm not sure what to do if the field I'm looking up is not the display+reference field of the lookup table. But for now this script works and brings back the "name" field from the table cmn_location for the u_building_code that matches in cmdb_ci_printer.

[Manoj Kumar16]

You can do it through script

You can check whether target.location.building_code == source.sys_import_set.data_source.building_code

the you can set the target location with the target location name if thats the display value of the reference field