9 Replies Latest reply on Jul 14, 2010 7:09 PM by aikiko

    Serial numbers - consecutive numbers for non-consecutive records

    aikiko

      Title

      Serial numbers - consecutive numbers for non-consecutive records

      Post

      Dear All,

      A tricky problem that I cannot solve.

      My DB has 2 serial numbers for each record: 1) database serial number for each record that is entered and 2) a serial number that is called ID number for each record that is of interest.

      Automating the database serial number is straight forward, but how does one create the calc for the ID number so that every time a yes/no checkbox in the field ('ID number required') is clicked yes, then the ID number is automatically created. 

      Of note, the ID number should be a consecutive number, but for non-consecutive records, for example:

      DB Serial no =1       ID number=1

      DB Serial no =2       ID number= not required

      DB Serial no =3       ID number= not required

      DB Serial no =4       ID number=2

      DB Serial no =5       ID number=3

      DB Serial no =6       ID number=not required

      DB Serial no =7       ID number=4

      Many thanks in advance for any assistance provided once again,

      Aikiko

      OSX10.6 FMP10Adv

        • 1. Re: Serial numbers - consecutive numbers for non-consecutive records
          sunmoonstar.13

          Hi Aikiko,

          I have exactly the same situation with my database. The way I solved the problem was to use two related tables to store the different serial number sequences (I'll use your field names for the sake of clarity):

          Table 1 contains records with the "DB serial number" field.
          Table 2 contains records with the "ID number" field. Define Table 2::ID number with an Auto-Entered serial number. 

          Table 2 also contains "DB serial number" as the key field to relate the two tables:

          Table 1::DB serial number >====< Table 2::DB serial number

           

          Then create a script and attach it to a button in Table 1:

          New Record/Request (the next sequential "DB serial number" is auto-generated at this point)
          Show Custom Dialog (The dialog asks if an "ID number" is required. The default button is "OK")
          If [Get (LastMessageChoice) = 1]
          Set Variable [$DB_serial_number; Value:Table 1::DB serial number]
            (this step stores "DB serial number" temporarily as a variable)
          Go to Layout ["Table 2" (Table 2)]
          New Record/Request (the next sequential "ID number" is auto-generated at this point)
          Set Field [Table 2::DB serial number; $DB_serial_number] (this step sets the variable to the key field in Table 2 so that the two records are correctly related)
          Go to Layout ["Table 1" (Table 1)] 
          End If

          With this technique, you don't really need the checkbox field at all.

          Hope this helps :-)

           

          Nick

           

           

           

           

          • 2. Re: Serial numbers - consecutive numbers for non-consecutive records
            raybaudi

            I think that you do not need a new table.

            You need only a new occurrence of the same table, related to the first by DB Serial with the cartesian operator "X" ( simply change the default "=" to "X" ).

            The field ID number must have the option of auto-entering a calculation:

            If( YourCheckBox = "Yes" ; Max ( RelatedTO::ID number ) + 1 )

            and unflagged the box: "Do not Replace existing value..."

            • 3. Re: Serial numbers - consecutive numbers for non-consecutive records
              sunmoonstar.13

              > The field ID number must have the option of auto-entering a calculation:

              > If( YourCheckBox = "Yes" ; Max ( RelatedTO::ID number ) + 1 )

               

              How can that auto-enter calculation work? The checkbox can only be checked after the record has already been created.

              Am I missing something?

              Nick

              • 4. Re: Serial numbers - consecutive numbers for non-consecutive records
                raybaudi

                Yes...

                Who said that the field must be evaluated on record creation ?

                After the creation of the record, checking the check box, the field will auto-enter the value imposted by that calculation.

                • 5. Re: Serial numbers - consecutive numbers for non-consecutive records
                  LaRetta_1

                  There is possibility that this will break if two users are creating records at same time.  You would do best to use a specific Serial table and script the process.

                  • 6. Re: Serial numbers - consecutive numbers for non-consecutive records
                    aikiko

                    Dear sunmoonstar_13, raybaudi, LaRetta,

                    Thank you very much for all your help and suggestions. I have tried both solutions and with some minor tweaking I have managed to get them to work.

                    May I be so bold as to add another spanner in the works?

                    My ID number is actually not as easy as 1,2,3.

                    My ID number is actually a concatenate of four numbers with the following fields: A,B,C,D

                    e.g. A=10, B=23, C=111, D=22 

                    Therefore the ID number is 102311122

                    A=year

                    B=village (1-50 villages to be surveyed)

                    C=household (number of houses in the village - ranging from 10 to 200)

                    D= no. of individual in each household (Field D will increase by 1 for each different individual, range 1-99)

                    Therefore assuming A is fixed, the ID number will vary considerably.

                    Referring to my first post then - the DB serial number will be an automated serial number from 1 onwards, with the ID number changing as it is dependent on the fields entered for each of the fields A, B, C and D.

                    Example for village #23 & household #111:

                    DB Serial no =1       ID number= 102311101

                    DB Serial no =2       ID number= not required

                    DB Serial no =3       ID number= not required

                    DB Serial no =4       ID number=102311102

                    DB Serial no =5       ID number=102311103

                    DB Serial no =6       ID number=not required

                    DB Serial no =7       ID number=102311104

                    Continuing example for the same village #23 & the next household #112:

                    DB Serial no =8         ID number= 102311201

                    DB Serial no =9         ID number= 102311202

                    DB Serial no =10       ID number= not required

                    DB Serial no =11       ID number= not required

                    DB Serial no =12       ID number= not required

                    DB Serial no =13       ID number= 102311203

                    DB Serial no =14       ID number= 102311204

                    Any thoughts?

                    I hope that makes some sense, and once again thank you all very much in advance for your expertise,

                    Aikiko

                    • 7. Re: Serial numbers - consecutive numbers for non-consecutive records
                      LaRetta_1

                      Your unique IDs should be meaningless.  When each piece of the serial has a meaning, you are in trouble.  

                      A=year

                      B=village (1-50 villages to be surveyed)

                      C=household (number of houses in the village - ranging from 10 to 200)

                      D= no. of individual in each household (Field D will increase by 1 for each different individual, range 1-99)

                      Each of these pieces of information should be an individual field and not part of the unique ID.  You are complicating something which should be simple auto-enter serial number (FM-generated using the auto-enter serial feature, increment by 1).  If you continue down this path, it will cause you major issues and you can NOT depend upon it producing a unique result in all cases. 

                      You will receive the same advice from all top Developers and for a very good reason.  I strongly urge that you drop this direction for your IDs.

                      • 8. Re: Serial numbers - consecutive numbers for non-consecutive records
                        aikiko

                        Dear LaRetta,

                        Thank you for your thoughts. I agree with you, but unfortunately I've inherited a developing country system that is has been ingrained in their health system for a while. Alas, that is why I have included a database number. Indeed each of A,B,C,D are individual fields, but they do make up the unique ID.

                        I will play around with some nested If statements to see if I can get around this problem anyway.

                        Thanks for your input once again - much appreciated.

                        Aikiko

                        • 9. Re: Serial numbers - consecutive numbers for non-consecutive records
                          aikiko

                          I have selected raybaudi's post as an intermediary answer for this post. sunmoonstar_13's post also works but I like raybaudi's because it is easier to set-up for a novice like myself.

                          Thanks

                          Aikiko