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.