Skip to main content

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?

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

}

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

}

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?

 

 

Reply


Terms & Conditions