4 Replies Latest reply on Feb 1, 2010 12:05 PM by Pitman

    Changing placement of record in database



      Changing placement of record in database


      I am entering information about various letters that were published in a book.  I noticed that I forget to enter the information about one of the letters, and I would like to enter the information about this letter in the order which it appears in the book.  Is there a way to rearrange the order of the records in a database or insert this new record in its proper place?


      Thanks for any help.


      I am using Filemaker Pro 10 on Mac OS. 

        • 1. Re: Changing placement of record in database

          Basically a database puts new things at the end of existing things; kind of common sense. How you want to "see" it is another matter. The most common way to change the order in which data appears is to sort it by some data. In this case you said, "order in which it appears in the book." So you'd need to a data field in Letters which contains that info, which you could edit. Then you could Sort it. 


          Since we don't know what your data structure is, Book vs. Letters, we can't really say the specifics of how to get a default order (unless you want to just enter it manually). But I'd use an auto-enter serial ID field for Books, and one for Letters. You'd need Number type fields to sort directly; Sort by Book, then by Letter; or you could create calculation fields from Text IDs which would sort correctly.


          Since the numbers would be consecutive, you would use a number with a decimal in order to "insert" a letter.






          That's the basics. Not pretty, but it works. Or you could go further, and create a separate* field in Letters to auto-enter, by calculation, with [ ] "Do not replace existing value" off, to produce a "count" field for a Book's Letters. So that Book 1 could have 3 Letters, with "1, 2, 3" in that field, and Book 2 could have 4 Letters, with "1, 2, 3, 4". But you'd still use the same decimal technique to "insert" or "reorder."


          This more advanced technique would allow you to Sort, then Replace, with Serial Numbers option, to change the decimals back to integers. It would then be:



          2 (was 1.5)

          3 (was 2)


          * Obviously this would NOT be the real auto-enter serial (which would just increment in Letters by 1 consecutively forever), nor would it be used in any way to identify a Letter record, other than visually; for 2 reasons: 1. It changes, and 2. Auto-enter by calculation, using a self-relationship (which it would) is not an iron-clad method of identification.

          • 2. Re: Changing placement of record in database

            Thanks for your response.  I do have a data structure which is like this:  xxx 1:3, with "1" being the book volume and "3" being the letter.  When I sorted the records I ran into the following problem.


            The records are similar to this: 

            xx 1:1 through 1:9

            xx 1:10 onwards 

            xx 1:20 onwards


            When I sorted the records I ran into the following issue.  The order in which they were sorted was:


            xx 1:1

            xx 1:10

            xx 1:100


            I would want it to be 1:1, 1:2, 1:3, etc. 


            • 3. Re: Changing placement of record in database

              What you are doing is sorting Text and expecting it to sort like numbers. It doesn't. So, you have 2 choices. Make your Letter number be a Number field (maybe Book field also), and sort them separately, Book # first, Letter # 2nd (this is what I'd do). Or leave it as is, but create a calculation field to pad the text to make it sort "naturally". This involves pulling it apart then putting it back together again; which is more difficult since you stuck them together. So...


              1. Do you have them separately as Number fields?


              If so, just sort as above. 


              2. Are they separate, but Text fields?


              If so, create calculations to pad them; then Sort separately, or put them together padded. Or calculations to coerce them to numbers, and Sort separately.


              3. If they are not separate, are they really: book#:letter#? 


              If so, create a calculation to take them apart, at ":", pad each, then put them back together.








              Assuming a maximum for Book # of 99, and a max of Letter# of 999. If more, then more padding.


              Basic examples of padding for result of Text, which will sort "naturally":


              Right ( "0" & Book_num; 2)

              Right ( "00" & Letter_num; 3)

              • 4. Re: Changing placement of record in database
                   Thanks for your help.  I had mixed text and numbers and after I made a separate field for source no. it worked out fine.