Rolling up information for linked records in linked record

[Andres_Moreno]

I have 3 tables

• Job - Table that has open job requisitions
• Applicant - Table that has records for applicants, linked to job (many to many, as an applicant can apply to many jobs)
• Applicant_evaluation - Table that has records for each evaluation made for an applicant, as interviewers people can evaluate the applicant for different jobs

Links:

• Job <–> Applicant
• Applicant <–> Applicant evaluation

In my Jobs table I can count the # of people that have applied to the job with a simple roll up. In my applicant_evaluation table I have a “Offered” column (single select yes/no). In my Jobs table I want to count the number of applicants who I’ve “offered”.
Is this possible?

[AlliAlosa]

Hi there!

This is possible if your [Applicant Evaluation] table is linked to the [Jobs] table. If it’s not, how does Airtable know which job you’re offering the applicant?

If it is linked, then you can use a conditional rollup to accomplish this. Add a formula field to the [Applicant Evaluation] table, and use the formula:

IF({Offered} = "Yes", 1)

Next, add a rollup field to the [Jobs] table and use the formula function:

SUM(values)

And that should do it!

If you didn’t have the link between [Applicant Evaluations] and [Jobs], you A) would need to add the above rollup field to the [Applicants] table, B) carry that over to the [Jobs] table with another rollup, and C) could and likely would get an incorrect count on the [Jobs] table. Example…

Say “Applicant A” applies to “Job 1” and “Job 2”. You evaluate them and offer them a job; changing {Offered} to “Yes”. This would cause both “Job 1” and “Job 2” to display a count of 1, where in reality you probably offered them specifically “Job 1” or “Job 2”.

I hope this helps! :slightly_smiling_face:

[Andres_Moreno]

Yeah, that was easier than I thought! I was missing in my application evaluation to what job the person was actually applying to.

Thanks!