Filtering records by age

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:
   

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.

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

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)

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.

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

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)

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:
   

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.

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!

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:
   

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.

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?

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!!