Table A contains a list of products. The first column is the name of the product.
Example product titles in Table A:
White Long Sleeve T-Shirt
Blue Short Sleeve Hoodie
Table B in the same base contains a list of product keywords.
Example Keywords from Table B:
White
Long Sleeve
T-Shirt
Blue
Short Sleeve
Hoodie
I would like help to create a script that automatically searches Table A's first column with the keywords in Table B, and "tags" them accordingly in a new column as linked records. The result of the script would look something like this:
Table A
Name
Tags (from Table B)
White Long Sleeve T-Shirt
White, Long Sleeve, T-Shirt
Table A contains thousands of products. Products are added regularly and titles can also be modified occasionally. The list of keywords in Table B are added manually as a new product types or categories are created. This can continue.
I am considering hiring an expert to help create this script and I would like to get a better sense of how complex this is and how much it might cost.
Thank you!
Best answer by Ben_Young1
Thank you for this extremely helpful reply and step by step guide. Using the instructions provided, I created a script that appears to work successfully for many records. The script seems to run into the following error:
"j: Can't set cell values: invalid cell value for field 'Keywords'. Duplicate linked record objects"
I would really appreciate your guidance. Thank you again for such a comprehensive overview.
That's rather strange.
Within the matchedKeywords() function, could you please change it from this format:
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 }));
};
To this format instead:
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());
let matches = productWords
.map(productWord => flatKeywords.filter(keyword => keyword.value == productWord))
.filter(matches => matches)
.flat();
compoundKeywords.forEach(keyword => {
flattenedName.includes(keyword.value) && matches.push(keyword)
})
const matchIds = matches.map(wordMatch => ({id: wordMatch.id}));
return matchIds.filter((match, index) => index === matchIds.findIndex(duplicate => duplicate.id === match.id));
}
Be sure to set the field ID/field name within the productName variable assignment. I've added a final operation at the bottom that will filter the matched keyword records to only return unique keyword records to link to each product.
Let me know if that change solves the problem and I'll update the gist and documentation to reflect the change.
Here's a gist containing some relevant information and the script itself. I'll make some changes in the future to assist in making it easier to plug in to any base, but there should be enough there for you to get up and going. I included specific callouts in the documentation that will walk you through where you need to enter your field and table ids.
There are a few limitations, but everything flows for the most part.
Let me know if you run into any issues or have any questions.
Here's a gist containing some relevant information and the script itself. I'll make some changes in the future to assist in making it easier to plug in to any base, but there should be enough there for you to get up and going. I included specific callouts in the documentation that will walk you through where you need to enter your field and table ids.
There are a few limitations, but everything flows for the most part.
Let me know if you run into any issues or have any questions.
Thank you for this extremely helpful reply and step by step guide. Using the instructions provided, I created a script that appears to work successfully for many records. The script seems to run into the following error:
"j: Can't set cell values: invalid cell value for field 'Keywords'. Duplicate linked record objects"
I would really appreciate your guidance. Thank you again for such a comprehensive overview.
Thank you for this extremely helpful reply and step by step guide. Using the instructions provided, I created a script that appears to work successfully for many records. The script seems to run into the following error:
"j: Can't set cell values: invalid cell value for field 'Keywords'. Duplicate linked record objects"
I would really appreciate your guidance. Thank you again for such a comprehensive overview.
That's rather strange.
Within the matchedKeywords() function, could you please change it from this format:
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 }));
};
To this format instead:
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());
let matches = productWords
.map(productWord => flatKeywords.filter(keyword => keyword.value == productWord))
.filter(matches => matches)
.flat();
compoundKeywords.forEach(keyword => {
flattenedName.includes(keyword.value) && matches.push(keyword)
})
const matchIds = matches.map(wordMatch => ({id: wordMatch.id}));
return matchIds.filter((match, index) => index === matchIds.findIndex(duplicate => duplicate.id === match.id));
}
Be sure to set the field ID/field name within the productName variable assignment. I've added a final operation at the bottom that will filter the matched keyword records to only return unique keyword records to link to each product.
Let me know if that change solves the problem and I'll update the gist and documentation to reflect the change.
