As @Jeremy_Oglesby said, this currently isn’t possible in Airtable. (And not without reason, either: In the sample base provided, it would be impossible to ascertain training limitations for an Advanced Mazda training package, as it is defined in two separate records.) I tried a couple of other approaches to mapping {OEM}+{Package} to a specific {Will Not Train On}, and after outsmarting myself a couple of times, came up with only two working alternatives:
- Modify
[OEM Packages] so that each record has at most one OEM and one package level.
- Replace the
{Will Not Train On} lookup field with a multi-branching IF() statement matching on both {OEM} and {Package}.
In the latter case, to simplify development and upkeep, I also added a formula field to [OEM Packages] that allows Airtable automatically to generate the code for the IF() statement — a trick I find myself increasingly often relying upon.
Of the two, the second approach is probably nearest your original intent. (Note that it, too, falls victim to the double-entry of ‘Mazda Advance’ training: If a combination can be found multiple times, the IF() statement will select the first-encountered instance of the pair.) That said, I’d recommend revisiting this section to see if some other construction might be possible. In particular, I’m uncomfortable with the selection of appropriate packages: After selecting the OEM, the user is then asked to choose the package level. Unfortunately, at this point the applicable OEMs are visible only as a value within the [OEM Packages] record. At the least, I’d recommend adding a field that tests the match of {OEM} to {Package} to ensure a valid match.
If you chose to pursue either of these options, here are links to my versions of both. The first takes advantage of a modified [OEM Packages] table with one record per OEM-package pair. Please note only the first 20-odd rows of [Trainings] are accurate. The method I used to expand the [OEM Packages] table codified incorrect links, and I went through and corrected only the first screen’s worth. For entries following ‘Blue Grass Motorsport,’ you will need to delete the existing link to [OEM Packages] and add a link to the correct record in that table. (The [OEM Packages (hold)] table is your original, retained for reference.
The second makes use of that ugly IF()-driven formula field in [Trainings], which I’ve called {Will Not Train On v2}. All records in it should be correct — with the possible exception of any clients booking an advanced Mazda package. In order to generate the formula code automatically, I’ve added two fields and a record to [OEM Packages]:
-
{Number} is an autonumber field, used in calculating the necessary number of closing parentheses needed to end the formula.
-
{Code Snippet} is a formula field that generates the portion of the IF() statement dealing with that particular record.
- The final record of
[OEM Packages] is used to signal the end of the IF() statement; its only defined field value is for {Name}, which is set to 'END'.
To update the formula for {Trainings::Will Not Train On v2} — that is, the {Will Not Train On v2} field in the [Trainings] table — do the following:
- Select the
[OEM Packages] table.
- Click on the
{Code Snippet} header, selecting the entire column.
- Press
Ctrl-C to copy all the values in the column.
- Select the
[Trainings] table.
- Right-click on the header for
{Will Not Train On v2}.
- Select
'Customize field type'.
- Click within the formula configuration window.
- Press
Ctrl-A to select all of the current formula configuration.
- Press
Ctrl-V to paste the new configuration, replacing the old.
- Save the new configuration.
(Rest assured the process is far more straightforward than it appears when broken down into steps!)
Note: For the formula code to be generated correctly, two things are assumed:
- The bottom-most row in the table has as the value of
{Name] the text 'END'.
- The value of
{Number} in the bottom-most row is equal to the number of the row. That is, if there are 18 records in [OEM Packages], the value of {Name} in row 18 is 'END', and the value of {Number} in row 18 is 18.
Accordingly, if you add records to [OEM Packages], make certain the bottom-most row is 'END'. If you delete row, you will need to re-generate autonumbers; this can be done by first changing {Number} to a number or single-line text field and then changing it back to an autonumber field.
