Finding records base on a single date

[tottijohn]

Most of the post here are basically finding records that fall within a range of dates. Right now, i need to search records base on a date that falls within a date range in the record.

E.g.A record with these fields: Arrival date = 20-12-06, Departure date = 25-12-06.

With the find mode, i need to find a specific day, say 23-12-06 and if this date falls within the arrival and departure date, will display all related records. If i input 26-12-06, it will not display the record above.

[Tim W]

Hi,

In find mode, place the date in each field with the symbols >= and <= respectively. Make sure both are on the same find request.

HTH,

Tim

[LaRetta]

Ranges aren't wanted - only a specific date in each. You can script this easily.

Set Error Capture [ On ]

Enter Find Mode [ pause ] ... allows User to enter a date in either Arrival or Departure

Duplicate Record/Request

Set Field [ Case ( not ArrivalDate ; DepartureDate ; ArrivalDate ) ]

Set Field [ Case ( not DepartureDate ; ArrivalDate ; DepartureDate ) ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

... do whatever if none found

Else

... do whatever with found set

End If

Edited November 21, 2006 by Guest
Corrected script

[tottijohn]

Tried this out, seems to work only if user enters date that relates to either the ArrivalDate or DepartureDate. No results if the find is between the ArrivalDate or the DepartureDate.

Sorry i have not much experience in fm but i am trying :P

Edited November 22, 2006 by Guest

[LaRetta]

Without asking a User to enter an Arrival AND a Departure date, how will you know where to end it? You could use recursion, I suppose. But I thought ... why wouldn't repeating fields work nicely here? Awhile back, Comment presented a similar use to extend out dates. Using that theory, I was able to extend the Arrival date in repeating fields but couldn't stop it. He just provided the answer for me (THANK YOU, MICHAEL)!

So the approach I suggest is: Create a calculation (result is Date) with as many repeating fields as you will ever need. I created one with 800 reps; they take little (no?) resources. Insert this calculation:

Let ( [

i = Get ( CalculationRepetitionNumber ) - 1 ;

d = Extend ( ArrivalDate ) + i

] ;

Case ( d ≤ Extend ( DepartureDate ) ; d )

)

This calculation doesn't need to be on the layout. Leave your Arrival and Departure fields there for the User to put the date in either field. Then adjust your script to:

Set Error Capture [ On ]

Enter Find Mode [ pause ] ... allows User to enter a date in either Arrival or Departure

Modify Last Find

Set Field [ thisNewRepCalc ; Case ( not ArrivalDate ; DepartureDate ; ArrivalDate ) ]

Set Field [ ArrivalDate ; "" ]

Set Field [ DepartureDate ; "" ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

... do whatever if none found

Else

... do whatever with found set

End If

I like repetitions more and more! There may be other ways of extending out dates for searching and I'd love to hear how. But, while in Find Mode, we can't specify a range without knowing both ends. By extending the dates, we can find any of them. Other ways to extend and turn two dates into a range? None are coming to me without hard-coding a multi-line and that seems so limited.

UPDATE: I can't remove the space in Get ( CalculationRepetitionNumber ) between 'b' and 'e'.

LaRetta

Edited November 22, 2006 by Guest
Added update

[LaRetta]

GTRR would work as well.

This is known as Delayed Intelligence ... something I suffer from more than I would like to admit.

It would require another field, however. If you are interested in adding another table occurrence to your graph (and a global date field), we can help you there as well. If your record count is quite large, a GTRR (Go To Related Record) would be a bit quicker, I think, and would 'overall' require less resources than an indexed repeater calc. Six of one ... half-dozen ...

Edited November 22, 2006 by Guest

[tottijohn]

Still trying to digest here, that's really out of my league

Let ( [

i = Get ( CalculationRepetitionNumb er ) - 1 ;

d = Extend ( ArrivalDate ) + i

] ;

Case ( d ≤ Extend ( DepartureDate ) ; d )

)

There is no reference to this calculation in the script, so what purpose does it serves here? *Getting confuse*

[tottijohn]

Alright, i see it. Its the "thisNewRepCalc" field. Back to more trial and error!

Set Field [ thisNewRepCalc ; [color:blue]Case ( not ArrivalDate ; DepartureDate ; ArrivalDate ) ]

I need to understand what this means...

Edited November 22, 2006 by Guest
More queries

[LaRetta]

Set Field [ thisNewRepCalc ; Case ( not ArrivalDate ; DepartureDate ; ArrivalDate ) ]

After entering Find Mode, the calc inserts into the thisNewRepCalc field the result of this logic:

We are looking for wherever the User inserted the date during the Find request. If it's not in ArrivalDate it must be in DepartureDate so use DepartureDate; otherwise use ArrivalDate because the date must be there. 'not ArrivalDate' is a boolean test to see whether there is an integer - any number (and dates are numbers) in the ArrivalDate. It's a quick way to test that the field contains a date.

[comment]

If I understand the original question correctly, the user inputs a SINGLE date, and wants to find records that are active for that date, i.e. those records where the searched date falls between their ArrivalDate and DepartureDate.

You COULD do this via a relationship from a global SearchDate to the repeating calculation field, but for a Find I think Tim's first reply was correct. For example:

Show Custom Dialog [ Buttons: "OK"; Input #1: Table::gSearchDate, "Enter a date to search for:" ]

Enter Find Mode [ ]

Set Field [ Table::ArrivalDate ; "≥" & Table::gSearchDate ]

Set Field [ Table::DepartureDate ; "≤" & Table::gSearchDate ]

Perform Find [ ]

...

[LaRetta]

Why yes it does.

It didn't when I tried it originally and I thought it should. But it works now. Time for some time off, I guess. :wink2:

[tottijohn]

Is it possible for this script to work if user is to find through the arrivalDate and departureDate field?

I tried Tim's method and its exactly what i want! But now i need to come out with a script so that user do not have to enter the <= or >= himself. Its alright if he has to enter two times, once in each date field.

[comment]

OK, now you have lost me. In my script, the user enters a single date into a single field, and clicks OK. The script takes care of everything else, including the <= or >=. If that's not what you need, please elaborate.

[tottijohn]

Sorry if i am not clear. Anyway right now, i am trying to write a find script with the set field functions to append a >= to the arrivaldate and <= to the departuredate. Thus when user activate this script, he will need to enter the date, e.g. 23/12/06 twice, once in both fields without having to type in >= or <= himself.

Set Field [Table1::ArrivalDate; "≥" & Table1:ArrivalDate]

There is an error here saying an operator is missing, but i have no clue which one....

It will be even better if the user can enter only one date to search in either of these two fields and still be able to use the above calculation. Time for me to keep trying!

[comment]

See if the attached helps.

example.fp7.zip

[tottijohn]

That works well! My script is long and messy and doesn't work.... Yours is so simple and it works!

Thanks comment! :)