Ok, so I thought that I had this working, but apparently there's something wrong with the query, or how I'm defining the relationship that I need in the related list. Basically what I have is a custom table that extends off of the Task table, where an internal team will be creating after action review reports with fields that need to be filled in. There are several fields on the form that basically relate to people, and we want to track their relationship to each report on the user record of that person.
We have several fields called "Principal" 1-6 ("u_principal_1", "u_principal_2", etc.) to track 6 main principal users that may potentially be associated with each report. They may or may not appear in one of the fields on the report, and if they do appear, they could be added in any order 1-6. The principal users are not on the "sys_user" table, but rather on a custom table that was created to ensure segregation of the data, but it mimics the setup of the "sys_user" table for the most part.
Now, the issue that I am having is that I created a relationship between the two custom tables under System Definition> Relationships and defined it as shown below:
Then I went to System UI> Related Lists and created an entry that references the relationship (using what appears to be the Attachments list as an example):
The issue that I am having though is that I thought that the Relationship script that I created would query the "Principal" 1-6 fields and show the related AAR records IF they appear in any one of those fields, but it's listing all of the records regardless if they are in any of the fields or not, and in fact display all the AAR records even if they are none of the fields on any of the AARs at all.
I also want to do something similar for some records on the "sys_user" table as well, showing what AAR records they appear on in similar fields "Additional staff" 1-6. I'm not sure if I need to create M2M relationships as well, so the above is what I have so far.
