Australian Business Number (ABN) Validation Rule for Salesforce.com

Assuming your Custom field is "ABN" (API Name = "ABN__c") then you can stuff this formula in a validation rule to ensure that ABNs entered are valid by using the same logic the government does.

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
))

Comments

  1. AnonymousSeptember 11, 2014 at 12:22 PM

    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!

    ReplyDelete
  2. FosnezSeptember 12, 2014 at 10:25 AM

    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/

    ReplyDelete
  3. AnonymousSeptember 12, 2014 at 11:05 AM

    OK, that's great & once again thank you for sharing!

    ReplyDelete
  4. AnonymousJuly 2, 2015 at 11:53 AM

    This comment has been removed by a blog administrator.

    ReplyDelete
  5. AnonymousJuly 2, 2015 at 11:53 AM

    This comment has been removed by a blog administrator.

    ReplyDelete
  6. AnonymousJuly 2, 2015 at 3:09 PM

    This comment has been removed by the author.

    ReplyDelete
  7. FosnezJuly 2, 2015 at 3:11 PM

    Hi San,

    The idea with the formula is that there are no spaces in the ABN. Removing those first parts breaks that checking.

    ReplyDelete
  8. AnonymousJuly 2, 2015 at 3:15 PM

    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)

    ReplyDelete
  9. AnonymousJuly 2, 2015 at 3:49 PM

    Thanks Fosnez. Just pasted my formula which refers a Text field with spaces in ABN format.

    ReplyDelete

Post a Comment

Popular posts from this blog

Salesforce Merge Accounts URL Hacking

The Merge Accounts page is OK to use, however to start the process you have to search for the Accounts you want to merge, and to do that they have to have the same name. This is not always the case, and it can be annoying to have to rename an account, just so you can find it to merge it. The official SFDC selection screen also limits you to a max of 3 accounts. This isn't actually a hard limit... More of a guide, as you'll see below. Instead of this, you can actually hack the URL to include the IDs of any accounts, regardless of Account Name similarity. This is especially useful if you are using your own code to manage merges and would like to "deep link" into the SFDC platform. Here's an example URL: https://emea.salesforce.com/merge/accmergewizard.jsp?goNext=+Next+&cid=001200000012345&cid=001200000012346&cid=001200000012347&cid=001200000012348 It can be broken down into 3 main segments: The Merge Accounts page URL: https://emea.sale...
Read more

Australian Postcode Boundaries for Google Earth (KMZ)

Image
I searched for ages, and couldn't find where to get a KMZ / KML file for Google Earth that had all the Australian Postcode Boundaries in it... So I made one based on the 1270.0.55.003 - Australian Statistical Geography Standard (ASGS): Volume 3 - Non ABS Structures, July 2011 . I claim no additional copyright on this, but please ensure you are able to use the data according to the ABS Copyright rules Here is the download link
Read more

Don't look up!

Image
Don't look up people. The Feces is about to hit the rotating ceiling appliance. (for those of you requiring monosyllabic prose, this means the shit is about to hit the fan) It would appear that the almighty ACMA ( Australian Censorship Management Authority ) blacklist of websites has been leaked online. It had to happen, and it is better it happens now. The saviour of free speech , WikiLeaks (Not to be confused with Wikipedia), has posted it online. Interestingly, I cannot access WikiLeaks at the moment, this could be due to a crap internet connection, or perhaps it is a little more clandestine than that (do I hear black helicopters?). According to the Sydney Morning Herald , who have apparently seen the list, the below are some of the more interesting things on the blacklist: A Queensland dentist (Not illegal) A tour operator (Not illegal) Several Christian sites (Not illegal) Fetish sites (Not illegal) Fringe religion sites, such as satanism (Not illegal) Euthanasia how-to sites...
Read more