not(and
(
LEN(TRIM( ABN__c )) == 11,
ISNUMBER(ABN__c),
mod(
(((VALUE(MID(ABN__c, 1, 1)) - 1 ) * 10) +
(VALUE(MID(ABN__c, 2, 1)) * 1) +
(VALUE(MID(ABN__c, 3, 1)) * 3) +
(VALUE(MID(ABN__c, 4, 1)) * 5) +
(VALUE(MID(ABN__c, 5, 1)) * 7) +
(VALUE(MID(ABN__c, 6, 1)) * 9) +
(VALUE(MID(ABN__c, 7, 1)) * 11) +
(VALUE(MID(ABN__c, 8, 1)) * 13) +
(VALUE(MID(ABN__c, 9, 1)) * 15) +
(VALUE(MID(ABN__c, 10, 1)) * 17) +
(VALUE(MID(ABN__c, 11, 1)) * 19)),
89
) == 0
))
9 comments:
Looks like a great validation rule and I have to admit I tested it as working. However I fail to see what this is actually doing. Could you explain in further detail? Otherwise great post and thanks for sharing!
I've updated the original post to include a link to the "Format of the ABN" page from the government: https://www.ato.gov.au/Business/Australian-business-number/In-detail/Introduction/Format-of-the-ABN/
OK, that's great & once again thank you for sharing!
Hi San,
The idea with the formula is that there are no spaces in the ABN. Removing those first parts breaks that checking.
IF(
MOD(
10 * ((VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 1, 1)))-1) +
1 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 2, 1)) +
3 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 3, 1)) +
5 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 4, 1)) +
7 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 5, 1)) +
9 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 6, 1)) +
11 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 7, 1)) +
13 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 8, 1)) +
15 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 9, 1))+
17 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 10, 1))+
19 * VALUE(MID(SUBSTITUTE(ABN__c, ' ', ''), 11, 1))
,89) <> 0,
TRUE,FALSE)
Thanks Fosnez. Just pasted my formula which refers a Text field with spaces in ABN format.
Post a Comment
Note: Only a member of this blog may post a comment.