10 Replies Latest reply on Dec 14, 2009 9:05 AM by philmodjunk

    Updating an Auto-Enter Serial Number if Deleting Records?

    jdanniel

      Title

      Updating an Auto-Enter Serial Number if Deleting Records?

      Post

      Hello.

      I'm brand new to File Maker 10 Advanced, and am running it on Windows 7 Home Premium.

      May I please ask what I hope are two very basic and rudimentary questions?

      My first File Maker 10 database is a very simple flat-file movie collection database.

      The fields are as follows:

      Index Number
      Title
      Director
      Genre
      Location

      These are the only fields I want or need.

      I set the Index Number field to “Number” and have it set to auto-enter. I am using the Serial Number on Creation option.

      The Next Value is 1, increment by 1.

      Question # 1:

       

      If I delete a record or records, does File Maker adjust the list so that the records are renumbered in chronological, sequential order? This is what I want, so if I am using the Auto-Enter feature incorrectly, please let me know, and could you tell me what to use instead?

      My goal is to have File Maker Pro not have any gaps with the numbering. Is this possible? 


      Question #2:

      When I click the NEW RECORD button, the boxes where I enter data appear. These boxes are not solid...they are dotted. The problem is, if I click anywhere in the form outside of these boxes, the boxes vanish, and reappear only when I click my mouse.

      Is there a way to change this so they appear on screen all the time? Also, can I change their look? Can I change the borders, for example, or even give the borders a different color? If this is possible, how do I do that?

      Thank you very much for your assistance. 

       

      (Please feel free to call me Jd.) 














        • 1. Re: Updating an Auto-Enter Serial Number if Deleting Records?
          comment_1
            

          Serial numbers do not adjust when records are deleted - nor would you want them to. These numbers serve as unique record identifiers, used - among other things - to link related records in another table. Having them change would be disastrous, as the actors of "Gone with the Wind" would now be listed under "Forrest Gump".

          Why do you need a chronological sequential numbering? There shouldn't be any importance to the order in which movies were entered into your database. You can always sort them any way you want. As for gaps: suppose you do a find for comedies only - you will have gaps in the numbering, even if you haven't deleted a single record.

          You can always have your records display a sequential number, either by typing @@ on the layout, or by using an unstored calculation of Get(RecordNumber). Such numbering is produced "on-the-fly" - it will change with your found set and/or sort order.

           

           

          ---

          See also:

          http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&thread.id=11821


          • 2. Re: Updating an Auto-Enter Serial Number if Deleting Records?
            jdanniel
              

            After playing with the database for a little while, I realize I don't need sequential numbering.  I'm not sure if I even need an Index number for each movie!  At the top of the form, it shows how many records I have, and that's what I am interested in.  

             

            So, forget about the numbering. 

             

            As for the formatting of the boxes:  I tweaked them in various ways to see what I liked, and I found a combination that appeals to me.  

             

            I guess I answered my own questions!

             

            But I'm sure I'll have more, as I start dabbling with relational databases.  I just started with this flat-file db to get my feet wet.

             

            Oh, you know what...I DO have another question:  If I add a value to a value list, will the list in the records be automatically updated to reflect the changes?

             

            Thanks again!  Jd 

            • 3. Re: Updating an Auto-Enter Serial Number if Deleting Records?
              comment_1
                

              jdanniel wrote:

              I'm not sure if I even need an Index number for each movie!


              You will need it if your solution ever grows up from a flat file into a relational database.

               

               


              jdanniel wrote:
              If I add a value to a value list, will the list in the records be automatically updated to reflect the changes?

              No. A value list is a list of possible choices. Each record stores the selected choice/s.



              • 4. Re: Updating an Auto-Enter Serial Number if Deleting Records?
                jdanniel
                  

                Sorry, I didn't ask the question very well.

                 

                I found out the answer to the question, but I'll ask it here anyway, just so people can follow what I'm doing.

                 

                I created a value list, and then used it to create a set of radio buttons on my form.

                 

                The value list has nine entries.  This means there are nine radio buttons in the group.

                 

                Now...if I go into the Value List and add three more entries, then will the group with the radio buttons be updated to show 12 options instead of nine?

                 

                That's the question I originally meant to ask.

                 

                The answer is yes, because I tried doing it, and when I did, the radio button group was updated and now shows the most current value list entries.

                 

                Jd 

                 

                 

                • 5. Re: Updating an Auto-Enter Serial Number if Deleting Records?
                  comment_1
                     Ah, I see. Yes, that's correct - and also the opposite: if you delete an item from the value list, the field contents will not change. But a field formatted as checkboxes/radio buttons will not display the item, even if it's there.
                  • 6. Re: Updating an Auto-Enter Serial Number if Deleting Records?
                    user14360
                      

                    Just wanted to add to Comment's comment!

                     

                    You'll definitely want to keep the ID number.  I started out a while ago thinking what's all the fuss about the ID numbers, and was using names and all sorts of other text things as keys for relationships.  Now that I'm working with lots of relational database elements, I understand the importance of a unique identifier that will never change.  

                     

                    I had to go back and reassign ID numbers to each of the records and then find all their matching records in the other tables and update them accordingly.  Not fun, and prone to errors!  So definitely start using unique ID numbers/codes from the beginning, even if you think you won't need them.  There's no harm, and you may well end up wanting to make your database into a relational one in which case you will definitely need them. 

                    • 7. Re: Updating an Auto-Enter Serial Number if Deleting Records?
                      HugoLidia
                         Don't forget, even if you don't create a unique ID record or number id field in your database, filemaker automatically creates and stores ones!
                      • 8. Re: Updating an Auto-Enter Serial Number if Deleting Records?
                          

                        HugoLidia wrote:
                        Don't forget, even if you don't create a unique ID record or number id field in your database, filemaker automatically creates and stores ones!

                        Yes that is true, but if you ever have to retrieve data from a backup by importing recors into a set of data, these ID's will most likely get replaced. So it is still better to incorporate some sort of unique ID in every table.


                        • 9. Re: Updating an Auto-Enter Serial Number if Deleting Records?
                          comment_1
                            

                          menno wrote:
                          if you ever have to retrieve data from a backup by importing recors into a set of data, these ID's will most likely get replaced. So it is still better to incorporate some sort of unique ID in every table.

                          Indeed:

                          http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=11828#M11828

                          • 10. Re: Updating an Auto-Enter Serial Number if Deleting Records?
                            philmodjunk
                              

                            A note of caution on formatting a field with radio buttons/check boxes. As comment has correctly stated this shows the list of possible values that can be input. Note that such a value list can obscure values entered at an earlier point in time if you edit your value list to remove that possible value.

                             

                            Example:

                             

                            Value list has two items: Red, Yellow

                             

                            Create three records and select "Yellow" as the value for the third record. Now update the value list to be: Red, Purple, Orange.

                             

                            The checkboxes or radio button values will show the third record as being empty, but "yellow" is still stored in that field--as you will discover if you perform searches, sorts or simply remove the value list format from the field.