5 Replies Latest reply on Aug 2, 2010 11:27 AM by philmodjunk

    Find script on many fields using "or"

    ElizabethKennedy

      Title

      Find script on many fields using "or"

      Post

      Hi,

      I have records that contain testing dates for products.  Each product has to be tested at various times in the future.  I have a series of fields date_1_mo, date_2_mo, date_6_month, etc.  I would like to develop a script that will find testing dates within a given period of time. 

      I wrote a script using the script maker that looks like this (with more similar "set field" lines containing the other fields) but the script seems to be assuming an "and".  I would like to get it to us "or".  That is, if any of the test date fields occur within the next 30 days, I would like the record returned.

      Enter Find Mode [ ]
      Set Field [ Testing Dates::date_1_mo; Get ( CurrentDate ) & "..." & Get ( CurrentDate )+30 ]
      Set Field [ Testing Dates::date_2_mo; Get ( CurrentDate ) & "..." & Get ( CurrentDate )+30 ]
      Set Field [ Testing Dates::date_3_mo; Get ( CurrentDate ) & "..." & Get ( CurrentDate )+30 ]
      Set Field [ Testing Dates::date_6_mo; Get ( CurrentDate ) & "..." & Get ( CurrentDate )+30 ]
      Perform Find [ ]

       

       

      Can anyone help me?  Am I even close?

      Libby

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        • 1. Re: Find script on many fields using "or"
          philmodjunk

          Your script could be modified to work like this:

          Enter Find Mode [ ]
          Set Field [ Testing Dates::date_1_mo; Get ( CurrentDate ) & "..." & Get ( CurrentDate )+30 ]
          New Record/Request
          Set Field [ Testing Dates::date_2_mo; Get ( CurrentDate ) & "..." & Get ( CurrentDate )+30 ]
          New Record/Request
          Set Field [ Testing Dates::date_3_mo; Get ( CurrentDate ) & "..." & Get ( CurrentDate )+30 ]
          New Record/Request
          Set Field [ Testing Dates::date_6_mo; Get ( CurrentDate ) & "..." & Get ( CurrentDate )+30 ]
          Perform Find [ ]

          Putting each criterion in a different Request produces the Or logic you want.

          However, all those dedicated fields suggest a design change may simplify your life all along the way. You might consider setting up a related table where each of the above fields is replaced by a single record in the related table.

          • 2. Re: Find script on many fields using "or"
            ElizabethKennedy

            The script tip worked great.  Thanks for the help.

            When it comes to Filemaker and databases, I am Toonces the Driving cat.  "See, I told you he could drive! Just not very well!". 

            I am both intriqued and confused by the table layout suggestion.  Right now I have two tables:  the first, has the product, cat. no., manufacture date, expiry date and check boxes for each future test required.  The second table has all the test dates (calculated on the manufacture date if the box for the test is checked) with the two tables linked by an ID number.  Seemed logical to me, but, then again, I'm a noob.

            Can you explain a little bit more?

            Thanks again,

            Libby

             

            • 3. Re: Find script on many fields using "or"
              philmodjunk

              I have only a very incomplete picture of your database structure and function...

              "Testing Dates" is the related table of testing dates? It sounds like you have one record (or no record) in testing dates for each record you created in your first table.

              If so, just keep a single testing date field in your Testing Dates table and display a group of related testing date records in a portal. You can create a script that creates the whole group of testing date records each time your user clicks the check box for test.

              • 4. Re: Find script on many fields using "or"
                ElizabethKennedy

                Yes, I kinda looked the the portal thing while flattening my face against a wall on the scripting problem.  The whole portal thing is a mist shrouded concept for me, but I'll give it a shot.

                Once again, thanks for your time and help.

                • 5. Re: Find script on many fields using "or"
                  philmodjunk

                  A portal is an extremely useful tool in filemaker. Read up on it in filemaker help and feel free to ask questions here if you can't figure something out.