Using formula tool to check if one of two columns have a value

Hi,

I want to do a comparison between two columns.

Ie make a formula to check if ONLY one of the two columns have a value.

Does this make sense? Im not sure if it fully covers the logic because i only want to identify if 1 of them have a value and 1 doesnt as "ERROR" . I do not want to flag if both are Blank then error. Hopefully that makes sense.

My formula:

If [Date] = '' OR [Status] = '' then "ERROR" else "" Endif

Custom Tools

Accepted answers

Felipe_Ribeir0

Hi @wonka1234

You can check if your formula works applying it into a dummy dataset that cover all 4 possibilities

I attached a workflow with your formula (does not work) and the proposed formula for you to check out:

If (IsNull([Date]) AND !IsNull([Status]))
OR (!IsNull([Date]) AND IsNull([Status]))
then "ERROR"
else Null()
Endif

Using formula tool to check if one of two columns have a value.yxmd

All comments

FreeRangeDingo

Instead of using [Date] = '', consider using the IsEmpty or IsNull functions.

IsEmpty([Date]) - means is empty

!IsEmpty([Date]) - means is not empty

I believe Empty will encompass Null but Null does not include Empty. They are slightly different functions, and I always use IsEmpty.

wonka1234

thanks but not sure if that checks the logic, ERROR if ONE of the TWO columns are Empty.

Felipe_Ribeir0

Hi @wonka1234

You can check if your formula works applying it into a dummy dataset that cover all 4 possibilities

I attached a workflow with your formula (does not work) and the proposed formula for you to check out:

If (IsNull([Date]) AND !IsNull([Status]))
OR (!IsNull([Date]) AND IsNull([Status]))
then "ERROR"
else Null()
Endif

Using formula tool to check if one of two columns have a value.yxmd

FreeRangeDingo

What you want is this....

if (

if IsEmpty([Date]) then 1 else 0 endif + if IsEmpty([Date2]) then 1 else 0 endif

) =1 then "ERROR" else "OK" endif

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703