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 ) ) ]
If [$I < ValueCount ( $RangeList ) ]
#Date in date field is within a listed range
# Date in date field is not within a listed range
"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
Go To Record (Next ; Exit after last )
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.
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?
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.
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.
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?
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!
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.
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?
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" )
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
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.
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.