You could try this with an Automation involving a Script. Trigger the automation on the creation of a new record in one of your tables. The first step will be a script, and your script will need two inputs:
- Name: options, Value: [Record (Step 1 Trigger) … MultiSelectField … List of ‘name’]
- Name: triggerRecordId, Value: [Record (Step 1 Trigger) … Record ID]
The script would look like this:
const inputConfig = input.config()
const options = inputConfig.options.split(", ").sort()
const triggerRecordId = inputConfig.triggerRecordId
const triggerTable = base.getTable("Table 1")
const oppositeTable = base.getTable("Table 2")
const oppositeQ = await oppositeTable.selectRecordsAsync()
const oppositeR = oppositeQ.records
const matches = new Set()
oppositeR.forEach(x=> {
const checkOptions = x.getCellValueAsString("Multiselect Field Name").split(", ").sort()
if(options.length === checkOptions.length) {
let isMatch = true
for (var i = 0; i < options.length; i++) {
if (options[i] !== checkOptions[i]) {
isMatch = false
}
}
isMatch && matches.add(x.id)
}
})
const combinedMatches = Array(...matches)
combinedMatches.length && triggerTable.updateRecordAsync(triggerRecordId, {
"Link": combinedMatches.map(x => ({id: x}))
})
const matchesEmails = combinedMatches.length ? combinedMatches.map(x => {
return oppositeQ.getRecord(x).getCellValue("Email")
}) : null
output.set("matchesEmails", matchesEmails)
The above script checks the values for the multiselect field against the values of the “same” multiselect field in the other table. If the values match, the script finds that matching record’s associated email.
From there your next step could send an email using the output from the script: matchesEmails
