Formula Filter by two variables

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:

User.Tasks.Filter(Sprint.Name = [Assigned Sprint].Name)

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")?

You can’t filter Tasks on a field that is part of the Sprint Capacity database.

Can you confirm what the relations are between the various databases:

Sprint 1:n Tasks
Tasks n:? User(s)
SP n:1 Sprint ([Assigned Sprint] relation)
SP n:1 User

Is this correct? Can a Task be assigned to multiple Users?

The SP database seems to exist to link Users to Sprints, implying that a User can participate in multiple Sprints.
If this is the case, when you ask

how should the formula know which Sprint to calculate the sum of story points for?

The relations are
Sprint 1:n Tasks
Tasks n:n Users
SP n:1 Sprint ([Assigned Sprint] relation)
SP n:1 User

A task indeed can be assigned to multiple users.

If use a constant like so

User.Tasks.Filter(Sprint.Name = "Sprint 1").Sum(Effort)

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"?

If there is a variable, where does the value of this variable come from?

From the SP object. All I want is a way to do is

User.Tasks.Filter(Sprint.Name = SP.[Assigned Sprint]).Sum(Effort)

And I feel that I’m missing something syntactic here.

But if the solution is changing the relationships someway I’m also open to that.

But a User is not tied to a single Sprint via the SP relation…

Sprint 1:n SP n:1 User

implies that a User can theoretically be assigned to multiple Sprints.

Yeah, they indeed can. There is an inherent Sprint n:n User relation that is implemented like this

Sprint 1:n SP
SP n:1 User

The use of SP is just to have a place to manage the user’s capacity for each Sprint.

Maybe I’m overcomplicating things. Is there some other way to do something like this in Fibery?

OK, I think I realise now what you’re getting at.
I think it’s possible, but I’ll need to check first.

1 Like

It’s not very pretty, but I suggest the following:

  1. Create a formula field in SP which gets the number of User’s Tasks:
    User.Tasks.Count()

  2. Create a formula field in SP which gets the number of Tasks in the Sprint:
    Sprint.Tasks.Count()

  3. Create formula field in Task db which lists all the User IDs:
    If(Users.count()> 0, "<" + Users.join([Public Id],"><")+ ">","")

  4. Create a relation between SP and Task (n:n):
    image

  5. 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:

  • SP Sprint = Task Sprint
  • SP User’s ID is in Task’s User ID list

The formula will be something like this:
Tasks.Filter([Step 1 SP].Sprint = Sprint and Find([User IDs],"<"+ [Step 1 SP].User.[Public Id] + ">")>0)

  1. Create a formula that sums the effort of these Tasks:

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.

3 Likes