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?

 

 

Terms & ConditionsAccessibility statement