How to query a list of record based on their related list and not a field.

[JacksonDo]

I am currently in my table of customer_accounts. I want to filter accounts that DO NOT have any record associated with their related list of "sn_install_base_sold_product".   The funnel filter doesn't work since the sn_install_base_sold_product isnt a field on a form. How would I go about this?

I am guessing a script that query a record's related list, however, I am not script savy and not sure how to query a record's related list.

[Brad Bowman]

I assume this is a list view of the customer accounts table.  To be clear, do you want to show accounts that do not have any records in the Sold Products related list, or only show accounts that do have Sold Products? 

[JacksonDo]

I would like to do both. I want to see accounts that that DO NOT have any records in sold product related list, and I want to be able to switch it to the ones that DO if i need to.

[Brad Bowman · Accepted solution]

What you need to do in this case is create a Database View.  You can name it whatever you like, then create the View Tables like this:

When you click the 'Try It' related link, you can customize the List Layout like any other list view.  Since there is a left join on the account table, all accounts are shown, and a row for each Sold Product related to each account.  With this view you can now easily filter out rows where the Sold Product is empty or not empty.