[Sorry for the delay: First, I was distracted. Then I discovered Airtable doesn’t work the way I thought it did (which is, of course, the way it should work. :winking_face: ) Then I grudgingly began to write up this approach and realized a different work flow made more sense. Then I forgot I hadn’t finished this and answered some other questions. And then I had no more excuses.]
So, despite how much fun it was to create that earlier monstrosity, here’s what I think you should really do.
Caveat: When I started this Thursday night, there was no use case posted — so I made one up for you. It just occurred to me you may have since posted more about your application or your process. If so, I apologize, as I am going to pretend you haven’t, as it makes things easier for me…
My Made-up Version of Your Workflow
A customer calls and wishes to lease a piece of equipment, a widget, for a specific period of time. For argument’s sake, let’s stipulate this is a brand-new widget and as such has never been leased before. Your employee makes a note of the rental period’s start and end dates, calculates the rental price accordingly, and books the reservation.
However, word is out you carry the absolute latest-model widget in existence, and almost immediately another customer calls, looking to lease it. Your employee checks the requested beginning and end dates for potential overlap with the existing booking. If no overlap exists, he or she books the second reservation; if the two requested time spans do overlap, the employee works with the customer to identify an alternative schedule that meets his or her needs.
Your Faux Workflow Mapped Against Recommended Base
Just to confuse the heck out of people who accessed the base in the interval between my first reply and this one, my recommended approach can be found — surprise! —in the same base as the seriously ugly approach. Since my initial reply, the base has grown two additional tables, [Equipment] and [Rentals]. In [Equipment], you will find two date fields. {Req[uested] Start Date} and {Req End Date}; you will also find the {Alert} field, which displays an error message when the requested date span overlaps an existing reservation.
To return to the scenario from my made-up version of your workflow, when Customer 1 calls to schedule widget rental, using this base the employee would enter the requested start and end dates in the applicable field. As no error message appears in {Alert} — unsurprisingly, as no earlier rental exists to conflict with the newly requested one — the employee proceeds to generate a new rental for Customer 1. To do so, he or she
- selects THE
{Req[uested] Span} field
- presses
Ctrl-C to copy its value
- selects the
plus sign ('+') in the {Link to Rentals} field, drilling through to the [Rentals] table to display a list of existing rentals
- selects
'+ Add new record', creating a new [Rentals] record and opening it for data entry
- selects the
{Rental Span} field
- presses
Ctrl-V to paste the value into {Rentals::Rental Span}
Once {Rental Span} is filled, {Rentals::Start Date} and {Rentals::End Date} are populated, as well. The employee then proceeds to enter pertinent data (customer name, address, phone, payment info, name of first-born son, and so forth) in fields I didn’t bother to create.
When Customer 2 calls, your employee once again enters the requested start and end dates. (After the [Rentals] record has been created, {Equipment::Req Start Date} and {Equipment::Req End Date} can be cleared. However, I’ve tried to structure {Alert}'s logic in such a way they don’t have to be: The employee can overwrite the previous requested dates with the new ones.) However, should either requested date fall within the span of an existing rental, a message appears in {Alert} notifying the user and offering some guidance as to how the requested rental should be shifted. (For example, if {Req Start Date} falls within another rental, {Alert} will read 🔥🔥🔥 Bad start date - Widget unavailable until 05-16-18.) If there is more than one existing rental, the requested dates are checked against each.
Once the employee and customer have negotiated a non-conflicting rental span, the employee creates and populates a [Rentals] record as before.
Caveats: The system as published assumes equipment will be returned at COB {End Date} and issued at opening of business {Start Date}; no provision is made for either overlapping {End Date}/{Start Date} or for between-rental maintenance. No explicit provision is made for equipment returned prior to {End Date} (although retroactively changing {End Date} should prevent later rental requests from being blocked inappropriately). To prevent invalid alarming if requested dates are entered into already-populated {Req xxx Date} fields, the system ignores instances where {End Date} falls before {Start Date} (although I do throw an alarm if an attempt is made to configure a newly created record as such).
What I Wish It Could Do
Originally, my plans were to have all [Rentals] records to be generated by a three-keystroke copy/select/paste sequence. (This also required [Rentals]’ primary field to be {Rental Span}.) Unfortunately, while such a sequence works fine for the first linked record created, subsequent actions overwrite the existing field rather than create a new one. Since all but the first record creation would have to be performed manually, I chose to require them all to be performed manually, gaining a little more informative linked record value in the tradeoff. Record creation would be even more streamlined if Airtable didn’t absurdly always make the primary field of a newly opened record to be the active field — even, as in this case, if the primary field does not allow data entry. If it instead made the first editable field active, things would flow more smoothly.
