Goal
Our organization is hoping to move our annual application off of a very expensive custom third-party contractor's system (which we have to messily export CSVs from to upload to AirTable, then engage in substantial data cleaning due to issues in how the contractor's system structures data). The goal is to move the application to our org's AirTable Workspace directly. We're exploring ways to handle this smoothly, so I'll talk through the overall workflow/how things work now first and foremost, then talk about some of our initial thoughts about how to achieve this with AT, and then finally I'll talk about the things I'm not sure how to do (or if they can be done at all).
Upfront, I am hopeful all this is achievable using AirTable Forms, but some things I've read elsewhere suggest that Interfaces might be a more appropriate tool. I'll use the term Forms from here on out, but answers with instructions for achieving our goals with an Interface or other built-in AT functionality are totally okay! We're mostly just trying to avoid paying for yet another service, especially a subscription service, to handle this for us.
Overall Workflow
Current Process
We run a Fellowship program for public educators and publicly openly applications from across the state for several months each year. Applicants aim to be placed in specific Fellowship projects, which are often limited by funder preferences; e.g., a company might only want to recruit educators from certain nearby counties, or focus on teachers in certain grade levels. Although an applicant doesn't have to indicate projects they're interested in, we do like them to have the option. We collect a great deal of information about these educators in order to make our selection process as unbiased and reliable as possible, but an issue we've had to deal with is that the sheer size of the current application scares a lot of people off from finishing it. Even though they can save and continue at a later date, many never return. Even those who complete it often have trouble getting their recommenders to submit letters on their behalf through the system.
So, by and large, our process currently looks like:
- Applicant arrives at the application system via a link and registers for it
- Across one or more sessions, the Applicant completes their core demographic data (name/email/phone/subject area/grade level/county/employer history/essay/recommenders/etc.)
- After inputting employment information (county, grade level, subject), the current system displays a filtered lists of available Fellowships that meet those criteria which candidates can then pick from to highlight as projects they are especially interested in
- After the applicant finishes their entire submission, the system automatically requests (via email) recommendation letters from two people the applicant puts in contact information for during their application
- When recommenders complete their letters using the form they receive a unique link to, their letters are automatically attached to the correct applicant's record
- Our staff can then download a completed application packet for each applicant including their main application as well as any/all completed recommendations
- The system tracks applicants that are in progress (only registered/some data finished), submitted (applicant has filled out everything and finalized it), and complete (all requested recommendations are in), and we can do things like send out reminders to those that aren't finished.
Proposed AirTable Process
Our goal is to replicate as much of that as possible while streamlining and freeing ourselves of the custom system. But, obviously, some of those existing functionalities don't play well with AirTable. We won't be having folks "register" for the application system first and then have the option to stop and continue later, since Forms don't seem to support that at all. But, on the other hand, our applications require enough pretty specific information that we want to find ways to set things up such that folks don't have to complete everything at once.
A solution we arrived at was having folks complete the core demographic/identifying information using a relatively short AT Form, then having AT automatically email them a link to a form-fillable PDF or similar that lets them fill in the really detailed stuff -- employer history, education history, awards received, essay, etc. -- on their own time. We want them to then be able to submit that PDF, ideally via an AT Form, and have it automatically connect to their existing application record so long as they fill things out appropriately (e.g., spell their own name right, which is more of an issue than you'd think. . . ).
We would love it if something similar could be done through an automation to reach out to recommender email addresses entered by an applicant initially to have them complete a separate form (or fill out a PDF + upload it to AT via Form, same difference) in a way that also connects automatically to the existing application record.
So, long story short, we'd like to be able to have people submit a basic application to our program with an AT Form, then have time to fill out more detailed information at their own pace and upload it to the existing application record they created within AT in a way that doesn't give them access to other applicants' information. If possible, we'd love for their recommenders to be able to do the same, but we're also okay with applicants collecting the letters directly from their recommenders and uploading those as PDFs/doc files when they submit their own "full application" upload.
Issues We're Running Into
Right now, I don't see obvious ways to, for instance, have a dropdown list of options from a multi-select field/lookup in a Form be limited based on a choice made earlier in that Form. So, e.g., if we have a Fellowships Table in the Base of all Fellowship opportunities with multi-select lists for applicable grade levels and counties, we'd love for an applicant filling out a Form feeding into the Applications Table be able to enter (or pick) their county and grade level, and then when time comes to pick the project(s) they're interested in, have the Form only display those records which meet the grade/county criteria they entered.
Additionally, if we do wind up "splitting" the application (relatively quick demo info Form + later-submitted PDF of detailed professional history), is there a way to have a second Form -- either feeding directly into the Applications Table, or possibly a second Table using Lookups to get info from submitted Applications -- "check" the name/email/phone (some unique identifier) a candidate enters and connect their new submission to a previous Form Submission (e.g., attach the uploaded PDF to their previously submitted demographic info) without revealing all other candidate records to them (that is, we wouldn't want this to be a dropdown list of all applicants where they pick themself -- the privacy issues there are substantial!).
Finally, if we use an automation to send an email to recommenders based off, say, contact info submitted in the initial demographic info portion of the application, is there a similarly privacy-respecting way they can submit a form response and/or PDF upload that attaches to the correct candidate's record (if they correctly enter some unique ID like email or phone #; all the better if that info is already prefilled for them based on the automation email they received somehow?) without revealing all existing applicants to them?
TL;DR
- We want Form fields to automatically update "live" (mid-fill) based on prior choices and lookups (e.g., if I say I am from Sullivan County, have a subsequent dropdown picker field only let form-fillers choose options that match to Sullivan County)
- We want form-fillers to be able to return and submit additional info, likely via document uploads, that appropriately attaches to the existing record of their first submission, ideally in a way where they can't see a full list of applicant records to pick themselves from, but rather, where they enter identifying information and AT automatically attaches the submission to the matching record after-the-fact
- If #2 is possible, we would love for an automation-based email inviting recommenders to submit on a candidate's behalf drive them to a partially pre-filled form that ensures their submission's contents are attached to the matching candidate's record automatically without the recommender needing to pick from a privacy-poor list of existing records
We're happy to do any of this using Forms, Interfaces, Views, etc., and are glad to wrangle some complex formulas/Base constructions if that's necessary. We'd ideally like to avoid having to add on an additional paid extension/integration, but if that is the only way to achieve these things, we'd strongly prefer a "pay once" option versus an ongoing subscription.
