Formula help needed

I'm trying to do and if/then statement with two conditions. Can this be done? The first condition is the field length. It has to be 10 digits to be a valid code.

Second, it can't be the same code as the code in the calculated field to the left from this field I'm working on.

First field with no issues

Field Name: First_Code_Found

If Length ([Tax_Code1]) >9 then [Tax_Code1] elseif Length ([Tax_Code2] >9 then [Tax_Code2] (etc., etc.) else '' endif

Second field needs to be the correct length in characters AND also not match the field above - First_Code_Found.

Field Name: Second_Tax_Code_Found

If Length ([Tax_Code1]) >9 and [Tax_Code1] != [First_Code_Found] then [Tax_Code1] elseif Length ([Tax_Code2] >9 and [Tax_Code2] != [First_Code_Found] then [Tax_Code2] (etc., etc.) else '' endif

This second field isn't working - any advice? Thanks for your help in advance.

Preparation

Expression

Accepted answers

Brad1

Oh, looking at the formula all spread out - I see it's wrong. All the "4"s need to be different numbers.

All comments

ivoller

It looks valid. What is the error message (if any)? Can you share with sample data?

harikakummara

As @ivoller said condition looks valid. But "Second_Tax_Code_Found" :works only when the last ELSEIF statement column has some values left whose length is more than 9 digits.

for example:

Tax_Code1	Tax_Code2
12345678910	1234
12345678910	12345678911

Result:

Tax_Code1	Tax_Code2	First_Code_Found	Second_Code_Found
12345678910	1234	12345678910	0
12345678910	12345678911	12345678910	12345678911

As you see for the first line the "Secnod_Code_Found" column is 0 as the Tax_Code1 > 9 and is Equal to First_Code_Found column value. But for the second row, data populated as the conditions are satisfied.

Let me know if this clear your question. For more clarification please provide the entire formula to have a look at it.

Thanks,

Harika

Brad1

Ok, here is formula 1 (higher up in the formula tool)

if Length([TAXONOMY_CODE_00]) > 9 then [TAXONOMY_CODE_00] elseif Length([TAXONOMY_CODE_01]) > 9 then [TAXONOMY_CODE_01] elseif Length([TAXONOMY_CODE_02]) > 9 then [TAXONOMY_CODE_02] elseif Length([TAXONOMY_CODE_03]) > 9 then [TAXONOMY_CODE_03] elseif Length([TAXONOMY_CODE_04]) > 9 then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_05]) > 9 then [TAXONOMY_CODE_05] elseif Length([TAXONOMY_CODE_06]) > 9 then [TAXONOMY_CODE_06] elseif Length([TAXONOMY_CODE_07]) > 9 then [TAXONOMY_CODE_07] elseif Length([TAXONOMY_CODE_08]) > 9 then [TAXONOMY_CODE_08] elseif Length([TAXONOMY_CODE_09]) > 9 then [TAXONOMY_CODE_09] elseif Length([TAXONOMY_CODE_10]) > 9 then [TAXONOMY_CODE_10] elseif Length([TAXONOMY_CODE_11]) > 9 then [TAXONOMY_CODE_11] elseif Length([TAXONOMY_CODE_12]) > 9 then [TAXONOMY_CODE_12] elseif Length([TAXONOMY_CODE_13]) > 9 then [TAXONOMY_CODE_13] elseif Length([TAXONOMY_CODE_14]) > 9 then [TAXONOMY_CODE_14] elseif Length([TAXONOMY_CODE_15]) > 9 then [TAXONOMY_CODE_15] elseif Length([TAXONOMY_CODE_16]) > 9 then [TAXONOMY_CODE_16] elseif Length([TAXONOMY_CODE_17]) > 9 then [TAXONOMY_CODE_17] elseif Length([TAXONOMY_CODE_18]) > 9 then [TAXONOMY_CODE_18] elseif Length([TAXONOMY_CODE_19]) > 9 then [TAXONOMY_CODE_19] elseif Length([TAXONOMY_CODE_20]) > 9 then [TAXONOMY_CODE_20] elseif Length([TAXONOMY_CODE_21]) > 9 then [TAXONOMY_CODE_21] elseif Length([TAXONOMY_CODE_22]) > 9 then [TAXONOMY_CODE_22] elseif Length([TAXONOMY_CODE_23]) > 9 then [TAXONOMY_CODE_23] elseif Length([TAXONOMY_CODE_24]) > 9 then [TAXONOMY_CODE_24] elseif Length([TAXONOMY_CODE_25]) > 9 then [TAXONOMY_CODE_25] else '' endif

and here is Formula 2 based on the result of above.

IF Length([TAXONOMY_CODE_00]) > 9 and [TAXONOMY_CODE_00] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_00] elseif Length([TAXONOMY_CODE_01]) > 9 and [TAXONOMY_CODE_01] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_01] elseif Length([TAXONOMY_CODE_02]) > 9 and [TAXONOMY_CODE_02] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_02] elseif Length([TAXONOMY_CODE_03]) > 9 and [TAXONOMY_CODE_03] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_03] elseif Length([TAXONOMY_CODE_04]) > 9 and [TAXONOMY_CODE_04] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_05]) > 9 and [TAXONOMY_CODE_05] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_06]) > 9 and [TAXONOMY_CODE_06] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_07]) > 9 and [TAXONOMY_CODE_07] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_08]) > 9 and [TAXONOMY_CODE_08] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_09]) > 9 and [TAXONOMY_CODE_08] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_10]) > 9 and [TAXONOMY_CODE_10] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_11]) > 9 and [TAXONOMY_CODE_11] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_12]) > 9 and [TAXONOMY_CODE_12] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_13]) > 9 and [TAXONOMY_CODE_13] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_14]) > 9 and [TAXONOMY_CODE_24] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_15]) > 9 and [TAXONOMY_CODE_15] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_16]) > 9 and [TAXONOMY_CODE_16] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_17]) > 9 and [TAXONOMY_CODE_17] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_18]) > 9 and [TAXONOMY_CODE_18] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_19]) > 9 and [TAXONOMY_CODE_19] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_20]) > 9 and [TAXONOMY_CODE_20] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_21]) > 9 and [TAXONOMY_CODE_21] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_22]) > 9 and [TAXONOMY_CODE_22] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_23]) > 9 and [TAXONOMY_CODE_23] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_24]) > 9 and [TAXONOMY_CODE_24] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_25]) > 9 and [TAXONOMY_CODE_25] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] else '' endif

What seems to be happening is it is giving me the first Tax Code in the second field - even though it's not suppose to. Spot 00 isn't always filled and neither are any of the given spots. So I need it to find the first Code and then the second Code in all these fields.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703