Help

Re: Blank & Not(Blank) Formulas for Contact Information

Solved
Jump to Solution
2183 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Karim_Badra
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

I have landline and mobile numbers in my data base. If one of them is missing, i want to show the other, if both are missing, i want to keep it blank, and if both are full i want to concantenate them. This is my formula syntax but its not working. Can you please help correct it?

IF(
AND(
{Mobile Phone 1} = BLANK(),
Landline = BLANK()
),
“Both Blank”,

IF(
AND(
{Mobile Phone 1} = BLANK(),
Landline = NOT(BLANK())
),
“Landline”,

IF(
AND(
{Mobile Phone 1} = NOT(BLANK()),
Landline = BLANK()
),
“Mobile”,
“Both”
)
)
)

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

I find it easier to look for filled fields vs empty ones, which means you can avoid using BLANK() completely. Not only that, but this can be greatly simplified by reordering the logic.

Here’s the key: virtually any non-empty field (that’s not a number field) is treated as “truthy”—i.e. equivalent to True—and any empty field is considered “falsy”. You can test for this by only including the field name, like so:

IF({Field Name}, result_if_true, optional_result_if_false)

The same goes for comparing multiple fields using AND() and OR().

With that in mind, I’d start this formula by looking to see if both fields are full, and combining them if they are:

IF(
  AND(
    {Mobile Phone 1}, Landline
  ), {Mobile Phone 1} & " / " & Landline
)

If that AND() returns False, we know that we either have one of the two numbers, or no number at all. In any of those three situations, we can simply concatenate both of those fields, and we’ll either have one number or no number.

With that, the full formula is this:

IF(
  AND(
    {Mobile Phone 1}, Landline
  ), {Mobile Phone 1} & " / " & Landline,
  {Mobile Phone 1} & Landline
)

Screen Shot 2021-09-30 at 8.29.55 AM

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

I find it easier to look for filled fields vs empty ones, which means you can avoid using BLANK() completely. Not only that, but this can be greatly simplified by reordering the logic.

Here’s the key: virtually any non-empty field (that’s not a number field) is treated as “truthy”—i.e. equivalent to True—and any empty field is considered “falsy”. You can test for this by only including the field name, like so:

IF({Field Name}, result_if_true, optional_result_if_false)

The same goes for comparing multiple fields using AND() and OR().

With that in mind, I’d start this formula by looking to see if both fields are full, and combining them if they are:

IF(
  AND(
    {Mobile Phone 1}, Landline
  ), {Mobile Phone 1} & " / " & Landline
)

If that AND() returns False, we know that we either have one of the two numbers, or no number at all. In any of those three situations, we can simply concatenate both of those fields, and we’ll either have one number or no number.

With that, the full formula is this:

IF(
  AND(
    {Mobile Phone 1}, Landline
  ), {Mobile Phone 1} & " / " & Landline,
  {Mobile Phone 1} & Landline
)

Screen Shot 2021-09-30 at 8.29.55 AM

Brilliant Justin! Thank you!!