Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 30, 2019 08:35 AM
Hello!
I have a Project Tracking workspace, with two tables - a list of Tasks, and a list of Projects. I’ve found it easier to manage the tasks by keeping them in a separate table. But in the Project table, I’d like to see “Incomplete” tasks, so I created a column to return only these incomplete tasks. I can’t seem to reference this column in my Project table. How can I reference a specific column? For some reason the Client table is the only one that is available under a rollup reference.
Thanks!
Dec 30, 2019 08:48 AM
Hi,
It should be LookUp instead of Rollup. That will give you the ability to select a given table and field.
Cheers
Fred
Dec 30, 2019 09:24 PM
Both Lookup and Rollup fields offer that ability. The difference is that a Lookup field just pulls in the data from the referenced field in the other table, whereas a Rollup lets you process that retrieved data via an aggregation function.
Assuming that every task in the [Tasks]
table is linked to a project in the [Projects]
table, then you should be able to look up any task info you want. My task base is similarly structured, and I’ve got all kinds of data being passed back and forth.
Here’s the basic setup. In your [Tasks]
table, you should have a link field that lets you choose a project from the [Projects]
table. After creating that {Project}
link field in [Tasks]
, you should see a reciprocal {Tasks}
link field that Airtable automatically made in [Projects]
.
Back in [Tasks]
, add a formula field named {Incomplete}
. Assuming for now that the checkbox field that you use to mark tasks as complete is named {Done}
and the name of the task is in a {Name}
field, you would use the following formula in {Incomplete}
:
IF(NOT(Done), Name)
This will echo the task name in {Incomplete}
only if it’s incomplete.
Back in [Projects]
, make a Lookup/Rollup field. Set it to use the links in the {Tasks}
field I mentioned above, and pull in the data from the {Incomplete}
field. If you just want to pull the data in and not do anything with it, a Lookup field will suffice. If you want to process it in any way, use a Rollup field with an appropriate aggregation function. For example, you could add automatic line breaks using this aggregation function: ARRAYJOIN(values, "\n")