Field dependencies based on 3 tables in Catalog Item

[Rutvik Khandeka]

I have 3 tables called as Countries, Jurisdiction and Qualification Type.
Countries table stores number of country records.
Jurisdiction stores Jurisdiction records
Qualification Type stores member type records.
On Jurisdiction and Qualification Type table I have created a field called Dependent Value that is of type List with reference to Countries table and assigned the country values to Jurisdiction and Qualification Type data.
After that I created a catalog item with 3 fields What is Country, What is Jurisdiction and What is Person Type and the type of these 3 fields is reference in catalog item. These 3 fields refers to the Countries, Jurisdiction and Qualification Type table respectively.

Can you please assist ?

So here when I select a any country the What is Jurisdiction and What is Person Type field get filtered based on Country. For this I have used the reference qualifier in What is Jurisdiction and What is Person Type field.

This is working as expected with the current approach

Now, for some specific data in Jurisdiction I want to filter the data in What is Person Type field. In this new scenario we don't have any relation with Countries table. The new scenario is specifically with Jurisdiction and Qualification Type table.

So, now I want the data to be displayed in What is Person Type field with existing scenario and with the current scenario without affecting the existing scenario in servicenow

[Rushi Savarkar · Accepted solution]

Hello @Rutvik Khandeka 

I understand your question. 

Let's consider A, B, and C are the variables on catalog item and referenced with Country, Jurisdiction, Person Type tables respectively.

As I understand it, B is dependent on A, and C is dependent on both A and B. Also, C is dependent on only A

I believe you have added the Ref. Qualifier on B to fetch the dependent Jurisdictions on Countries and it is straightforward. 

Now, we need to maintain the current and existing behavior; you will have to create the sys_m2m table and take the Jurisdictions and Person Type as "From" and "To" tables, respectively. In the m2m table, you can create custom dependencies. 

Now, you will have to create a server callable script include with function parameter as (jurisdictionSysID, countrySysID).

In the above script include you can do both things like. For ex. C is dependent on A & B and C is dependent on only A. 

Call the script include in the variable C as below:

javascript: new scriptIncludeName().functionName(current.variables.variable_a, current.variables.variable_b);

It should solve your dependency problem

Thank you!

[Ankur Bawiskar]

@Rutvik Khandeka 

you can use advanced ref qualifier with script include

To filter you need to have some direct or indirect relationship between those 2 tables.

You are currently filtering 2nd and 3rd variable based on 1st because the other 2 tables have field to store country.

Now when you select Jurisdiction how you want and on what basis you want to filter the Person? what's the common criteria?

If my response helped please mark it correct and close the thread so that it benefits future readers.