Adding a reference field for a reference field

[carram]

Hi, all,

This is my first time requesting assistance on the forums, so I extend my apologies if this confuses anyone who may want to help out. My team is looking at adding a reference field to the [alm_hardware] table which refers to the Manufacturer as it appears in a reference field under a model on the [cmdb_hardware_product_model].

Our current setup utilizes two fields on our Hardware form: (1.) a field labeled "Model" that references the [cmdb_hardware_product_model] table; and (2.) a field labeled "Manufacture" that references [core_company]. In this arrangement, we are using a business rule to set the "Manufacture" field based on a field on the selected Model record called "Manufacturer", which can be referred to as "cmdb_hardware_product_model.manufacturer". This field is actually a reference field to [core_company] as well. This works great when the model that we choose to put in the "Model" field is already documented in our system with the manufacturer. Where this has gotten tricky is with import sets, when we use a transform map to import data from Excel sheets into this form structure.

When a new model is present in our import set, a new record is generated on the [cmdb_hardware_product_model] via the "Model" reference field. The manufacturer noted in the Excel sheet, which gets brought in as a mapped field on an import set row, is then created/chosen from the choice list on the "Manufacture" field. In either case, when presented with a new model, the model gets created in the [cmdb_hardware_product_model] without a manufacturer (the aforementioned "cmdb_hardware_product_model.manufacturer" value).

Ideally, we could have something like a related table field, which can be added to our form via Configure > Form Layout. However, adding this field "cmdb_hardware_product_model.manufacturer" to the form does not make it accessible to us when importing data via a transform map, as this does not create a field on the target table. One approach I considered that didn't use reference fields was creating a string field on the [alm_hardware] table which would essentially "synchronize" values between a local field "manufacturer" with the "cmdb_hardware_product_model.manufacturer" field. I haven't given it much thought, however, as this seems to be a bloated workaround with lots of UI Policy wizardry that I don't think I could code myself.

Thank you so much for reading and for your help. I've searched seemingly everywhere and have come up empty-handed. I turn to you, Giga Sages and Tera Experts!

TL;DR: I need to create a reference field on one table that refers to a specific field on a related table.

[Anil Lande]

Hi,

As you said 'Manufacturer' field is already available on Model table. You can add it to Hardware form by using form layout configuration (using dot walk).

You don't need to worry about updating manufacturer in Hardware table. Actual data resides in model table and as soon as manufacturer field updated in Model record it will show same value in Hardware. As it is reference field and you have dot waled field on form. Thats the beauty of reference tables and dot walk fields. Reference data can be presented on related tables without updating target records and will reduce the efforts of maintaining additional column (at database)

Lets take a example:

You are loading new model with empty Manufacturer and also loaded few hardware records under your new model.

In this case both Model and Hardwares will have No Manufacturer info.

Lets say you manually (or through data import) update the manufacturer value in your New model, If you have dot walked field on hardware it will show the updated value of manufacturer. No need to load data of Hardwares with updated manufacturer.

[carram]

Hi, Anil,

The dot-walked "Manufacturer" field that exists on the Model table can be added to the form, but how can we import data/field map to a dot-walked field? To my knowledge, dot-walked fields only appear on the form and cannot be field mapped using Load Data/Transform Maps because the field doesn't actually exist on the target table itself.

In your example, you pointed out that the newly generated Model record would not have a manufacturer listed, and the same goes for the affiliated Hardware record. You then mentioned updating the manufacturer value in the new model through data import; is there a way to do this in a single transform map from ISET to two different target tables (in this case, both Hardware and Model tables)? 

Thank you very much,

Carlos

[Anil Lande]

Hi,

There are multiple ways to achieve this and depends on current data available in ServiceNow and what we are importing.

If you have required information for Models (including manufacturer ) then you can create another transform map for the same import set table and run it first by setting lowest order.

Another way is to use transform scripts, run transform scripts to update dependent records (onAfter would be good choice in your case)

https://developer.servicenow.com/dev.do#!/learn/learning-plans/utah/new_to_servicenow/app_store_lear...

https://www.youtube.com/watch?v=sFXNBF103MM