Help

Search for string in cell and count if string exists

Topic Labels: Scripting extentions
4108 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Eren_Alkan
4 - Data Explorer
4 - Data Explorer

@JonathanBowen @Bill.French - From what I’ve read so far, you folks are the people to go to for this question. I’m currently developing a script that I would like to leverage to search a keyword within column and then assign points to that record.

To add context, lets say I have a base with a teacher ID in each row and each teacher has 3 classes (columns) with sets of student names in the cells. My goal is to input let’s say “Jane Doe”, and have the script search each column for the name. If the name exists in a cell in Column 1 connected to Teacher-A, then 1 point would be added to the Teacher A Record (in a separate column or variable)

So far I’ve been able to write a script that asks for inputs and creates new records in a new table called search results. However, the columns where I’d like to record points per record are not be updated correctly. In other words, I’m not able to search the string within the cell. Was wondering if you could direct me in any direction or if what I am attempting is possible? Happy to provide additional information if needed.

3 Replies 3

@Eren_Alkan

Here’s a start … it’s a block that lets you determine the occurrence of a given string in a text field for all records in the selected table.

image

Source Code
/*

   ***********************************************************
   Airdrop - Counting Example
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

output.markdown('# Counting Example');

// get the table name
output.markdown("### Select the Table");
let sourceTable     = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;

// get the source field name
output.markdown("### Select the Field to be Counted");
let sourceField = await input.fieldAsync("Pick the name of the field to be counted:", sourceTable.id);
let sourceFieldName = sourceField.name;

// set the value to be counted
output.markdown("### Enter the Value to be Counted:");
let thisQueryValue = await input.textAsync('What value do you want to count?');

// get the records
let result = await sourceTable.selectRecordsAsync();
let sourceRecords = result.records;

// iterate across all the records
let thisCount = 0;
let thisFieldValue;
for (var r = 0; r < sourceRecords.length; r++)
{
    // get the field value for this record
    thisFieldValue = sourceRecords[r].getCellValue(sourceFieldName);

    if (thisFieldValue != null) {
        
        if (thisFieldValue.toString().toLowerCase().includes(thisQueryValue.toString().toLowerCase()))
            thisCount += 1;
    }

}

// display the count
output.markdown("Counting the occurrence of **'" + thisQueryValue + "'** in field **'" + sourceFieldName + "'** = **" + thisCount + "**");

@Bill.French - Thank you for the advice! Definitely, a great start!

Mike_Pennisi
7 - App Architect
7 - App Architect

@Eren_Alkan Here’s another version that’s just a little closer to your original
request–it queries 3 fields, and it writes the “score” back to the same table.
The way it detects a match is somewhat simplistic (it only recognizes exact
matches), but it would be short work to adapt it to be more forgiving (e.g.
ignoring case or whitespace).

Click to expand
/**
 * Copyright 2020 Bocoup
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to
 * deal in the Software without restriction, including without limitation the
 * rights to use, copy, modify, merge, publish, distribute, sublicense, and/or
 * sell copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
 * IN THE SOFTWARE.
 */

/**
 * Count word occurrences script
 *
 * Determine the number of fields that contain a given search term, and store
 * that number (also known as its "score") in a separate field.
 *
 * **Notes on adapting this script.**
 *
 * The script prompts for input every time it is run. For some users, one or
 * more of these values may be the same with every execution. To streamline
 * their workflow, these users may modify this script by defining the constant
 * values in the first few lines. The values should be expressed as JavaScript
 * strings in the object named `hardCoded`.
 */
'use strict';

/**
 * Users may provide values for any of the properties in the following object
 * to streamline the script's startup.
 */
const hardCoded = {
    tableName: '',
    firstSearchFieldName: '',
    secondSearchFieldName: '',
    thirdSearchFieldName: '',
    scoreFieldName: '',
    searchTerm: ''
};

/**
 * Do not edit any code following this message.
 */

// Airtable limits batch operations to 50 records or fewer.
const maxRecordsPerCall = 50;

const table = hardCoded.tableName
    ? base.getTable(hardCoded.tableName)
    : await input.tableAsync('Table to search within');
const firstSearchField = hardCoded.firstSearchFieldName
    ? table.getField(hardCoded.firstSearchFieldName)
    : await input.fieldAsync('First field to seach within', table);
const secondSearchField = hardCoded.secondSearchFieldName
    ? table.getField(hardCoded.secondSearchFieldName)
    : await input.fieldAsync('Second field to seach within', table);
const thirdSearchField = hardCoded.thirdSearchFieldName
    ? table.getField(hardCoded.thirdSearchFieldName)
    : await input.fieldAsync('Third field to seach within', table);
const scoreField = hardCoded.scoreFieldName
    ? table.getField(hardCoded.scoreFieldName)
    : await input.fieldAsync("Field to store records's scores", table);
const searchTerm = hardCoded.searchTerm
    ? hardCoded.searchTerm
    : await input.textAsync('Search term');

const queryResult = await table.selectRecordsAsync();
let operations = [];

// Part 1: Prepare the operations

for (let record of queryResult.records) {
    let score = 0;

    if (record.getCellValueAsString(firstSearchField).includes(searchTerm)) {
        score += 1;
    }

    if (record.getCellValueAsString(secondSearchField).includes(searchTerm)) {
        score += 1;
    }

    if (record.getCellValueAsString(thirdSearchField).includes(searchTerm)) {
        score += 1;
    }

    operations.push({
        id: record.id,
        fields: {
            [scoreField.id]: score
        }
    });
}

// Part 2: Perform the operations in batches

while (operations.length > 0) {
    await table.updateRecordsAsync(operations.slice(0, maxRecordsPerCall));
    operations = operations.slice(maxRecordsPerCall);
}

output.text('Done');

You can also get pretty far with a formula:

IF(SEARCH("my term", MyField1) = "", 0, 1) + IF(SEARCH("my term", MyField2) = "", 0, 1) + IF(SEARCH("my term", MyField3) = "", 0, 1)

That has the advantage of immediately synchronizing with the state of the
table. Though the UI for changing the search term there is less intuitive than
an explicit prompt in the Scripting Block. In some respects, it might be
preferable to store the search term in the table itself:

IF(SEARCH(MyTermField, MyField1) = "", 0, 1) + IF(SEARCH(MyTermField, MyField2) = "", 0, 1) + IF(SEARCH(MyTermField, MyField3) = "", 0, 1)

This promotes search term modification out of a field configuration, though
with this, you’ll have to update an entire column of cells in order to change
the term.