Populate multi-select field based on text match in primary field

[Curious_Carrot]

Hello! I would like to set-up an automation using "Run a script" that checks the text in the primary field [Name] for a matching value in a multi-select field [Topic] and then populates the field with the matching value. Would greatly appreciate any help with creating a script and automation that can accomplish this as I am unfamiliar with scripting. 

Included sample data screenshot for visual. The real dataset has ~ 20 selection options.

The process should look something like this:

1) Record is created
2) Script runs
3) Record Updated

Thank you!

[Arthur_Tutt]

Hey @Curious_Carrot ! Will the keyword always exist in the primary field? For example, where you say 'a bear is a large ANIMAL' , animal is the keyword and you want that to populate in the topic field. Just trying to understand if can match for exact keywords, or will need to infer based on context. 

[Curious_Carrot]

Yes, @Arthur_Tutt we will be following a naming convention where the keyword will always be required to be in the primary field.

[Arthur_Tutt · Accepted solution]

Alright @Curious_Carrot I've got good news and bad news. The good news is I've got an automation working for you. The bad news is it's a little complicated and may be difficult to maintain. If you're okay to switch the Topic data type from a 'single select' to a 'single line text', the automation becomes much simpler (simpler data type). But either way I recorded a quick Loom Video to walk you through step by step how to get this setup! 

*** Remember to update all Table / Field / Variable names to match your own ***

Base Setup:

Automation Setup:

Script  1:

// get table
var table = base.getTable("Animals") // change "Animals" to the name of your table
var query = await table.selectRecordsAsync({fields: ["Topic"]});
var record = query.records;
// console.log(record)
var topicDetails = [];


// run this script to get the ID, name and color for each single select topic
for (var i = 0; i < record.length; i++) {
    topicDetails.push( record[i].getCellValue("Topic"))
}

console.log(topicDetails)

Script 2:

*** Remember to update all Table / Field / Variable names to match your own ***

// get table
var table = base.getTable("Animals") // change "Animals" to the name of your table

// get input variables
var inputConfig = input.config();
var recordID = inputConfig.recordID;
var name = inputConfig.name;

var topic = {
    id: "",
    name: "",
    color: ""
};

if (name.includes("animal")) {
  topic = {
            id: "selQpVsyZjDnqgfam",
            name: "animal",
            color: "blueLight2"
  };
} else if (name.includes("tree")) {
  topic = {
            id: "selaMTx6u0x3kFI1g",
            name: "tree",
            color: "yellowLight2"
  };
} else if (name.includes("grain")) {
  topic = {
            id: "selDevZSyCs1xnJwu",
            name: "grain",
            color: "pinkLight2"
  };
} // insert more 'else if' statements below as needed for more topics

console.log(topic)

// Updating Topic With New Data
var updates = [{
  "id": recordID,
        fields: {
            "Topic": topic   
        }
}]
console.log(updates)

await table.updateRecordsAsync(updates);

Let me know if this works for you!! 

[Curious_Carrot]

This is awesome @Arthur_Tutt! Thank you for the walkthrough and explaining the automation for me. I'd prefer the method with less maintenance of course,  but have to confer with my team on whether a text field is acceptable for their use case. We will probably be using the field for filtering in an Interface so I also have to determine how well that would work.