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

Ask the community Formulas
#1

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:

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

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

#2

Hi @Tom_Muller, and welcome to the community!

Yes. The API supports sorting.

image
image1380×812 106 KB

#3

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.

#4

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.
#5

@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.

IF(
  AND(
    FIND(LOWER("First"),LOWER({Select})),
    FIND(LOWER("Second"),LOWER({Select}))
  ),
  1,
  IF(
    OR(
      FIND(LOWER("First"),LOWER({Select})),
      FIND(LOWER("Second"),LOWER({Select}))
    ),
    2, 
    3
  )
)
#6

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.”

#7

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