[SOLVED] Limit linked record selection to a view

With a ‘link to another record’ data type, I’d love to be able to filter which options are available to pick.

Use Case:
I’m using Airtable to make a rostering database. I’ve got one table that stores the jobs to be rostered for each event, and another table with a list of volunteers and the jobs they’re able to do.

Each job is a column in the Roster table, and each job is linked to the ‘People’ table. I would find it useful if I could filter people based on whether they are able to do the job specified in the column.

Agreed. I have a similar use case and it would greatly increase usability if we could filter the records available to link. This would prevent users from entering an invalid entry.

+1 here. I’d also be OK if I could use a filtered view instead of the base table.

Yes to this. It’s extremely important to be able to filter the options in a dropdown. For example one of mine is a list of events. Currently past events are able to be selected, which is not ok! Also it makes the dropdown too long and unwieldy.

I agree with this request. I have a similar use case-I have a “transactions” table, and need to be able to see only transactions from the past month in the “category” table.

This feature was already requested 3 months ago but it was never answered except by other people saying how important it is! So I’m doing it again.

Please please let us use views on linked fields, not just tables. The capacity for corrupting the data is huge when we can’t control what can be selected. For example, I have a list of events at which something can be hired. I want to prevent the selection of an event in the past, the selection of an item that’s already hired out, the selection of a blacklisted user. When I process the return of a hire I select the hire from a list - I want to only display current hires, not have to trawl to the bottom of a list of past hires first. There’s so many ways this is essential!

At the moment I solve this by using a sort order to push the most likely selections to the top of the list. But given that the sort order isn’t applied unless you actually click the button, this creates quite an overhead. For example, when I add a user it’s probably because I want to immediately add a hire for that user. So I want the user at the top of the list, which is where my sort order would put it, but first I have to go into the base and reapply the sort.

+1 for this request. My project table links to a tasks table. I need to assign uncompleted tasks to projects, but linking to the tasks table shows both completed and incomplete tasks. I might be able to use a workaround for the time being (suggestions?) but long-term linking to table views, or filtering results, is a better approach. Thanks!

Have you tried sorting the Linked Table (on the actual table view) so that the most recent records are at the top? Obviously this is still cumbersome however just a thought.

I’ve found myself using the index fields of linked tables as Formulas and tie in additional data so that when I am choosing the Record I have a fair amount of the data of a record available in the Linked Table list.

Was this ever responded to or acknowledged? I hear a lot of people concerned about linking Bases however I believe this request along with more Permissions capabilities have received far more requests.

Yes this is what I do, but since there is no autosort feature, it’s very cumbersome. Plus although this allows the correct data to be presented at the top, it doesn’t prevent the user from selecting wrong data.

Nope, not here nor on the other request.

Hi! Thanks for giving us such an in-depth explanation of why and how you would want to use filtering on linked record fields—it’s really helpful for us, and what you’re saying makes a lot of sense.

In addition to filtering the foreign keys in a linked record field, we’ve also had requests to auto-sort the foreign keys in a linked record field. As you hinted, one way to deal with both filtering and sorting on linked record fields might be for there to be “views” that apply within the contents of a single field. In any case, if this is the approach we decide to take to solve the problem, we need to make sure that we design the new feature in such a way that it doesn’t needlessly complicate the user experience. Apologies for the long response time!

Katherine,

What I have found myself doing (although this is still very cumbersome) is creating a new table that just has Lookup fields of the table I need to see records of sorted in the way that I need them to look and have those Lookup fields point to the table I need to see data from. Keep in mind that this only works as a form of a Report rather than modifiable data fields.

You can setup multiple Views of the Related Table formatted the way that you need and then change the View of the Related Table, go back to the Source Table and it will inherit the last used View of the Related Table. Again very cumbersome however it works in a pinch.

For the Auto Sort you can as well use a Formula to Auto Sort the records and then Link to the Formula Field. Just another thought.

Thank you for your response!

Thanks, those are both very interesting suggestions.

+1! It would be great to be able to apply filters to linked fields, such as “only show rows whose X column is null”.

hi everyone.
is there a way to link to a filtered list of records?
Here is a scenario :I am shooting a commercial.
I have thousands of candidates that I have auditioned. Only a few have been selected in the candidates table (check box)
now in my scene table, i would like to link a field to the candidates table. but I only want to be prompted with selected candidates and not the thousands i have auditioned…
is there a way to do that ?
i hope it’s clear. :-/
thks in advance
T

Hi T!

Right now you can’t link to views or subtypes (selected candidates).

You could create a second table for your selected candidates, filter + copy & paste those people (that’s only 1 action) and create a link to the new table.

Not ideal, but maybe not problematic too in this case… :slightly_smiling_face:

Another, very common use case:

You want someone to select the state, and then the city, in separate fields. Right now, selecting the state first means you would still see every city, not just the cities in the state, in the dropdown menu.

Hello Tuur,

Thanks for answering this:

Right now you can’t link to views or subtypes (selected candidates).

It is a shame that is the state of affairs.

Regarding this point:

You could create a second table for your selected candidates…

(Lets call that table “A”)

…filter + copy & paste those people (that’s only 1 action)…

(Lets call this table “B”)

and create a link to the new table…

(Lets call this the “filtered prompt”)

What happens when the user updates table “A”? Wouldn’t the user now have two tables to maintain? Maybe that will help in the OP’s situation. But for most users, wont that create double the amount of work in terms of maintenance? The “filtered prompt” will be obsolete as soon as a row gets added to table A without adding it to table B.

Your suggestion might work better if there were a way to create a query type of table… (going do research to see if that is available in airtable)…

A temporary fix would be expanding the search capabilities of the pop-up dialog for linking records.

At the moment it is difficult to link records (even with the current search tool) because the search tool only looks at the key column (see picture from @Jason_Woltz post)
https://community.airtable.com/uploads/short-url/yJ86i0fUTKEXl0EaeFGqjlnwfVo.png

The Search Expansion feature request (posed by @Jason_Woltz) can be found here: Find a Record to Link - Search Expansion

Sure. But in this case the selection has already been made so I figured it wouldn’t be a big deal.

In more dynamic scenarios I agree it’s not an option. :slightly_smiling_face:

The query solution is not available either, which is why I’m working on a custom Airtable scripting web service.

Nice![quote=“Tuur, post:4, topic:1408”]
The query solution is not available either, which is why I’m working on a custom Airtable scripting web service.

Looking forward to that…Would be nice if the “link to record” pop up could use your service! :grinning_face_with_big_eyes:

@Tuur, check this out: Filter on linked fields
Airtable team has on their map adding the ability to Link to a filtered record.