Never admit defeat! Here's a way that you can setup a Database view between a custom table and the change_request table, only showing the rows of the custom table that have one or more change requests populated in the list field, and showing one row for each change in the custom table list. The custom table column data is repeated for each row since the emphasis is showing a row for each change record represented in the list field. In my example my custom table has 5 records that are populated with 1, 2, 3, 4, and 5 change request records in a field named u_change. The end result is a Database View with 15 rows (5+4+3+2+1).
To do this, setup your view like so.
This should give you one row for each custom table record that has a value in the list field, plus one row for each change_request record. Next, create a before Query Business Rule, using the Database View Name as the Table. My Script on the Advanced tab looks like this.
(function executeRule(current, previous /*null when async*/ ) {
var answerArr = [];
var gr = new GlideRecord('u_list');
gr.query();
while(gr.next()){
if(gr.bkb_u_change.indexOf(gr.chg_sys_id)>-1){
answerArr.push(gr.sys_id.toString());
}
}
current.addQuery('sys_id', 'IN', answerArr.join(','));
})(current, previous);This is where we're doing the IN/contains join that can't be done in the view itself, then filtering the list results to only those records where the change sys_id is one of the custom table list field records.
