5 Replies Latest reply on Mar 2, 2015 5:08 AM by philmodjunk

    Assign custom serial number based on content in another field.

    frankst

      Title

      Assign custom serial number based on content in another field.

      Post

       

      Hi,

      I have done search and couldn't find a solution that would work for us.
      We will like to assign a serial number automatically using information from another field. For example

      Project Field: 1502
      Serial Nr.: 150201 (Record 01)
      Serial Nr.: 150202 (Record 02)
      This will condition until the Project field changes
      Project Field: 1602
      Serial Nr.: 160201 (Record 02)
      Serial Nr.: 160202 (Record 03)
      Serial Nr.: 160203 (Record 04)
      and so on.

       

      How would this be possible?

      Thanks

       

        • 1. Re: Assign custom serial number based on content in another field.
          philmodjunk

          And what if you need 100 records for a given project? (Just noting a built in limit to your numbering format)

          I strongly advise against using such a serial number as a primary key for linking records in relationships. Use a simple serial number for that purpose.

          But what you want can be implemented using a self join relationship:

          YourTable::Project = YourTable 2::Project

          Use one field for the serial sequence and a second field that combines it with the project number. and now you can define these two fields as:

          Sequence:     Max ( YourTable 2:: Sequence ) + 1

          CustomSerial: Project & Right ( "0" & Sequence ; 2 )

          Make CustomSerial a number or text field with the above expression as an auto-enter calculation and also give it a unique value validation as there is a small chance that two users creating new records for the same project at the same time might get the same exact customSerial value.

          • 2. Re: Assign custom serial number based on content in another field.
            frankst

            We haven't had close to 100 records per project but if so why not just add a three digit like 1502101?
            This serial number wouldn't be used as the primary key the primary key would created by Filemaker Pro (Auto-enter Serial) will not be shown for the user.

            So I have to have two Tables? 

             

            • 4. Re: Assign custom serial number based on content in another field.
              frankst

              Good morning, I gave it a try from what I understood but for some reason it is not working on my side.

              I have two tables that are identical with the following Fields:
              ID_Record: (Auto-enter Serial)
              Date:
              ProjectNr: (1501 sequence)
              JobNr: (Auto-enter Serial)
               

              • 5. Re: Assign custom serial number based on content in another field.
                philmodjunk

                To repeat, you would not create two identical tables. You would create two table occurrences. I included a link in a previous post here to a thread that explains in detail what is meant by a table occurrence as this is an important concept to master in order to employ effective database design in a FileMaker database.

                An "occurrence" is one of the "boxes" found in Manage | Database | Relationships. As spelled out in that other thread, you can select a box and then click the duplicate button (two green plus signs) to make a duplicate table occurrence. This does not create a duplicate table, but creates a duplicate reference to the same table so that you can link a table to itself in a "self join" relationship.