Auto update applicant status based on multiple records marked complete (Application waiting on returned character reference responses)

[Kevin_Hickey]

Hi,

I’m new to Airtable and hoping for some help. Not sure how easy/difficult my scenario is to set up or achieve.

I have one table with records of student applications and a linked field to another table with records of character references that the applicant supplied.

What I would like to be able to do is automatically update the status field in the student applicant table based on whether their character references have been marked as returned/complete.

My statuses would be something like:

• 3 References pending
• 2 References pending
• 1 Reference pending
• All references returned

Thanks for any help, and I apologize if I didn’t articulate my issue clearly. Happy to answer questions or to dialogue with anyone able and willing to help!

-Kevin
Detroit, MI

[Alexey_Gusev]

Hello,
If your ‘marked complete’ fields are checkboxes, you can create formula field in second table, smth like SWITCH(ref1+ref2+ref3,3,‘all returned’,2,‘one pending’,1,‘two pending’,0,‘three pending’) and choose it for lookup added to the link in first table.
Otherwise, check formula field reference to get desired result.

For text values, you can put LEN(CONCATENATE(ref1,ref2,ref)) in switch expression.
I would suggest to get numeric result to avoid multiple nested IFs.