13 Replies Latest reply on Jul 11, 2011 2:49 PM by cproject

    drop down menu containing serial number options



      drop down menu containing serial number options


      Disclaimer: I apologize as I am very new to Filemaker - please keep this in mind when instructing me on my issue as I do not understand the formula/function language... I will likely need basic step-by-step instructions.  Feel free to tell me if this is worth the $45 fee that Filemaker support charges.

      I am trying to adapt a template for a tape archival database for research on a film.  This is the challenge I have at the moment:

      I have a field for "tape number" (Each main data entry page is for a different tape, so the tape number will relate the record to the physical piece of media - dvd, digibeta etc). Ideally I would like this field to have a drop down or pop-up menu with six options/categories. Each category represents a different type of tape and would need to be numbered sequentially WITHIN that category. 

      1000 (would number 1001, 1002, 1003 etc)

      2000 (would number 2001, 2002, etc and so on for each below)





      Each option would work like the serial number -on commit -option that is offered when you set up a number field.  So if you clicked on tape number and then clicked on the drop-down menu 1000, the number that showed in the box would be 1001 or whatever number was the next available sequentially within the 1000 category. 

      I am wondering if this is possible or if I need to make two separate fields, one that specifies that it is the 1000 category, and the next field then acting as a serial number field, numbering that 1000 category sequentially.

      Please let me know (if this makes any sense at all) how I can make this happen.  Remember I need super simplified instruction.  Thank you!!!

        • 1. Re: drop down menu containing serial number options

          From an interface design stand point, would it not make more sense to select a media type such as dvd, digibeta, etc and then the new record receives a number in the appropriate series? (Why make the user remember which number series goes with each type?)

          Do you truly have to use a different number series for each media type? (Perhaps to support existing practices?)

          If you didn't use such a series, but used text identifying the different media types instead, then this becomes less work to set up.

          Will this be a single user database or might multiple users all be doing this same operation at the same time?

          • 2. Re: drop down menu containing serial number options

            the categories don't represent the differnt media types, they are categories for the type of material (archival screener, archival master, fair use source, scratch source etc). This info is useful in numeric form for when this gets passed on to the editors.  they tend to not use filemaker so it's a way of easily transferring the info in numerical form. 

            regardless, i'm just wondering if what i'd like to do is possible in filemaker or not...

            • 3. Re: drop down menu containing serial number options

              oh and it is multi-user - probably 4-6 people but 2 people doing most of the entry.

              thanks for your help!

              • 4. Re: drop down menu containing serial number options

                You could use a scripted scheme of 6 serial number tables. These tables will have an auto enter serial number and a userid field that is auto enter Get(AccountName).

                You could script trigger an OnObjectModify dropdown list of Media type to Go to the appropriate table (A case statement could set a global field to the approprieate layout name)  then use the Go to Layout by calculation using the Global field to navigate to to the layout

                then  create a new record request creating a new serial number

                 then pass the new serial number back to a global field

                and then set your films record number to that serial number.

                You could also write back to the related record the serial id from this table to complete the circle

                They idea is still full of holes where serial ids could be wasted etc. (Suppose they selected the wrong type) But with the userid (and possibly a couple of other fields) you could at least justify any gaps.

                You could also rather  than use the script trigger you could ensure that the type is correct by having them press a "get serial number" button.

                A little more "clunky" but would cut down on  the "mistakes"


                • 5. Re: drop down menu containing serial number options

                  It's possible, but since it's not possible to use FileMaker's built in serial number system "off the shelf", special care has to be taken in a multi-user environment or your system can possibly generate duplicate numbers if two users try to generate the next number in the same series at the same time. Thus, life is simpler if you avoid that approach.

                  I don't think you fully understood my first question. Whether this is a "media type" or "type of material" isn't the issue. What I'm suggesting is that it will be much easier for your users to specify the "type" and then let FileMaker assign the appropriate number from the associated number series. Thus, your value list would list values such as: archival screener, archival master, fair use source, scratch source etc instead of numbers.

                  First, select the Unique Values validation option on this number field. This is our final line of defence to make sure that two records don't get the same value. If we design our system right, this validation rule will never actually kick in and display an error message, but we'll need it in a script that checks for possible duplications.

                  Add a table of your "types" with at least Three fields: Type (holds values such as "archival screener, archival master, fair use source, scratch source etc"), Initial Value ( 1000, 2000, 3000, etc) and cMaxValue a calculation we'll set up in a moment. You'll create one record for each Type of material and this table should be the source of the values in your value list.

                  Define a relationship to your existing table like this:

                  Tapes::Type = Types::Type

                  Now you can return to field definitions and define cMaxValue as Max ( Tapes::SerialNumber )

                  Use a script like this to create a new record and assign it your serial number:

                  New record/Request
                  Set Error capture [on]
                    Set Variable [$Tries ; Value: $Tries + 1]
                    Set Field [Tapes::SerialNumber ; Max ( Types::InitialValue ; Types::cMaxValue + 1 )]
                    Set Variable [$ERR ; value: Get ( LastError ) ]
                    Exit Loop If [$ERR ≠ 502]
                    Exit Loop If [$Tries > 100 ]
                  End Loop
                  IF [$Tries > 100]
                     Show Custom Dialog ["Error assigning serial number: " & $ERR ]
                  End I

                  This script assumes that Tapes::SerialNumber is of type number, not text.

                  • 6. Re: drop down menu containing serial number options

                    Thanks for suggestions:

                    TO PhilModJunk: I think I'm getting what you are saying - can I repeat back to you so you can let me know if I'm getting it?  For my example I will just limit to 3 types to make it easier to follow. 

                    Let's say the types are Production Footage,  Screener and Master.  Let's say I want any record listed as Production Footage to get a sequential number beginning with 1000, Screener to get a number beginning with 2000 and Master to get a number beginning with 3000. Is that what your formula will do?

                    I just learned of another issue that may change some of this. 

                    There will be instances when I will have more than one record for the same tape number assigned.  Now I could add an additional field to differentiate between the records (so the three fields might be, production footage / 1001 / 01  on one record and the next record from the same tape would be production footage / 1001 / 02) And this third field could be manual and that's fine.  But because of this I'm wondering would it make more sense (ie be easier) rather than have filemaker assign the sequential number for the material type, just ask filemaker to let me know what the next number would be so I can manually enter it?  or is that even more complicated?

                    thanks for your help - I hope I'm making sense.

                    • 7. Re: drop down menu containing serial number options

                      Why would you have two tapes with the same serial number? What does that represent? Such a change complicates the validation rule used to help keep two users from generating the same serial number for different records.

                      Oh yes, and before I forget again to add this comment: Do not use these serial numbers we are discussing as primary keys for your database. Add an auto-entered serial number field to your tapes table and use it to relate records in it to any other records--not this serial number process we are discussing here. Use this "type specific" serial number only as a label field to help identify the tape it represents.

                      • 8. Re: drop down menu containing serial number options

                        Sorry - let me clarify: the database is actually logging the info on the tapes.  So each record is logging a different segment of visual material (some tapes will have more than one segment, and each segment needs its own record).  Thus the tape number may be repeated on more than one data entry record. The tape number will allow us to know which tape this record is located on, but also tells someone NOT using the database, what type of material is on this tape just by looking at the tape number.


                        • 9. Re: drop down menu containing serial number options

                          It seems you have more than one record for the same tape and one record can represent more than one tape. Thus, you appear to have two distinct items here: Segments, and Tapes. I would use a separate table for each with a join table used to list the tape or tapes that contain a given segment record. This numbering system would then apply to the tapes table and not directly to the segments table.

                          Does that make sense?

                          I'm still not clear, though on why you would number two tapes as 1001 / 01 1001 / 02 instead of just 1001, 1002 if the number only identifies the type of material as opposed to it's content.

                          "The tape number will allow us to know which tape this record is located on, but also tells someone NOT using the database, what type of material is on this tape just by looking at the tape number."

                          I'm always amazed and amused when people decide to use numbering systems as labels to communicate information directly to other people. (As opposed to using it as an index to look up information from a computer or at least from some reference source.) I realize this may be an existing system that you cannot change, but if it isn't, wouldn't it be simpler to indicate the type of material by putting a label on it in plain English that labels the type of material rather than a cryptic number?

                          • 10. Re: drop down menu containing serial number options

                            Thanks PhilModJunk:

                            The tapes wouldn't have more than one number - just one tape number per tape.  The thing that needs to be differentiated are the segments of video on the tape itself (there might be an interview from a news show, then a cip from a tv show, then another interview all on one tape delivered from an archival house).

                            The data entry record is for the segment on the tape - for instance a particular interview - so this might be tape #2001-01 and then we might log the clip from a tv show that is on the same tape, but make it a different data entry record, #2001-02. make sense?  As per my earlier example, when the editor received the info, they would know that this is an archival screener tape that we logged at least two entries for.

                            I like your idea of the tapes table and joining that to the main data entry record (logging the segment). How would I go about making that happen?  I know how to create a table, but then what kind of formula do I need to input and how to relate it to the main entry?

                            thanks again - super helpful!

                            • 11. Re: drop down menu containing serial number options

                              Actually its not so much that people use numbering systems to communicate info directly  to other people. Its people who learn to see the pattterns in data that have been used to improve efficiency of some process.

                              Prefixes/Suffixes/Patterns exist so that there can be some identification of records for processing reporting etc. It's humans that have learned to use these patterns as recognition of the intelligence behind them.

                              Computers handle "codes" much easier than the language equivilent. So humans have learn to associate the "code" with the language. Its what we are good at. (making associations).

                              A truely intelligence free numbering system would produce great problems for computers as well since ultimately a human needs to tell it what to do with the data.

                              Its ultimately about usefulness, without a human being able to interpret the data (even if its for the purpose of building a better program) there would be no point.

                              The more familiarity with the processes the easier it is to "read" the coding.

                              • 12. Re: drop down menu containing serial number options

                                Yes, but it's not a physical tape with a label that reads 2001-01. It's a segment stored on tape 2001   that is number 01 because it is the first segment. Thus, the full label is a segment label, not a tape label. Correct?

                                Thus, you do need your two, possibly three tables to log all this information. Each tape record will document one physical tape, with it's number and type recorded, plus any other data specific to that physical object. The Segments table would document each segment and be linked via a relationship to the tape record on which it is stored. I don't think you'll have any segments that span more than one tape. Is this correct, or is it possible to have a segment start on one tape and then continue on another?

                                Assuming you never start a segment on one tape and continue it on another....

                                Tapes::__pk_TapeID = Segments::_fk_TapeID

                                __pk_TapeID is NOT the serial number we discussed at the beginning. This is our primary key and should be defined as an auto-entered serial number field. Define a second field, I'll call it TapeLabel and use the method I described at the beginning to control how it is numbered. Define a numbre field in Segments as TapeSequence. Enter the sequence number of the segment here.

                                When you want to combine the two values into the labelling format you've described, combine the two values with a calculation field like this:

                                Tapes::TapeLable & "-" & Right ( "0" & TapeSequence ; 2 )

                                • 13. Re: drop down menu containing serial number options

                                  thanks I'll try this and see if it works out.

                                  Much appreciated!