Pull data across 3 tables in ServiceNow

Question

I am trying to collect data across 3 different tables, through a m2m table. Specifically I am trying to connect portfolios and projects. I was asked to make it so they could have multiple portfolios attached to a single project, for when projects cross internal departments. I was able to do this with a new m2m table and a related list. I am now finding that the info in the related list is not good enough. The basically want all the info from the project table displayed in the list, but coming from the portfolio listed in the m2m. So high level the query would look something like, from the portfolio view/table "foreach $project in m2m_portfolio_projects select all fields from pm_project where $project is linked to $portfolio"

I think I need a reference table or view or something like that or some kind of table join but I have not been able to figure out how to do that.

Thanks in advance for any suggestions

Answer 1

You can create a database view and then work with it as if it is normal table. Except for write operation, that is not allowed for obvious reason.

https://docs.servicenow.com/bundle/washingtondc-platform-administration/page/use/reporting/concept/c_DatabaseViews.html

Maybe a nested reports might be of some use.