Prevent Duplicate Card Selection Across PPM Projects Using Reference Variable from cmdb_ci_ni_site

[vs00884087]

I’m working on a use case in PPM (Project Portfolio Management) where I want to ensure that a specific resource (referred to as a "card") from the cmdb_ci_ni_site table is not reused across multiple projects.

Each entry in cmdb_ci_ni_site (e.g., a, b, c, ..., y) represents a unique card/resource.
I’ve added a custom field called card_list in the pm_project_task table, which also references cmdb_ci_ni_site

Example Scenario:

1. I create Project PRJ001 and assign cards a, b, d, and e to its tasks.
2. Now, when I create Project PRJ002, I want to exclude these already-assigned cards (a, b, d, e) from appearing in the card_list field dropdown.

Objective:

How can I dynamically filter the card_list reference field in pm_project_task so that it excludes cards already assigned to other active projects?

Additional Notes:

• I don't want to create any new column in cmdb_ci_ni_site table to store the project reference since its been managed by TNI and cant do any changes to their table.
• I want this to work across all projects, so that once a card is assigned in one project, it becomes unavailable for selection in others.
• Ideally, this should be implemented using a Reference Qualifier or a Scripted Filter.

[Kieran Anson]

You could create a simple script include to obtain the values. However for this use case, a related list would be more performative to query and build the exclusion rather than a list field type. I'd also consider caching the results, as the query could run numerous times. You'll also need to validate the chosen values on insert/update to avoid parallel updates with the same chosen resource/card

var AvailableResources = Class.create();

AvailableResources.filterAvailableCards = function(){
	let projectTasksGR = new GlideRecord('pm_project_task');
	//Consider whether you only want to include active project tasks
	//Or only projects in a certain state
	projectTasksGR.addActiveQuery();
	projectTasksGR.setNoCount(true);
	projectTasksGR.addNotNullQuery('card_list'); //custom field
	projectTasksGR.query();
	let chosenCards = [];

	while(projectTasksGR.next()){
		taskCards = projectTasksGR.getValue('card_list').split(',');
		chosenCards.push(taskCards)

	}

	//get unique elements
	let uniqueCardValues = [...new Set(chosenCards)];

	return 'sys_idNOT IN' + uniqueCardValues.join();

}

AvailableResources.prototype = {
    initialize: function() { },
    type: 'AvailableResources'
};