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?
thanks!
If I understood correctly, you have two one-to-many tables:
Projects table:
Project column
Requests column (formula: Requests.filter(Projects.contains(thisrow)))
Quarter column
Team column
Requests table:
Request column
Projects column (Lookup of Projects table, multiple selected)
The grouping of rows breaks away cells in a given column into different types and groups them by those types. It doesn’t “go into” each cell in the column and break away the cell into it’s members and then form types out of that.
What you need is a one-to-one table:
Calendar table
Project column (Lookup of Projects table, single selected)
Team column (Lookup of Teams table, formula: Project.Team)
Request column (Lookup of Requests table, single selected)
Quarter column (Lookup of Quarter table, single selected)
You can easily autogenerate this table using button:
Projects.FormulaMap(WithName(currentvalue, CurrentProject, CurrentProject.Requests.FormulaMap(Calendar.AddRow(
Project, CurrentProject,
Request, currentvalue,
Quarter, CurrentProject.Quarter,
Team, CurrentProject.Team
)))
Now you have single quarter and single request in any given cell, and grouping will group by them individually.
Thanks for the quick reply. By the nature of your answer, I assume that it’s not possible to build this view without a separate cross-ref table that is one-to-one. The autogenerate method is useful and I will try that out.
The way that I got around it (for now) is to build formulaic columns (1 per quarter) into the requests table and then filter that shows the projects in that quarter. I recognize it’s a hack, but it gets it done.
thanks again,
Mark