Create formula with multiple linked columns


#1

Hi! I’ve been a casual user of AirTable for a few years now, but at my current new job I’ve decided to spread the gospel so I’m building my first bases from scratch. Turns out not as easy as I thought!

I have two tables, one tracking external companies (let’s say table 1) and one tracking internal programs (table 2). Both have columns identifying “population” and “condition”. These multi-select columns contain the exact same dropdown options. I would like to create a column where, If condition AND population from table 1 = condition AND population from table 2, table 1 would get checkbox checked, and table 2 would get the relevant company linked. Right now I’m manually linking the company name when this occurs which seems really unnecessary.

Similarly, I have a third table tracking tool requests (let’s say table 3). If tool in table 1 = tool in table 3, I’d like to link the company name from table 1 into table 3. Again, I’m doing this manually now. I’ve gotten kinda close with this one, with this formula: IF({Tool type}=({Partners seeking:}, “:white_check_mark:”, BLANK())… but I’m getting the checkmark for matching blank boxes (where I just haven’t input the information yet).

Any help would be appreciated! I’m sorry if this is a simple question and I’m just noob. Feel free to link to other convos on the same topic if I’ve missed them!


#2

When you say you want to “link the company name from table 1 into table 3”, do you just mean to perform a lookup to pull the contents of a field over from one table to another? Or do you mean you want a linked record?

To solve the problem of “getting the checkmark for matching blank boxes”, you can wrap your IF() statement in another IF() like so:

IF( 
    OR( {Tool type} = BLANK(), 
        {Partners seeking:} = BLANK() 
       ), 
    BLANK(), 
    IF( {Tool type} = {Partners seeking:}, 
         "✅", BLANK()
      )
  )

This will check first to see if either {Tool type} or {Partners seeking:} is blank, and if either one of them is blank, it returns BLANK() - otherwise, it processes your original IF() statement.

For your other issue of automatically “linking” records (if that’s what you mean), it’s not something that can be done automatically with the built in functionality of just Airtable. But there are services, like Zapier that would be capable of doing this for you.

If you can clarify a little more what you mean, we may be able to work out another solution.


#3

Dear Jeremy,

Thanks sharing your knowledge, it’s a very clear explanation.

The structure of the formula in this lay-out is very readable and easier to understand. Is there a tool to create this structure? If yes, please could you advise the name of it?

Thank you very much,
Jean Pierre


#4

@Jean_Pierre_Traets1, I’m not aware of any tool for writing formulas like this in Airtable. Airtable will definitely accept formulas in this format (ie, you could copy paste the formula I wrote up there, as I wrote it, into a formula field and it would still be formatted like that, and it would still work).

The formula field editor is not very friendly for typing structured formulas like that though. If I know I’m going to need to write a long, complicated formula, I will usually open up a text-editor built for coding to write the formula in a structured format like you see above, and then copy-paste it into Airtable.

There are a couple good, free coding text-editors out there, including Atom and VS Code. Both of those editors will allow you to write formulas with auto-tabbing of nested lines.


#5

Jean Pierre -

I’m a big fan of Notepad++ — see this post.


#6

Hi Jeremy! Thanks so much for your reply! It looks like you solved one of my problems, but naturally I’ve encountered a new roadblock. {Partners seeking} and {Tool type} are both multi select. When I created a lookup column to produce {Partners seeking} in Table 1, it seems the formula will only match the whole string. For example, if “Chatbot” is a tool in the multiselect field of {Tool type} and the string produced from the lookup of {Partners seeking}, I’d like to see the checkmark. Regarding your question, if this match exists, where Partners seeking = tool type, I’d like a linked record auto-populated in Table 3.

BUT, as mentioned, this isn’t my primary concern. I’ll elaborate on my first question!!
I am indeed talking about automatically linking records, via a VLOOKUP type situation. If (population 1 AND (one of) condition 1) = (population 2 AND (one of) condition 2), table 1 would get a checkmark in the row of that company profile (indicating it’s a viable candidate for a partnership), and table 3 would have a linked record created indicating the company name. It is possible (and likely) that multiple company names would appear linked to a program in table 2. Is that at all more explicit? If not, let me know and I’ll happily elaborate!!

Thanks again for your help!


#7

I’m 99% sure that what you are wanting is not possible without some outside automation tool like Zapier, but @W_Vann_Hall has come up with some pretty ingenious solutions within Airtable for other problems that I would not have thought possible.

Perhaps @W_Vann_Hall will chime in here :grin:


#8

Gee, thanks, @Jeremy_Oglesby — no pressure here… :wink:

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.


  1. 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
    )

Lookup based on a text (i.e. non-linked) field
#9

@W_Vann_Hall = :mage:
:clap:


#10

Dear Jeremy, Thanks for your advice. I will try them and see what will work for me the best. :clap:


#11

Wow @W_Vann_Hall thank you so much for taking the time to write this all out. I’ve never used Zapier, but the ability to automate sounds appealing.

I didn’t have time to play around implementing this today but I’ll follow up if I have any questions! Regarding the first “easy” question, thanks for taking the extra step if both fields are multi-select – they are!

I’ll provide some feedback after I work through this. I’m excited to try this out and show my boss :sunglasses: