6 Replies Latest reply on Jun 11, 2012 7:41 AM by pwskura

    auto-enter calculation of non-serial number increment

    pwskura

      If I want to set an auto-enter calculation to generate a unique iteration of a field, is there an approach you know of to do this?

       

      For example,

      I have records of computer host names. All the records exist generally (as a static IP list), but the host name field gets changed regularly. They need to be unique. The practce is to default to account name, with successive entries getting a numeric suffix. Thus, if "jed" exists, the next time I try to enter jed, the field would be auto-entered with "jed-1" (plus domain suffix). And if "jed-1" exists, make it "jed-2". It is not a serial number and it is not just a numeric suffix.

       

      Right now I auto-enter the account name and tack on the domain suffix. So, I enter account then as I tab to the field in question it has auto built the name, then I need to manually insert the number between the account name and the domain suffix to correct the name. The name itself is referenced from DNS and so I cannot change the practice, rather I want to attempt automating following it.

       

      My concept seems simple as algorithms go, but how does one implement this in FileMaker?

       

      Thanks for tips on this!

        • 1. Re: auto-enter calculation of non-serial number increment
          Mike_Mitchell

          Hello, pwskura.

           

          One way to do this is to use a self-joining relationship based on the prefix (in your case, the prefix is the same as the account name). Then, tack the serial number on the end based on the Count function in an auto-enter calculation.

           

          Example: Get ( AccountName ) & Case ( Count ( SelfJoin::keyField ) > 1 ; "-" & Count ( SelfJoin::keyField ) - 1 )

           

          Probably needs to be cleaned up, and I haven't tested it, but it's one idea.

           

          HTH

           

          Mike

          • 2. Re: auto-enter calculation of non-serial number increment
            pwskura

            Thanks Mike. It gets me a lot closer, as it handles the first case, but the existing -1, -2 instances are not regarded and so I will have to try a few more pieces to accomodate that. The self join was something I consdered, but I missed how to put it into a calc.

             

            Here are the pieces I went with to implement your suggestion:

            Since I did not have a host name only field, I added a Calc field:

                 Host_c = Left ( Hostname ; Position ( Hostname ; "." ; 1 ; 1 ) -1 )

            I added a relationship of AccountID and Host_c

            I inserted this in the HostName field a-e calc:

                 AccountID & Case ( Count (UserHostCheck::AccountID) > 1; "-" & Count (UserHostCheck::AccountID) - 1 )  & HostSuffix

             

             

            BTW, I meant field with an account name versus the FileMaker entity account name.

             

            -Pete Skura

            • 3. Re: auto-enter calculation of non-serial number increment
              Mike_Mitchell

              Hello, Pete.

               

              One thing I've done in the past is use a Sequential ID field that holds the sequential ID number (1, 2, 3, etc.) for that record, then splicing them together. One prefix field (on which the self-join is based), one for the sequential ID, and then a calc field for the final result. That accounts for previous values.

               

              However, this sometimes can be a little risky if the system has a significant number of users. It can be vulnerable to duplicates. That may not be a problem here, though, since the prefix is the account name.

               

              Not a perfect system, but can work in the right circumstances.

               

              Mike

              • 4. Re: auto-enter calculation of non-serial number increment
                pwskura

                With a few tries, I am seeing your initial suggestion gets what I need. I won't use the sequence ID at this point.


                Thanks again.

                • 5. Re: auto-enter calculation of non-serial number increment
                  BowdenData

                  Pete,

                   

                  Take a look at the built in function called "SerialIncrement". I don't think it will do exactly what you are looking for, but you never know.

                   

                  HTH.

                   

                  Doug

                  • 6. Re: auto-enter calculation of non-serial number increment
                    pwskura

                    Doug,

                         It does help setting an increment, but the relationship part also seems to be necessary. For just changing a record it fits best, but for chainging relative to other values in the table it is not a direct answer. I do appreciate bringing it to my attention as another way to change a field besides in its definition.