5 Replies Latest reply on Nov 18, 2010 3:09 PM by philmodjunk

    How to auto-enter a serial number just in a set of related records

    jthulin

      Title

      How to auto-enter a serial number just in a set of related records

      Post

      I'm a veterinarian and amateur FMPro developer. I'm using FMPro to build an animal health record db. There are several tables, e.g., the main animal record (name, species, DOB, etc.), a problem table for listing the medical problems for each animal, treatment table, and others. So in the animal record layout I have a portal set up that shows the related medical problems for the particular animal; the problems for each animal are numbered sequentially beginning with 1. What I'm stuck with is how upon creation of new problem record in the portal for the new problem number to be auto-entered. Of course I know how to auto-enter a serial number, and indeed I do have a "ProblemID" field that assigns a serial number for each problem created in the system. However, I don't know how to script for the auto-entry of the next number in just the related set of records. For example, Fluffy has two medical problems, and upon creation of the next problem for Fluffy, I want an auto-entry of the number "3". Does anyone have a the solution to this? Thanks much.

        • 1. Re: How to auto-enter a serial number just in a set of related records
          philmodjunk

          The simplest is to not put the number in the field at all.

          In your portal, Just use the layout text tool, (T), to add the text @@ to your portal row. This will display a sequence of numbers in the portal starting with a 1.

          • 2. Re: How to auto-enter a serial number just in a set of related records
            jthulin

            Phil, thanks for the idea, this does indeed number the portal rows well. However, I need to keep the problem number with a problem detail layout and table. This way all physical examinations and treatments can be related to the specific animal and problem in an easy way. For example at the top of the problem detail layout, the animal name and problem number are displayed.

            • 3. Re: How to auto-enter a serial number just in a set of related records
              philmodjunk

              I would not use any such number to relate data. I'd use the serial number you already described. Any additional numbering such as you've asked for here, should only be used in searches and sorts to help with idenfication and organisation.

              I've suggested the @@ idea first because, any way you slice it, the numbering you describe adds a level of complexity to the design of your database. If you are hosting the file over a network such that several different users might be entering data at the same time, it can be very difficult to keep duplicate numbers from being auto-entered.

              So key question, and keep future expansion in mind here, is this a system that might have more than one user creating records at the same time?

              • 4. Re: How to auto-enter a serial number just in a set of related records
                jthulin

                Yes, multiple users creating records at the same time, but not for the same animal.

                • 5. Re: How to auto-enter a serial number just in a set of related records
                  philmodjunk

                  So you have an Animal table with AnimalID as an auto-entered serial number.

                  You need a series of related records in a Problems table where a second field, Sequence, will number sequentially for each animal record in the animal table.

                  If you always add new records in the bottom blank row of a portal, or as the last record in a found set of all the problem records for a given animal, you could just use Get ( RecordNumber ) as an auto-entered calculation.

                  For a more robust approach:

                  You should already have this relationship:

                  Animal::AnimalID = Problems::AnimalID

                  Add a second table occurrence of Problems named ProblemsSameAnimal and relate it by AnimalID:

                  Problems::AnimalID = ProblemsSameAnimal::AnimalID   (click Problems, then click button with two green plus signs...)

                  Now write this script:

                  Set Field [ Problems::Sequence ; If ( IsEmpty ( Problems::Sequence ) ; Max ( ProblemsSameAnimal::Sequence ) + 1 ; Problems::Sequence ) ]

                  Use an OnObjectSave script trigger on a required field for this record to run this script.

                  Should two users pull up the same animal record and attempt to log new problem record, that would be a mistake, but mistakes happen, you could add a validation check in the Problems table.

                  Define a text field with this auto-entered calculation: AnimalID & " " & Sequence. Clear the "Do not replace existing value..." option and select the Unique Values option on the validation tab.