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.
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.
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?
Yes, multiple users creating records at the same time, but not for the same animal.
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.