Adding a reference field for a reference field

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.