Query Reference field based on a list type field of another table

[Vasilis Anastas]

Hello, 

I have an account (Test Account) in customer_account table. In this table I have crated a list type field called  "u_assignment_groups". In this I add for example 4 assignment groups. So Test Account has these 4 assignment groups.
Now I want in case form, when I select  this account (Test Account) to query Assignment group reference field and see only these 4 groups , not all the groups of the instance. I tried with script include and reference qualifier but not succeed. Any ideas?

[Sarika S Nair1 · Accepted solution]

Hi @Vasilis Anastas 

Add below reference qualifier in assignment group field( override dictionary)

javascript:'sys_idIN'+current.account.u_assignment_groups;

[Sarika S Nair1]

Hi @Vasilis Anastas  

Hope my response is helpful for you...please confirm.

[Maddysunil]

@Vasilis Anastas 

you can use a reference qualifier on the Assignment Group reference field. Reference qualifiers allow you to filter the available reference field values based on certain conditions.

Below script you can modify as a reference qualifier for the Assignment Group field on your form:

(function () {
// Get the current selected Account on the form
var accountId = current.u_assignment_groups.toString(); // Assuming u_assignment_groups is a reference field to the account table

// If the Account is selected, filter assignment groups based on the selected account
if (accountId) {
// Create a GlideRecord to query the assignment groups related to the selected account
var assignmentGroupGr = new GlideRecord('your_assignment_group_table'); // Replace 'your_assignment_group_table' with the actual table name of the Assignment Group table
assignmentGroupGr.addQuery('account', accountId);
assignmentGroupGr.query();

// Create an array to store the sys_ids of the allowed assignment groups
var allowedAssignmentGroups = [];

// Populate the array with sys_ids of assignment groups related to the account
while (assignmentGroupGr.next()) {
allowedAssignmentGroups.push(assignmentGroupGr.sys_id.toString());
}

// Set the reference qualifier to filter Assignment Group choices
current.addQuery('sys_id', 'IN', allowedAssignmentGroups.join(',')); // Assuming sys_id is the field name of the Assignment Group reference field
}
})();

Please mark this helpful, if it helps you in anyway, thanks