So this system will replace an Access/SQL Server system which has long passed it’s sell-by date :grimacing:
Applicants complete a form (Jotform) with just over 100 fields (many conditional), which populates a table in Airtable through Jotform/Airtable integration.
Admin then conduct an initial review, referring to some of the application data. For each criteria Admin record: pass, partial, or fail, (new Single Select field) and also any comments relating to that specific criteria in a comments field (commenting is not used as a) it must be preserved, and b) it relates to a field not the entire application). This means that most fields become 3 in order to record the admin review.
If the application passes the intial review stage, Admin send automated emails to application contacts (from the form data).
Views are used to create a process to move applications through the different stages of initial review.
Responses from the application contacts determine whether the application is rejected at this stage, or proceeds for review.
Applications for review are assigned to a Reviewer, who then reviews the application, and makes a number of recommendations. This works effectively the same as for Admin, in that many Single Select and Comments (Long Text) fields are added to record the Reviewer’s findings. The Application is then passed back to Admin for final processing (rejected, accepted, etc).
What starts as around 115 fields on the application form, becomes 197 with Admin fields added, then around 280 including the Reviewer fields. I’m aware that the max is 500 fields and that this applies whether the fields are synced, lookup, or ‘original’ - ie every visible field counts. (Presumably not fields that are not visible? Even if synced?)
The structure I have (so far) is a Base for Applications, with a table that includes the Admin review fields. I then have another Base (Reviews) which syncs with the Applications table ie Applications is the source. The Reviews table therefore pulls in the data from the original application and the Admin review fields (which the Reviewer should see).
The Reviewer should not be able to edit any of the Application data (though the Admin may need to). Reviewers should be able to see (and comment on) all applications in process, but should (ideally) only be able to edit the application assigned to them. (By edit - I mean add data to the new fields in the Review table.)
This is my first foray with Airtable. My background (20 years ago ) was web database developer, but I’m aware that my thinking may be taking me in the wrong direction. So I’d really appreciate some thoughts on how I might best structure the system, or any issues that may arise because of how I’ve structured it.
The other thing is that at least one other table will be needed - this will be for accepted applications. They will include a small subset of the original application data, and also information from the Reviewer (eg whether a discount is permitted and what percentage that should be). (Invoicing etc will be generated from this.)
So, to summarise:
Any advice on this would be hugely appreciated! :upside_down_face:
This made me laugh so I decided to read your lengthy message. :winking_face: I think it’s past its “consume-by” date as well. I think that was back on my 46th birthday (20 years ago).
Briefly, here are some observations:
My advice - take the requirements and fully expand them to identify all of the nuanced requirements to ensure that Airtable is capable of meeting all the needs going forward.
I’m glad I made someone laugh :grinning_face_with_sweat: Definitely past it’s Use by date! :rofl:
Thanks for the pointers @Bill.French - I’ve split into separate tables now (one of which is in a separate base to manage the requirements for restricted access of reviewers).
I have spent a lot of time thinking about how the data needs to be used, but as I’m only working on this myself I feel like I need to get the MVP finished so that it can then be built on (or refactored) to better support future needs. At that point we may need to engage an Airtable expert.
The bottom line is that even now, this is waaaay better than the current system, so if nothing else this is a useful exercise to review how the application review process works (and uncover where it’s currently being propped up with endless manual tasks).
Thanks again for the reply! :upside_down_face: :upside_down_face: And for reading the lengthy post! :slightly_smiling_face: