1

Can anyone plz suggest , where i went wrong in creating the below formula, as per me formula is correct, and its working on some values, but not working on others. I couldn't ablle to identify where exactly i went wrong. thnx for your support.

Formula Creation Criteria :

enter image description here

 AND( 
NOT(ISBLANK(Thai_Identification_Number__c)), 
VALUE(RIGHT(Thai_Identification_Number__c,1)) <> 
( 
11- 
( 
IF( 
MOD( 
ROUND( 
( 
VALUE(MID( Thai_Identification_Number__c ,1,1))*13 + 
VALUE(MID( Thai_Identification_Number__c ,2,1))*12 + 
VALUE(MID( Thai_Identification_Number__c ,3,1))*11 + 
VALUE(MID( Thai_Identification_Number__c ,4,1))*10 + 
VALUE(MID( Thai_Identification_Number__c ,5,1))*9 + 
VALUE(MID( Thai_Identification_Number__c ,6,1))*8 + 
VALUE(MID( Thai_Identification_Number__c ,7,1))*7 + 
VALUE(MID( Thai_Identification_Number__c ,8,1))*6 + 
VALUE(MID( Thai_Identification_Number__c ,9,1))*5 + 
VALUE(MID( Thai_Identification_Number__c ,10,1))*4 + 
VALUE(MID( Thai_Identification_Number__c ,11,1))*3 + 
VALUE(MID( Thai_Identification_Number__c ,12,1))*2 
) /11,1 )*10,10 
)=0, 
10, 
MOD( 
ROUND( 
( 
VALUE(MID( Thai_Identification_Number__c ,1,1))*13 + 
VALUE(MID( Thai_Identification_Number__c ,2,1))*12 + 
VALUE(MID( Thai_Identification_Number__c ,3,1))*11 + 
VALUE(MID( Thai_Identification_Number__c ,4,1))*10 + 
VALUE(MID( Thai_Identification_Number__c ,5,1))*9 + 
VALUE(MID( Thai_Identification_Number__c ,6,1))*8 + 
VALUE(MID( Thai_Identification_Number__c ,7,1))*7 + 
VALUE(MID( Thai_Identification_Number__c ,8,1))*6 + 
VALUE(MID( Thai_Identification_Number__c ,9,1))*5 + 
VALUE(MID( Thai_Identification_Number__c ,10,1))*4 + 
VALUE(MID( Thai_Identification_Number__c ,11,1))*3 + 
VALUE(MID( Thai_Identification_Number__c ,12,1))*2 
) /11,1 )*10,10 
) 
) 
) 
) 
)

While its working for some values, I ont know why its not working for the value mentioned in my screenshot " 1488708326392"

enter image description here

1

1 Answer 1

2

Your main problem is that ROUND takes values less than 0.5 down to 0, and 0.5 and above to 1, which is not quite the same as rounding up to the nearest decimal. There's a few different ways to do this, but I believe I've shortened the formula significantly by shuffling some of the math around. Here's a modified version of the formula:

NOT(ISBLANK(AccountNumber)) && 
( 
LEN(AccountNumber) <> 13 || 
TEXT(
MOD(
11-MOD(
CEILING(((
VALUE(MID(AccountNumber, 1,1))*13+ 
VALUE(MID(AccountNumber, 2,1))*12+ 
VALUE(MID(AccountNumber, 3,1))*11+ 
VALUE(MID(AccountNumber, 4,1))*10+ 
VALUE(MID(AccountNumber, 5,1))* 9+ 
VALUE(MID(AccountNumber, 6,1))* 8+ 
VALUE(MID(AccountNumber, 7,1))* 7+ 
VALUE(MID(AccountNumber, 8,1))* 6+ 
VALUE(MID(AccountNumber, 9,1))* 5+ 
VALUE(MID(AccountNumber,10,1))* 4+ 
VALUE(MID(AccountNumber,11,1))* 3+ 
VALUE(MID(AccountNumber,12,1))* 2 
)*10)/11),10),10))<>RIGHT(AccountNumber,1) 
)

Explanation:

    (
    VALUE(MID(AccountNumber, 1,1))*13+ 
    VALUE(MID(AccountNumber, 2,1))*12+ 
    VALUE(MID(AccountNumber, 3,1))*11+ 
    VALUE(MID(AccountNumber, 4,1))*10+ 
    VALUE(MID(AccountNumber, 5,1))* 9+ 
    VALUE(MID(AccountNumber, 6,1))* 8+ 
    VALUE(MID(AccountNumber, 7,1))* 7+ 
    VALUE(MID(AccountNumber, 8,1))* 6+ 
    VALUE(MID(AccountNumber, 9,1))* 5+ 
    VALUE(MID(AccountNumber,10,1))* 4+ 
    VALUE(MID(AccountNumber,11,1))* 3+ 
    VALUE(MID(AccountNumber,12,1))* 2)

Steps A, B, C, and D.

CEILING(
(...)*10)
/11),

This part simulates rounding up to the nearest single decimal by multiplying by 10, then rounding up to the nearest whole number.

                MOD(
                   ...,
                10)

Grabs just the left-most digit from the result (Step F). We'll make the result 10 when it's 0 in the next step:

MOD(
    11-(...),
    10
)

We take 11 minus the number from 0 to 9. We mod by 10, because when you take 11-10 (where 0 should be), you get 1 left over, and when you take 1 from 11, you should get a check digit of 0.

I haven't verified all check digits, but this looks good on paper, and, perhaps more importantly, it does validate the example number you've provided. Please test this and let me know if it's working correctly.

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .