i think this must be scripted.
create a field "dupcounter numerical" to your fields.
use script triggers (needs fm 10), or your "add new record button", to check if the id field is a duplicate. be sure to search for id field contence without the "-something".
if duplicate, add 1 to your dupcounter field. add "-"& dupcounter to your id field. and at last, replace the current dupcounter value in *all* found duplicates. (so if one of the dups will be deleted, all others will keep the dupcounter information)
greetings from gemany
Whichever method you use to create these serials, I would highly recommend that they only be used as a display serial. You should keep FileMaker auto-serial as the primary serial. A true serial should remain meaningless.
Thanks, I am going to try this now. The only thing I think I cannot do is to replace the current dupcounter value on all existing duplicates if one duplciate gets deleted. That is because the id will be tied to other records in other tables once it is created, and needs to be referenced.
So, I believe it is best to keep the counter incrementing regardless of any gaps or deleted records in the count.
For example, if I had the following records:
and 10019-2 got deleted, the next duplicate of 10019 that gets created should still be 10019-4, resulting in:
I believe this is the way to go, any comments are welcome.
When I do this, if I have an auto-increment serial as the estimateid, is there a way to ensure the next non-duplicate record will use the next sequential serial number?
The issue is, even if I replace the auto generated estimateid in a duplicate record with the estimateid & "-" & $dupcounter value, it still acts as if I used a serial number and puts the next auto increment serial number to the next value.
I create estimate 10019 , with EstimateID 10019
I create a duplciate of estimate 10019, with EstimateID of 10019-01
I want to create a new Estimate, and have the auto increment serial be 10020 (not 10021 like it is doing now)
It is incrementing that value even when I create a duplicate (which I do not want), and i am not sure how to stop that (or set it back to what it was before the duplicate was created, if that's easier).
Any suggestions would be great.
Do you have FileMaker pro advanced?
Yes, FileMaker Pro Advanced 10.0v1
It might help if you use one field for 'parent_id', in your example, 10019, and have the table self-relate based on that field. The create a auto-entered number field = count ( Table Self:: Parent ID ). Then, a third field, which is an concatentation calculation of the two, ( parent_id & "-" & dup_id ).
I apreciate that suggestion, but is there any way to simply have that auto-serial ID not be used or incremented when creating a duplicate record only?
I have the rest of the script working, so that would be nice if it was possible......
Since you have advanced, you can use custom menus to replace your duplicate command for that layout. The new duplicate command would run your script. In your script you can script it to grab a count of the related records using the self join method that etripoli stated earlier, into a variable ($sjcount), then duplicate the record, and then you can set your text field to add the "-" & $sjcount to your base number.
I agree with etripoli that you should have a field to store what base it is. Because if you dont, you will have issues when you use the Max () command to set the next number.
For a new record, you will have to use Max () on the self join to figure out what the highest value you have in your base field and then add 1.
base ( 1,2,3,4,5, etc )
display ( 1, 2, 2-1, 2-2, 3, 4, 4-1, 5 ) The display will take the base and add the self join ( keyed on base ) to it.
Please keep in mind what I said iin my earlier about keeping a true unique serial I. This kind of mock id system should only be for display. You may run into issues in a multi-user environment as people can create new and duplicate records at teh same time.