4 Replies Latest reply on Oct 3, 2012 12:19 PM by Frances

    Field Formatting - Auto Entry - Customer ID

    Frances

      Title

      Field Formatting - Auto Entry - Customer ID

      Post

           I'm new to Filemaker and wonder if someone may be able to help with regard to formatting a field.

           I would like a field to automatically generate data based on the first three letters of a customer's surname field followed by a sequential 3 digit number. 

           For example, a customer called John Smith would have a customer ID of SMI001.  A second customer called Peter Smith would need to have an ID of SMI002.  If a third customer was called Williams they should have an ID of WIL001.

           If it is possible to perform this calculation, I hope to be able to create a relationship with other tables whereby once the ID is entered the customer's details appear.  For example, customer details appearing on a quotation table.

           Any help would be much appreciated, I have already spent hours trying to work this out myself, but I'm confident someone on the forum will have the knowledge.

           Many thanks in advance.

            

        • 1. Re: Field Formatting - Auto Entry - Customer ID
          philmodjunk

               Why use this numbering method instead of just a simple auto-entered serial number with no letters?

               What you want can be done, but implementaion is much more complex and if your database is hosted over a network such that multiple users may be creating records for new customers at the same time, it can be tricky to keep duplicate values from appearing where this is not an issue with an auto-entered serial number.

               I suspect that there are other ways to get what you want where your relationships can remain based on a simple serial number, but a field in Customers can store this value if it is needed.

          • 2. Re: Field Formatting - Auto Entry - Customer ID
            Frances

                 It may not be the best solution, but a serial number will not work as whoever is creating the quotation would have to know the customer's serial number or at least check it first from the customer table (not ideal).  To save time they would know the customer's first three letters and there would not then be too many numbers to select the right customer from. 

                 As an alternative, would it be possible to start entering the customer's name then have it autofill the remaining information in the quotation.

            • 3. Re: Field Formatting - Auto Entry - Customer ID
              philmodjunk
                   

                        As an alternative, would it be possible to start entering the customer's name then have it autofill the remaining information in the quotation.

                   This is the option I'd recommend. For one thing, your users won't have to remember that John Smith is SMI001 and Johan Smith is SMI002.

                   Here's a demo file of a script supported auto-complete drop down list of names. You can start entering a name and the drop down filters to fewer and fewer matches. When you exit the field, a script uses the name entered to look up the serial ID field for the name so entered. The script includes code so that if you get two customers with exactly the same name, the system will list them with additional info displayd such that the user can easily click to select a specific individual.

                   If you are using FileMaker 12: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   If you are using FileMaker 11 or older:  https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

              • 4. Re: Field Formatting - Auto Entry - Customer ID
                Frances

                     Many thanks Phil, I have Filemaker 12 and your link is very helpful. I will reassess the need for an ID.