3 Replies Latest reply on Jun 18, 2013 10:09 AM by philmodjunk

    Number series that allows to repeat number for sub-categories



      Number series that allows to repeat number for sub-categories


           Hello Filemaker braintrust!

           I am not sure how to title this post!!

           I inquired about this scenario a couple of years ago, but was unable to come up with a solution - I figure I will try again now that I have a bit more understanding of the FM language (but I'm still a novice!). 

           My database is used to organize 'archival footage clip' records. These clips come compiled on a tape let's say. I have to differentiate what 'TYPE' of tape in terms of whether it is a 'screener tape' or a 'master tape' or a 'scratch source tape' and so on. I do this by using a numbering system as it is easier down the line for other purposes. So when the TAPE comes in I immediately give it a number in the 1000, 2000, 3000 etc category (1000 might be screener, 2000 for master and so on).

           After numbering the TAPE, the CLIPS are numbered sequentially as they appear on the tape. Thus a tape with 3 clips would end up with 3 records in my database. In the past I had a field for the tape # 2001 for example, and a field for the clip # (01 for first clip/record, 02 for second clip/record) and just entered the information manually. I created a layout that listed the tape #s, sorted sequentially so I could see what  2000, 3000 or 4000 etc number would be next available for use.  

           But I just know there is an easier way! My problem is implementing! 

           I would be open to simply using one series of numbers for the tape # and having a field to specify S for screener, M for master, X for Scratch source for example, if that is easier but the tape # has to be able to be repeated as some tape #s will have more than one clip (thus need more than one record in my database).

           The tables I used in the past were basically just two: Source (who gave me the tape) and Tapes (and within tapes I had layouts for the clip records, and the sequential list of the tape #s so I could see what was the next number in line).

           I'm wondering if there is a way to set this up so that I can tell Filemaker to assign the next sequential number in the series, based on the 'type' of tape (screener 's', master 'm', or scratch 'x') while allowing me to duplicate the tape number for each clip record needed for the tape (sometimes 1, sometimes 10 or more clips).


           Any thoughts? If this topic has come up before and been resolved I would appreciate a link to the discussion! Otherwise, any tips would be greatly appreciated. Please remember I am still a novice and doing lots of Lynda.com tutorials but having difficulty applying to my particular situation!

           many thanks,


           I am attaching a shot of the sequential tape list layout I made that allowed me to know what number to use next...



        • 1. Re: Number series that allows to repeat number for sub-categories

               Is there even the slightest chance that two users might be numbering clips from the same tape at the same time?

               There's a method using relationships and an auto-enter calculation that can do this, but it can result in records with duplicate values if two users do the same operation at the same time.

          • 2. Re: Number series that allows to repeat number for sub-categories

                 I would likely designate one user as the "tape numbering master" to eliminate any conflicts.

                 Just in case it matters, I'll also mention that often the clips are previewed and a clip record is created  for log notes. This record is not numbered. The tape # and clip # fields are left blank until the tape arrives (if the clip is ordered). Once the tape arrives and is numbered, then the number is applied to the clip record. 

                 I wonder if that step does make my old system the best option (though ripe for human error, I know).

                 thanks for your help!


            • 3. Re: Number series that allows to repeat number for sub-categories

                   It is a significant detail as I was going to suggest an auto-entered calculation that assigns the next clip number in the series for a given tape number, but that needs to take place when the record is created.

                   Instead, you can use this method:

                   Presumably, you have these tables/relationships already:


                   Tapes::__pkTapeID = Clips::_fkTapeID

                   You can then create a new Table Occurrence for Clips named "Clips|SameTape" and link it in like this:


                   Clips::_fkTapeID = Clips|SameTape::_fkTapeID

                   Then the following script can assign the next number in the series for a given clip:

                   If [Not IsEmpty ( Clips::ClipNumber ) ]
                      Set Field [Clips::ClipNumber ; Max ( Clips|SameTape::ClipNumber ) + 1 ]
                   End If

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained