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.

[jeffreymangan]

Thanks Michael.
I really appreciate all your help.

Here is my final version and it's working good.
I'll further adapt what you gave me.

answer=(function transformEntry(source) {  
var lookupModel= new GlideRecord('cmdb_model');
lookupModel.addQuery('model_number', source.u_model);
lookupModel.addQuery('u_vendor_model','vendor name here');
lookupModel.query();
if (lookupModel.next()){
return lookupModel.sys_id;
} else {
var ciException = new GlideRecord('u_ci_exceptions');
ciException.addQuery("u_ci", target.sys_id);
ciException.addQuery("u_status","IN","exception_found,fix_pending");   // Just increment the same exception if still not fixed
              ciException.addQuery("u_exception_type","invalid_model");
ciException.query();
if (ciException.next()) {
  ciException.u_exception_comment = ciException.u_exception_comment +","+gs.now();
  ciException.update();
} else {
  ciException.initialize();
  ciException.u_exception_comment = "Model was: "+source.u_model +" on "+gs.now();
              ciException.u_ci = target.sys_id;
  ciException.u_source= "TransformMap: Name Goes Here";
                              ciException.u_exception_type="invalid_model"  
ciException.insert();
  return source.u_model;
}
}
})(source);

[Michael Ritchie]

Awesome glad you got this working.

[jeffreymangan]

I think I understand when you talk about using the "referenced value field name" on the field map.

Even though model_number is not a reference field on the target table, the model_id is a reference field on the target table cmdb_ci_printer.

So if I set the Target field to "Model ID", I don't have to pass the model_id because I don't have it but I can enter "model_number" in the "Referenced value field name" and it will know to lookup the Model ID from cmdb_model using the model_number that I will pass in the transport map?

Is that correct?

I guess I can try it so I understand it better. But in the end I need to lookup model_number plus for a specific vendor in case more than one vendor has the same model number. So in this case, I'll use the script to map this field.

[Michael Ritchie]

Yes that is exactly the use case for the referenced value field name.   It is often used with user references where a user ID is passed in versus the ServiceNow SysID and you want to do the lookup based on the user id field instead.   The only issue with your use case is model numbers could be duplicated across vendors so you may need to do it via script instead so you can also search based on vendor as well.