How to get the script Find Mode to accept field as date value?

[Taipan]

Hi, I’m a little new to Filemaker so I hope this is not a foolish question. I am trying to figure out how to perform extended finds covering multiple fields based on the same date range. Or more basic, I’m trying to figure out how to have the find accept its date value from a field. I’m running FM 9 Advanced.

I have a client that wanted to record each date their patient came in for a visit for trials. We don’t want these overwritten, so I created fields the for 9 trial visits (Vist#1, Visit#2…) I want the user to be able to enter a beginning and end date to search for dates. For example all patient visits between 03/15/2009 and 04/18/2009 regardless if vist#1 or visit 2-9.

Besides the visit fields, I created date fields on the form, Bdate and Edate so he could set his range in one place, then I would create a script that would enter this range for each of the visits (Because visit #1 for one patient may start in January but for another patient start in June.)

I cannot figure out how to get the Find Mode criteria to accept the values in Bdate and Edate as part of the range. It keeps telling me that it must be in mm/dd/yyyy format and there doesn’t seem to be a method for me to specify a field or calculation. Any ideas on how to get the find to accept date values from a field? I don’t want the user to have to enter the same date range 9 times.

By the way, this is a flat file, if it matters.

[bcooney]

Your structure is incorrect. You should have a related table for Visits, and each visit should be a record in that table. Do you have multiple trials? If so, I see three tables: Patients, Visits and Trials. Each visit would have the trial_ID and Patient_ID.

Then finding visits between date ranges is a simple find in the visits table.

You need to restructure the system.

[Taipan]

Hi, thanks for the quick response. I understand that relational DBs are better (though I've never done one). However, can you tell me is there any way to have the Find Mode accept a date value from a field?

[mr_vodka]

I can only reiterate what Barbara has already stated. You really should have appointments in a separate table.

You should use two global date fields to enter in your range and then you can simply script.

Now in your case, you will have to specify each date field with the range.

Set Variable [ $range; gStart & "..." & gEnd ]

Enter Find Mode []

Set Field [ VisitDate1; $range ]

New Record / Request

Set Field [ VisitDate2; $range ]

New Record / Request

Set Field [ VisitDate3; $range ]

New Record / Request

Set Field [ VisitDate4; $range ]

New Record / Request

Set Field [ VisitDate5; $range ]

Perform Find []

etc etc etc...

AGain this is not ideal and really should be in a seperate table.

[Taipan]

Mr Vodka (John),

Thanks, I know I'm going to have to redo this or at least make future ones Relational but I'm finished with this except getting this date to work. So I appreciate the response on how to do this.

I am a real newbe. After looking at your response it took me a long time to figure out to set the target field first and then set the calulation to $Range. I was trying to enter in the full command in the calulation at first. But you have set me in the right direction to get this done. Now I need to study relatioships for the next database.

[mr_vodka]

Ah but laying the ground work now will save you hours of redesign and work arounds later. Its like asking later to build a 10 story office building on a weak foundation used with cheap materials. You can only patch work it so many times until you realize that all the money and time you spent to do the patchwork will be gone as the only way to build the office building is to destroy the entire thing and start over...

[Taipan]

John,

Something is not working with what I set up.

The BeginDate and EndDates that the user enter changes each time the script is run. For example,

when I start I enter 5/15/2009 as the begin date. THen I enter 6/23/2009 as the end date. I click the button to run the script and the begin date changes to 06/18/2008 and the end is now 06/30/2009. I thought I was following what you gave me but I've messed it up somehow.

My dates range from 4/3/2008 to 6/30/2009; In my test data I have 8 total dates.

On my form in the Heading, I have two fields for the user to complete. The BeginDateRange and the DateRangeEnd. Then I have a GO button that runs the script when the user presses.

In the Body is where I have the First and Last name and then a field for each of the 9 possible visits.

My script looks like this:

Set Variable [$Range; Value:MyFileName::DateRangeBegin & "..." & MyFileName::DateRangeEnd]

Enter Find Mode []

Set Field [MyFileName::PartDate1; $Range]

New Record/Request

Set Field [MyFileName::PartDate2; $Range]

New Record/Request

Set Field [MyFileName::PartDate3; $Range]

New Record/Request

Set Field [MyFileName::PartDate4; $Range]

New Record/Request

Set Field [MyFileName::PartDate5; $Range]

New Record/Request

Set Field [MyFileName::PartDate6; $Range]

New Record/Request

Set Field [MyFileName::PartDate7; $Range]

New Record/Request

Set Field [MyFileName::PartDate8; $Range]

New Record/Request

Set Field [MyFileName::PartDate9; $Range]

Perform FInd []

The value I have for $Range is: MyFileName::DateRangeBegin

& "..." &

MyFileName::DateRangeEnd

Any suggestions?

[mr_vodka]

You should use two global date fields to enter in your range and then you can simply script.