1 Reply Latest reply on Jan 13, 2014 9:40 AM by philmodjunk

    Two different sets of serial numbers

    SamuelWeiller

      Title

      Two different sets of serial numbers

      Post

           Hi All,

           I am making a database of research participants. Each participant is given a basic serial number regardless of what type of experiment they participate in. However, certain participants are given a second serial number if they are in one type of experiment. What I would like to do is to have a script button someone could click to assign the secondary serial number if it is needed. I have tried making the following script:

           Set Field ( Subjects::ScannerID ; Max ( Subjects::ScannerID ) + 1 )

           but this start back at 1 for each record. Any idea how to do this?

           Thanks!

        • 1. Re: Two different sets of serial numbers
          philmodjunk
               

                    I have tried making the following script:

               

                    Set Field ( Subjects::ScannerID ; Max ( Subjects::ScannerID ) + 1 )

               

                    but this start back at 1 for each record. Any idea how to do this?

               That suggests that you don't have the correct relationship in place. Please note that this secondary serial number should not be used as a match field in relationships.

               Define a self join relationship between two occurrences of your subjects table:

               Subjects::_fkExperimentID = Subjects|SameExperiment::_fkExperimentID

               then your set field step becomes:

               Set Field ( Subjects::ScannerID ; Max ( Subjects|SameExperiment::ScannerID ) + 1 )

               And I recommend that you define a text field with this auto-enter calculation:

               _fkExperimentID & " " & ScannerID

               so that you can specify a "unique values" validation on this text field to make sure that you can't get two subjects with the same scanner ID for the same experiment.