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]

In transform maps when you map to a Choice or Reference field a "choice action" field appears allowing you to set what happens when an invalid value comes through the import.   You have the following choices:

1. Create - create a choice value or for reference fields create a record in the target reference table

2. Ignore - process the imported record but don't create the choice or record in the target reference table.

3. Reject - reject/error the entire row.   I would suggest this is what you do for invalid models since a CI cannot be created without a valid model.

You have the ability to set a specific error message too so when you look at the import set you can see why it was rejected.

Given you want to create a record in an exception table over and beyond the error message that would appear in the import set, you can create a onChoiceCreate Transform Script and insert the record in your exception table.   More details on that can be found here:

http://wiki.servicenow.com/?title=Transform_Map_Scripts#Mapping_with_Transformation_Event_Scripts

In that script you still have the ability to flag the row as an error and not process it but still create log entry in your exception table.

[jeffreymangan]

Thanks for the reply.

I did look at the page you referenced but it doesn't do what I need.

I need to lookup the vendor provided value to see if it's valid or not.

I have many imports that happen so it's probably best to write one business rule to validate the field no matter what imports the data.

Today, at least in this environment, the imports sure can create CI with null or bad data. Doesn't seem they reject anything.

So, I'm ok with vendor sending an unknown model. It's more important to let the CI create then have someone remediate these 'bad' or 'unknown' values.

I don't want a person to have to sift through import sets or logs to find any imports that sent unknown or bad values.

I want the business rule to lookup the field such as model_number and if it doesn't match an existing model_number in cmdb_model then don't stop the CI update or CI creation. I want to capture that in a ci_exception table to note that serial number has an "Invalid Model" so someone can investigate.

If a user attempts to add a bad model those are already rejected today. The user then has to investigate and resolve. However, during an import at 4am there is no one there to intervene at the point of CI creation to resolve. I want an import to move forward but make a note so later someone can remediate.

[Michael Ritchie]

What do you mean by "I need to lookup the vendor provided value to see if it's valid or not"?   The transform map does that for you and no script should be involved unless its a complex lookup.   What I mean by that is the default mapping for reference fields is the "display value" of the record but you can override that by filling in the "referenced value field name" field on the field map record.   So if your vendor is passing in a part number that is also stored on your CI you can just enter that column name (not the label) into that field and it will query using that field instead.

[jeffreymangan]

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.

None of these are reference field types.

Today the vendor sends a model_number. If I enter the model_number on the cmdb_ci_printer form it does nothing. You must provide the model_id on the cmdb_ci_printer form. The vendor does not provide the model_id. Therefore, I need to accept the model_number from the vendor, then have a business rule lookup that model_number in cmdb_model table to bring back the model_id to the cmdb_ci_printer form which then sets the manufacturer and updates the asset.

I need to fetch the model_id from cmdb_model using model_number. If no match then I want to create or update a record in a totally separate custom table where I will throw all exceptions. I'm pretty sure I need to use glide records in a business rule.

Something like....

var lookupModel= new GlideRecord('cmdb_model');
lookupModel.addQuery('model_number',current.model_number);
lookupModel.addQuery('u_vendor_model','Ricoh');
lookupModel.query();
if(lookupModel.next()){
current.setDisplayValue('model_id',lookupModel.getDisplayValue());
}

I got this business rule to lookup the model_id based on the model_number the vendor import puts into the cmdb_ci_printer.

It seems to work but I don't know how to expand this to

1) if no match then insert/update a record in the ci_exception table (described in my scenario).

I want to use this same business rule logic to validate other fields to see if they exist and to return other values in some cases.

For example, the vendor sends a building_code. I need to bring in the building_code to the cmdb_ci form but then I have to lookup the location_name from the cmn_location form where building_code=building_code.