10 Replies Latest reply on Feb 14, 2013 9:15 AM by DavidJondreau

    Incrementing a field without using auto-enter

    soberbob

      I'm looking to increment a field from a previous record as the same type. For example if a user selects "Computer" from a value list it will auto generate the next hostname (e.g. ABC-1000, ABC-1001, ect...). This also brings up the previous record could be a Scanner, which will end up with a blank hostname. Keeping this in mind the hostname can't be generated on the previous record. I could use the auto-enter option, but not every record needs a hostname and I would prefer to keep all the assets in the same table as I feel it would make it easier for a portal to display a particular users assets. If this doesn't make sense please advise as I am new to FM and FM 12 in general.

       

      Thanks

       

      Rob

        • 1. Re: Incrementing a field without using auto-enter
          taylorsharpe

          First, let me say I don't fully understand what you are asking, but let me start trying.  You can create a field that in the field options under the Auto-Enter tab, has the serial number box checked and the next value be "ABC-1000" and increment by 1.  If you do this, the next record will be ABC-1001, ABC-1002. 

           

          I do not, but apparently there is some relationship between say the "ABC" and the selected option "Computer".  You could set up such a relationship and have it sorted descending so that the relationship always sees the highest or last number entered (e.g., ABC-1002).  Instead of using the Serial Number Auto Enter function, you could use the Calculated Value Auto Enter to take whatever the last ABC-1002 number and add 1 to it.  You have to watch the behavior of a number field with text in it because it just ignores the text.  So "ABC-1001" as a number is "-1001".  Anyway, your calculation could be something like LeftWords ( Last Value ; 1 ) & "-" & RightWords ( Last Value ; 1 ) + 1.   Or if you are separating by the dash, you could use the position of the dash to set everything to the left as the hostname and everything to the right of the dash as a number to add 1 to. 

           

          Also, this could be something you do with a Modify Script Trigger when you select "Comptuer" that it then jumps over to another table to find the associated host name and then searches for all host names that are the same, sorts them, and gets the highest number, and returns to the original layout with the value of the next host name.  That is what I probably would do. 

           

          Well, I don't know that I answered your question, but hopefully those are some ideas.  Let us know what you are thinking and we'll give you some more input.  I'm sure someone will have an Execute SQL Suggestion too!

          • 2. Re: Incrementing a field without using auto-enter
            soberbob

            To clarify the ABC will equal whatever Organizational Unit (OU) in Active Directory I decide to create.  For the first two options won't the auto-enter field options always create a hostname for any new record created?  As when a user selects a different device it will load a different layout that hides the HostName option, but if you look at the table it should just show nothing.  If I set the regular Auto-Enter Serial the incrementing works perfect except for the fact I get those extra hostnames for everything.

             

            It seems this should be done with a few variables, but I would need to make it increment the global/local variable $$HostName and save it.  Here is what I thought would be a good start without using another table.

             

            SCRIPT:

             

            Set Variable [$$ABC; Value:"ABC-"]

            if[Assets::Category="Computer"]

                 Set Variable [$HostNumber; Value:1000]

                 Insert Calculated Result [Assets::HostName; $$ABC & $HostNumber]

                 Set Variable [$HostNumber; Value:$HostNumber  + 1]

            End if

             

            That would result in the hostname being populated with ABC-1000.  However I try to increment the $HostNumber the Set Variable at the beginning will always change it back to 1000. 

            • 3. Re: Incrementing a field without using auto-enter
              BruceHerbach

              Your variable $Hostnumber is a local varaible,  so it vanishes when the script ends. As is your script will always come up with a host name of ABC-1000. If you want it to persist after the script ends,  then it should be either a field ( global or local ) or a global ($$) variable.

               

              You can do a number of things to come up with a more unique number.  For instance if the Primary key for the Assets table is an incrementing Serial number  set $Hostnumber to the primary key value.  If not add a field that is an auto incrementing serial number for this purpose. 

               

              HTH

              Bruce

              • 4. Re: Incrementing a field without using auto-enter
                soberbob

                I believe I tried the exact same thing with a global variable first.  If I use a field to auto generate the serial (host number) it will do it for every asset.  This is an issue as all the host names would be further apart.  My Primary key is the serial number of the asset, which could be anything.

                 

                Thanks,

                Rob

                Sent from my iPhone

                • 5. Re: Incrementing a field without using auto-enter
                  BruceHerbach

                  Rob,

                   

                  In order to set some form of numeric value and not have duplicates,  you will have to store/create the value some place. Your script as is even using a global variable will always start with the same value, 1000.  There are a number of ways to handle this.  One is to have a field in the record that is a serial number.  This field doesn't have to be the primary key, just one that you use for this purpose.  As records get created in the table and you go to set the Hostname field it will be have a unique value.

                   

                  By the way these days it is recommended that you use set field instead of Insert Calculated value. It has full access to the Filemaker calculaton engine so you calculate any value you want.

                   

                  So your sript could look something like this:

                   

                  Set Variable [$$ABC; Value:"ABC-"]

                  if[Assets::Category="Computer"]

                       Set Variable [$HostNumber; Value:Assets::SerialNum]

                       Set Field [Assets::HostName; $$ABC & $HostNumber]

                       Set Variable [$HostNumber; Value:$HostNumber  + 1]

                  End if

                   

                  Or you could skip the Set Varaible[$Hostnumber ] script step and do all of the work in the Set field step

                     Set Field[Assetts::HostName; $$ABC & Assets::SerialNum]

                   

                  Another might be to get a count of all of the "Computers" and add 1 to the value when creating the Hostname.

                  HTH

                  Bruce

                  • 6. Re: Incrementing a field without using auto-enter
                    soberbob

                    This appears to just be the same process as using Auto-Enter--Serial Number next value ABC-1000.  It will always create a new serial regardless if I choose a Computer or another random asset.  If the variable option that I have tried doesn't seem to work, how would I go about creating a modify script trigger that Taylor mentioned? 

                     

                    As I thought about the process of Taylor's comment I figured I would try it out.  I created a table with an Auto-Enter Serial Number field to start at ABC-1000 and created a script to perform the following:

                     

                    // Ignore variable names and additional tables/fields that may be confusing as I plan on changing them to be more meaniful.

                     

                    if [Assets::Category="Computer"]

                         Go to Layout["HostNames" (ADHostName)]

                         New Record/Request

                         Set Variable [$$HostNumber; Value:HostNames::ADHostName]

                         Go to Layout [original layout]

                         Set Field [Assets::HostName; $$HostNumber]

                    End if

                     

                     

                    When I run the script it auto populates the field HostName properly.  Now what would be the best way to run the script automatically when a user selects "Computer" from the value list?  I don't really see the option to run the script in Layout mode-Script Triggers.

                     

                    Rob

                    • 7. Re: Incrementing a field without using auto-enter
                      taylorsharpe

                      OK, enter Layout Mode.  Click on the field that the "Computer" value list is on.  While that field is selected, go up to FORMAT and pull down to "Set Script Triggers".  Select the "OnObjectModify" box and assign it to the script you created above.  Then click OK and save the layout change.  Then when in the layout, select "Computer" in that field and it will run the script anytime you modify what is in the field. 

                      • 8. Re: Incrementing a field without using auto-enter
                        soberbob

                        Awesome!  Worked like a charm.

                        • 9. Re: Incrementing a field without using auto-enter
                          soberbob

                          Is there anyway to get the script to run on a mass import?  Since I'm always just adding record by record and manually selecting "Computer" the host name doesn't get set on a import.  Another option I have been looking at is having a function do everything, but I haven't had much luck with that as well.

                          • 10. Re: Incrementing a field without using auto-enter
                            DavidJondreau

                            A couple options:

                             

                            1) After the Import[] script step, add a Replace Field Contents[] script step.

                            2) In the source Import[] table, create a global text field. Set Field[] that to Computer and add the field to the Import[] order.