Blank Linked record into conditional formula

[David_Ohanessia]

Hello to the community,

And happy 2022 for everyone who reads this topic :winking_face:

Maybe I’m the only one to face this situation but I’ve created a pretty simple formula field which should provide a simple 1/0 boolean answer based on 3 conditions. One of those conditions is if one linked field to another table (Order_ID) is still blank/empty.

IF(AND({Payment Status}!=“paid”,Order_ID=BLANK(),REGEX_MATCH(Email, “(\W|^)[\w.\-]{0,25}@[A-Za-z0-9.-]+\.[A-Za-z]{2,}(\W|$)”)=1),1,0)

Guess what ? It doesn’t work.

I’ve also tested the NOT(Order_ID) based on what I’ve read and it doesn’t work either BUT it works on a separate formula cell (see below the second line) :

Other 2 conditions work perfectly. I assume this is due to the structure of a linked record by itself but I’m not sure.

Thank you for your help.
Best,
Y

[Williams_Innova · Accepted solution]

Hi @David_Ohanessian,

This is interesting and not sure why it wouldn’t work. My first thought for a work around, as clunky as it may sound, is to have a formula field that creates a text string from the link, so in this case something like…

CONCATENATE(Order_ID)

…would make “rectdhowUFxWXYovM” and then use the conditional field to see if the text string is empty (Order_ID="") or not.

Like I said, not the cleanest approach but I believe it would work.

Thanks,
Chris

Williams Innovations

Information Ecosystem | Williams Innovations

Williams Innovations offers consulting and management services for Airtable, information and communication structures, process mapping, and content development.