10 Replies Latest reply on Jan 5, 2009 7:49 AM by crispylx

    finding records between records



      finding records between records


      Hi, the database i get given is from another program in csv format and contains more than one table. Each table has a unique Start and End record. 


      How can i find records with certain criteria within (between) these specified records.


      There is probably a better way to do it and use SQL terms when importing the data into their own tables but I'm still new to filemaker and still learning.   


      Any help would be great, Thanks




      P.S. Sorry about the screen name, it was the only thing the forum would accept! 

        • 1. Re: finding records between records
          Jens Teich
             See http://filemaker.com/help/MacTOC3.html, FileMaker's find capabilities are quite powerful.

          • 2. Re: finding records between records

            Thanks Jens for your speedy response. I have been to each sub-link on the page you suggested but couldn't find the answer to my question. i had already looked through the manual for this. Perhaps, i can't see the wood for the trees.


            Or maybe i'm not being clear.


            If record 11 contained the value "start1" and record 21 contained the value "end1";  how can i say just find "x" between start1 and end 1? thus ignoring anything before record 11 and after 21?


            Thanks again 

            • 3. Re: finding records between records
              Jens Teich
                 Ah I didn't understand what you intend to do.

              You have two tables. One with search criteria FROM and TILL and want to find all records in a different table with VALUE between FROM and TILL, right?

              Create a relationship between both tables with a double condition:

              - FROM < VALUE

               - TILL > VALUE

              and you can show your values in a portal. Or isolate them in a list with the script step GoToRelatedRecord.

              Rereading your posting I saw that FROM and TILL are in different records. So transfer one of the two into the other record and keep it in a global field. Use this global field for the relationship.


              • 4. Re: finding records between records

                Ah great. Actually, there is only one table; but if i can use FROM and TILL in a find request then i can just say find me records with "Smith" in the surname field FROM record "start" TILL record "end" 


                Would that be right?


                Sorry, i am desperately trying to ingest the bible as quickly as i can.

                • 5. Re: finding records between records
                  Jens Teich
                     There are two different appoaches. Search via find mode and search via relationship.

                  I directed you towards the first approach with my first answer. To search a field for entries from FROM to TILL use

                  1. Enter find mode Cmd-F

                  2. Enter 'FROM ... TILL' without quotes.

                  3. Click FIND or press Enter.

                  This can be scripted and thus automated.

                  The second approach would be possible for you with two global fields FROM and TILL and the relationships from my second answer.


                  • 6. Re: finding records between records

                    Hmm, I've tried both your suggestions and neither of them are working ...yet (i have little doubt that it me doing something wrong)


                    The first suggestion seems to be on the lines of what i want but when i do your steps i just get the "No records found" pop up even if i copy the text in the field that exists. I've tried every variation i can think of. Could you placate me with more layman terms?



                    • 7. Re: finding records between records
                      Jens Teich
                         Can you please give some example data of the table you are working in? It is difficult to type tables here, but you will surely find a way to show three or four rows of example data.

                      Additionally you explain what you want to search and find.


                      • 8. Re: finding records between records

                        Sure, so here is an example of the table i get given:



                        1 109 1 cue label

                        1 110 1 cue  label

                        1 111 1 cue label



                        1 109 1 50

                        1 110 1 70

                        1 110 2 30



                        I've used tabs to separate fields.


                        Lets say i want to find a record between Start_1 and End_1 where the 2nd field is "110" and i want to ignore all records between Start_2 and End_2


                        Thanks for taking the time, 

                        • 9. Re: finding records between records
                          Jens Teich
                             Do we see 6 records which are divided into two groups?

                          You can only search the contents of the records. So an easy seach request would be
                          field2 = 110 AND field5 = label

                          If this does not work for larger sets you could introduce a serial number and search
                          field2 = 110 AND serial  < 4


                          • 10. Re: finding records between records
                               Sorry, the label field is a variable and so i can't use it in a find request. Also, i'm afraid i don't understand your use of the serial. The number of records between the start and end record is variable. Does this mean i cant use find between 2 specified records if i just wanted to view the records after Start_1 and End_1?