1 2 Previous Next 15 Replies Latest reply on Sep 20, 2011 9:10 PM by LaRetta_1

    Search within day ranges.



      Search within day ranges.


      I have a text field with ranges of numerical dates with carriage returns. For example:

      734358 ... 734366
      734365 ... 734368
      734371 ... 734387
      734392 ... 734396
      734403 ... 734406

      I can't use any relationships or create another field, by restriction. But I must find if another numerical date is among the above ranges. Lets say 734360 for example which is within the first range.


      Is there any way to do this? May be by using patterncount somehow...

      Thanks in advance.



        • 1. Re: Search within day ranges.

          Why not use a set of records with two date fields in each records instead of this list of number ranges? That would greatly simplify this task.

          I think that you'd need a script or recursive calculation that looped through these values stopping either on the first true result or when the end of the list is reached in order to use the current set up.

          Here's a script example:

          Set Variable [$RangeList ; YourTable::yourTextField ]
             Exit Loop If [$I > valueCount ( $RangeList ) ]
             Set Variable [$I ; Value: $I + 1 ]
             Exit Loop If [Let ( Range = GetValue ( $RangeList ; $I ) ;
                                YourTable::DateField > GetAsNumber ( LeftWords ( Range ; 1 ) ) And
                                YourTable::DateField < GetasNumber ( RightWords ( Range ; 2 ) ) ]
          End Loop
          If [$I < ValueCount ( $RangeList ) ]

             #Date in date field is within a listed range


             # Date in date field is not within a listed range

          End If

          • 2. Re: Search within day ranges.

            "I can't use any relationships or create another field, by restriction" - that's quite a significant restriction...

            Why not do it by script (This assumes you have access to a global field for temporary data capture, but if you haven't you can get round that):

            Enter browse mode

            Show Custom Dialogue [ "What date do you want to search for?" [gTextField] )

            Set variable ($DateToFind ) ; gTextField )

            Show all records

            Go to record [first]



            Left (DateField ; Position ( Date Field ; "[returncharacter]" ; 1 ; 1 ) - 1 <= $DateToFind
            Right (DateField ; ( Length ( Date Field) - Position ( Date Field ; "[returncharacter]" ; 1 ; 1 ) ) >= $DateToFind

            Omit Record


            Go To Record (Next ; Exit after last )

            End If

            Show omitted

            It needs tidying, for example if no records match the date.

            Just check which record becomes the active one when a record is omitted: I have assumed it goes to the next record (rather than the previous).
            Anyway: it might get you started on an idea.

            • 3. Re: Search within day ranges.

              PhilModJunk, your answer is very good and thanks for it Wink

              Let me make it a little more difficult! What about if I must find if the date is within the ranges but inside a text or calculation field?


              I mean that I already have the field with the ranges (lets say "DayRanges") and within another field (lets say "Result") I must give a "1" if the given numerical day is within the ranges of "DayRanges". Is there an solution for this?


              • 4. Re: Search within day ranges.

                The best solution is to use a table of related records with two date fields to document each range. Then a simple check for a matching related record will work for your calculation field.

                Yourtable::PrimaryKey = DateRanges::ForeignKey AND
                DateField > DateRanges::Date1 AND
                YourTable::DateField < DateRanges::Date2

                Then a reference to DateRanges::Date1 will be either a number (True) or empty (false).

                Otherwise a recursive calculation would be need to loop through the list of values.

                • 5. Re: Search within day ranges.

                  Sorbsbuster, I am working on a database that already is in use, so I have many restrictions. I cant use Perform Find or Ommit records. We are talking about a database with hundreds of thousands of records that is used by a great number of users in the same time around the world. So Find and Ommit is prohibited. Creating new tables is also prohibited.

                  So, is it possible to do it within a single field?

                  Can you thing of something?

                  Something like Patterncount ("734358 ... 734366" ; "734360") ?

                  Thanks for all your answers.


                  • 6. Re: Search within day ranges.

                    Maybe it's not important that I understand, but why can you not perform a find, just because the database is in use?  And why can't you omit records?  But can you design scripts in this database?  Also, my suggestion didn't need you to perform a Find - although I wouldn't suggest you use it for x00,000 records.

                    With so many restrictions I would suggest you need to extract all the data you want as a result of the search into another Filemaker file (which you have full control of) and then do the search there.  But I'm still baffled by the restriction on Performing Find.  A Find by definition omits records - those that don't match the search criteria are omitted -  so even if you could do something with a single field, how would you isolate the result to show the user?

                    • 7. Re: Search within day ranges.

                      We have tried Perfom Find and ommiting records, but the database gave us long times of waiting. Can you imagine what is going to happen when, lets say 1000 users are performing find simultaneously? And another 1000 are ommmiting records? The database's perfomace drops down dramatically.

                      I' m considering creating a Value list with the range of dates and have results using PatternCount...

                      Thanks for all!

                      • 8. Re: Search within day ranges.

                        But you don't have any "pattern" that matches in pattern count--which is strictly a text based matching function.

                        Using your last example: Patterncount ("734358 ... 734366" ; "734360") will return 0 as 734360 is not present in the quoted string of the first parameter.

                        usually, the most dramatic improvments in system responsiveness require structural changes to your database. Given the size of your user base, this is not nearly so simple an operation as modifying the structure of a single user system, but it can be done and may be your only practical option.

                        • 9. Re: Search within day ranges.

                          I know that 734360 is not present in 734358 ... 734366. I use "..." meaning range.

                          I am now looking at your 7:20 answer. Seems nice... 

                          Can you explain it a bit more?

                          • 10. Re: Search within day ranges.

                            It would help to know more about what the data in your text field represents and how it is used.

                            I've assumed that each record in your current table has a unique list of date ranges that applies only to it. Thus the first pair of fields match by Primary/foreign keys so that each record in your current table would match to a unique set of date range records in the second table. The second two field pairs match by range a given date will lie within a specified range if it is greater than or equal to date1 and less than or equal to date2. Thus, the last two field pairs in this relationship reproduce the 734358...734366 type of date range for one such line in your original text field.

                            Please note that this set up may not be the best alternative here, but I'd need to know more before I could suggest a modified approach.

                            Note that date fields store dates as the numbers that you have used in your examples, so you can keep them as dates and do not need to convert them to numbers and you can still use them in the same way.

                            To set this up, you'd need to write a script that creates one record in the related table of date ranges for each range in your current text field. Once you have done so and modified your database design to store these ranges as records in this related table instead of the current text field, you can use the method described in that post. (On my screen, this is the "9:20 post" Wink )


                            • 11. Re: Search within day ranges.

                              PhilModJunk you are not only two hours ahead, but genarally ahead, Smile

                              Unfortunatelly, the database is used worldwide and I can not reconstruct it. I' ve been called to do some improvements. For example, in many occasions the database in order to give results, please sit down..., 45 seconds needed. I drop down the time by 40% in 25 seconds. If I manage to solve this problem the time will be less than 3 or 4 seconds. As you can imagine we are talking about a huge amount of data!


                              I wish to thank you, but I must leave now. If you are interested in our conversation, I may come back tommorow with more details. 

                              The only bad thing is that I have a deadline until Thursday night.


                              Thanks for your suggestions.

                              Kostas from Greece

                              • 12. Re: Search within day ranges.

                                Ideally, as discussed, those range strings should be From and To dates in a related table where every line is a record.

                                The problem with searching for a number within a text range such as 734358 ... 734366 is that the number does not exist within the field (and that is why records are best).  But you CAN use recursion to check the spans on each line to see if the number is within the range (see link).  You will need to use a global field to accept user request.  You need Advanced to create a custom function in your file.

                                For more flexability, I created the function to count the number of times this exact number exists within each line.  But you will still need to search this calculation for >0 after entering your number.



                                • 13. Re: Search within day ranges.

                                  You don't need to reconstruct it, just add a new related table. This may not be as big a job as you think, but can depend on how the original table is set up.

                                  Even widely used, 24/7 systems have to go down for maintenance once in a while. If you can use a script on a back up copy, you can build the records in this table on a development platform, then deploy it during such a down time. If you have Modification time stamp fields in this table, you can then run a second update script on just the records with modification time stamps later than that of the copy used to create the original record to do a final update and you are good to go.

                                  • 14. Re: Search within day ranges.

                                    LaRetta, you are amazing !!!!!!!


                                    ... and a life saver!!!

                                    Thanks, I am obliged! What can I do for you?



                                    1 2 Previous Next