4 Replies Latest reply on Jan 24, 2014 6:31 PM by wguan

    Help with Set Next Serial Value and Max (Field) + 1 after import

    wguan

      Hi I've been learning FileMaker for the past few months and are currently working with 2 versions of a database. I have a Deployed database and a Development database.

       

      The Development database is based off of the deployed version. I have been making changes to the Development version's layouts. The layout and field names in the Development version match all field names in the Deployed version, so I'm here trying to write a script that will import all data over from Deployed into Development.

       

      Preparation and thoughts:

      1. Delete all records on all tables in Development version.

      2. When I import records from Deployed into Development, the next auto-enter serial number for any given Development table may be a lower number than the next auto-enter serial number for the corresponding table from the Deployed database. Therefore I have created a script step to "Set Next Serial Value ( Table::PrimaryKeyField; Max (Table::PrimaryKeyField ) + 1"

      3. The auto-enter serial number is a number. Field type is a number. Examples: 1, 2, 3, 5, 10, 15, 200, 315.

       

      Sample script step to import a table:

      Go to Layout ["Example Layout" ( Example )]

      Import Records [ No Dialog; "DeployedDatabase.fmp12"; Add; Windows ANSI]

      Show All Records

      Set Next Serial Value [ Example::PrimaryKeyField; Max ( Example::PrimaryKeyfield ) + 1 ]

      Show Custom Dialog ["Import Complete"; "Import has completed"]

       

      Issue:

      My Example table has 400 records with some missing in between. The latest record had the highest primary key serial number of 600; the lowest serial number for a record is 74. The correct, next auto-enter serial number for a new record should be 601. The problem I'm experiencing is, the next auto-enter serial number is set to be 75 after the import. I assume the Max function would get 600+1, but instead I got 74+1.

       

      Is there a step I'm missing or something that I'm doing wrong in my script? I'd appreciate any help. Thank you!

        • 1. Re: Help with Set Next Serial Value and Max (Field) + 1 after import
          erolst

          Max () can “only” process one or more fields of one record, or a set of related records, or a list of literal numeric values (or any combination thereof), but it does not work across a found set. For this you need a summary field (see below).

          Your script step reads the “maximum’ of the field in the record you happen to be on after the import (probably the first one), which is simply the value itself.

           

          To get the maximum value across a found set (all records, in your case), create a field of type summary, say 'sMaxOfPrimaryKeyfield’, and define it as Maximum of PrimaryKeyField.

           

          To use it in your script:

           

          […]

          Show all Records

          Set Next Serial Value [ Example::PrimaryKeyField ; GetSummary ( sMaxOfPrimaryKeyfield ; sMaxOfPrimaryKeyfield ) + 1 ]

          • 2. Re: Help with Set Next Serial Value and Max (Field) + 1 after import
            Stephen Huston

            I have solved this a couple of different ways in the past:

             

            1. Run a script at the end of the import to:

                 Show all Records

                 Sort on the serial number field (ascending)

                 Go to last record

                 Set Next Serial Value (this record's serial number +1)

             

            2. Establish an X-type self-join relationship for the table, and have that relationship SORTED (Descending) in the relationship definition. Then, from any record, the value returned for the first related record will be the largest existing Serial number, and you can use the same basic script step to reset the serial value:

                 Set Next Serial Value (related serial number +1)

            This method requires creating the sorted relationship, but allows you to leave the imported set as the found set without having to show all records or sort them.

            • 3. Re: Help with Set Next Serial Value and Max (Field) + 1 after import
              keywords

              An alternative similar to Stephen's option 2 is to then use the Max () function pointed through this relationship. That way the Set Next Serial Value step can be used as in the original script.

              • 4. Re: Help with Set Next Serial Value and Max (Field) + 1 after import
                wguan

                erolst, thank you!  You are the best, it worked.  Here is the revised script that I used for my solution.

                 

                Go to Layout ["Example Layout" ( Example )]

                Import Records [ No Dialog; "DeployedDatabase.fmp12"; Add; Windows ANSI]

                Show All Records

                Set Next Serial Value [ Example::sMaxOfPrimaryKeyField; Max ( Example::sMaxOfPrimaryKeyfield ) + 1 ]

                Show Custom Dialog ["Import Complete"; "Import has completed"]