Sounds like a great use case for Airtable. It cannot be 100% automated, but it can be pretty close.
It sounds like you already have
- a [Members] table
- a [Reaffiliations] table
- links between the [Members] and [Reaffiliations] table
- a form to populate the [Reaffiliations] table.
Propagating Reaffiliation Date to the [Members] table
In the [Reaffiliations] table, create a field called [Date Reaffiliated] that shows the date the record was created (either through the field type or a formula).
In the [Members] table, create the field {Latest Reaffiliation Date} as a rollup field that references the linked record and the [Date Reaffiliated] field. Use the MAX aggregation formula.
The {Latest Reaffiliaton Date} will either show the date of the most recent reaffiliation, or be blank.
If you need to display a check box in addition to the date, you can use a formula field with an IF formula. As the years progress, you can adapt the formula to calculate if the {Latest Reaffiliation Date} was recent enough or not.
Updating member information with the Scripting block
If you have the Scripting block, you can write a script to copy all the new member data from the [Reaffiliation] table to the [Members] table whenever you press a buttton.
(If you have the Scripting block, you can make the check box as part of this process, so that the the {Latest Reaffiliation Date} would be a date field instead of a rollup field.)
Updating member information with bulk copy/paste (free)
If you do not have the Scripting block, and are willing to delete out any old records from the [Reaffiliations] table, you can use lookups and formulas to make it easier to see whose data has changed.
For every input field in the [Reaffiliations] table, create a lookup field in the [Members] table.
Arrange both sets of of fields (original and lookup) in the [Members] table so that all the original fields are together and all the lookup fields are in order. Make sure that the order of both sets of fields is the same.
Multi-select all the data in the lookup fields, then paste it to the original fields in one fell swoop.
Checking who has updates (before you update them)
For every pair of fields (the field with the original data and the new lookup field) create a formula field with an IF formula that compares the two and says if they are different, and is blank if they are not. (Or create a single formula field that makes all the comparisons and reports if anything is different.)
Hiding the Member List
If you have a large membership or are concerned about privacy, you also might want to reconsider having students manually select their own ID from a linked record. The web form would be open to the public and anyone who sees the form could (1) see the entire membership list, and (2) easily submit the form for other people. College students are
You would need to manually make the links from the [Affiliations] table to the [Members] table, but it could be done in in a single copy/paste. You just need something to uniquely match what each student enters with info you already have.
- something that rarely changes, such as cell phone number
- exact name (if they are all unique and people type their names consistently)
- a private code (which can be automatically generated by Airtable) for each student. This would require sending a slightly different email to each student with that student’s code.
