# Re: Formula to show a field if another is empty

Solved
1385 0
cancel
Showing results for
Did you mean:
4 - Data Explorer

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.

1 Solution

Accepted Solutions
18 - Pluto

Welcome to the community, @gmclelland! :grinning_face_with_big_eyes:

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.

7 Replies 7
4 - Data Explorer

It looks like this works:

IF(
AND(
Phone,
{Phone Extension}
),
IF(
Phone,
Phone,
{Phone Extension}
),
IF(
Phone,
Phone,
{Phone Extension}
)
)
4 - Data Explorer

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://

4 - Data Explorer

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?

18 - Pluto

Welcome to the community, @gmclelland! :grinning_face_with_big_eyes:

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.

4 - Data Explorer

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.

4 - Data Explorer

@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.

18 - Pluto

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.