Help

Re: Comparing amount of common linked records

449 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Grasiu
4 - Data Explorer
4 - Data Explorer

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?

2 Replies 2
Grasiu
4 - Data Explorer
4 - Data Explorer

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

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?

MelloJello_0-1678476637609.png

MelloJello_2-1678476654011.png

 

MelloJello_1-1678476653988.png