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

[Tom_Muller]

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

[Bill_French]

Hi @Tom_Muller, and welcome to the community!

Tom_Muller:

I would like to sort these more accurate matches to the top of my results. Is there any way to do this?

Yes. The API supports sorting.

[Tom_Muller]

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.

[Bill_French]

Tom_Muller:

I do not want to sort by a field, but by the accuracy of the match.

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]

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

[Bill_French]

kuovonne:

I think that @Tom_Muller is asking for a simple formula field for sorting within the regular interface.

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]

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