Matching by name is not the best field to use in relationships. If the need to change the show name arises, you lose the link to your related cues table records.
What you describe isn't technically a serial number as it is not intended to unique identify the cue record. (The combination of showID and this number uniquely identifies the record.) Instead, it would appear that the value in the field is intended to identify the sequence for your cues.
Is this correct? This can be done with a self join relationship, but let's confirm those details first.
Also, have you considered this alternative?
Simply assign an auto-entered serial number to each newly created Cues record. The "insert" issue we discussed, can likewise be modified to simply insert cues and without trying to get the single decimal format we discussed there.
Then, when an editing session of the cues record is finished, you click a button and all the records for a given show are "cleaned up" to give them the desired sequence values. I suggest this option as it is fairly simple to implement.
What would you suggest to use as the match field for the cues? Something like cueShowID?
I like your idea much more than the one I was thinking of, but I am not entirely sure how to go about doing it. I tried to go about creating the cue number from an auto-entered serial number, but ran into trouble. I figured when I started creating cues for a new show I could just change the auto-entered number and it would auto increment from there. That was not the case, unfortunately.
Could you try to describe what you are suggesting in more detail? I believe what you are saying is something like this: create all the cues for a show and just accept whatever number is assigned by the auto-enter serial number and when done entering cues, hit a button that assigns new numbers beginning with 1 and ending with however many cue records there are. The only problem I have with this is that it is very important to keep track of the cue numbers while writing them because it is often done with another member of the production team that is recording the cue number you tell them, where you tell them to place it.
I will try to describe what I need to to in a step-by-step process so maybe it is more clear:
- A new cue is created. FMP checks to see if there are any cues already associated with a particular showID.
- If cue(s) are present, assign the cue being created the last existing cue number associated with a showID, incremented by 1.
- If no cue(s) are present, begin numbering the created cue at 1
- Increment the cue number by 1 for each new cue created associated with the showID.
I have a global variable named DesiredShow that I choose when opening the database and that, in this case, is the show that I am creating cues for. Not sure if that helps or not...
For the serial numbers, you would set up the field as an auto entered serial number and leave it's settings unchanged. The way you will use that field is that the numbers in this field will allow you to assign values that increase with each new record. You aren't interested in them looking correct, just as long as the cues sort into the correct sequence. Then, when you end your edit session, you click a button that performs a script that uses Replace Field Contents with the serial number option to assign serial numbers to your entire found set of cue records.
Please note that this process is quite different step by step from what you outline in the last post, but ends up with an identical result.
Set your Next serial value to a large number such as 1000. You want a value that will always be larger then any number that you end up with after you click the button to clean up the sequence numbers.
The last few Cue records in your found set might be numbered something like:
The gaps in the sequence might have been created by a second user generating cue records for a different show at the same time, or due to deleting a cue record. 1027.25 might appear after inserting several cues and then deciding to delete several leaving just the one inserted cue behine.
To clean up and get the numbers that you want, your script uses Replace Field Contents with the serial number option (Do not select the update entry options check box) to renumber them to where they might look like:
This assumes that you have a total of 29 cues in your found set for the currently selected show.