IF Statements with OR

[javieroneil]

If there a way to write a formula that queries, basically, IF value X is in column C OR column D, then Y.

I just want to be able to filter a base to show only the records where a Name appears in either one of two columns, if that makes sense.

I wish the filter options weren’t by default AND, and could show all records that meet one of two filter conditions.

Am I overlooking an easy way to do this?

Javier

[W_Vann_Hall · Accepted solution]

Javier_O_Neil:

Am I overlooking an easy way to do this?

Yes! :winking_face:

If you haven’t discovered this already, the construction you want is

IF(OR(ColumnC=X,ColumnD=X),This,That)

Essentially, you wrap all your ‘or’ conditions — and you’re not limited to two — within the call to OR(). That call is evaluated as a piece and returns TRUE is any of its component tests proves true. That call in turn becomes the test used to determine how the call to IF() branches. There are comparable AND() and XOR() functions, as well — and, of course, can be nested recursively to such a depth as to make your Airtable code as unreadable as mine…

[Arlo_Haskell]

I’m struggling with something similar here and can’t get IF(OR()) as outlined above to work for me. But I’m using a series of rollup and formula fields and I wonder if the error lies in there somewhere. Can anyone help?

In my Transactions table, each transaction has two fields that link to the People table – call these “primary” and “guest.” If there’s an updated email address for anyone in the party, I add today’s date into a date field in the Transactions table after updating the address.

In the People table, I use rollups to get the latest date an email has been changed – “primary email change” and “guest email change.” These output as a date.

Then I use IS_AFTER() in two formula fields to determine if the person’s email has changed since their most recent transaction date, whether they were a primary or a guest, like this:

IS_AFTER({Primary Email Change}, {Transaction Date})

IS_AFTER({Guest Email Change}, {Transaction Date})

Finally, and here’s the trouble: I want a single field that will tell me if either the primary OR the guest email are later than the transaction date. I try the following, but all I get is #ERROR!

IF(OR({Primary Email Changed}=1, {Guest Email Changed}=1), "New Email", "No Change")

[Jeremy_Oglesby]

Are the IS_AFTER() fields calculating properly, and outputting either 1 or 0?

If so, there doesn’t appear to be any reason that your IF(OR()) statement shouldn’t work.

[Arlo_Haskell]

Not exactly – they are outputting either 1 or #ERROR!

Why might I get the error there? Both fields used in the IS_AFTER() calculation are formatted as dates. One is a rollup and the other is a formula that calculates properly.

Thank you

[Jeremy_Oglesby]

If the rollup is pulling more than 1 date, the field will not be formatted as a date - instead, it will be formatted as an array.

The “rollup” field is pulling a field that only contains a single date right? And presumably, you want this only pulling the date from a single linked record (not multiple linked records), so that you only get one date to make the comparison with.

That being the case, it might be safer to just use “Lookup” instead of “Rollup”.

[Arlo_Haskell]

Thanks so much for your time @Jeremy_Oglesby. I’m a little ashamed this morning, as I’ve figured out that my problem was rather simple. Since the email change date was only present if an email had actually changed, I was sometimes rolling up blanks to be used in the calculation – i.e. I was using IS_AFTER() to compare a date to an empty value, hence the error.

(Incidentally, this is all part of a kind of elaborate workaround for the lack of a “last modified” function.)

To fix, I’ve made a new field in the Transactions table and am rolling up from there:

IF({Email Change Date}>"", {Email Change Date}, DATETIME_PARSE(01/01/1900))

Now I can successfully use IS_AFTER() to tell me whether the email has been recently updated for either the primary or the guest. But I also find an interesting quirk – Airtable outputs my date as 1/1/1970. Any date before 1970 that I put through ‘DATETIME_PARSE()’ returns 1/1/1970. (This is partly discussed here) This is hardly a dealbreaker, but is it possible to get this to return the year 1900? I use that year elsewhere in the base to denote “historic” data from our pre-Airtable system, so this would just help us maintain some internal consistency.

Thank you

[Jeremy_Oglesby]

Arlo_Haskell:

is it possible to get this to return the year 1900?

No, that won’t be possible - Airtable (and I’m pretty sure all other database systems) use Unix timestamps behind the scenes when dealing with DateTime data. Unix timestamps originate at 1/1/1970 (the beginning of the Unix Epoch), and there literally are no timestamps prior to that date. You can manually enter DateTimes of arbitrary points in history, and even operate on them, but apparently the DATETIME_PARSE() function is unable to operate on/output anything but Unix timestamps.

Here’s some info about that:
https://www.unixtimestamp.com

[MIX_CPH]

Can you help me? I have two columns, one with price in EUR and one with price in USD and I want a formula that automatically calculated that price in DKK (Danish crowns). So if there is a value under the EUR column OR the USD column, the DKK price is automatically caculated.

I have create the following formula:

IF(OR({USD price}>0,{EUR price}>0),{USD price}*6.52,{EUR price}*7.45)

However this only works if there is a value in USD price and not EUR price. So if there is 0 in USD price but 300 in EUR the DKK value is 0.

Hope it makes sense :confused: