2 Replies Latest reply on Jun 19, 2013 2:51 PM by pilot_john

    finding records in one layout based on a found set in another layout from a related table

    pilot_john

      Title

      finding records in one layout based on a found set in another layout from a related table

      Post

      Background:

           I'm sure this has been answered before in the forums, but I have not found the answer although i'm not sure i wasn't searching for the right phrase or terminology.

           I have filemaker pro 11 on one mac and filemaker pro 11 advanced on another.

           I have multiple tables and relationships in my database but the two concerned here are Invoices and LogBook. In the logbook table I have a field called "related inv id". In the Invoice table I have an Invoice Id field that is automatically populated in sequence with each new record. I have these two tables joined with the mentioned fields. Just the plain old regular join that you get when on the relationship diagram, you drag one field from one table occurrence to a field on another table or occurrence.

           One invoice may have multiple related logbook records or just one, or it may have none.

           One logbook record may only have one related invoice or it may have no related invoices but it can not have more than one related invoice.

            

      What I would like to do:

           I would like to search the logbook records for certain criteria and then find invoices not related to that found set.

           What is the easiest way to do this? And what are the pro / cons of each way?

      More Background:

           I have worked with portals but I don't think this is what I want to do, or at least i'm not sure how to make it work right. I have also made a few simple scripts (for other things not related), so I'm willing to try either of those, but I still feel like a beginner with filemaker so be easy, lol.

            

        • 1. Re: finding records in one layout based on a found set in another layout from a related table
          philmodjunk

               You appear to have this relationship:

               Invoices----<LogBook

               Invoices::Invoice Id = LogBook::related inv id

               After you perform a find on LogBook, you want to use that found set to find all records in Invoices that are not linked to a record in the current found set in LogBook.

               Use Go To Related Records with the match found set option to pull up a found set of records in Invoices that link to at least one record in your found set in LogBook. Then use Show Omitted Only to swap the found set of those records that match for all the other records in your table--the records that do not match to a record in your found set in LogBook.

          • 2. Re: finding records in one layout based on a found set in another layout from a related table
            pilot_john

                 Great thanks !!!   That's exactly what I was trying to do.

                 When you said use "Go To Related Records", I wasn't sure if you meant in a script or if that was a menu option but I found it as a script step and so I created a one line script using that with the "Match all records in current found set" option. and it worked great. I then wanted to narrow the search more so I was able to go into find mode and use "constrain found set" to get exactly what I needed.

                 Thanks again.