Comparing amount of common linked records

[Grasiu]

Let me describe my issue with a mock-up.

I have a database with two tables: People and Projects. People are linked to Projects (people can be on multiple projects and vice-versa). I want to have an information how many People in common each pair of projects has.

I would start with creating a junction table Projects x Projects, lest call it Project Matrix, using a script. And after that is there any way to compare those two?

Here is a database created to present the issue - https://airtable.com/shrQjmLrg3CHtXUlI

Creating some flag for each Project or Person won't work, because in the real thing (the non-mockup) there are hundredths of People and hundredths of Projects.

Can this be done without a script? If this has to be done with a script - how such script would have to look like?

[Grasiu]

After some Googling I guess one needs to use scripts for that. I've spent some time and was able to come up with such one working:

for(let a=0; a<query.records.length; a++){
    let counter = 0;
    let record = query.records[a];
    let pf1p = record.getCellValue("People (from First Project)");
    let pf2p = record.getCellValue("People (from Second Project)");
    for(let i=0; i<pf1p.length; i++ ) {
        for(let j=0; j<pf2p.length; j++ ) {
            //console.log(pf1p[i], pf2p[j]);
            if(pf1p[i].id==pf2p[j].id) {
                counter++;
                //console.log(pf1p[i], pf2p[j]);
            }
        }
    }
    console.log(record.name, counter);
    table.updateRecordAsync(record, {"How many common People in the pair?":counter});
}

[MelloJello]

Hi @Grasiu 

I am not sure what the common item is between people  (name, email...)

Could you add a Roll Up to the People table on the Projects Field with COUNTA(values) as the formula?