9 Replies Latest reply on Oct 21, 2011 9:57 AM by philmodjunk

    Multiple find criteria

    MehrdadBastan

      Title

      Multiple find criteria

      Post

      Hi Everyone
      Please help me figure this out. I have a database that I schedule my photographers to photograph schools. I have 21 photographers who need to know where they have to be on any particular day. Here is some of the fields I have:
      School name
      Shoot date
      retake date
      photographers
      Confirmed
      Tentative
      Main_Retake_Cal (This is a calculation field for fining Main shoot or Retake purposes)
      Start_date
      End_Date
      I wrote a script that will find the schools that their retake date has be scheduled and confirmed and the script will no longer show the original shoot on the list. (Awesome if the shoot has happened already)
      However I need it to show the shoot date as well as retake date if the shoot date has not yet happened.
      This is my script:
      Enter Find Mode [Restore]
      Set Filed[School_Info::Main_Retake_Cal;School_Info::Start_date::&...&School_Info::End_Date]
      Perform Find[]
      Please help
      Thanks

        • 1. Re: Multiple find criteria
          philmodjunk

          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).

          • 2. Re: Multiple find criteria
            MehrdadBastan

            Thanks for your response. I tried what you said but it didn't work.

            The Start_date and End_date fields have Global storgae specified.

            I have a school that has a shoot date of October 25th,11 (5 days from today) and retake date of Nov 3, 2011 I notice that it still shows up as Nov 3,2011 on the list onlyFrownbeacuse my script says, if Retake is confirmed only show the retake date.

            The Main_Retake_Cal field is a calculation field that calls for:

            If(IsEmpty (Retake_date) or (Staus_Retake=""  or Staus_Retake="Tentative");Shoot_date;Retake_date)

            Could this be the problem?

            Thanks

            Mehrdad

            • 3. Re: Multiple find criteria
              philmodjunk

              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.

              • 4. Re: Multiple find criteria
                MehrdadBastan

                This is great however now when the Shoot date is today or in the future the script will only show the shoot date not the retake date. I need it to show both if the shoot has not happened yet.

                I like to give them the the schedule way in advance so they know when they are shooting the main job and when they are doing the retakes.

                • 5. Re: Multiple find criteria
                  philmodjunk

                  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.

                  • 6. Re: Multiple find criteria
                    MehrdadBastan

                    Hi Again

                    Can I email you a clone version of my database to show you what I have done? Please excuse the mess I am not an expert.

                    Click on the Division 1 (Red text) and you'll see What I need to achieve. School 2 need to be in the list as Oct 25th main shoot and Nov 10 Retake shoot.

                    How did I get myself so confused?

                     

                    • 7. Re: Multiple find criteria
                      philmodjunk

                      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.

                      • 9. Re: Multiple find criteria
                        philmodjunk

                        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.)