Skip to main content

Is there any way to get some sort of matching accuracy for formulas?

  • February 16, 2021
  • 6 replies
  • 18 views

Hello!

I am using the airtable API with a formula to basically filter a set of records from a table. I am filtering on a multiple-select-field, combined with a logical OR, so something like this:

1OR((FIND(LOWER("First"),LOWER({Select})) >= 1),(FIND(LOWER("Second"),LOWER({Select})) >= 1))
2

Now I will have records that have both “First” and “Second” selected in the “Select” column. I would like to sort these more accurate matches to the top of my results. Is there any way to do this?

Thanks in advance!
Tom

6 replies

Forum|alt.badge.img+19
  • Inspiring
  • 3263 replies
  • February 16, 2021

Hi @Tom_Muller, and welcome to the community!

Yes. The API supports sorting.


  • Author
  • New Participant
  • 1 reply
  • February 16, 2021

Thanks @Bill.French, but this itself does not help. I do not want to sort by a field, but by the accuracy of the match. So if I have to records, where record A has just the element “First” selected in the column and the record B has both “First” and “Second” selected, I want to sort the results, so that record B comes before record A.


Forum|alt.badge.img+19
  • Inspiring
  • 3263 replies
  • February 16, 2021
Tom_Muller wrote:

Thanks @Bill.French, but this itself does not help. I do not want to sort by a field, but by the accuracy of the match. So if I have to records, where record A has just the element “First” selected in the column and the record B has both “First” and “Second” selected, I want to sort the results, so that record B comes before record A.


Since Airtable doesn’t support fuzzy matching, you have to build that.

  • Perform a query that retrieves all the possibilities.
  • Use javascript to apply the last mile of the query logic.

kuovonne
Forum|alt.badge.img+29
  • Brainy
  • 6009 replies
  • February 16, 2021

@Bill.French tends to do a lot of work with the REST API, so he tends to think in those terms. However, I think that @Tom_Muller is asking for a simple formula field for sorting within the regular interface.

Have your formula return a value that says how accurate the match is.

  • If both values are found, return a 1
  • If only one of the values is found, return a 2
  • If neither value is found, return a 3

Then sort on the formula value.

1IF(
2 AND(
3 FIND(LOWER("First"),LOWER({Select})),
4 FIND(LOWER("Second"),LOWER({Select}))
5 ),
6 1,
7 IF(
8 OR(
9 FIND(LOWER("First"),LOWER({Select})),
10 FIND(LOWER("Second"),LOWER({Select}))
11 ),
12 2,
13 3
14 )
15)
16

Forum|alt.badge.img+19
  • Inspiring
  • 3263 replies
  • February 17, 2021
kuovonne wrote:

@Bill.French tends to do a lot of work with the REST API, so he tends to think in those terms. However, I think that @Tom_Muller is asking for a simple formula field for sorting within the regular interface.

Have your formula return a value that says how accurate the match is.

  • If both values are found, return a 1
  • If only one of the values is found, return a 2
  • If neither value is found, return a 3

Then sort on the formula value.

1IF(
2 AND(
3 FIND(LOWER("First"),LOWER({Select})),
4 FIND(LOWER("Second"),LOWER({Select}))
5 ),
6 1,
7 IF(
8 OR(
9 FIND(LOWER("First"),LOWER({Select})),
10 FIND(LOWER("Second"),LOWER({Select}))
11 ),
12 2,
13 3
14 )
15)
16

I thought exactly the opposite because he said this – which I interpreted to mean he was using a filterByFormula in the API call.

“I am using the airtable API with a formula to basically filter a set of records from a table.”


kuovonne
Forum|alt.badge.img+29
  • Brainy
  • 6009 replies
  • February 17, 2021
Bill_French wrote:

I thought exactly the opposite because he said this – which I interpreted to mean he was using a filterByFormula in the API call.

“I am using the airtable API with a formula to basically filter a set of records from a table.”


Lol, You’re right! Sometimes answering questions is too much fun.


Reply