7 Replies Latest reply on Jun 2, 2011 10:27 AM by scottage

    script to find fields based on range of dates

    scottage

      Title

      script to find fields based on range of dates

      Post

      Hi, all

      I am working on a db for an automotive shop.  I am trying to create a script that will perform a find for customers whose state inspections are due sometime in the next 60 days.

      So far I have a field in a table called Work_Order called inspection_due that keeps track of the date, and I have confirmed that it's field type is a date.

      In the script I have:

      Show All Records

      Enter Find Mode []

      Set Field [Work_Order::inspection_due; Get(CurrentDate) & "..." & Get(CurrentDate)+60]

      Perform Find []

      When I run the script it tells me no records match the criteria.  I know that I have dates inside of that range.  Can anyone show me what I am doing wrong?

      Thanks!

      Scott

        • 1. Re: script to find fields based on range of dates
          scottage

          Ok....I kept working at this and found that my error was coming from not opening the right layout in the script.  I changed that and the error message went way.  Yay!!!......but then when I printed the found set, all customers regardless of the inspection due criteria printed.  

          Any help is appreciated.  Thanks!

          • 2. Re: script to find fields based on range of dates
            philmodjunk

            Show All records can be removed from your script it has no effect on your find one way or the other.

            What you show in your script should work, provided you are performing the find on a layout where "Work Order" is the exact text shown in Layout Setup | Show Records From.

            Is that the case here? (If this field is in a portal or on a layout that refers to a different table occurrence in layout setup, then you may get different results than you'd expect.)

            • 3. Re: script to find fields based on range of dates
              scottage

              Thanks for the quick reply!

              I posted a second time right before your reply was posted.  The problem has changed a bit.

              The layout that is being opened in the script is the native layout/table to where the field is being entered.

              • 4. Re: script to find fields based on range of dates
                philmodjunk

                On the contrary, I saw both posts and posted my questions accordingly.

                "The layout that is being opened in the script is the native layout/table to where the field is being entered"

                That doesn't actually answer my question with complete certainty. Please confirm that the name in "Show Records From" is "Work Order".

                Assuming that it is Work Order...

                Are your printing your records by selecting Print from the File menu right after running your scripted find or are you useing a script to do this?

                If using a script to print your records, what records do you see in your found set immediately after you run your script. Do you see the records you expect or do you see a found set of all records?

                • 5. Re: script to find fields based on range of dates
                  scottage

                  The script is opening the layout that is being opened is showing records from the table Work_Order.

                  Right after the Perform Find command, I have the script go to another layout that is designed to print Avery 5160 labels with the customers' names and addresses.  That layout shows itself in preview mode and asks if the user to confirm printing with a custom dialog that is a simple Cancel and Print option.  In that preview mode screen, I only see one name listed, but when I confirm the print, all the names in the db print on those labels.  

                  To be honest, the difference between what I see in Browse Mode and Preview Mode have been confusing to me because the two modes seem to show different things.  I just hoped I would figure out the difference as I moved along.  BTW, I'm using FMP 11.

                  Thanks for all the help!

                  • 6. Re: script to find fields based on range of dates
                    philmodjunk

                    I always assume FMP 11 until I learn differently. Wink

                    Ok, the problem isn't with your find script. It's with the script and/or layout used to print your labels. If it does not show Work_Order in it's show Records from drop down, it's found set will be completely different from the one where you performed your find. Every layout specifies a table occurrence by name in Show Records From. A table occurrence is one of the "boxes" shown in Manage | Database | Relationships. Each table occurrence (for a given fileMaker window) has it's own current record, sort order and found set.

                    It's also possbile that your script is doing something to change the found set. Preview normally shows all records in the found set, (though you may have to click on the book control or drag the slider to see additional pages), so the fact that you see only one label, but the script prints all labels suggests that it is doing a show all records or something between the preview and print steps.

                    You'll need to look at show records from for this layout and also examine your print script to see what's going on here.

                    • 7. Re: script to find fields based on range of dates
                      scottage

                      that was it!  I had the wrong show records shown in the avery 5160 layout.  

                      Thank You!