I have a formula field in a “Task” object and want to display that field as a lookup on it’s associated “Iteration” object, but it’s not showing up as an option. I’ve double checked that everything’s associated correctly. Is this a bug or is there a lookup limitation I’m unaware of?
What is the data type of the result of the formula?
Lookups only work for certain field types - you can’t use them for dates, number or text.
But you might be able to use a formula instead of a lookup…
It’s a number formula, so that maybe explains it. Your suggestion is to create an new formula that simply displays the value of the other formula?
Assuming that there is a to-one relation, then yes, a formula is the right way to show the number value in a related entity.
Lookups are typically used when the result is a collection of entities, e.g. if a Project has many Tasks, and each Task has an Owner, you can make a lookup on the Project to get the Owners of all the Tasks.
On the other hand, if your Projects have a score, and you want to see it on each of the Tasks, you can just use a formula: Project.Score
And to add a bit more, if the Tasks each have an Effort value and you want to get the total Effort for a Project, you would use an aggregation function in the formula, e.g. Tasks.Sum(Effort)
Thanks. I’ve been thinking of lookups more like “rollups” in ClickUp, where they will automatically sum up all the values below them. Formulas are a little more complex, but I understand now. Here’s what I ended up using:
For the Tasks to sum all the “Points” associated them into a field called “Used Points”:
If(
IsEmpty([Parent Task]) = true,
Subtask.Sum(Points) + Subtask.Sum([Used Points]) + Points,
Subtask.Sum(Points)
)
For the Iteration to sum up all the “Used Points” of the tasks underneath them:
Tasks.Sum([Used Points])
I’m slowly getting the hang of things!
Note:
If the Task db has a one-to-many self-relation to itself, then you can actually write a recursive formula that will sum all the Points of all Subtasks, and grandchild Subtasks, and great-grandchild Subtasks, etc.
To do so, just create and save a formula with a constant number output, e.g.
and then once this is done, you can edit the formula to refer to itself for related items, e.g.
2 Likes
Thanks, your formula works and is cleaner than mine, I just don’t understand in your example why the “Total points” field with the constant is required to make it work and I’d prefer not to have an extra field that only exists to help a formula be a bit simpler. Could you help me understand?
Like, this seems to work for me with no constant, but perhaps there’s a scenario where it would break?:
If(Subtask.Count() > 0, Points + Subtask.Sum([Used Points]), Points)
Perhaps there has been a misunderstanding - there are two fields in my example, and only one of them is a formula field.
Points is the manually entered value.
Total Points is the automatically calculated sum of all Points, including subtasks
Total Points needs to refer to itself, but it can only be referenced once it exists. The first step (of making and saving a formula field called Total Points) is needed so that this is possible. The actual value assigned is irrelevant, since it gets overwritten in step 2. I just chose to make it = 1. All that matters is that there exists a formula field that returns a number value.
In your case, it looks like Used Points already existed, so updating it to be recursive was immediately possible, but for anyone reading this thread, I figured it’s useful to know the full process.
Perfect, sorry for the confusion and thanks for clarifying!