Help

Filtering records by age

Topic Labels: Formulas
Solved
Jump to Solution
1856 13
cancel
Showing results for 
Search instead for 
Did you mean: 
Hannah_Powell
5 - Automation Enthusiast
5 - Automation Enthusiast

I have created a base for my companies therapists. I want our front desk to be able to filter the data to match clients up with therapists. I’m stumped on how to add the age ranges, as they vary greatly. Some therapists see ages 3-10, others 18+, or 25-40, etc.
What I’m wanting is to be able to enter the clients age, 32 for example, and be able to filter out therapists that don’t work with that age.
plz help

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

It doesn’t need to be anywhere near that complicated. There are several ways of doing this that are super easy & simple.

  1. One easy solution is to just create 2 fields for each therapist: Min Age and Max Age. Then filter the view based on the age that you’re looking for. For example, if you’re looking for age 7, this is what the filter would look like:
    image

  2. Another easy solution is to just create a multiple select field with the different age ranges that therapists might see. Then, once again, use filters to just show the age ranges that match.

  3. If you really need a user to type an age into a table, then just use automations to always link all new therapist records to a single age record in another table where you type the age once (not pasting multiple times into the therapist table). Then, use a lookup field in the therapists table to bring in the age, and use a formula similar to the formula that Adam wrote above, and then filter on that formula. This could be combined with an interface in Airtable to make this a really slick UI.

There are probably many other easy & simple ways of doing this too, that I’m not currently thinking of.

See Solution in Thread

13 Replies 13

Hi Hannah, when I first read your message I was like, yeah this seems simple enough. Boy, was I wrong.

I’ve created two solutions for you here

I can’t think of a way for you to use filters easily for this, especially with a workflow where your front desk has to be able to key in ages many times a day and get the result out. As such, the first solution I have for you involves the front desk pasting the client’s age into the “Age” column. The “Will work with” column then checks whether the therapist is willing to work with them based on the following formula:

IF(
  AND(
    Age >= {Min Age},
    Age <= {Max Age}
  ),
  "Yes",
  "No"
)

The second solution I have for you involves you using a scripting app for it, and I threw it together for you because, well, pasting things into the column seems like a very tedious workflow, and if you have more than one person pasting values in at a time then it wouldn’t work anymore. Here’s a gif of how it’d work
find therapists

I’ve pasted the script below, but I recommend duplicating the base I linked so you can see how everything works together.

let settings = input.config({
    title: 'Match therapists',
    description: `Key in an age and this app will output the therapists that are willing to work with them`,
    items: [
        input.config.table('table', { label: 'Table containing the therapists' }),
        input.config.field('therapistNameField', { parentTable: 'table', label: 'Field that contains the name of the therapist' }),
        input.config.field('minAgeField', { parentTable: 'table', label: 'Field that contains the mininum age they are willing to work with' }),
        input.config.field('maxAgeField', { parentTable: 'table', label: 'Field that contains the maximum age they are willing to work with' }),
    ],
});

let { table, therapistNameField, minAgeField, maxAgeField } = settings;

output.text(`Finding therapists..`);

let clientAgeString = await input.textAsync(`Enter client's age`);

// Load all of the records in the table
let query = await table.selectRecordsAsync({
    fields:[therapistNameField, minAgeField, maxAgeField]
});

let therapistList = new Array

let clientAgeNum = Number(clientAgeString)

for (let record of query.records){
    let minAge = record.getCellValue(minAgeField)
    let maxAge = record.getCellValue(maxAgeField)
    let name = record.getCellValue(therapistNameField)
    if(clientAgeNum >= minAge && clientAgeNum <= maxAge){
        therapistList.push({'Name':name, 'Min Age': minAge, 'Max Age': maxAge})
    }
}

output.text('Therapists willing to work with this client age:')
output.table(therapistList)
ScottWorld
18 - Pluto
18 - Pluto

