4 Replies Latest reply on Feb 11, 2014 4:58 AM by NaturalSound

    Fields that reference themselves

    NaturalSound

      Title

      Fields that reference themselves

      Post

           I did some searching but unable to find what I'm looking for.

           I'm attempting to create my own serial number generator without using the AutoEnter Serial number

           Can I make a Global field: FieldX

           Auto enter a calculation:  FieldX = FieldX+1

           So that every time a record is created it advances the clock by 1. (this way I can create a script later on that can change FieldX)

           I sort of answered my own question by doing it, and FM seems to be happy with it.

           Just wondering if FM will at some point pull a "HAL9000" on me.

           thank you for your time.

            

        • 1. Re: Fields that reference themselves
          philmodjunk

               Will this be a database with multiple users where two or more might try to generate such serial numbers? The value of a global field is specific to the user--changes made to the global field by one user will not be seen by another so your set up is pretty much guaranteed to produce duplicates if you have two or more users doing this at the same time.

               Why won't FileMaker's auto-entered serial number work for you? Need to know that in order to discuss alternatives.

          • 2. Re: Fields that reference themselves
            NaturalSound

                 Thank you for your assistance.

                 This is a database that is distributed to various companies of the same type of industry.

                 I'm trying to plan ahead, but perhaps my planning is flawed.

                 If I use the internal serial number starting at 1 to generate a client ID#, then at some point in the future I make a design update to the database (new features etc), the user will need to import records from the old version.

                 Unless I'm wrong, after import, the next number from the internal serial number will either be 1 or the number of records imported depending on the import criteria. Neither will work because it will generate duplicate ID#s (right?)

                 What I need is a way to look at the highest Client ID# imported and re-start the serial number clock from that point.

                 I've been out for quite a while. My last FM version was V7. I've now jumped back in at V12. So there may be something available now that I'm not aware of yet.

                 In a previous version, I did the "let x = x+1" idea, and while that seems to work, FM doesn't let you create that without first creating the field and then going back and changing the options.

                 I did have some glitches, but I was never able to get to the root of it, so I'm not sure it was related to this, so I'm essentially starting over.

                 Hope that's clearer than mud.

                 thanks,

                 t

                  

                  

            • 3. Re: Fields that reference themselves
              Jade
                   

              Unless I'm wrong, after import, the next number from the internal serial number will either be 1 or the number of records imported depending on the import criteria. Neither will work because it will generate duplicate ID#s (right?)

                    

                   There's a script step to solve the problem you mention in resetting the next serial number after import:: Set Next Serial Value[ ]

                   • create a duplicate TO with a cartesian (X) self join

                   • set the next serial number to the Max of the serial number field in the duplicate TO + 1 

              • 4. Re: Fields that reference themselves
                NaturalSound

                     Yep, that looks like that will solve my issues. 

                     thanks