3 Replies Latest reply on Nov 5, 2012 7:17 AM by philmodjunk

    Creating serial number from calculation?

    RyanMcCoy

      Title

      Creating serial number from calculation?

      Post

           I have a fairly simple problem for you guys.  As you can see in the screenshot, I have a table that stores all my cues from all of my shows.  The cues each have a unique ID which cannot be modified and relates it to a particular show.  What I need to do is perform some sort of calculation to determine when creating a new cue if any cues exist for that show; if not begin numbering them at 1, if so, begin numbering at the last one present after incrementing by 1.  Hopefully you can understand what I am trying to achieve. If not, I can try to be a little more clear. 

            

           Thanks!

      ss22.PNG

        • 1. Re: Creating serial number from calculation?
          philmodjunk

               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.

          • 2. Re: Creating serial number from calculation?
            RyanMcCoy

                 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:

                   
            1.           A new cue is created.  FMP checks to see if there are any cues already associated with a particular showID.
            2.      
            3.           If cue(s) are present, assign the cue being created the last existing cue number associated with a showID, incremented by 1.
            4.      
            5.           If no cue(s) are present, begin numbering the created cue at 1
            6.      
            7.           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...

                  

            • 3. Re: Creating serial number from calculation?
              philmodjunk

                   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:

                   1025
                   1027
                   1027.25
                   1028
                   1030

                   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:

                   25
                   26
                   27
                   28
                   29

                   This assumes that you have a total of 29 cues in your found set for the currently selected show.