Issue with filtered views and linked records

[Bree_Cooksey]

Hi there! I am new to the Airtable community. :slightly_smiling_face: I use Airtable as a case management platform to basically manage peoples lives. I have it setup to where all the tables in the base link to one main table, which is the “Client Profile” table and the goal is have everything synced/automated in a way where all the work is done through the “Client Profile” table, without having to manipulate any other tables in the base.

The issue I am running into is a linked record not disappearing from a record/profile in the “Client Profile” table, even when a particular view is selected. For instance, there are two scenarios in which, I need the “Client Profile” table to automatically update.

Scenario 1: I have two task-oriented tables linked to the “Client Profile” table, “Case Manager Tasks” and “Client Tasks.” Both of these tables have a view only showing uncompleted tasks, which are linked to individual records/profiles in the “Client Profile” table. When I check the task “complete” it disappears from the view in the Case Manager/Client Tasks table, but not in the Client Profile table.

Scenario 2: Similar to the scenario above, I want the same automation to happen for upcoming appointments. This one is a little tricky because I have it integrated with Calendly. When the appointment is made (in Calendly) a new record is created in the “Client Appointments” table. When the record is created it is automatically linked and it shows up in corresponding record/profile in “Client Profile” table. In the “Client Appointments” table I have a view to only show upcoming appointments, and the same view is linked to the Client Profile table as “Upcoming Appointments”. However, when the appointment date passes and is filtered from the Client Appointments table, it’s still showing in the Client Profile table.

The reason I cannot unlink records to no longer show in the “Client Profile” table is because I will lose the client’s history. Every record in all tables are linked by the Client’s Name and Case Number. I did this through link/lookup fields, so although I don’t “lose” the information, I can no longer determine what client it is linked to for archiving purposes.

I really hope someone can help! Maybe I did something wrong in my setup, which is why I am having issues at this step? I’ll take all the suggestions I can get. Thanks!

[AlliAlosa]

Hi there! Unfortunately, once a record is linked, the blue rectangle in the linked record field will always be there, even if the task is complete. You can’t filter out what is displayed in a linked record field, it’s always a constant.

However, you can use conditional rollups to only show the incomplete tasks (or appointments) in the [Client Profile] table in separate fields from the linked records.

If I’m understanding correctly, you should be able to apply the below steps to both scenarios. I’ll use the incomplete tasks as an example.

Add a formula field to your [Case Manager Tasks] table, and use something like the following…

IF(Status != "Complete", {Task Name})

Let’s say you call this field {Incomplete Tasks}. Next, add a rollup field to your [Client Profile] table. Point it at the [Case Manager Tasks] table, and the {Incomplete Tasks} field, then use the following formula function…

ARRAYJOIN(ARRAYCOMPACT(ARRAYUNIQUE(values)), "\n")

I like to dress this up even further and use emojis like bullet-points.

IF(ARRAYCOMPACT(ARRAYUNIQUE(values)), "‼️ " & ARRAYJOIN(ARRAYCOMPACT(ARRAYUNIQUE(values)), "\n‼️ "))

The end result on the [Client Profiles] table would look something like this (mine includes the person’s name assigned to task as well):

You can repeat this for the [Client Tasks] table, and for the [Appointments] table as well.

I hope this helps!