So this system will replace an Access/SQL Server system which has long passed it’s sell-by date
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:
- 300 fields so far (could get to 400 by the time it’s finished!) - it’s not just about reaching the max, but about the difficulty working with that many fields. (Even just ordering them for a View.)
- Permissions are a major consideration (as I’m sure with most systems)
- The ‘Accepted applications table’ - maybe this could sync with the Reviews table? ie pull in the data from that table?
- (For info - this is for one membership type. So there will be the same set up needed for another 3/4 different membership types, which don’t use the same form fields, though there is some repetition.)
Any advice on this would be hugely appreciated!