6 Replies Latest reply on Jun 27, 2013 11:43 AM by RonaldCarr

    How to find orphans

    RonaldCarr

      Title

      How to find orphans

      Post

           How do i find child records with no master record?

        • 1. Re: How to find orphans
          philmodjunk

               Use the delete option in manage | database | relationships to prevent them from occurring in the first place.

               But to find those you already have in your table, go to a layout based on the child table and add the primary key field from the parent table to that layout. Enter find mode and put an "*" (without the quotes) in this field. Click the Omit button so that your find will find all records that do not have data in this field.

               This can also be scripted.

          • 2. Re: How to find orphans
            RonaldCarr

                 That does seem logical.

                 Is there a reason i would use the asterisk & Omit instaed of "=" and Find? Just curious.

                 In any case when I do this and perform find, I get a message that "This ID Field is automatically generated: do not change".

                 It is indeed an auto-entry increment, but why get this message in a Find?

            • 3. Re: How to find orphans
              philmodjunk

                   WHen the field is from a related table, = won't work. It works to find records where the field is empty and this doesn't work when the record is missing rather than the record is present but empty.

                   The error message you report is not a standard FileMaker error message. It appears to be a custom validation message set in field options for this field. It might also be from a script performed from a script trigger on the field. You'll first need to determine how it is produced before you can figure out how to keep it from appearing while in find mode.

              • 4. Re: How to find orphans
                RonaldCarr

                     As always, you were right on.

                     I had validations on the primary key field: Numeric only and Unique, and that message upon a failed validation.

                     What escapes me is why those validations would be applied when entering Find criteria...

                     And of course I found the records i wanted once I got rid of the validations.

                     It does seem counter-productive to have to do so.

                • 5. Re: How to find orphans
                  philmodjunk

                       I find that FileMaker validations are often clumsy, though this sounds like a bug I've documented in the Known Bugs List. Is this FileMaker 11 or 12 that you are using? (Report only ilsts fileMaker 11 so if it also occurs in 12, I can update the report to include 12.)

                       You probably can keep the Unique validation and just replace the Numeric setting with a validation calculation such as:

                       Get ( WindowMode ) = 1 or Exact ( Self ; GetAsNumber ( Self ) )

                       I haven't tested this validation calc, but I think it'll avoid the = tripping when you specify the = in the field.

                       For More Information on the bug, see:     Validation active in find mode

                       This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.

                       It can also be downloaded as a database file from:    https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

                  • 6. Re: How to find orphans
                    RonaldCarr

                         I am using FM 12.

                         Thanks I'll try that.