One possible approach would be to use two fields: a {DateEntry} single-select field from which the user chooses an appointment date and a {PermDate} date field that holds the requested date permanently. {PermDate} is populated outside the normal Airtable process flow, either manually or by such third-party middleware as Zapier, Integromat, or IFTTT.
Actually, I’d recommend doing so manually, as there is no compelling reason for records to be updated individually, in real time. One can batch-update an unlimited number of records manually with three mouse clicks and two keystrokes; the only limitation is that it must be done prior to the single-select being reconfigured to eliminate past dates.
To update a batch of records manually:
- Select the
{DateEntry} field in the top-most row with no {PermDate} defined.
- Scroll to the bottom of the table and, while holding down the
Shift key, select {PermDate} in the table’s bottom most row. ## cells selected appears in the lower lefthand corner of the base.
- Press
Ctrl-C to copy the selected cells; `## cells copied’ pops up in the lower lefthand corner.
- Select
{PermDate} in the topmost row for which {PermDate} is empty.
- Press
Ctrl-V to paste the {DateEntry} values into {PermDate}.
This would only need to be done immediately prior to reconfiguring the single-select field. To simplify performing any other calculations or displays that draw upon the requested appointment date, you could create a third field, {ApptDate}, a formula field containing this formula:
IF({PermDate},{PermDate},DATETIME_PARSE({DateEntry,'M/D/YYYY'))
This will allow any calculations using the date to continue uncorrupted even after the single-select has been changed.
A note on sources: I largely plagiarized myself from this reply, which uses many of the same techniques, albeit for a different purpose. You can find example code — and perhaps a more-eloquent description — in that reply and the base it references.
