1 2 Previous Next 19 Replies Latest reply on May 7, 2009 4:53 PM by madeline

    selecting range of records

    madeline

      Title

      selecting range of records

      Post

      I have a database of magazine writers, with a field with the history of their articles. It looks like this:

      Review history: 05/02, 07/10 letter, 08/04

      (that means the February 2005 issue, a letter in the October 2007 issue, the April 2008)

       

      I want to be able to select records from a certain period (like all the contributors for 2007 ofr from 2006 to 2008). Is there a way I can do that?

       

      Alternatively, how should I have set this database up to make this kind of thing possible in the future? There are about 1700 records, and some of them have as many as 10 entries in this history field.

       

      Thanks,

      Madeline

        • 1. Re: selecting range of records
          philmodjunk
            

          You definitely need to restructure your data if you want to search this information for a range of values.

           

          Create a related table of dates. Write a script that parses this text field into dates and enters them into this table or get a temp worker to type them in by hand.

           

          Display these date records in a portal and now you can create a find request to search by date or a range of dates.

           

          With date fields you can enter 5/1/05...5/25/05 to find all records that fall in that date range.

          • 2. Re: selecting range of records
            comment_1
              

            madeline wrote:

            that means the February 2005 issue, a letter in the October 2007 issue, the April 2008


            I don't know what that means. It looks like you want a table of writers, and another table for their articles - where each article would be separate record. And maybe another one for the issues - so that eventually your structure would be:

             

            Authors -< Articles >- Issues 


            • 3. Re: selecting range of records
              madeline
                

              Thanks a lot, both of you, for your speedy replies!

              By "table" do you mean another filemaker file?

              Parsing the field shouldn't be too hard, because all the items in the field are separated by commas. But I confess I'm still a bit confused about how to do it (and don't really know how to write a script, either).

               

              I probably have more questions, but let's deal with the table one first...

              Cheers,

              Madeline

              • 4. Re: selecting range of records
                comment_1
                  

                madeline wrote:

                By "table" do you mean another filemaker file?


                No, unless you are using version 6 or earlier. Starting with version 7, you can have multiple tables in a single file - and this is much more convenient that multiple files, esp. for beginners.


                • 5. Re: selecting range of records
                  madeline
                     But of course I'm using v6! Wouldn't you know it? In fact the file in question is one part of a database that combines three.
                  • 6. Re: selecting range of records
                    comment_1
                      

                    madeline wrote:
                    But of course I'm using v6! Wouldn't you know it?

                    Alas, my crystal ball is out for repairs... Anyway, in version 6 every table is a file, so the answer to your previous question would be yes.


                    • 7. Re: selecting range of records
                      madeline
                        

                      Yes, well, I should have mentioned it in my original post!

                       

                      But what I don't quite understand is how to make the table/file: would it be each issue in a separate field? a repeating field? Is there a sample somewhere online I might be able to see?

                      • 8. Re: selecting range of records
                        philmodjunk
                           I suggest reading up on Portals in your on line help file. With the right relationship linking your two FMP 6 files, you can use a portal to list each of your dates in a scrolling list. If you have text like "letter" that you want to preserve, I'd put that in a separate field from your dates. I'd also set the dates in actual date fields so that they can be more easily searched with a find request.
                        • 9. Re: selecting range of records
                          madeline
                             Okay. When I get a chunk of time I'll give it a shot. Thanks again for your advice -- and your speediness!
                          • 10. Re: selecting range of records
                            comment_1
                              

                            I am not sure how to answer this, because your original question wasn't clear to me. If my guess was correct, and you want to track each article's author and the issue it was published in, then you would have the three tables (i.e. files) I mentioned earlier. Each article would be a separate RECORD in the Articles table. In the Issues table, there would be a record for each published issue.

                             

                            Samples for version 6 are rather difficult to find these days, I think. 

                            • 11. Re: selecting range of records
                              madeline
                                

                              Maybe this would help -- go to http://files.me.com/mmmad/llvli8 and you'll see a screen shot of my three FMP6 files.

                               

                              "Articles.fmp" is the main file. Each record is one article. The pink fields are unique to that file.

                               

                              "Books.fmp" contains an individual record for each book reviewed (an artcle might review several so there are multiple fields). In the Articles file, the books fields are blue.

                               

                              "Rolodex.fmp" contains the writers -- each record is one writer, but we have many regulars so I picked one here who has written a lot over the years!

                               

                              So I want to be able to see at a glance how often a writer has written for us and in which issues, and I want to be able to, for example, search all the contributors from 2004 to 2006. I guess it could be done the same way I did for the books records, but I don't really know how I'd search for a range, and also if we have someone as prolific as this guy, there'd be an awful lot of those fields...

                              • 12. Re: selecting range of records
                                comment_1
                                   I am not sure I follow this fully, so let me say something in general: just about any time you find yourself putting several things of the same kind in the same field (such as your comma-separated list), it's time to open a new file and make an individual record there for each item on the list.

                                Same thing applies to a situation where you have many fields for the same thing, such as Book1, Book2, Book3, etc. - these too should be individual records in a related Books file.

                                This is the only way you'll be able to find contributors by issues, or by date range, or by any other criteria you can think of.

                                • 13. Re: selecting range of records
                                  madeline
                                    

                                  comment wrote:
                                  Same thing applies to a situation where you have many fields for the same thing, such as Book1, Book2, Book3, etc. - these too should be individual records in a related Books file.

                                  yes, that's how I set up the books info. Those blue boxes across the top of the "articles" window is an identifier number for each record in the books file. To populate the rest of the blue boxes in the article file, I just type in that number in one box; for a review with multiple books, I fill in a different number in each of those little boxes.

                                   

                                  So if I've got a record in the article file that has more than one book, the first box would say 11, the second 12, the third 13, etc. If I want to do a find for the book with the identifier 12, I have to put that in the second box -- if I search for 12 in the first box it comes up unfound.


                                  • 14. Re: selecting range of records
                                    comment_1
                                      

                                    I am really struggling to understand what you say, but I am not at all sure what you mean by those "boxes". If these are fields in the article record, then you're NOT doing it right. An article should be able to have ANY number of books - without being limited by the number of "boxes" you have chosen to define.

                                     

                                    The right way to do this is to have an ArticleID field in the Books table. In the Articles table, the ArticleID field would be the unique identifier of an article - typically a number field with auto-entered serial number. In the Books table, it would be just a number field. These two fields would be used to establish the relationship between the tables.

                                     

                                    If you set the relationship to enable automatic creation of book records, you can enter the reviewed books directly into a portal to Books placed on a layout of Articles.

                                    1 2 Previous Next