How to get the values filtered for Reference variable based on the other variable value

[harinya]

HI 
I have 2 variables in catalog form, employee type and manager
Employee type is choice field have 2 values(Temporary, permanent) 
Manager field is Reference of user table
Below is my Requirement,
If user selects Employee type is Temporary then Manager field should show values active users
If user selects Employee type is permanent then Manager field should show values active users and Employee number is not empty
Can some one please help
Thanks in Advance

[Zach Koch]

So this is a multi step process. First, you'll need a Script Include, then you'll need to set your reference qualifier to Advanced and call your script include. We will break down each step below.

Here is the example script include I created

You'll need to change out the values in the script to match your Employee Type values.

As for the record configuration, here is my example that calls the Script Include above

You'll need to change the values to match yourScriptIncludeName().yourMethodInTheScriptInclude(current.variables.yourFieldEmployeeType);

Here is the results from my example

Results in Manager when Employee type is Temporary (for context I only have 1 user record with an employee number who is active, so this returns all Active users except for one)

Here is the result with Permanent selected (returns the 1 user who has an employee number and is active)

Let me know if you run into issues and I can try to talk you through it.

[Slava Savitsky · Accepted solution]

In general, your approach is correct. However, it can be simplified and thus optimized for better performance. There is no need to loop through the list of users, collect their sys_ids and return a "sys_idIN..." encoded query. Instead, you should simply return either "active=true" or "active=true^employee_numberISNOTEMPTY" depending on the selected type. In fact, this can be done directly inside the reference qualifier field with just a single line of code:

javascript: var ref_qual = 'active=true'; if (current.variables.user_type == 'permanent') ref_qual += '^employee_numberISNOTEMPTY'; ref_qual;

[harinya]

Thanks for your Response