Formula to show a field if another is empty

[gmclelland]

Hello, I have two fields Phone and Phone Extension.

I need a formula that evaluates the following conditions:

• if Both are present, show Phone if exist, otherwise show Phone Extension if exist

• if only Phone is present, show Phone

• if only Phone Extension is present, show Phone Extension

• if both are blank, show it as blank

I’ve been racking my brains out trying to figure this out and I know it’s probably stupid simple, but I spent far to long working on this.

I appreciate any help anyone can provide.

[gmclelland]

It looks like this works:

IF(
  AND(
    Phone,
    {Phone Extension}
  ),
  IF(
    Phone,
    Phone,
    {Phone Extension}
  ),
  IF(
    Phone,
    Phone,
    {Phone Extension}
  )
)

[gmclelland]

Strangely this shows an error:

IF(
  AND(
    Phone,
    {Phone Extension}
  ),
  IF(
    Phone,
    'tel://' & VALUE(Phone),
    'tel://' & VALUE({Phone Extension})
  ),
  IF(
    Phone,
    'tel://' & VALUE(Phone),
    'tel://' & VALUE({Phone Extension})
  )
)

Not sure what I’m doing wrong?
All I want to do is prefix the raw value with tel://

[gmclelland]

This also works:

IF(
  AND(
    Phone,
    {Phone Extension}
  ),
  IF(Phone, Phone, {Phone Extension}),
  IF(Phone, Phone, {Phone Extension})
)

But this strangely shows an error:

IF(
  AND(
    Phone,
    {Phone Extension}
  ),
  IF(Phone,
  'tel://' & VALUE(Phone),
  'tel://' & VALUE({Phone Extension})),
  IF(Phone,
  'tel://' & VALUE(Phone),
  'tel://' & VALUE({Phone Extension}))
)

Anyone have any ideas?

[Justin_Barrett · Accepted solution]

Welcome to the community, @gmclelland! :grinning_face_with_big_eyes:

gmclelland:

if Both are present, show Phone if exist, otherwise show Phone Extension if exist

This logic is a bit odd. If both exist, then there’s no need to test again to see if {Phone} exists because its presence is already assured by passing the “if both exist” test.

I was able to simplify your formula, and get the “tel://” prefix to work. Here’s what I came up with:

IF(
    OR(Phone, {Phone Extension}),
    "tel://" & VALUE(IF(Phone, Phone, {Phone Extension}))
)

If either {Phone} or {Phone Extension} exist, the formula starts with the “tel://” prefix. To that prefix, it will add the value of either {Phone} or {Phone Extension}, with {Phone} taking priority by being tested first. By the time that inner test arrives, the formula knows that one of the two is there, so it’s a simple process of elimination. If the initial OR() test fails, then nothing else happens.

Notice that the {Phone} field is the phone number field type, and “Phone Extension” is single line text. My gut says that one of those two fields is a number field in your base. If so, that explains the error. The VALUE() function requires a string, and throws an error if you feed it a number.

[gmclelland]

Wow, that was incredibly helpful! Thank you for taking the time to explain the logic.

Also, yes my {Phone Extension} field was a number field. After changing it to the text field my error went away.

[gmclelland]

@Justin_Barrett, One other related question:

If I take your working formula and put it into a button field, even if the field doesn’t contain any values it will still show a greyed out button.

Is there anyway to remove the greyed out buttons?

My ultimate goal was to have a “Call with GoToConnect” button for users to be able to click to call with their phone.

[Justin_Barrett]

I also wish that inactive buttons would just stay hidden instead of appearing lighter, but that’s not currently an option. I’m not sure if there’s a thread in the #show-and-tell:product-suggestions category for hiding inactive buttons, but you might want to search and add your support, or start a new thread if you don’t find anything.