Skip to main content
Solved

Filtering Using a Lookup Field

  • August 15, 2022
  • 6 replies
  • 184 views
S

I need help figuring out an issue I’m having when filtering a view using a lookup field. I have a project tab and a task tab set up. For the tasks, I have task owners set up.

The tasks work in a chronological order. The first task needs to be completed before task 2 can start, and so on. For the projects, I have a formula set up that automatically updates the project status when someone completes their task using a checkbox.

I want to have a view for each person in the task tab – where they can see all tasks they have to do, but I want them to ONLY be able to see the tasks where they are up to complete the task (aka the task(s) before them are all completed and the status of the project aligns with their task). I have it set up where it currently filters for the task owner, the project status (through a lookup field from the project tab), and if the task has been marked complete through the checkbox.

However, for some reason it is not filtering correctly and is showing all of the tasks (even if the task owner is not “up to the plate” to complete it based on the status of the project). Is this because the lookup field is text-based? Is there a workaround so I can filter for a specific task owner, for the project status being the specific task, and for the completed checkbox to be empty?

Alternatively, is there a way to turn a lookup field INTO a single select field where I can use the single select to filter?

Best answer by Suzie_Gilmore

This might be happening because of a quirk with lookup fields. Long story short: they return arrays (most of the time), and comparing against the contents of an array can be tricky.

If the target field that’s referenced by the lookup is just a formula that outputs a string (if I’m reading your comments correctly), you can turn your lookup field into a rollup field. Use this aggregation formula to ensure that the final output is a string: values & "" . From there the conditions should work more reliably.


Thank you for the insight! I learned that the [project status] field (my lookup field) is case sensitive so that’s where part of my issue was coming from. The other issue I resolved was adding the task itself to the filters so each conditional group, so I was filtering for:
[task owner] [is exactly] [person]
[complete checkbox] [is] [empty]
[project status] [is] [status to match task case sensitive]
[task] [is exactly] [single select]

