Filter on linked records (yes, i've read other posts
Dear all,
I do have a base with several tables in it of which HCO’s and HCP’s are two. HCO’s are Hospitals where HCP’s (doctors) are employed. I would like to be able to select and/or display hospitals based on whether or not certain HCP’s are available in a hospital (e.g. : display hospitals which do employ orthopaedic surgeons but do not have ophthalmologists available.
Tried Airtable support, which came up with scripting. Please feel challenged to propose a non (or very, very, low) code solution for this!
Thanks so much,
Michiel
The Netherlands
Page 1 / 1
Hey @MJvD!
For sure I am missing on a lot of context here, so what I might be about to suggest might not be even close to what you are looking for.
Did you try limiting the selection of the linked field either to a view or to a specific condition? Please take a look at the screenshot below.
You could do this with a Rollup field and Airtable’s filtering in the usual Grid view.
So, in your HCO table you would Rollup the specialties of the HCPs. Then, you would filter like this:
WHERE Specialty Rollup (from HCP) contains Orthopaedic and Specialty Rollup (from HCP) does not contain Ophthalmology
Here’s some images to see what I’m saying:
Image 1: Rollup field the Specialty of the HCPs using ARRAYUNIQUEImage 2: Filter by processing the Rollup field in Image 1.
Let me know if this helps!
@MJvD
I’m not sure I fully understand either, but have you started playing around with lookup fields and rollup fields yet?
That allows you to bring in data from other tables into your current table, as long as you link records together.
Then, you can search in one table for values that normally live in another table, if those values are tied to the records in your current table.
I created a free Airtable training course many years ago which explains linking records and lookup fields in more detail. The course is very outdated now, but the core concepts remain the same. You can take the course for free by signing up for a free 30-day trial with LinkedIn Learning.
In your HCOs table, add one rollup field for each specialty. These rollup fields will show whether the hospital has any HCPs with that specialty. If the field is empty, it means the hospital doesn't have any.
Once the rollups are set up, you can create a view or apply filters in the HCOs table using conditions like: "Has Ortho is not empty" and "Has Ophthalmo is empty".
