Gee, thanks, @Jeremy_Oglesby — no pressure here… :winking_face:
To answer the easy one first, instead of
IF({Tool type} = {Partners seeking},...
try
IF(FIND({Tool type},{Partners seeking}), ...
That will match any time the string in {Tool type} matches any of the options in {Partners seeking}. If both {Tool type} and {Partners seeking} are multi-selects, your best bet is probably to code an explicit test for each option in {Tool type} that tests both fields, à la
IF(
AND(
FIND([explicit string],{Tool type}),
FIND([explicit string],{Partners seeking)
),
...
(In this case, ’[explicit string] isn’t “$*&^%()$#!”, like something Sarge would say after dropping a heavy object on his foot in an old Sad Sack comic book, but simply one of the actual text strings that make up the options in {Tool type} — 'chatbot', for instance.)
As for the not-so-easy one, as Jeremy says, such automatic record creation isn’t possible without assistance from such a third-party middleware tool as Zapier — with one exception: If you can live with having to perform a single, two three-keystroke action, you can automate link creation.
The process is described in this article. In brief, if you copy-and-paste a value into a link record field, Airtable will either link to the record whose primary field matches the pasted value or will create a new record with {Primary Field} set to ‘value’ and link to it. You can then use lookup/rollup fields to populate the newly created record. (At times, I’ve created a ‘portmanteau’[1] field in the originating record, consisting of values drawn from still other records through other links to the originating record, and passed that value to the new record to use in auto-populating.)
Typically, if I’m going to rely on this trick, I’ll place the linked record field immediately to the right of the field where I get the value. This allows me to use the three-keystroke sequence Ctrl-C/right-arrow/Ctrl-V to copy the value, step to the next cell, and paste. I’ll also define an {Alert} field with a formula that looks for instances where the value field isn’t empty but the linked record. (In your case, you’d look for records where the ‘viable partnership’ box was checked but the linked record field was empty.) When such a case exists, I set {Alert} to equal something like '🔥🔥🔥 No Linked Record! 🔥🔥🔥'. (Alternatively, for Pro accounts, I’ll set the record color to flag the missing link.)
You can create more than one record by copy-and-pasting multiple values separated by a comma. I’ve created 468 at a time, which takes Airtable a couple of minutes to choke down.
If you decide to automate through Zapier, my recommendation would be simply to automate the process I just described. Doing so allows you to use a two-step Zap, which can be performed from a free account; if you implement the same functionality with an explicit call to Zapier’s record creation routine, it requires a three-step Zap and, accordingly, a paid account.
-
The term ‘portmanteau’ I borrowed from Humpty Dumpty, by way of Lewis Carroll, to mean a field whose value is made up of values from other fields. Typically I use a key:value pair with the key being a vertical bar character followed by a two-digit index number followed by a colon, like so: |01:. I also append a vertical bar to the end of the entire field:
|01:24541|02:3500.00|03:TRUE|04:FALSE|05:FALSE|06:Long text field|
This way, to extract the value, I can use
MID(
{Portmanteau},
FIND({key},{Portmanteau})+4,
(LEN({Portmanteau})-
FIND('|',{Portmanteau},FIND({key},{Portmanteau}+1))+3
)
