Create a filter of user table depending on selected field form

[Magali Legaspi]

Hi all!

I need help with this. I have a territory field on a form, and depending the selected territory, I need to filter a Reference field that has the user table. The users from that territory only have to show. I guess I'll have to use a Client script. Any idea on how I can do this?

Thanks in advance!

[Community Alums]

I've assumed that you have a territory field and a user field in one form and you want to filter users by the territory value, but apparently not. Please tell me more details of the relationships between the tables. 

In which table are the "Company" and "wbs_territory" fields located? And what kind of type are they? And what is "variables" field?

[Magali Legaspi]

So, I have one field that's a select box that contains territories made from scratch. The name of that field is "wbs_territory".
Then, I have one field that references the users on the User table. On the user table we use the field "company" to refer to a location/territory.

Thing is, the company field does not match the territories on the "wbs_territory" field. So I'd have to take the text on the first field and turn it into another text (like you did "US" into "United states of america"). This is all on a catalog item.

What I need to do is, for example, the user selects from field 1 "wbs_territory", the 'US' option. I need to transform that into "United States of America". Then I have to add this last text to the filter like "Company" "is" "United States of America".

[Community Alums · Accepted solution]

I prepared two fields.

WBS Territory is a Choice fields with two choices.

As I think company is a reference field, Script Include should return the sys_id of the company record. You should compare not a Label but a Value of choices.

var TerritoryUserUtils = Class.create();
TerritoryUserUtils.prototype = {
    initialize: function() {
    },

	getCompany: function(territory) {
		
		var grCompany = new GlideRecord("core_company");
		
		if (territory == 'us')
			grCompany.addQuery('name', 'ACME North America');
		else if (territory == 'jp')
			grCompany.addQuery('name', 'ACME Japan');
		
		grCompany.query();
		
		if (grCompany.next())
			return grCompany.sys_id;
		
	},
	
    type: 'TerritoryUserUtils'
};

And then, I call it from advanced ref qual of User fields. (Please rename the variables to suit your environment)

If your Territory isn't "Dropdown with --None--", if clause might not be needed.

javascript:if (current.u_wbs_territory != '') "company=" + new TerritoryUserUtils().getCompany(current.u_wbs_territory);​

Finally it seems to be working fine.

[Magali Legaspi]

Hello! First of all, thank you so much for your help so far 😁 but it's still not working for me. 😞 

The wbs_territory field is a select box with variables created from scratch:

Here's the script include:

and the reference qual from the User Reference field:

[Magali Legaspi]

Hello!! Ignore the other message. It's working perfect now! Thank you SO MUCH!!!! 😁