2 Replies Latest reply on Apr 4, 2012 2:05 PM by philmodjunk

    Question about a field

    AaronRoss

      Title

      Question about a field

      Post

      hello. i have a field called APPT ID: it is a unique id auto generated each time a rep creates a new record. I also have a field called TM-ID. i need a calculation that will take the first 6 letters of the APPT ID so i can auto copy and past it to the TM-ID.

      EX: John Doe creates a record. the APPT ID generates as GATM04APT1232. the TM-ID is always the first 6 letters of that ID. tm-ID: GATM04

      Is there a way to maybe create a filter that filters out everything but the first 6 letters in a field?

        • 1. Re: Question about a field
          GuyStevens

          This is a copy / Paste from the Filemaker help book: (can be found as a pdf on the filemaker site)

          Left
          Purpose
          Returns numberOfCharacters in text, counting from the left.
          Format
          Left(text;numberOfCharacters)
          Parameters
          text - any text expression or text field
          numberOfCharacters - any numeric expression or field containing a number
          Data type returned
          text
          Originated in
          FileMaker Pro 6.0 or earlier
          FILEMAKER FUNCTIONS REFERENCE
          208
          Examples
          Left(“Manufacturing”;4) returns Manu.
          Left(Name;Position(Name;“ “;1;1)) returns Sophie, when the Name field contains Sophie
          Tang.
          Left(PostalCode;3) & Upper(Left(LastName;4)) returns 481JOHN when the
          PostalCode field contains 48187 and LastName contains Johnson.

          So for you, your TM-ID should be a field that has an auto enter calculation of:
          Left ( APPT ID ; 6 )

          • 2. Re: Question about a field
            philmodjunk

            I prefer to keep serial number fields like APPTID a pure number if they are used in relationships to other tables. Any leading characters that add "meaning" or a format (such as leading zeroes) to the number, I put in a different field and don't, except for very specific reasons, use those characters as part of a primary key used in relationships. Any time I need to combine the leading text and the number, I can either use a calculation to combine them or I can put them on a layout as merge fields placed side by side so that they display as a single field.

            When you include content intended to provide meaning to the user in a primary key, you open the door at least by a tiny bit, that you might be required to change this content in the future and making such a change can be tricky and time consuming, and completely avoidable when you stickwith pure serial numbers for your field.

            In your case, if you keep the leading characters in a separate field, it's directly available to any calculations that need to access these characters.