It doesn’t need to be anywhere near that complicated. There are several ways of doing this that are super easy & simple.

  1. One easy solution is to just create 2 fields for each therapist: Min Age and Max Age. Then filter the view based on the age that you’re looking for. For example, if you’re looking for age 7, this is what the filter would look like:
    image

  2. Another easy solution is to just create a multiple select field with the different age ranges that therapists might see. Then, once again, use filters to just show the age ranges that match.

  3. If you really need a user to type an age into a table, then just use automations to always link all new therapist records to a single age record in another table where you type the age once (not pasting multiple times into the therapist table). Then, use a lookup field in the therapists table to bring in the age, and use a formula similar to the formula that Adam wrote above, and then filter on that formula. This could be combined with an interface in Airtable to make this a really slick UI.

There are probably many other easy & simple ways of doing this too, that I’m not currently thinking of.

Welcome to the Airtable community!

Could you tell a little more about this use case? Is this for a a single client at a time and there is no client record? Or do you need to batch process multiple records?

If it is for one-at-time ages with no client record, you could use a filtered view with two conditions (one for min age and another for max age). Then edit the number in the view filters.

If this is for batch processing of new records, you could have an automation with a find records action that links matching therapists based on age.

It may also be convenient if you therapists that do not have a maximum age still have an age in the {maximum age} field, just one that is really big, like 200.

I agree, and I like the screen shot.

I don’t like this method because there are multiple overlapping age ranges. The data entry gets cumbersome. Adding a new therapist with a slightly different age range would also require editing the configuration of the multiple select field.

Not sure I understand this one.

It is similar, but not quite the same as another option of having a table of ages, where each record is one age. Link each therapist to all age records that apply. This can be done with a find records automation. Then when looking up therapists for an age, just look for the appropriate age record and see the linked therapists.

This formula will work, but you would have to paste the age in every row, and using the drag handle can be a bit of a pain.

Another option would be to use a formula field for the age, so you only have to edit the formula field. But that means schema changes and requires creator permissions, which has its own problems.

I am impressed by your ability to quickly write and share solutions! You create your sample bases, sample data, scripts, screen shots, and gifs really quickly. Care to share any tips on your workflow that enable you to be so productive?

In most medical practices, there are usually only 4 or 5 age ranges to choose from

The single age record would be where the user types the current age they’re searching for. (Different age fields could accommodate different users.)

Ultimately, though, the original question is simply a question on how to use Airtable filters, which seems like the easiest, quickest, and best way to handle this.

Wow! This is extremely impressive! As the novice that I am, I haven’t ever used a scripting app. I appreciate you writing one for me. I’m definitely going to try it out and see how it works. My goal is to make this process as easy as possible for our front office. Thank youuuu!!

Ah! I knew there had to be something I was missing. Thank you for pointing this out! The min and max age fields are perfect for what I need. Our therapists have SEVERAL different age ranges.

Thanks for the welcome! It’s great to be here. I wish I would have found Airtable months ago. I can’t believe how quickly y’all responded to my query!

I’m going to use the min and max filters. Airtable is so simple to use… I was just overthinking this LOL!

You’re welcome! I’m glad that I was able to help! :slightly_smiling_face:

Ha, that’s great, too! :grinning_face_with_smiling_eyes: It doesn’t always happen that way, but you must have piqued our interests! :winking_face:

This forum is simply comprised of your fellow Airtable users (just like you), who are just voluntarily spending their free time here. Airtable employees don’t officially answer questions here, so it’s just Airtable users helping fellow Airtable users.

Although a few people — like @kuovonne and myself — are professional Airtable consultants who are available for hire. You can feel free to check out my Airtable consulting website, in case you ever need to hire someone for help.

Also, since you’re new to Airtable, you might benefit from my free Airtable training course as well:

Hahaha yeah, I can see why it seems complicated. I think that we have to consider the trade off between how complicated set-up is versus how complicated the resulting workflow is though!

From the perspective of the person using it (in this case, front desk staff), whether or not the set-up is complicated doesn’t matter though, right? The primary concern would only be how easy it was to use.

Ultimately, balancing the tradeoffs of set-up cost and ease of resulting workflow are pretty subjective, and, personally, I would rather incur a higher set-up cost (within reason!) for an easier daily workflow than the opposite

Ooh yeah, I was thinking we’d click the column header so that the whole column is selected and paste the values in that way, which seemed alright?

Thank you very much for saying that! I’ll DM you!

No problem! Let me know if you need any changes or anything explained!