Help

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

Solved
Jump to Solution
2120 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Erik_Lindquist
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
Erik_Lindquist
5 - Automation Enthusiast
5 - Automation Enthusiast

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);
}

See Solution in Thread

11 Replies 11

Check out the “Count” field type. It will let you count the number of linked records in a linked record field, and you can also set it to only count linked records that match a specific condition.

Erik_Lindquist
5 - Automation Enthusiast
5 - Automation Enthusiast

I tried that. It does not tell you how many times an employee is referenced in a linked project column. Worse there are seven columns that are linked to the employee column.

And in the employee table there is a reciprocal linked field and it rightly records the project numbers for the each role in a project an employee is assigned to. But you can’t even count the number of project numbers per employee, as noted above.

Indeed, and this is a common misconception about the fitness of purpose of Airtable. It is a database management system that has some strengths of spreadsheets and some strengths of data management. We can all agree that it has a pretty good UI/UX for basic stuff. In both dimensions – spreadsheets and data – it has weaknesses; this community has documented almost all of them.

But one thing it is not - a business analytics platform.

Sorry, no answers here - just opinion and recommendations from here down. :winking_face:

It’s unreasonable to expect a platform that is intended to blend the essential benefits of data and formulas to also magically provide the last mile of metrics, analytics, and complex operational performance indicators.

For the last mile, you must be willing to (i) shape the raw data into useful analytics, or (ii) you must shape the data model to allow these key indicators to calculate using native capabilities in Airtable.

The former (i) is best done with script blocks, custom apps, or the API to blend your data into external apps such as Count.co. Achieving your objectives is entirely in reach with a script block, but it’s premature to say exactly how to approach this because we don’t really know in what contexts you hope to convey such analytics to business users.

The latter (ii) is achieved by designing a data model with deference to the desired analytics. I used the term “complex operational performance indicators” above which is to say that the more distant your data model is from your desired metrics, the more complex computing the indicators will become.

In my view, I think you need to back into this challenge by expressing the ideal analytics requirements, and then determine if the data model could be changed to fit your needs. In most cases, subtle modifications to the way the data is modelled will often lead to far greater analytics agility.

Yes it does. That’s exactly what the count field does. You need to add this field to the employees table, not the projects table.

Yes you can, as noted above. If you want to count roles, then you need to use the count field. If you have 7 different roles, then you need to create 7 different count fields.

If you want to count projects per employee, you can add up your existing count fields. You could also get away with only ONE count field, but…

…it doesn’t sound like you’ve setup your system in the most ideal fashion, because you only need to create ONE link from projects to employees, not 7 links. You should ideally have ONE “role” field per project, which is either a “single-select field” or “multi-select field“.

Then, create one linked record field between employees and projects. Then, you’ll only have one reverse linked record field per employee instead of 7 reverse linked record fields per employee.

If your needs are more complex than that, then you may also want to consider setting up a many-to-many relationship.

Erik_Lindquist
5 - Automation Enthusiast
5 - Automation Enthusiast

The count filed counts the number of fields with data.

In the project table, each project is represented by a row. In additionI have 7 different roles per project, because each project can have 7 different roles. Any given employee can only falls into one role per project.

Count Column Property

What you can count in a column is the number of times a specific value occurs, not the number of times each entry in a list of entries (the Employee column) appears in another list, and sum for each entry in the list (by Employee).

What is needed is a COUNTIF statement as outlined above, or a COUNT statement that actually counts the number of entries per field. The dumb thing is that Airtable already knows, because it shows you all the projects by employee in the linked column that is replicated in the employee table. It just can’t count them. Or I just haven’t found a way to count them.

Employee linked projects

The project numbers are right there 3 - 6 projects per employee. It just cant count them. If you use the COUNTA it returns 1 per non blank field, which is not correct.

Sorry, but I already gave you the answer 2 times already, and never once did I mention the COUNTA function, which is not the answer.

Sorry, but I can’t help you further, unless you would like to pay for my consulting time. I am a professional Airtable consultant, and you can contact me through my website at scottworld.com.

Hi Bill,
I agree with everything you said, and we are using AirTable solely as a prototyping platform.
I have built a small Airtable App to look at a small list. However, the lack of error checking means it might compile but doesn’t run, which makes for a lot of extra work for what appears to be such a simple task.
Erik

Yeah - when I read your requirements this jumped out at me and triggered the scree I composed. On weekends I tend to write about philosophical issues, so you are the lucky guy that got my rant for the week. :winking_face: And it was intentional that I used the term “scree” - my rants are often just a lot of loose rock readers try to climb over.

In any case…

This is one of the challenges when you have limited time to assist and without perfect knowledge about the problem domain. You might want to share sample data in the format you are trying to compile the metrics for - this often gives onlookers some ideas.

The fact that you need 120 count fields tells you two things:

  1. Count won’t work, at least not in a practical sense.
  2. A pivot table might help (but I haven’t thought this one all the way through because I’m slammed today).
  3. A data model change might be an out (but invasive).
  4. Script will certainly help and it probably makes sense for the script to compute the totals and push them into a new table designed for analytics, charting, etc.

Thanks Bill,

I know you understand the challenge. My comment on not understanding the problem was to Scott and his rant on how he had solved it with COUNT.

It would be helpful if my client were to normalize a lot of their tables that is true. At the end of the day I am going to push everything to firebase. I was just using Airtable to better assess the views and how to segment the data. And get greater consensus from the client on how they run their business.

I havent played with script blocks yet.

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?

Thanks,
Erik

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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);
}