Jump to content

Finding records base on a single date


tottijohn
 Share

This topic is 5785 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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 by Guest
Corrected script
Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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 by Guest
Added update
Link to comment
Share on other sites

GTRR would work as well. :P

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

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 by Guest
Link to comment
Share on other sites

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

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*

Link to comment
Share on other sites

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 by Guest
More queries
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 [ ]

...

Link to comment
Share on other sites

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! :P 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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 5785 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.