I have a table (“projects”) with a one-to-many column (“requests”), quarter column (this is when the project will be completed), and a team column (who is doing the project).
“requests” is a lookup on the “requests” table.
with this project structure, I can create many projects across many teams, and across many requests such that:
any request may have 0 or more projects
any team may have 0 or more projects
any project may support 0 or more requests
a common use is to show which projects are being executed and when in support of a given request, i.e. team T1 is doing project P3 that supports request R5 in Q3; another team T2 is doing project P4 that also supports request R5 in Q4.
My goal is to show a 2x2 matrix grouped by request on the left, and quarter on the top, with each cell showing projects that are being done for that request in that quarter.
problem is getting 1 request per row. I built a view on the projects table that groups requests on left, but, I get rows for each combination of requests that a given project supports, instead of 1 request per row.
is there a way to do this without building a separate manually entered cross-reference table?