Skip to main content

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!

Hi there! This is fairly easy and takes 1-2 hours. Happy to help with this, please reach out to rupert.hoffschmidt@gmail.com

Best,
Rupert

I was a bit bored, so I played around with the idea.
Is this along the lines of what you're looking for?

 

I was a bit bored, so I played around with the idea.
Is this along the lines of what you're looking for?

 


Yes, that's exactly what I'm looking for. 

Yes, that's exactly what I'm looking for. 


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.

This worked perfectly. Thank you very much @Ben_Young1 !

Reply


Terms & Conditions