How to reference multiple tables in one field using scripted reference qualifier?

[sambhogal]

Happy New Year Folks!!

I want to be able to reference multiple tables in a field (named 'Target', reference type field) based on the value of another field (named 'Type').

For e.g.

when 'Type' = 'Service Offering', field 'Target' should reference 'service_offering' table;

when 'Type' = 'Configuration Item', field 'Target' should reference 'cmdb_ci' table etc.

How can this be achieved? Thanks for your help!

[The SN Nerd]

As long as your tables share a base table, this is very achievable.

Simplest way to do this would be to have the value of the type field correspond to the table you to filter by.

E.g. Type is a choice list, Display value "Service Offering" has a value of 'service_offering'

Then in your advanced reference qualifier (Anvanced view), put

javascript:"sys_class_name=" + current.getValue('type')

Otherwise, implement similar logic via a script include and call that.

[Robert Beeman]

I like Paul's solution, but from your description it doesn't sound to me that these tables are extended off of a single parent. Is there any reason that there needs to be only one "Target" variable? You can have multiple reference variables to different tables with the same question(label) of "Target", and then just use a UI Policy to show only the correct one for the value of the "Type" field. In your workflow, you could then key off of the "Type" variable again to know which "Target" variable to use.

[anusha60]

Hi

what is the reference table value of Target field?

Please select it as "Task" table and use below reference qualifier :

javascript:"sys_class_name=" + current.u_type;

Note:   This will not work for the custom tables which are not extending from task table.

[sambhogal]

Thank you for the quick responses.

I like Paul's solution as well. However, the tables don't have a common base table. For example, some of the tables which need to be referenced are core_company, service_offering etc. There may be custom tables in the future as wel.

To Robert's point of creating new fields with a similar label, is not scale-able for the solution we intend. The intention is to use the 'Type'+'Target' to identify the reference source and take an action accordingly as the there can be many different types of Targets.

Anusha - The reference table value is not static and the solution needs to be smart enough to identify which table to reference based on the selected 'Type' value.

Looking forward to your responses.