Is a field valid? If yes, bring back a value. If not, then create/update a record in an exception table.

[jeffreymangan]

Hi All,

I'm new to ServiceNow.

My question is probably very basic and common, however I can't find any examples in our environment or online where someone wants to validate field data that comes in via a file import.

Here is my scenario....

I import files from a vendor on a schedule into the cmdb.

I want to throw an exception if the vendor sends an invalid model or location or anything I need to lookup & validate.

If the model_number they send does not exist in cmdb_model then create a record in an exception table for that serial number, bad value and reason for exception.

I don't think this should happen during the transform map but probably as a business rule so it can validate the incoming field no matter which import brings in the value.

1. Transform map brings model_number into cmdb_ci_printer form.

2. Business rule should fire when model_number changes

3. Query the cmdb_model table where model_number & vendor in cmdb_ci_printer matches a record in cmdb_model for model_number & vendor.

4. If a match is found then bring back the display_name from cmdb_model and enter display_name into the model_id field on cmdb_ci_printer

5. If there is no match for that model_number then create or update a record in ci_exception table.

6. Insert the serial_number, model_number into ci_exception with a exception_type as "Invalid Model" and set exception_status as "Active".

7. If an "Active" ci_exception record for serial_number + exception_type already exists then just update the record so it has a new date/timestamp.

8. If no record exists in ci_exception table for serial_number+exception_type+exception_status=active then create a new ci_exception record

9. If the vendor sends a null model_number. I think the above logic would fire as it should not find a null model_number in cmdb_model table.

I would then use this same logic to test other fields such as building_code against cmn_location, etc....

Hopefully, someone has a sample of this business rule logic. I have to believe this is common practice to validate/lookup fields in a table.

I have 10 years Remedy development experience. I'm new to ServiceNow. I did take several courses. usually when a person is new like myself, we can use google and docs to find examples to get us started. This is one of those times when I can't find any similar code in our system nor examples online.   I'm going to need an example or step-by-step, I'm afraid. Once I see an example or can get some good details, I won't ask again and will start to understand better. We are using an on-site installation of the Geneva version.

[Michael Ritchie]

You can run these scripts in the transform map instead.   My personal preference is to compartmentalize everything together so I go to one place for all the logic versus having code in different places.   In your case you have the transform map plus the business rule.

I see based on your requirements you have to lookup a reference based on two values.   If it was simply model_number you could just enter model_number in the "referenced value field name" field on the field map record and be good to go.   So here is what I would suggest:

1. Open your field map for the Model and check the Use Source Script checkbox.
2. Paste in script similar to the following:

answer = (function transformEntry(source) {

       var lookupModel= new GlideRecord('cmdb_model');
       lookupModel.addQuery('model_number', source.model_number); //Validate the Import Set Table Source column name
       lookupModel.addQuery('u_vendor_model','Ricoh');
       lookupModel.query();
       if (lookupModel.next()){
               return lookupModel.sys_id;
       } else {
               var ciException = new GlideRecord("ci_exception");
               ciException.short_description = "Model provided by vendor is not valid";
               ciException.target_ci = target.sys_id; // Add a reference field to the cmdb_ci table and set it to the newly created CI so you can easily refer to it.
               ciException.WHATEVER_ELSE = "ADD ANY OTHER FIELDS YOU WANT TO SET";
               ciException.insert();
               return ""; // return the value to be put into the target field
       }

})(source);

  3. Inactivate (no need for) your business rule.

For anything else in your business rule you can add to the field maps too.   Hopefully this makes sense!

[jeffreymangan]

Thanks Michael,

I feel like such an infant. It's not a good feeling being a newbie.

I appreciate your time.

I will take this example and see how it goes.

I'm good with putting the script in the transform map on the field that needs looked up and/or validated.

After I get this working, I think I'll try to tweak it to ensure the exception doesn't 'insert' more than once for the same serial number + offense combination.

Not a big deal, but if the model is bad for serial number ABC, I don't need an entry each time the import runs. An update to the   already created serial_number+type will do. But that's minor. Hopefully the users will have corrected the bad data by the next time it tries to import.

I think I understand when you say "// Add a reference field to the cmdb_ci table and set it to the"..... You mean in my ci_exception table, I should make the serial number be a reference field back to the cmdb_ci table so when inside the ci_exception table you can have a link back to the CI to make the corrections.

Thanks again Michael.

[Michael Ritchie]

Hey no worries happy to help!   I was a newbie too 5.5 years ago.   Your knowledge about the platform will come fast, it just there are several different ways to accomplish something and that often creates added confusion.   Plus we all interpret and do things differently anyway, but like I said I prefer to put all the script together in one place to prevent hunting down the road for maintenance or enhancements.

In regards to inserting multiple exceptions, I thought of that too, but depending on your business process you may want multiple records.   The only other scenario, which may not apply, is what if a row has both a bad model and a bad location, do you want 1 or 2 exception records?   If 1 you could remove the field maps all together and put all this script in the main transform map record (click run script to expose it) and then use source.imported_field_name and target.target_record_field_name type syntax to manually map the values.   You could set variables along the way to flag that an exception needs to be created and then at the end of your script check the variable and insert 1 record with all the exception text.

Your approach with a serial number reference field is what I was suggesting.   Call it whatever makes sense but having a reference field that takes the user working the exception to the affected CI record is useful.   My recommendation is to make the reference to the Configuration Item (cmdb_ci) table is better that way you can use this table to handle all types of CI exceptions not just printers, as your example code deals with.

Let me know if you have any further questions.   Please mark any of these posts helpful or the correct answer to your question so others reading this thread can benefit.

[jeffreymangan]

Ok good. I understand the reference field from my exception table back to the cmdb_ci parent.

I'm ok with multiple entries for now into the ci_exception table. Ideally, I would want one for each type of offense.

Rec 1: Serial Number ABC has a bad model.

Rec 2: Serial Number ABC has a bad location.

This way one person may be tasked to resolved all "bad model" and another all "bad locations".

Each subsequent import would just overwrite the same Serial Number and type to show the last time it was encountered but no need for 10 of the same records.

I'm going to get to work now. I will let you know how it goes.

[Michael Ritchie · Accepted solution]

Here is a quick update to my earlier example code that will check for an active exception record on the CI and if so update it versus inserting:

answer = (function transformEntry(source) {

       var lookupModel= new GlideRecord('cmdb_model');
       lookupModel.addQuery('model_number', source.model_number); //Validate the Import Set Table Source column name
       lookupModel.addQuery('u_vendor_model','Ricoh');
       lookupModel.query();
       if (lookupModel.next()){
               return lookupModel.sys_id;
       } else {
               var ciException = new GlideRecord("ci_exception");
               ciException.addQuery("serial_number", target.sys_id);
               ciException.addQuery("active", true);   // You may want a state or active flag on your exception table to deal with situations where an exception was resolved but appears again.
               ciException.query();
               if (ciException.next()) {
                       ciException.short_description = ciException.short_description + "Found another exception";
                       ciException.update();
               } else {
                       ciException.initialize();
                       ciException.short_description = "Model provided by vendor is not valid";
                       ciException.target_ci = target.sys_id; // Add a reference field to the cmdb_ci table and set it to the newly created CI so you can easily refer to it.
                       ciException.WHATEVER_ELSE = "ADD ANY OTHER FIELDS YOU WANT TO SET";
                       ciException.insert();
                       return ""; // return the value to be put into the target field
               }
       }

})(source);