How to limit choices in a Link field based on 1-to-many relationships?

[klonaway]

Hi ! What I’m trying to do is a basic relational DB behavior, but I can’t find my way in AirTable. Here is the idea…

I have 3 tables interacting for this problem :

1. table “Clients”
2. table “Appointments”
3. table “Invoices”

Relationships through “Link to” fields :

• Appointments <— many-to-one —> Client
• Appointments <— many-to-one —> Invoice
• Invoices <— many-to-one —> Client

In the Appointments table, when I link to an invoice, I obviously want to see only the invoices linked to the correct client. (The incomplete invoices are created beforehand)

Therefore, in the Appointments table, I want to filter the Invoice choices only to the invoices linked to the client of that appointment.

As I understand it, a filtering based on a view won’t be of help, since I can’t create a view for every client in my base…

Ideas ? Tips ? Help ? Is it even possible in AirTable ? Would be a serious limitation for my use cases, and I might regret MS Access for the very first time since I signed up for AirTable !

Thx for anything !

[ScottWorld]

Unfortunately & sadly, this is not a feature that is natively built into Airtable. :frowning: In my personal opinion, this is one of the key missing features from Airtable.

However, there is one known workaround/hack to make this work, which is outlined here:

Conditional Logic-ish Linked Records Show and tell

TL;DR version: Airtable’s recently added capability to limit linked-record selection to a specific view allows users to incorporate, to a degree, conditional logic. The specifics: ‘Conditional logic’ can mean a lot of things: Any use of an IF() or SWITCH() statement is a form of conditional logic. Here, I’m using it in what’s become an increasingly common manner: to mean the limiting of available options based on an earlier choice. For example, take a look at the following diagram. [Condition…

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

[klonaway]

Dammit ! Another workaround ! Just took me a few days to code my own Node.js two-way sync AirTable <-> GoogleCalendar, I hope this will be shorter… I’ll read those tomorrow and come back here for you :winking_face:

[ScottWorld]

Just took me a few days to code my own Node.js two-way sync AirTable <-> GoogleCalendar.

Wow, impressive! There is also a 2-way sync Airtable to Google Calendar product here: https://www.zzbots.com/store/etsxGGrfvx5BtXmnt

[klonaway]

Heya ! After careful reading and study, I understand the workaround. Smart, tricky, beautifully creative, but unfortunately it requires an alteration of the base, and I can already see down the road a time when I have forgotten all about it and mess things up myself.

My logic here : when the workaround creates more complexity than the initial problem it solves, don’t implement it. I will probably rethink my relationships between tables, or accept this limitation until AirTable releases a straightforward solution. Not migrating from old-school DB management for nothing !

So, still waiting for a cleaner solution which probably won’t come…

I’m thinking about limiting the relations to :
Appointments <— Many-to-One —> Invoices <— Many-to-One —> Client

This would be better for data integrity, but requires lookup fields and limits the flexibility for everyday appointment management.

[ScottWorld]

klonaway:

My logic here : when the workaround creates more complexity than the initial problem it solves, don’t implement it.

I agree with this logic! Be sure to email support@airtable.com and let them know how important this feature is to you. It’s a standard feature of all other database systems, so it’s extremely disappointing that Airtable doesn’t have it.