Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 09, 2020 01:28 PM
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.
Solved! Go to Solution.
Oct 09, 2020 06:59 PM
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.
Oct 09, 2020 01:51 PM
It looks like this works:
IF(
AND(
Phone,
{Phone Extension}
),
IF(
Phone,
Phone,
{Phone Extension}
),
IF(
Phone,
Phone,
{Phone Extension}
)
)
Oct 09, 2020 02:03 PM
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://
Oct 09, 2020 02:18 PM
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?
Oct 09, 2020 06:59 PM
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.
Oct 10, 2020 09:35 AM
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.
Oct 10, 2020 10:04 AM
@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.
Oct 10, 2020 03:16 PM
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.