Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

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

Topic Labels: Formulas
688 6
cancel
Showing results for 
Search instead for 
Did you mean: 

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

6 Replies 6

Hi @Tom_Muller, and welcome to the community!

Yes. The API supports sorting.

image

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.

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

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.