12 Replies Latest reply on May 1, 2012 3:28 PM by philmodjunk

    Search queries on a subset of records

    JacquesL

      Title

      Search queries on a subset of records

      Post

      Hi,

      I have a table on which I need to perform periodic (weekly) searches using a series of embedded scripts which returns counts and sums. Instead of having to include the date parameter in every single script. I'm looking for a way to perform these scripts on a subset of records (e.g. the records that are valid for that date).  In other words, I need to first tell FMP that the searches need to be performed only a specific set specific records.

      Something tells me that there is a way...

      Thanks.

        • 1. Re: Search queries on a subset of records
          philmodjunk

          A single date or a range of dates?

          You can have the user enter a single date in a global field or two dates into two global fields to define a date range.

          Then your script can use the contents of those two date fields to enter date criteria for a find or use the same dates in a portal filter or a relationship to other tables.

          Enter Find Mode []
          Set Field [YOurTable::DateField ; Yourtable::Globaldatefield]
          Set Error capture [on]
          Perform Find[]

          or

          Enter Find Mode []
          Set Field [YOurTable::DateField ; Yourtable::Globaldatefield1 & "..." & yourtable::globalDatefield2]
          Set Error capture [on]
          Perform Find[]

          The fields must have global storage specified or their values will not be accessible while in find mode.

          • 2. Re: Search queries on a subset of records
            JacquesL

            It's just single date.

            So this script, put on top of the other scripts, would restrit the susequent finds on the "wanted" subset of records?

            • 3. Re: Search queries on a subset of records
              philmodjunk

              You would use the global date field specify this date in each script's find.

              I can suggest that approach as a universally applicable method for doing this.

              If you are searching the same table each time via the same table occurrence (This will be true if this is all taking place with the same layout. It may or may not be true with different layouts), then you also have the option of performing this find and using either constrain found set or extend found set to modify the found set thus produced. If you find all records for a given date and only need records with a specified value in a diferent field, you can enter find mode, specify this value and then use constrain found set in place of perform find. Extend found set would be a way to add records to the found set.

              • 4. Re: Search queries on a subset of records
                JacquesL

                I will take the time to reflect on this...

                your assistance is greatly appreciated!

                • 5. Re: Search queries on a subset of records
                  JacquesL

                  there is another level of complexity involved.

                  waht if dates are in a value list? should the field format be text? or date? (I suppose the latter is the good answer...)

                  • 6. Re: Search queries on a subset of records
                    philmodjunk

                    Dates should almost always be stored in fields of type date--even if you use a value list for entering dates into the field.

                    • 7. Re: Search queries on a subset of records
                      JacquesL

                      well since I changed the format of the checkbox list to "date" - , it seems that I cannot have multiple boxes checked at the same time.

                      i get this message: "...value of this field must be a valide date between..."

                      I have tried to fix the problem without success.

                      • 8. Re: Search queries on a subset of records
                        philmodjunk

                        That would be correct. A date field can only store a single date. Why do you need to store multiple dates in the same field?

                        • 9. Re: Search queries on a subset of records
                          JacquesL

                          good question, as always.

                          Remember that we deliver weekly vegetables baskets to customers. Our season lasts 17 weeks, and as customers migth join after the beginning of the season and others are on vacation (we do not deliver them baskets), we need to keep a weekly record of who is "in" and who is "out" for a given week.

                          my first attempt at keeping track of this was to create a text field and a value list from 1 to 17. it was easy to perform a search for a specific week. Now I want to assign real dates, if at all possible, without having to manipulate 17 different date fields, as your answer might suggest.

                          in conclusion, maybe my first idea was the best one, although I need to crete a script which handles the changing week number.

                          • 10. Re: Search queries on a subset of records
                            philmodjunk

                            I can see the advantage to a list of dates. It's just that a list of dates would have to be treated as text rather than a date field. If you remember, I said that dates should almost always be stored in a field of type date. It's possible to use the contents of such a field as search criteria against an actual date field and you will be able to perform finds on this field by entering find mode and clicking a check box to find all records where a specific check box is selected. This can even be done in a script. So you may be able to go ahead use a text field for this.

                            A more sophisticated approach uses a portal of related records with a single date field in each record. You can then select a date in a portal row to designate a given customer as receiving a delivery on that date. There are even ways to make this look and function like a group of check boxes.

                            Remember that we deliver weekly vegetables baskets to customers.

                            Hmmm, not that it matters, but there is no mention of that elsewhere in this thread that I can see and given that I have posted over 31,000 comments in this forum, it's very unlikely that I will accurately match your forum name up with a previous thread in this forum with any degree of reliability. Wink

                            • 11. Re: Search queries on a subset of records
                              JacquesL

                              Phil,

                              This works just fine when the find is performed only on the date (formatted as text)

                              Enter Find Mode []
                              Set Field [YOurTable::DateField ; Yourtable::Globaldatefield]
                              Set Error capture [on]
                              Perform Find[]

                              But when I add 2 other conditions in the same find (there are two other conditions I need to verifiy in the same find), it does not work at all.

                              Any clues as to what is the problem? Should I put all 3 conditions in the same find?

                              Thx

                              • 12. Re: Search queries on a subset of records
                                philmodjunk

                                What are the other conditions and how are you adding them?

                                Putting them all the same find is the most straight forward option--though if one of the fields where criteria is specified is not an indexed field, using a separate entry into find mode followed by Constrain found set instead of perform find can execute faster--especially with tables that contain very large numbers of records.

                                In otherwords, put it all in one find to start. If you get progress bars that tell you FileMaker is indexing a field, consider the constrain found set alternative.