Structure for multi-stage membership application review with circa 300 fields

[Christina_McGui]

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:

1. 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.)
2. Permissions are a major consideration (as I’m sure with most systems)
3. The ‘Accepted applications table’ - maybe this could sync with the Reviews table? ie pull in the data from that table?
4. (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! :upside_down_face:

[Bill_French]

Christina_McGuire:

an Access/SQL Server system which has long passed it’s sell-by date

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:

1. 400+ fields in a single record is a flag; not so much a red flag, but a concern. Without a deeper understanding of the workflow, data, and processes - it’s difficult to imagine a data model that would encourage this.
2. I think you have a solid understanding of the requirements, but I suspect not enough time has been exercised thinking about how the data needs to be used, and what business drivers are influenced – and this is the key to envisioning a good data model.
3. Given the vast number of fields and the need to replicate the data model with variants is a sure sign that you need automation at the administrative and data model level. And unfortunately, Airtable is [presently] unable to offer fully automated processes that make data architecture precise and controlled. Furthermore, there’s no way to build and replicate business logic (like formulas) without manually crafting every one of them.

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.

[Christina_McGui]

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: