Link Defects to an occurrence of itself by the contract number field:
Defects::ContractNumber = Defects|SameContract::ContractNumber
Define Sequence as a number field in Defects.
Use this calculation to assign a value to it:
Max ( Defects|SameContract::Sequence ) + 1//do not use this in a field of type calculation
Define a text field in Defects with this auto-enter calculation:
ContractNumber & "/" & Sequence
Select the unique values validation option for it.
Thanks for this. Think i am out of my depth.
You lost me on the first line.
How would I link defect to an occurrence of itself by the contract number field. I only installed the software yesterday :)
In Manage | Database | relationships, make a new table occurrence of Defects by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be Defects|SameContract.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
To learn more about table occurrences see: Tutorial: What are Table Occurrences?
That's great got there in the end :) One small problem is that the first record does not allocate a number 1 I assume that is because the field ids blank first time you input. can this be overcome
You may have had a record in your table before you added these features and thus you could just delete this one record. Each new record should not have that problem. Also, if you are using an auto-enter calculation (the other option is to use a script for creating new records and assigning this value), make sure to clear the check box labeled "do not evaluate if all referenced fields are empty".