Reference filter based on form changes

[Aaron Munoz]

Is it possible to change the filter of a reference field based on form changes?

Maybe there is something similar to how list collector variables can change their filter with in an onChange catalog client script.

Reference qualifiers can use the "current" object, though it reads data from the database and doesn't consider fields changes in the client side. This is specially troublesome for filtering data in new, not-yet-inserted records. To use a reference qualifier I would need something like "javascript:new refQualifier().filterFunction(g_form.getValue('assigned_to'));", though g_form is not available.

[leonidkhaylov]

Very good question! I'm having a similar task currently.

I'm looking into dynamic reference qualifiers at the moment: Reference qualifiers

Unfortunately, neither g_form.clearOptions(), nor g_form.removeOption() seem to be working on the Reference field from an onChange client script...

I hope we will find a solution soon!

[ccajohnson]

Please let us know what you have so far. My guess is that you want to have some sort of scripted reference qualifier that shows a list of users that is limited based upon selections in the form. Because reference qualifiers run server-side, there is no need to have any client scripting. If for example, you have key fields that need to be filled in before the list of users is selected, you may want to consider using a UI Policy to hide the user field until those key fields are filled out.

[ccajohnson]

Instead of using g_form.getValue() method, just use current.fieldname. So your sample reference qualifier would look like:

javascript:new refQualifier().filterFunction(current.assigned_to);

The current record object is honored even if the record has not been saved.

[Aaron Munoz]

It seems like my actual situation was a little more complex, but I got it to work in the end. I was trying to dot-walk to the parent's fields in the reference qualifier, but this did not work. Instead I only passed the parent's sys_id, which works even in new records, and the dot-walking was done inside the script include called from the reference qualifier.

Thanks for the feedback!

[leonidkhaylov · Accepted solution]

Hello, Aaron!

It looks like I found a working solution for my case. I hope it can help you as well.

I will describe it with high-level bullet points here and can provide you some code snippets later, if needed.

Application of the dynamic filter on the reference lookup based on the selected form lookup value.

In my scenario I have a form, which displays among other fields two reference lookup fields: Project and Department. Both reference lookups can be used by the user independently. Both reference lookups provide selection of all available data without pre-filtering (all projects, all departments). As soon as I select a project in the Project lookup I want to be able either to leave the Department field empty or get a pre-filtered list of Departments, which have a relation to the selected project.

Solution proposal

1. Define a dynamic reference qualifier on the Form table's reference column to Departments. This qualifier will use a custom (client-callable) Script Include method to check whether a Project is selected and return either "project.sys_id="+<selected project id> in case the project is selected OR an empty string otherwise. This will allow for filtering of the Department reference lookup options.
2. Now the challenge is to get the information in this Script Include, whether the Project is set. I was able to implement that with the help of the onChange Client Script on the form's Project field. This script uses GlideAjax call to the custom Script Include and provides the value of the selected Project field.
3. Finally, the custom Script Include method (which gets the new Project ID information from the GlideAjax call) saves it to the session (gs.getSession()).

Now, when the user open the Department reference lookup, the dynamic reference qualifier calls the custom Script Include method, which gets the selectd Project ID from the session and returns the filtered list of Departments.

Imlementation Steps

1. Create a custom client-callable Script Include with two methods (for dynamic filter reference and for setting the project ID from the Client Script).
2. Define the Department column reference as using a Dynamic reference qualifier and in the Script field define a call to the custom Script Include method.
3. Create an onChange Client Script for the Project field in the form. This script should make a call to your custom Script Include with the help of the GlideAjax and provide the newValue of the selected Project as a parameter to it.
4. Use session.putClientData() and session.getClientData() in your custom Script Include to store and retrieve the Project ID in your dynamic reference qualifier.

This should be enough to implement the solution.

Good luck!

Leonid