Skip to main content

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.

It looks like this works:


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

It looks like this works:


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

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


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


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?


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?


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.


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.


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.


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


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.


Reply