Show all records on Related List

[geoff3]

Hi,

I have a requirement for a User to be able to open any record on Table A and be able to see (and filter/search) a list of ALL records on Table B.   There is no relationship between the 2 tables. Table B is being used as a manual lookup.

I cannot use a Related List because there is no relationship to the current record (on Table A) to any record on Table B.   I tried adding a Reference field on Table B (referencing Table A) and then adding the Related List to the Table A form. But I cannot figure out how to remove the default filter referencing the current record so that I can see ALL records. So the result is there is always zero records displayed.

I tried creating a UI Action that uses GlideDialogWindow() to display a list of records. This was a very simple way of displaying all the records on Table B when the User clicks a button - but I cannot get the list to display the filter.   You can use the row context menu for "Filter Out" and "Show Matching", but I need the list filter at the top for the user to be able to effectively filter thousands of records.

I have seen an example of a UI Page using the line "ListProperties.setHasFilter(true);". But I have not seen it make any difference when I test it.

Another idea was to create a many-to-many relationship between the 2 tables, then add the m2m table as a Related List on the Table A form.   However, this would mean I would need to link every record in Table A to every record in Table B.   And since there are thousands of records in each table, this could create a significant drain on resources.

Since Table B is being used as a manual lookup, I could potentially limit the number of records needed in the m2m table be generating them only as needed (an on Display Business Rule on Table A) and then cleaning them up later. But it's not exactly an elegant solution.

So, my questions are:

1. How can you show ALL records in a Related List?
   OR
2. How do you display the filter on a list in a GlideDialogWindow()?
   OR
3. Can you think of another way to do this?

Thanks,

Geoffrey

[Jamie_douglas · Accepted solution]

Geoff can you try creating a relationship (System Definition > Relationships).. applies to the table you want it on.. then queries from the table you want all records to show.. then in the query just leave it blank and it should show ALL records. Give that a go and let me know how you go?

[Jamie_douglas]

to you second question this link may help

Displaying a Custom Dialog - ServiceNow Wiki