Jun 22, 2022 08:23 AM
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.
Jun 22, 2022 12:11 PM
Hi @Jessica_Shin - possible solution in this post:
Not based on the number of words, but the “closeness” of one string to another.
Jun 22, 2022 02:10 PM
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.
Jun 25, 2022 06:52 PM
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)