Dynamic related lists on forms

[david_rosalia]

This is a bit of a hack but it offers a great flexibility and potential.

This will enable you to have the related lists at the bottom of your forms be dependant on data present on the form.

Step 1:
Create a relationship record and select the advanced option.
Enter the form on which you want the related list to appear in the "Apply to" field:
for example:
answer='incident';

Do the same for the "Query from" field:
for example:
answer='incident'; /*the table here doesn't matter as it will be replaced by the code*/

Note: The apply to and Query from fields are meant for returning a table name from a script. Unfortunately, the script cannot "see" the contents of the data on your form (neither "current" nor "parent" is known from these fields or at least not in my tests) so the step 3 blow overrides this limitation.

Enter whatevery query criteria you want in the "Query with" field. Here you can reference your form and related list fields:
I use a condition builder on my form and enter the query here to make it really flexible:
for example:
current.addEncodedQuery(parent.u_condition_builder);

Step 2:
Add the related list to the form which is referred to in the "Apply to field" above

Step 3 (the hack): Add a calculated field to the form.
In the script of the calculated field add the following code :

// This condition must exist for this to work because the condition is run 2X on display
// The first time the value is empty and makes it fail
if(current.u_table_selector != ''){ // this is the field on my form which contains the related list table name
var relate = new GlideRecord("sys_relationship");
relate.addQuery('name','related records'); // The second parameter is the name of the relationship from step 1
relate.query();
relate.next();
// the following will give (for example) answer='incident'; in the relationship record in the Query from field
relate.query_from = "answer='" + current.u_table_selector + "';";
relate.update(); // modify the relationship record
}

And that's it! Now your forms can have dynamic related lists. I do not recommend this solution but just wanted to show that it is possible.

[B_10]

Thank you

[B_10]

Thank you for this.  I tried this, and even though the calculated-field script is successfully going and changing the relationship record, my related list on my page apparently loads before the script changes the relationship record, because the related list still shows according to the old values on the relationship record.

For example, if my relationship queries from alm_hardware, and I change the table to the problem table, I refresh the form--which triggers the script, but my related list still shows as alm_hardware.  If I refresh the page one more time, then the related list correctly reflects the problem table.

Any idea how I can get the changes made to the relationship record to be immediately reflected when the calculated-value script runs?  Instead of having to refresh the page afterwards?

Thanks

[B_10]

Thank you for this.

I can only get this to work if I have my related-list loaded set to "After Form Loads" or "On-demand."  Is there a way that I could get this to work even if I use the related-list loading option "With the Form?"

If I have the "With the Form" option selected, the calculated-value script does go correctly change the relationship record.  However, the related list does not reflect the changes made to the relationship record.  I have to refresh the page again to see the changes on the relationship record reflected on the related list.

I've tried OnLoad scripts with AJAX, and things like hiding/showing, and refreshing the related lists, but none of that makes the related list reflect the changes on the relationship records unless I refresh the page.  That doesn't make for a great user flow, and I am trying to make this functionality I am working on not require certain settings.  Any ideas?

[B_10]

Thank you