Help on finding records

[saraswati]

I have to find all records in the database which satisfy all of the following conditions:

1. Expiration Date is within a range AND whose

2. FirstExt Field is null or empty AND whose

3. SecondExt Field is null or empty AND whose

4. Completed Field is empty AND

5. Withdrew Field/Tranferred Field is empty

Expiration Date, FirstExt, Second Ext fields are all date fields and Completed and Withdrew and transferred fields are text fields. Please could anyone help me on how to go about this script.

[LiveOak]

Because of field type conversions, you can't use a Set Field script step to setup a date range find, you must use commands which Paste (or Insert) to a field. First assume:

gStartDate (global, date)

gEndDate (global, date)

Script:

Go to Layout (AllFields) <-- move to a layout with all the date fields visible.

Enter Find Mode []

Insert Calculated Result [Expiration Date, gStartDate, no select]

Insert Text[Expiration Date, "..."]

Insert Calculated Result [Expiration Date, gEndDate, no select]

Insert Text [FirstExt, "=", Select]

Insert Text [secondExt, "=", Select]

Insert Text [Completed, "=", Select]

Insert Text [Withdrew/Transferred, "=", Select]

Perform Find []

-bd

[saraswati]

I tried the script as per the step given by you but it did not work.It comes to find mode and displays equal to sign in the field FirstExt. The scriptwhich I have written is as given below. This script works for only one particular date range. For any other date range it does not show any records inspite of the records existing in the table.

Set Error Capture[ON]

Go to Layout[Layout#1]

Go to Field[Expiration Date]

Show Message["Enter the expiration date as mm/dd/yyyy...mm/dd/yyyy"]

Enter Find Mode[]

Pause/Resume Script

Set Field [FirstExt, ""=""]

Set Field [secondExt, ""=""]

Set Field [Completed, ""=""]

Set Field [Withdrew, ""=""]

Set Field[Transferred, ""=""]

Perform Find[]

Please could you tell me what could be the problem. I am using Filemaker Pro 5.5 version.

[kenneth2k1]

Hi:

I like to test things that others are talking about, so I set up a test file and made the script exactly as Brent has described. It works perfectly. I messed around a bit and found that you can also use

Insert Calculated Result [DateToText(gStartDate & "..." & DateToText(gEndDate)]

Either way, the only way it wouldnt work is if it was not set up properly. I would go with Brent's script and check it over. It would be easier for users if they just had to put the dates they want to range from in two globals.

Ken

[saraswati]

The problem was in the syntax. For the script to work with me I had to use Insert statement in

the following manner:

Insert[select, FirstExt, "="]

Thanks once again for the solution