Reference filter based on form changes

Aaron Munoz · ‎12-03-2017

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 · ‎12-06-2017

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

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.
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.
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

Create a custom client-callable Script Include with two methods (for dynamic filter reference and for setting the project ID from the Client Script).
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.
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.
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

View solution in original post

sachin_namjoshi · ‎12-03-2017

Hi Aaron,

You can refer script include in your reference qualifier.

You can refer values changed in form in client script and then use Glideajx to call script include. ( this script include is used in your referene qualifier).

Also, dynamic reference qualifier is another option.

Regards,

Sachin

Aaron Munoz · ‎12-03-2017

Is it possible you could provide an example of a client script that changes the reference qualifier of a reference field? That is the thing I can't figure out. Something like making the assignment group filter change based on the user assigned to an unsaved record.

sachin_namjoshi · ‎12-04-2017

Hi Aaron,

Please check below sample client script

//Type appropriate comment here, and begin script below

var action = g_form.getActionName();

if(!g_form.isNewRecord() && g_form.getValue('type') == 'Emergency' && action == 'request.approval')

{

// alert('inside');

var ajax = new GlideAjax('Change_Ajax'); // change_ajax is script include

ajax.addParam('sysparm_name','checkIncident');

ajax.addParam('sysparm_id',g_form.getUniqueValue());

ajax.getXMLWait();

var result = ajax.getAnswer();// your script include will return assignment groups based on user

if(result == 'no')

{

alert('Incident required. Associate on the Incident Caused Change tab.');

return false;

}

Regards,

Sachin

Aaron Munoz · ‎12-04-2017

Hi Sachin,

Thanks for your reply. Your code would work if I wanted to validate a chosen reference value only after clicking a UI Action named "request.approval". What I need is a way to filter the reference lookup popover which displayes the list of records, after clicking the magnitying glass icon next to the reference field. I also need it to work in new records, which is not supported in the script you supplied because it uses the condition " !g_form.isNewRecord() ".

Please see the image below to see the kind of list I'm trying to filter based on values that have changed only in the form.