I want to see the assigned capacity of user in a sprint, the sum of story points from all tasks a user assigned to in a given sprint.
I have a Sprint object with a one to many relationship with Tasks. Each Task can have one Sprint, and each Sprint can have many tasks. And the planning is done with a Sprint Participation database wherein each SP has one Assigned Sprint and one User.
My problem arises when I’m trying to create a formula to calculate the Assigned Capacity:
But I’m getting an error that [Assigned Sprint] is not recognized while it is a field of Sprint Participation. A User has Tasks, and each Task is related to only one Sprint. How do I create a formula to filter a User’s Tasks per Sprint without resorting to constants in the Filter (i.e. Sprint.Name = "Sprint 1")?
I get a correct calculation of the Assigned Effort of each User that is related to an SP, but it’s hard-coded. Is there a way to use some variable instead of "Sprint 1"?
Create an automation in the SP db that will run when the ‘User task count’ or ‘Sprint task count’ is changed (indicating that the Task has been changed in some relevant way) and will populate the Tasks relation based on the following criteria:
This solution assumes that the necessary SP entities have been created before the Tasks are assigned to Users/Sprints.
As Tasks are assigned to Users and/or Sprints, the automation will get triggered and recalculate which Tasks meet the criteria.
Until we get a formula function for ‘intersection’ of collections, I think this is the best we can hope for.
Note: potentially one could combine 5 and 6 so that the total effort was directly calculated in the automation, but I think the method above enhances clarity.