Multiple find criteria

Enter Find Mode [Restore]
Set Field[School_Info::Main_Retake_Cal;School_Info::Start_date::&...&School_Info::End_Date]
New Record/Request
Set Field[School_Info::Shoot date;School_Info::Start_date::&...&School_Info::End_Date]
Perform Find[]

This, like your original script, will only work if start_date and End_Date have global storage specified.

By using New Record/Request to create a new request, this find will find all records matching the first criteria (on the Main_Retake_Cal field) OR if they match the second criteria (on the Shoot Date field).

I tried what you said but it didn't work.

"It didn't work" doesn't give many anything to go on here.

I notice that it still shows up as Nov 3,2011 on the list

In what field? Main_Retake_Cal or Shoot Date?

The script does not alter what data is displayed in the fields, nor should it.

Your script, by the way, does not say "If Retake is confirmed only show the retake date". It finds all records where the Main_Retake_Cal field returns a date falling in the specified date range and also all records where the date in shoot date falls in that range. (mentioned global storage as an explanation to others reading this thread.)

Your calculation returns a date by that rule in the Main_Retake_Cal field. This should not affect how the script works, provided that you want the calculation to return dates in this fashion.

If Shoot_Date falls in the future, do you want the Shoot Date to appear in this field?

If so, change the calculation to be:

Case (Shoot_Date  ≥ Get ( CurrentDate ) ; Shoot_Date ;
         IsEmpty (Retake_date) ; Shoot_Date ;
         Staus_Retake=""  ; Shoot_Date ;
         Staus_Retake="Tentative" ; Shoot_date ;
         Retake_date)

Then click the storage options button and make this an unstored calculation field.

Also make sure that the Main_Retake_Cal calculation field has "date" specified as its return type.

Then I would use separate fields for this and not try to combine them in a single field like this. This should be workable as this script can enter the same date range criteria in both the shoot and retake date fields in separate requests.

You can upload the file to a file sharing site, post the download link here and then interested parties can download the file and respond back with their findings or even a download link to a modified copy.

Just don't upload a copy with senstive information in it. You may want to use save a copy as.. | clone and then set up the copy with just a few dummy records that illustrate the issue.

I see I missed on key detail: That you have additional criteria specified in the Enter Find Mode step. Completely missed the "Restore" in your original script step.

My original script should have been:

Enter Find Mode [Restore]
Set Field[School_Info::Main_Retake_Cal;School_Info::Start_date::&...&School_Info::End_Date]
Duplicate Record/Request
Set Field[School_Info::Main_Retake_Cal;""]
Set Field[School_Info::Shoot date;School_Info::Start_date::&...&School_Info::End_Date]
Perform Find[]

That way, the additional criteria specified for your original request is duplicated in the new request. (If this stored criteria should not be used, then keep the original script, but clear the "specify find criteria" check box.

That only affects what records are found and I don't fully understand the restored criteria you are specifying so I don't know if they really should be duplicated like this in the second request or not.

School 2 need to be in the list as Oct 25th main shoot and Nov 10 Retake shoot.

I ran the script without these changes and School 2 shows a date of Oct 25. as it is still in the future. Isn't this what you want? If I add the retake field to this layout, I can see a retake date of 11/10/2011 as it should. Running the modified script shown above finds the same two records so I think this is a case of specifying exactly what you want to see on the layout rather than an issue with the actual script.

(BTW, if you have Filemaker 10 or 11, you can change the view on this layout to List instead of Form and then you do not have to have the script enter preview mode in order to see both schools at the same time.)