Trying to create a Search query/script connecting two Airtables

I am looking to run multiple search queries/scripts in three fields where users will enter a long format answer explaining project needs. These are populating in a request table. I have second Airtable that lists out my talent at my agency and have created a ‘Keywords’ column that lists out all the different expertise fields and past industries.

My goal is to search through the long format answers client input and connect the keywords to the best match talent in my pool. Any thoughts or stimuli from past projects?

Below is what I have tried so far, but do not know if there is a simpler way since it is two air tables and not calling another database outside Airtable

let table = base.getTable('Project_Needs');
let resultsField = table.getField('Talent v2');

let record = await input.recordAsync('Record', table);
let searchQuery = record.getCellValueAsString("TalentTable_KeywordsField")

if (searchQuery) {
    table.updateRecordAsync(record, {[resultsField.id]: 'Updating...'});
    output.markdown(`Searching for ${searchQuery}...`);
    let results = await searchAsync(searchQuery.split(',').map(term => term.trim()).join('\n'));
    await table.updateRecordAsync(record, {[resultsField.id]: results});
    output.markdown(results);
}
async function searchAsync(Project_Needs) {
    let response = await fetch(
        `https://api.airtable.com/v0/appUBDlZzwWdC433V/Project_Needs`,
        {
            method: "POST",
            headers: {"content-type": "application/json"},
            body: JSON.stringify({queries: Project_Needs}),
        },
    );
    let result = await response.json();
    let resultLines = [];
    for (let {searchQuery, TalentTable_KeywordsField} of result) {
        resultLines.push(`## ${searchQuery.term}`);
        for (let {TalentTable_FirstNameField} of TalentTable_KeywordsField) {
            resultLines.push(`- [**${TalentTable_FirstNameField}**:])`);
        }
    }
    return resultLines.join('\n');

Any thoughts or stimuli from past projects?

Sure:

  • this is totally doable in the Scripting app
    • it’s pretty much exactly what the block was designed to enable
  • it should even make for an acccessible, app-like user experience if you have the script start from a button field

So, to sum it up:

Step 1

Have the script start from a button field:

const newAnswer = await input.recordAsync('',base.getTable(YourTableName));

Step 2

Do the thing you said you will.

Now, the sole existence of this thread suggests you’re new to JavaScript, so words of encouragement:

If you’re a highly technical person used to dealing with convoluted Excel setups and the like, you’ll have a working, not-half-bad prototype in a matter of weeks; possibly even days.

If you’re not but are willing to get stuck in, you’ll still have a prototype in a matter of weeks. Some parts of it might even work. But even if none do, you’ll have something to post here, after which I guarantee someone from the community will let you know how to improve it. In fact, probably multiple people will.

Alternatively, your requirement is in the work-for-hire territory. It’s impossible to be certain without the specifics, but it sounds like something that could be done rather swiftly, probably a day or two. If you have a budget, there’s a subforum here for hiring-help posts.

Thank you @Dominik_Bosnjak for your help and advice!

I like to think of myself as a master in excel formulas and automation, so I’ll look into javascript practices to learn my own formula.

I went with your advice to hire help, and hope to get something up and running sooner than the learning curve on my end. If you or someone you know is a great resource for this, would love to connect on this script.