Help

Re: Find best matching item name

18190 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jessica_Shin
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,
Is there any formula that can be used to find the best matching block from the table? please help!

for example, I have an input that’s collective of words as a product name:
YELLOW PRETTY WOMEN’S BAG

in a meanwhile, my inventory data would have:
PRETTY MEN’S SHIRT
YELLO WORLD PRETTY MEN’S HAIR
RED SALSA PRETTY WOMEN’S BAG
…etc

and output to be : RED SALSA PRETTY WOMEN’S BAG.
because it has the largest number of words matching.
*input string isn’t always 4 words combined.

PRETTY MEN’S SHIRT (1word match)
YELLO WORLD PRETTY MEN’S HAIR (2words match)
RED SALSA PRETTY WOMEN’S BAG (3words match)

Hope my explanation was good enough to understand.
Thank you in advanced.

3 Replies 3

Hi @Jessica_Shin - possible solution in this post:

Not based on the number of words, but the “closeness” of one string to another.

This is the domain of full-text search with fuzzy logic, a capability that could be made possible as a formula if Airtable would finally allow us to create custom formulas from script blocks. Lacking this, it is possible to build a fuzzy search feature on top of Airtable bases.

Hi,
how do you expect to use it?
I have a small script doing something close (it checks 2 addresses from different sources) and put Y / N based on some ‘likeness’ into third field, not so precise as “Levenstein distance”, just words match, even without case align, but if you want to use it on the whole field, it would be even easier, than receive user input and generate text output.
I just tried to add UI part, learning deconstruction syntax, and I like it.

instead of (example from API doc):

const ordersTable = config.ordersTable;
const priceField = config.priceField;
const openOrdersView = config.openOrdersView;

using
const {ordersTable,priceField,openOrdersView}=config;

and here is the script

const config = input.config({title: 'Find best match',items:[
    input.config.table('table', {label: 'Select table'}),
    input.config.field('myfield', {label: 'Select field',parentTable: 'table'}),
    input.config.text('mytext',{label:'input text'})
    ]})
const {table,myfield:{name:field},mytext:text}=config;
const txtarr=[...new Set(text.split(' '))]
const reSet=x=>new Set(x.split(' '));
const sets=r=>[...txtarr,...reSet((r.getCellValueAsString(field)||''))]
const likeness=arr=>(arr.length-(reSet(arr.join(' '))).size)/arr.length
const compare=rec=>likeness(sets(rec))
const query=await table.selectRecordsAsync({fields:[field]});
const index=arr=>arr.indexOf(Math.max(...arr));
const val=query.records[index(query.records.map(compare))].getCellValueAsString(field);
output.text(val)