8 Replies Latest reply on Sep 11, 2014 11:54 AM by philmodjunk

    extend found set to include empty records

    AdamReed

      Title

      extend found set to include empty records

      Post

      I've written a script to find records within a date range, and would like to add a step to extend the found set to include records which have no date entered at all.  I've tried "extend found set" to include date="", but that returns no records.  Thanks for any help.

      Adam

        • 1. Re: extend found set to include empty records
          philmodjunk

          Is the date field from a related Tutorial: What are Table Occurrences? or from the same table occurrence as that specified for your layout?

          • 2. Re: extend found set to include empty records
            AdamReed

            Sorry, I misunderstood -- the date field is in a related table occurrence.  (The dates which are being entered to define the search criteria (start date and end date) are global fields in an unrelated table.

            • 3. Re: extend found set to include empty records
              AdamReed

              This is my script:

               

              Go to Layout [ original layout ]

              Enter Find Mode [ ]

              Set Field [ book contract link::pub date; search::start date & "..." & search::end date ]

              Perform Find [ ]

              Constrain Found Set [ Specified Find Requests: Find Records; Criteria: contract::rights first serial: “Author” ] [ Restore ]

              Extend Found Set [ Specified Find Requests: Find Records; Criteria: book contract link::pub date: “=” Omit Records; Criteria: contract::subsidiary agreement: “yes” ]
              [ Restore ]

              Sort Records [ Keep records in sorted order; Specified Sort Order: book contract link::pub date; descending ] [ Restore; No dialog ] 

              • 4. Re: extend found set to include empty records
                philmodjunk

                "unrelated table" makes no sense.

                and this criteria is contradictory for and "Extend found set" script step:

                book contract link::pub date: “=” Omit Records

                Are you trying to add records where the field is empty or omit them? Seems like the Omit shouldn't be specified here.

                And I see several different table occurrence names used in your script.

                Which one is the basis for the layout where you perform this find and how is it related to the others listed here?

                There is a possible issue with = when the field is from a related table occurrence. Such a field can be empty because there is a related table with an empty field (and then = works to find records where this field is empty) or it can mean that there is no related record at all (and then = doesn't find this record).

                And there are other possible sources of confusion when you specify find criteria in the fields of related records. If you have this relationship:

                TableA-----<TableB

                where there can be many records in B linked to a record in A,

                Then if you perform a find on a layout based on TableA, but specify criteria in a field from B. FileMaker will find all Records in A that have at least one related record in B that match the specified criteria. Once found, all related records from B still link to each found record in A and this fact sometimes produces results that look misleading.

                • 5. Re: extend found set to include empty records
                  AdamReed

                  Sorry, I'm not very experienced.  I see now that the script is apparently working, but that it's stopping me with a dialog box at each step of the find to let me know there are no records found.  If I press continue several times, the end result is some records found (the ones without a date assigned).

                  I suppose now what I want to do is remove those dialog boxes, but I'll look into that.  I'm sorry for my confusion and the trouble.

                  • 6. Re: extend found set to include empty records
                    philmodjunk

                    Set Error Capture [on]

                    will suppress the error dialogs.

                    • 7. Re: extend found set to include empty records
                      AdamReed

                      Ah, many thanks.  I'd been trying Error Capture (off) -- thinking that would turn errors off, but turning error capture on makes perfect sense.  Very little sleep this week, thanks for your patience...

                      • 8. Re: extend found set to include empty records
                        philmodjunk

                        I hear  you, been burning late night oil to get Adventures in FileMaking #2 finished.

                        Caulkins Consulting, Home of Adventures In FileMaking