How to count the number of assignments have been given to an employee

[Erik_Lindquist]

I have a table with a list of employees.I have another table of projects and roles that the employee may be assigned to, for example in the project table we have a column for PM, Co-PM, Lead Analyst, Analyst 1, Analyst 2.

I want to count the number of times each employee is assigned a role in the projects table.

In Excel and Google it is a simple =COUNTIF(‘Project’!$P$2:$V$87,A1), where column A is the employee column in the employee table, but there is no COUNTIF function in Airtable…

I have also noticed that for every link I have made to the employee table I get a column in the employee table called “Project” or “Project 2” or “Project 3” and each cell has the Project ID from the Project Table.

I have tried to make a formula column called “Workload” and use the formula

COUNT({Project},{Project 2},{Project 3},{Project 4},{Project 5},{Project 6},{Project 7})

However this returns 0, because the links are not numeric references they are text references.Using COUNTA just counts the number of non-blank fields, rather than the number of entries in each field.

Any suggestions are greatly welcome.

Thank you,

Erik

[Bill_French]

Erik_Lindquist:

Can I setup a script to run through the list of employees in the {name} column of the employee info table, and read the {Project Manager} column in the Project table and count the number of times the employee shows up in that column, and then do the same thing for each of the other roles columns in the Project table, and then spit the result out into a Workload column next to the employee’s name, and then repeat for each employee in the {name} column?

That is one very long sentence, but yes. Pretty much anything you can imagine in terms of data acrobatics is possible in a script block.

But I recommend you start with a simpler objective - a subset of the big-a** sentence.

… run through the list of employees in the {name} column of the employee info table, and read the {Project Manager} column in the Project table and count the number of times the employee shows up in that column

Don’t try to update the Workload column until you have the basic process and you can see the counts as the script runs. Use output.inspect() to display your names and counts - Script Blocks are ideal for finding the route to the analytics.

Then - and only then - capture the date into another column or tables.

[Erik_Lindquist · Accepted solution]

Thanks @Bill.French for the encouragement, I found a scripts you had done to count, plus a few others, and merged them, tested them as you suggest by outputting first, and then inserted the script into an automation that is triggered on any of the roles columns being updated.

It works great!

**Expand for:** Summary of COUNTIF script to find the number of times an Employee Name appears in the roles in the Project table

/**
 * COUNTIF: to count the number of cells that meet a criterion; in this case to count 
 * the number of times an Employee Name appears in a role in the Project table.
 *
 * **Notes on adapting this script.**
 *  Employee names are found in table "Count" in the Field "Name"
 *  Roles are found in the table "Project Tracking or Board", and includ
 *      Fields: PM, PM2, Lead Analyst 1, Lead Analyst 2, Support Analyst 1-3
 *  Score is the number of times an Employee Name is found assigned to one of 
 *      the Roles in the Project Table
 *  loopArray stores the value of Score for each employee and updates once each employee 
 *      name has been searched for in the Roles
 *
 */
'use strict';
/**
 * Users may provide values for any of the properties in the following object
 * to streamline the script's startup.
 */
const hardCoded = {
    tableName: '',
    tableSearchName: '',
    firstSearchFieldName: '',
    secondSearchFieldName: '',
    thirdSearchFieldName: '',
    fourthSearchFieldName: '',
    fifthSearchFieldName: '',
    sixthSearchFieldName: '',
    seventhSearchFieldName: '',
    scoreFieldName: '',
    searchTerm: ''
};

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

// Open the Table to be searched (table) and the Table that provides the search string (tableSearch)
const table = base.getTable("Project Tracking or Board");
const tableSearch = base.getTable("Count");

// Fields to search for Employee's being assigned to rows
const firstSearchField = table.getField('Lead analyst 1');
const secondSearchField = table.getField('Lead Analyst 2');
const thirdSearchField = table.getField('Project Manager 2');
const fourthSearchField = table.getField('Project manager');
const fifthSearchField = table.getField('Support analyst 1');
const sixthSearchField = table.getField('Support Analyst 2');
const seventhSearchField = table.getField('Support Analyst 3');

// Field to update with WORKLOAD by Employee
const scoreField = tableSearch.getField('WORKLOAD');

// Load the elements of the Search Table
const querySearch = await tableSearch.selectRecordsAsync();

// Loading the elements of the table being searched
const queryResult = await table.selectRecordsAsync();

// Use loopArray to store the score for each Employee
let loopArray = [];

// Open up the Employee Table (Count) and get all the employee names and loop through updating the searchTerm

for ( let recordSearch of querySearch.records) {
    const searchTerm = recordSearch.getCellValueAsString('Name');

    // Set iniitial score to zero
    let score = 0;

    // loop through each of the search fields for the current Employee name (searchTerm)
    for (let record of queryResult.records) {

        if (record.getCellValueAsString(firstSearchField).includes(searchTerm)) {
            score += 1;
        }
        if (record.getCellValueAsString(secondSearchField).includes(searchTerm)) {
            score += 1;
        }
        if (record.getCellValueAsString(thirdSearchField).includes(searchTerm)) {
            score += 1;
        }
        if (record.getCellValueAsString(fourthSearchField).includes(searchTerm)) {
            score += 1;
        }
        if (record.getCellValueAsString(fifthSearchField).includes(searchTerm)) {
            score += 1;
        }
        if (record.getCellValueAsString(sixthSearchField).includes(searchTerm)) {
            score += 1;
        }
        if (record.getCellValueAsString(seventhSearchField).includes(searchTerm)) {
            score += 1;
        }
    }
    // display the count
    // output.markdown("Counting the occurrence of **'" + searchTerm + "**  **" + score + "**");

    // Push the score to loopArray
    loopArray.push({
        id: recordSearch.id,
        fields: {
            [scoreField.id]: score
        }
    });
}

//  Airtable limits the update to 50 updates at a time, so we break loopArray into groups of 50 and update

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