Building a defined related list using queries from another table

[Ben LaChance]

So right now, I'm trying to build out a defined related list who's construction depends on multiple tables.   There currently exists a many to many relationship between two tables, and I'm trying to leverage that table to show all the records that are associated with another record that is assigned to a user.   To make it more clear:

User is assigned to Table 1 Record 1

Table 1 Record 1 is associated to Table 2 Record 1, 2 and 3.

I would like a related list on the User table that shows Table 2 Record 1, 2, and 3

So my current idea is to query Table 1 for records assigned to the user.

Then I try to use that data to query the M2M table that exists between Table 1 and Table 2 for records containing the correct reference back to Table 1 as determined by the first query.

Those records SHOULD be the exact records that need to be displayed in the Related List on the User record.

My code for this is below, but I could be fundamentally wrong in my approach.   My problem is there is not a lot of documentation on scripting in defined related lists, and how exactly it works.   The parent is the user record that the list is being generated for.

var gr=new GlideRecord('u_key_table');
gr.addQuery('assigned_to', parent.sys_id);
gr.query();

var areaIDArray=new Array();

while(gr.next()){
       areaIDArray.push(gr.sys_id.toString());
}
var areaIDStr=areaIDArray.join();
current.addQuery('keys', 'IN', areaIDStr);

Any help would be hugely appreciated!

Some clarification:   The above code doesn't actually work to bring up the correct records.   I'm not entirely sure why, and it's one of a number (although probably the best) of different approaches I took at the problem.   The result is always the same:   Only one record is returned, ever.

Okay, final edit:     I've gone back to the problem and tried it again.   Last night, the code would not work at all, despite browser history being cleared, flushing the cache, whatever.   This morning, I went to add the related list back to the form (I had taken it off last night in order to demo the system this morning), and when I went to add it back onto the form this morning to test it, it wasn't there.   The relationship still existed, and all I did was look at the relationship record and save it... when I went back to the user table, the related list was again available, and it was actually working.   I have NO IDEA what made it start working, and now if anyone has any insight into why it would not work, and then suddenly start working, that would be hugely appreciated.

[coryseering]

Hi Benjamin,

There is nothing fundamentally wrong with your approach. However, if you expect that the list of records could get very large- say, because over time that user has hundreds of m2m relationships to records in Table 2, you will want to restrict the relationship to weed out the irrelevant stuff. For instance, if Table 2 has an Active flag, or an Open state, use that. Or date-limit the range to within the last month or two.

It's good to think of these things up-front so your users don't come to rely on some behavior that you then need to change. Overall, I think your approach is fine.

[Ben LaChance]

In this case, all the records that this will pull up will always be relevant, even if the list becomes very large.

The bigger problem is that the code doesn't work, and I can't pinpoint exactly why.   All it does it bring up the first relevant record on the table.

[coryseering]

Hi Benjamin,

When you cay clearing the cache, do you mean clearing the browser cache, or the Instance cache (by visiting /cache.do on your instance)?

Were there any interesting errors in your Node Log when you were testing this last night?

Anything show up in the Script Log around this time?

[Ben LaChance]

I've had problems with both types of cache in the past, so I cleared both of them.   I wasn't seeing anything unexpected showing up (a couple of syntax errors on first pass, and debugging messages appeared to be everything that was connected to the query actually occurring).     There was pretty massive LDAP update happening right around when I was doing this particular code, and the system was certainly less responsive than usual, but that's about all I noticed.   It's one of the reasons I was tearing my hair out, honestly, because it looked like it should be working, but it wouldn't do anything except return the same result every time.

(Also, I didn't try clearing the cache every time, but I cleared it at least once while this exact code should have been active.)