Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Project Collision Mapping - Database Design

Topic Labels: Base design Formulas
Solved
Jump to Solution
240 2
cancel
Showing results for 
Search instead for 
Did you mean: 
maddy
4 - Data Explorer
4 - Data Explorer

Hi all!

My team and I are using Airtable to store user research as we build out and iterate on different products within our org. One of our goals is creating a repository to reference research participants we've spoken with in the past to contact them more easily for future research. 

The goal of this base is two-fold: the first being a structured pool of participants to pull from, and the second is a collision map to make sure we're not pulling the same participant for several different projects and overloading them. Our project teams often aren't aware of who the other team is recruiting for research and this has happened a few times. 

In the participant repository base, my setup so far is 3 tabs, 1)Ongoing projects and their timelines, 2) Our staff members and 3)The participant database. I have a lookup field in the participant database tab that lists the projects a participant is involved with and another field with the status of those projects, whether they are active or inactive. I'd like the last field to tell whether there are 2+ active projects assigned to a participant so that it says "collision" or turns red. If there isn't, the field can return a "no collision" or be blank. I've been messing with IF/AND statements for formulas but haven't been able to piece together something that works with multiple statuses. The status field is a single-select dropdown if that is relevant - would a different field type help?

Any thoughts on how to approach this would be greatly appreciated!

1 Solution

Accepted Solutions
DisraeliGears01
7 - App Architect
7 - App Architect

This is a lot easier now that they made new settings available in the Rollup field... Instead of lookup from participant to project, use a rollup, only including linked records where status is active, with a Countall(values) function. That'll output the number of active records only, and then you can map that to your participant table where 2+=collision.

See Solution in Thread

2 Replies 2
DisraeliGears01
7 - App Architect
7 - App Architect

This is a lot easier now that they made new settings available in the Rollup field... Instead of lookup from participant to project, use a rollup, only including linked records where status is active, with a Countall(values) function. That'll output the number of active records only, and then you can map that to your participant table where 2+=collision.

Thanks so much for your help, I ended up using a count rollup field with only active criteria applied. This worked great!