Hey Folks of ServiceNow! I was wondering if there is a way to create a default filter in related list that references a many to many tables and that it must be dynamic! the issue I am having is because of the fact the tables Im using there is many to many relationship so when I try to get(or choose) information on a related list I get all of the data that it contains without restrictions! I would like to set a default filter that takes the current reference item that it has in common so it would only show the items associated to it for each form (each device in this case for what I am doing).. heres an example!
for example this is what I get in this related list without using a filter:
All of the elements in that table! but from those elements I only need those that has the "product" of the current table that has this related list:
in this case I only want to see those that belongs to this Panasonic Product, that could be achieved by a static default filter(I was able to do that) but it doesnt work because not all of the actions has as product like this panasonic device so another way to do this is manually each time you go into this slushbucket you create a filter for it:
wich is not good! we dont want to be creating filters each time we enter into this slushbucket we would like to have an automated dynamic filter for it! I've tryied everything but now I wonder if this is even possible or achievable?? thank you very much!
What I have tried:
*** Go into List control of this related list and modify the layout to show "Edit default filter" builder condition field.
*** Going into the dictionary of this field and modifying the UI Policy that hinders "the reference qualifier fields" , tested with a dynamic filter and it doesnt allows you to selec the dynamic filter you just created it doesnt even appear in the list of dynamic filters when you need to choose nor any dynamic filter.
*** Creating a ref qualifier for it. (DIDNT WORK).
*** Then I modified the "dynamic filter" field and created a dyanamic scripted filter as well didnt work, it doesnt show the filter once it is created.
*** All kind of different stuff and IT DIDNT WORK!
Thank you very much Guys in regard Jose
Hi all,
I had same requirement to make reference qualifier working for many-to-many tables when relating new records via Edit button of related list (e.g. Affected CI's on Change Request). Ideally it should work in slushbucket but also when you open the many-to-many record's form directly and use reference lookup there.
I made it working with following approach and hope it can help you as well so I share an example:
1) Reference qualifier
Firstly you need to define Advanced Reference Qualifier for the reference field on many-to-many table that references the records you want to restrict (limit their availability in reference lookups and slushbucket).
Call a script include and provide parameter as follows:
current ? current : parent
This ensures that if "current" is not defined in the scope, parent is sent to the function instead. This makes all the trick to handle both, reference lookups in the form and slushbucket because "current" is available only when many-to-many record's form is opened, however "parent" is available when slushbucket is opened via Edit button from parent's record form.
Note: if syntax above does not work, try this one as once only this one worked for me:
typeof current !== "undefined" ? current : parent
Here is example of refQual on [ci_item] reference field of CI's Affected [task_ci] table:
2) Script Include
Create a Script Include function that is called from advanced reference qualifier as shown above and implement logic that handles both scenarios, when "current" is provided or "parent" is provided. Example for CI's Affected table and Configuration Item [ci_item] reference field is here:
/**
* Returns reference qualifier for Affected CIs [task_ci] Configuration Item field [cmdb_ci].
* Since it is M2N table reference qualifier can be called from the record's form or via
* clicking Edit button in related list resulting in List Collector slush-bucket page.
* Based on the source either current or parent object is existing this function must be then
* called with "current ? current : parent" parameter.
*
* @param record {GlideRecord} either [task_ci] record or parent record (e.g. change request)
* @returns {String} encoded query to be used as advanced ref qualifier
*/
function u_affectedCIsRefQual(record) {
if (record.getTableName() == 'task_ci') {
// Reference qualifier is called from Task CI form
if (!gs.nil(record.task)) {
var taskGR = record.task.getRefRecord();
if (taskGR.getTableName() == 'change_request') {
return 'location=' + taskGR.getValue('location');
} else if (taskGR.getTableName() == 'change_task') {
return 'location=' + taskGR.parent.location.toString();
}
}
} else {
// Reference qualifier is called from List Collector via Edit button in related list
if (record.getTableName() == 'change_request') {
return 'location=' + record.getValue('location');
} else if (record.getTableName() == 'change_task') {
return 'location=' + record.parent.location.toString();
}
}
// Default no restrictions
return '';
}
This code ensures that CIs available in slushbucket opened from Change Request or Change Task are only those with same Location as parent's Change Request (second part of the script). Same when you open CI's Affected form directly and use Configuration Item reference field there (first part of the script). This is just example, you can imagine that any dynamic condition can be scripted here, just return encoded query you need. I used this approach for even more complex scenarios.
