Airtable Script - Search & Link Keywords To Products

readme.md

Keyword Matching Script

Description

The keyword matching script was created in relation to this forum post within the Airtable Community. This script works with two table objects assigned at the start of the script.
The table objects are defined as "productsTable" and "keywordsTable". These variables may be renamed however they may best fit the semantic needs of your implementation.

Example

To highlight the functionality of this script, here is an example of its usage.
In this example, we have a Products table and a Keywords table.

For each unique product record, we want to take the product name and query keyword records that return either partial of complete matches to the product name.
Once the script has been added and configured in a new Scripting Extension and we have inserted the correct table and field ids, we can simply hit the Run button that is displayed in the extension.

Configuration

Configuring this script to work in your base requires that you add and verify that the correct table and field ids (or names) have been inserted into the correct locations in the script.

Table IDs

Add the table IDs or table names to the following lines found at the top of the published script.
Note: Table IDs are always recommended and preferred. Relying on the table name will create an undocumented dependency on the (easily changable) name of the table. Implementing the table id will guarantee that changes to your table names will not break the script configuration.

const productsTable = base.getTable("tableId || tableName");
const keywordsTable = base.getTable("tableId || tableName");

Field IDs

There are five locations where you must enter the correct field IDs.
The first location is found at the productRecords variable within the options object passed to the table.selectRecordsAsync() function.
While the example previously displayed utilized the primary fields of the record objects in each table, you do not have to use the primary field to facilitate your queries.
As long as the target field's data type returns a string, then any field may be used to facilitate the search.

const productRecords = await productsTable
	.selectRecordsAsync({
		fields: [
			"fieldId || fieldName", //Product (Primary Field)
			"fieldId || fieldName" //Keywords linked record field
		]
	})
	.then(records => records.records);

The second location is found at the keywordRecords variable. This instance's context is identical to the first with the difference being that this table (and subsequent fields) need to be the table that you're querying against for your keywords.

const keywordRecords = await keywordsTable
	.selectRecordsAsync({
		fields: [
			"fieldId || fieldName", //Keyword (Primary field)
			"fieldId || fieldName" //Products linked record field.
		]
	})
	.then(records => records.records);

The third location is found at the keywords variable.
This field needs to be the field in the Keywords table that stores the string-typed value that you want to query against.
As previously stated, this field does not have to be the primary field. The operation is possible as long as the field returns a string-typed value.

const keywords = keywordRecords.map(record => ({
	value: record.getCellValueAsString("fieldId || fieldName").toLowerCase().trim(),
	id: record.id
}));

The fourth location is found at the productName variable (within the matchedKeywords() function.)
This field should be the string-typed field on the record that you are trying to link keywords to. Within the example workflow, this is the product name field.

const matchedKeywords = productRecord => {
    ...
	const productName = productRecord.getCellValueAsString("fieldId || fieldName");
    ...
};

The fifth and final location is found at the productUpdates variable.

Breakdown

This script creates an array of keyword record objects and writes the array to the keywords variable.
Once we have all of the keyword records, the script creates two unique arrays for two types of keywords, flatKeywords and compoundKeywords.
Flat keywords are defined as words that are a single word. Flat keywords are found using the String.prototype.search() method. If no spaces are found in the target string, it is written to the resulting array.

Compound keywords are more accurately described as terms. By technicality, compound keywords are identified by the presence of one or more spaces being present in the string after the String.prototype.trim() method is called on the string.
Examples of compound keywords include:

• Summer Cream
• Dark Orange
• Long Sleeve
• Cocktail Dress

The classifications of flat and compound keywords was implemented after the initial script testing was completed.
Take the following product name as an example: Long Sleeved White T-Shirt. If we call the String.prototype.split(" ") method on this string, the following array is returned: ["Long", "Sleeved", "White", "T-Shirt"].
Presume that we know that the following keyword records exist in the keywords table:

• Long Sleeved
• White
• T-Shirt

When we execute the search for the keywords, only the White and T-Shirt records will be returned.
To solve for this, there are two unique search configurations that occur when the matchedKeywords() function is called.
The first search is specifically looking for flat keywords using the method outlined above.

The second search method utilizes a the compoundKeywords array and the flattenedName string. The flattenedName string is created by the following chain sequence: String.toLowerCase().replace(/\s/g, "").
As an example, if we have the product name Long Sleeved White T-Shirt, the value of flattenedName will return longsleevedwhitet-shirt.
When we search for matching compound keywords against this format, we can reliably match the correct keyword values to the product name.
If any additional keywords are matched after the second search configuration, the values are pushed to an array of the results within the matchedKeywords() function.

The productUpdates variable contains an array of product record objects that we need to update in our final operation.
The `Object.fields

_keywordMatch.js

const productsTable = base.getTable("tableId || tableName");
const keywordsTable = base.getTable("tableId || tableName");

const productRecords = await productsTable
	.selectRecordsAsync({
		fields: [
			"fieldId || fieldName", //Product (Primary Field)
			"fieldId || fieldName" //Keywords linked record field
		]
	})
	.then(records => records.records);

const keywordRecords = await keywordsTable
	.selectRecordsAsync({
		fields: [
			"fieldId || fieldName", //Keyword (Primary field)
			"fieldId || fieldName" //Products linked record field.
		]
	})
	.then(records => records.records);

//Returns an array of objects containing the keyword value and its record ID.
//All term values are lowercased.
const keywords = keywordRecords.map(record => ({
	value: record.getCellValueAsString("fieldId || fieldName").toLowerCase().trim(),
	id: record.id
}));

//Array of word objects whose values are a single word.
const flatKeywords = keywords.filter(
	wordObject => wordObject.value.search(/\s/g) === -1
);

//Array of word objects whose values contain two or more words separated by spaces.
const compoundKeywords = keywords
	.filter(wordObject => wordObject.value.search(/\s/g) !== -1)
	.map(wordObject => ({
		value: wordObject.value.replace(/\s/g, ""),
		id: wordObject.id
	}));

//Returns an array of matched keyword record ids.
const matchedKeywords = productRecord => {
	//Break the words in the product name. Set to lowercase.
	const productName = productRecord.getCellValueAsString("fieldId || fieldName");
	const flattenedName = productName.toLowerCase().replace(/\s/g, "");
	const productWords = productName.split(" ").map(word => word.toLowerCase());

	//First, search for matches using the flat keywords.
	let matches = productWords
		.map(productWord =>
			flatKeywords.filter(keyword => keyword.value == productWord)
		)
		.filter(matches => matches)
		.flat();

	//Next, use the compound keywords to search the entire flattened name.
	//Push any results to the matches array.
	compoundKeywords.forEach(keyword => {
		flattenedName.includes(keyword.value) && matches.push(keyword);
	});

	return matches.map(wordMatch => ({ id: wordMatch.id }));
};

//The array of record objects that will be used to update the product records.
let productUpdates = productRecords.map(product => ({
	id: product.id,
	fields: {
		"fieldId || fieldName": matchedKeywords(product)
	}
}));

if (productUpdates) {
	//If there are more than fifty product records to update, batch the update operations by increments of fifty.
	while (productUpdates.length > 50) {
		await productsTable.updateRecordsAsync(productUpdates.slice(0, 50));
		productUpdates = productUpdates.slice(50);
	}
	await productsTable.updateRecordsAsync(productUpdates);
}