Help with Dynamic filtering in linked record fields

[jowan_qupty]

Hey everyone,

Really excited about the new dynamic filtering feature that was just released. I’m trying to implement it on one of my bases but I’ve hit a bit of a snag trying to make it work in my use case.

Here’s the deal: I’m with a company that handles housing for employees on a certain project. The employees work in rotations, being on the project for three months and then off for a month, before coming back again. To keep track of apartments and maintenance, we have check-in and check-out forms. The catch is not everyone fills them out because there are just so many of them, and it’s tough to keep track.

Right now, when filling out the forms, employees pick their name (which links to our Employee table) and their apartment (linked to an Apartment table). After they check in, their name gets linked to that apartment, and it gets unlinked when they check out. (This gives us a clear view of who's in which apartment at any point in time)

What I want to do sounds simple but I can’t quite crack it:

1. In the Check-In Form: Employees should only be able to pick an apartment if they’re not currently linked to any.
2. In the Check-Out Form: They should only see the apartment they’re actually in as an option to check out from.

We’ve got the following tables set up:

• Table 1: Apartments
• Table 2: Employees
• Table 3: Check-Ins (for tracking each time tenants move into an apartment)
• Table 4: Check-Outs (for tracking each time tenants move out of an apartment)

Any insights on how to effectively implement this functionality using dynamic filtering would be greatly appreciated!

[TheTimeSavingCo]

I've put something together here that does what you're looking for I think!

For the Check-In Form:
In the "Employees" table, I used "Count" fields and a formula field to check how many check ins vs check outs there were per employee, and if there are more check ins than check outs (i.e. they're still linked to an apartment), then linked field wouldn't allow them to be selected



For the Check-Out Form:
Hmm, the only way I could think of to do this was for the "Apartments" table to have the data of who was currently in each apartment. Unfortunately, to do that I needed to add a linked field to the "Employees" table, and then the "Check-Out" form then used that as a conditional.  We'd have to use automations to populate that new field in "Apartments" I'm afraid

I also put in a thing that'll only allow Apartments that are empty to be selected during check in

I'm hoping that I'm missing something and that there's a way to handle the check out form without needing that extra linked field, please do let me know if you figure that out!

[ScottWorld]

Hi @jowan_qupty,

In my personal opinion, the quickest, easiest, most elegant, and most bulletproof way to solve your Airtable dilemma for the check-out form would be to use Fillout’s advanced forms for Airtable, which is 100% free and gives you the ability to:

(1) Update existing Airtable records from a form, and 
(2) Apply multiple dynamic conditional filters to any field.

Your check-out form would simply be a unique link that you give each employee to use for checking out. When they click on that link, they will be taken to their own apartment and can check out.

Even better, Fillout offers over 100 advanced Airtable form features that might be of use to you, such as conditional multi page forms, accepting payments on forms, performing calculations and formulas live on forms, displaying Airtable lookup fields & Airtable rollup fields & Airtable attachment fields on forms, and much more.

So you could possibly improve your check-in forms by using Fillout as well.

If you wanted to take this to the next level beyond that — although this is where you might need to start spending extra $$$ money — you could setup an actual employee portal with either Airtable’s interfaces (which would be a basic portal), or Noloco or JetAdmin (which would be more advanced portals).

With a portal, employees can log in and see their entire history of apartments, update their own employee record, perform check-ins and check-outs at any time for any apartment, and much more.

I actually gave an entire one-hour webinar on Noloco called Building a Client Portal on Noloco powered by Airtable.

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld 

[jowan_qupty]

@ScottWorld  & @TheTimeSavingCo 

Thank you both for the great solutions! both very creative and have inspired new ideas in some other areas of my bases.

I'm a bit of a hard headed person and was adamant to finding a way to make it work with the new dynamic filtering feature, and have finally managed to do so in a fairly easy way.

Here's what i did:
This is a base dedicated for my checkins & checkouts, containing synced tables for the apartments and tenants.
In the original base, there is a direct link between the Apartments table and Tenants table. However in the synced base these do not sync as "links" but as "text" fields.
What i did was to create a new linked field between the Tenants and Apartments table (in the check-in & check-out base), which then allowed me to implement the dynamic filtering options. (video tutorial i found)

In the check-in table: 
I only showed names of tenants that do not have an apartment linked to them

In the check-out table:
I only allowed to show names of tenants that have an apartment linked to them

AND, once the tenant is picked, i only showed the apartment which is linked to their name

[ScottWorld]

That is fantastic! Great job!! 😃🙌

I don’t have access to the new dynamic filtering feature yet… I’m looking forward to playing with it when it gets rolled out to me!