And what if you need 100 records for a given project? (Just noting a built in limit to your numbering format)
I strongly advise against using such a serial number as a primary key for linking records in relationships. Use a simple serial number for that purpose.
But what you want can be implemented using a self join relationship:
YourTable::Project = YourTable 2::Project
Use one field for the serial sequence and a second field that combines it with the project number. and now you can define these two fields as:
Sequence: Max ( YourTable 2:: Sequence ) + 1
CustomSerial: Project & Right ( "0" & Sequence ; 2 )
Make CustomSerial a number or text field with the above expression as an auto-enter calculation and also give it a unique value validation as there is a small chance that two users creating new records for the same project at the same time might get the same exact customSerial value.
We haven't had close to 100 records per project but if so why not just add a three digit like 1502101?
This serial number wouldn't be used as the primary key the primary key would created by Filemaker Pro (Auto-enter Serial) will not be shown for the user.
So I have to have two Tables?
Good morning, I gave it a try from what I understood but for some reason it is not working on my side.
I have two tables that are identical with the following Fields:
ID_Record: (Auto-enter Serial)
ProjectNr: (1501 sequence)
JobNr: (Auto-enter Serial)
To repeat, you would not create two identical tables. You would create two table occurrences. I included a link in a previous post here to a thread that explains in detail what is meant by a table occurrence as this is an important concept to master in order to employ effective database design in a FileMaker database.
An "occurrence" is one of the "boxes" found in Manage | Database | Relationships. As spelled out in that other thread, you can select a box and then click the duplicate button (two green plus signs) to make a duplicate table occurrence. This does not create a duplicate table, but creates a duplicate reference to the same table so that you can link a table to itself in a "self join" relationship.