These two aspects seemed to fix my problem. Thank you everyone for the suggestions!

    6 replies

    TheTimeSavingCo
    Forum|alt.badge.img+31

    Hi Suzie!

    I believe when you add a lookup field for a single select field, it’ll automatically become a single select and have the single select filtering functionality

    This shouldn’t affect anything I think

    Could you provide screenshots of the Tasks and Projects table, as well as a screenshot that shows the records you would like displayed based on an example filter? Once I understand how your base is set up and what your expected outcome is I may be able to provide some suggestions

    Adam, Airtable Consultant - thetimesaving.company
    S
    • Suzie_Gilmore
    • Author
    • New Participant
    • 3 replies
    • August 16, 2022

    Hi Suzie!

    I believe when you add a lookup field for a single select field, it’ll automatically become a single select and have the single select filtering functionality

    This shouldn’t affect anything I think

    Could you provide screenshots of the Tasks and Projects table, as well as a screenshot that shows the records you would like displayed based on an example filter? Once I understand how your base is set up and what your expected outcome is I may be able to provide some suggestions


    The lookup field I have is not a single select, it’s a text-based formula. However, I’m wondering if there is any way to create a new field based on the text that populates and turn it into a single select? I’m thinking this may help with the filtering issue below.

    Project Screenshot:

    Task Screenshot:

    Each project contains 5 tasks, which work in chronological order.

    *task 1 completed → status updates → moves to task 2
    *task 2 completed → status updates → moves to task 3
    *task 3 completed → status updates → moves to task 4
    *task 4 completed → status updates → moves to task 5

    What I’m wanting to see happen is within my filtering for each person’s view, I’m wanting them to only be able to see the tasks when it is their “turn” in line based on the status of the project. For example, task 3 can’t be completed until task 1 is done and task 2 is done. For the person that holds task 3, I only want that to be included in their view when the status is aligned with their specific task aka “ready for task 3”.

    Based on the screenshot of the filtering I have set up in the original question, I assumed this would work. However, it is not filtering based on the status as intended and is showing all tasks that include that task owner, even if the prior tasks in the project are not completed.

    TheTimeSavingCo
    Forum|alt.badge.img+31

    The lookup field I have is not a single select, it’s a text-based formula. However, I’m wondering if there is any way to create a new field based on the text that populates and turn it into a single select? I’m thinking this may help with the filtering issue below.

    Project Screenshot:

    Task Screenshot:

    Each project contains 5 tasks, which work in chronological order.

    *task 1 completed → status updates → moves to task 2
    *task 2 completed → status updates → moves to task 3
    *task 3 completed → status updates → moves to task 4
    *task 4 completed → status updates → moves to task 5

    What I’m wanting to see happen is within my filtering for each person’s view, I’m wanting them to only be able to see the tasks when it is their “turn” in line based on the status of the project. For example, task 3 can’t be completed until task 1 is done and task 2 is done. For the person that holds task 3, I only want that to be included in their view when the status is aligned with their specific task aka “ready for task 3”.

    Based on the screenshot of the filtering I have set up in the original question, I assumed this would work. However, it is not filtering based on the status as intended and is showing all tasks that include that task owner, even if the prior tasks in the project are not completed.


    Hmm roger that. Within the Task table, what field is used to show the status of the project, i.e. “ready for task 3”?

    Adam, Airtable Consultant - thetimesaving.company
    Justin_Barrett
    Forum|alt.badge.img+21
    • Justin_Barrett
    • Inspiring
    • 4647 replies
    • August 17, 2022

    This might be happening because of a quirk with lookup fields. Long story short: they return arrays (most of the time), and comparing against the contents of an array can be tricky.

    If the target field that’s referenced by the lookup is just a formula that outputs a string (if I’m reading your comments correctly), you can turn your lookup field into a rollup field. Use this aggregation formula to ensure that the final output is a string: values & "" . From there the conditions should work more reliably.

    S
    • Suzie_Gilmore
    • Author
    • New Participant
    • 3 replies
    • Answer
    • August 17, 2022

    This might be happening because of a quirk with lookup fields. Long story short: they return arrays (most of the time), and comparing against the contents of an array can be tricky.

    If the target field that’s referenced by the lookup is just a formula that outputs a string (if I’m reading your comments correctly), you can turn your lookup field into a rollup field. Use this aggregation formula to ensure that the final output is a string: values & "" . From there the conditions should work more reliably.


    Thank you for the insight! I learned that the [project status] field (my lookup field) is case sensitive so that’s where part of my issue was coming from. The other issue I resolved was adding the task itself to the filters so each conditional group, so I was filtering for:
    [task owner] [is exactly] [person]
    [complete checkbox] [is] [empty]
    [project status] [is] [status to match task case sensitive]
    [task] [is exactly] [single select]

    These two aspects seemed to fix my problem. Thank you everyone for the suggestions!

    Justin_Barrett
    Forum|alt.badge.img+21
    • Justin_Barrett
    • Inspiring
    • 4647 replies
    • August 18, 2022

    Thank you for the insight! I learned that the [project status] field (my lookup field) is case sensitive so that’s where part of my issue was coming from. The other issue I resolved was adding the task itself to the filters so each conditional group, so I was filtering for:
    [task owner] [is exactly] [person]
    [complete checkbox] [is] [empty]
    [project status] [is] [status to match task case sensitive]
    [task] [is exactly] [single select]

    These two aspects seemed to fix my problem. Thank you everyone for the suggestions!


    Glad to hear that you worked it out!

    For future reference, virtually all comparisons in Airtable are case sensitive. It’s not something tied to certain field types. While some search features are case insensitive—e.g. when searching for a field name to show/hide it—the vast majority of the time you have to capitalize things exactly as they exist in the data.


    Terms & ConditionsAccessibility statement