2 Replies Latest reply on May 3, 2012 7:25 PM by ajayz

    Australia formats


      In Australia we use the following formats and I would like help on how to set these up for my Filemaker database. All values are set to TEXT and I know I would need to use a calulations, but not too sure how to write it.


      Formats required:


      a) ABN is the company registation number and is a number that needs to be formatted as: 00-000-000-000


      b) ACN numbers similar to the above but formatted as: 000-000-000


      d) Mobile phones numbers are formatted as 0000-000-000


      e) Phone numbers are formatted as: 00-0000-0000


      I've only been using FileMaker Pro 12 for one month and all my database development has been with MS Access, I'm trying to make the change and I find formatting is a very difficult thing to do in FileMaker.


      Thank you for your help.

        • 1. Re: Australia formats

          Welcome to FileMaker!


          For this kind of thing, your best bet would be to use an Auto-Enter Calculation that "replaces existing value".


          In File->Manage->Database, highlight the field in question and click "Options...". In the Options, select the Auto-Enter tab and select the "Calculation" checkbox. This will open a calculation dialog, where we can work our magic.


          Let's do the ABN as an example; the rest will be similar.


          We expect that the user has entered 11 numeric digits, so we can separate them into their groups and add the - between them (or space, or any other character). In the calculation dialog enter:


          Left ( Self ; 2 ) & "-" & Middle ( Self ; 3 ; 3 ) & "-" & Middle ( Self ; 6 ; 3 ) & "-" & Middle ( Self ; 9 ; 3 )


          So an input of








          You could filter out non-numeric characters using the Filter function, and you could write a custom function to include do this as well if you have FileMaker Pro Advanced.


          A more advanced approach using Filter might look like:


          Let ( [ source = Self ;

                     filt = Filter ( source ; "0123456789" ) ;   // keeps only numbers; any other characters are ignored

                     gap = "-" ] ;                                          // the character you want between number groups - could be space if you prefer, or anything


          Left ( filt ; 2 ) & gap & Middle ( filt ; 3 ; 3 ) & gap & Middle ( filt ; 6 ; 3 ) & gap & Middle ( filt ; 9 ; 3 )




          As in the first example, it takes the first two characters, adds the gap character, then the next three (starting position 3, take 3) and so on.


          Once you have saved the calculation, deselect the "Do not replace existing value of field (if any)" checkbox.


          Important note: phone numbers, ACNs and ABNs are data type TEXT, not number. They are made up of numeric characters, but they are text, since adding, subtracting etc are meaningless.


          Hope this helps.



          • 2. Re: Australia formats

            Thanks heaps Rob...   it's a big change from Access, but I love how there are less limitations as to the size of the database and the fact that it works on the iPad which is one of the main reasons to change.