4 Replies Latest reply on Dec 14, 2012 8:30 AM by appdev

    get records of parent table that have a list of similar child records, sort results by max child matches

    appdev

       

      Found this article and it looks like I could use new Execute SQL to get the desired result, but before I do that I was wondering if there were existing custom functions or plugins that would work, basically so I could compare performance.

       

      http://stackoverflow.com/questions/2165119/sql-query-to-get-records-of-parent-table-that-have-a-list-of-child-records

        • 1. Re: get records of parent table that have a list of similar child records, sort results by max child matches
          taylorsharpe

          I was thrown by your use of the word "similar" as if it meant a fuzzy logic search.  But I looked at the link and this is just a normal join relationship.  This can also be done with two relationships in FileMaker just fine too, which is how I would probaby do it.  Most of the Execute SQL functions I have seen on FileMaker do not significantly improve performance and sometimes it decreases performance.  So don't assume Execute SQL functions are any faster than native functions.  But if you have time, give a try and compare. 

           

          Now for reasons why you may want to use Execute SQL instead of relationships in FileMaker is to minimize the number of FileMaker relationships.  Often FileMaker files get bloated with lots of relationships that are needed only for one circumstance.  If a relationship is ony needed for one calculation or layout, then it is a good candidate to use with Execute SQL because it reduces the number of relationships you need in your relationship graph.  Other advantages of Execute SQL are that it is contextless (not based on a table occurrence) and there are a few SQL functions that just are not available in FileMaker.  But your circumstance is available in FileMaker via 2 relationships.  Try it both ways and then run some speed tests and let us know which works best. 

          • 2. Re: get records of parent table that have a list of similar child records, sort results by max child matches
            appdev

            Thanks for the reply Taylor. How would you propose doing this with 2 relationships exactly?

             


            To clarify further, I am looking to sit on a parent which contains (3) children records with values ABC, 123 and 456 and be able to find parent records that have child records with those same values. Some records may have all the same values in their children, in which case they should be sorted to the top and some records may have only matches on ABC and 456 which should appear lower in the list of "matches"

            • 3. Re: get records of parent table that have a list of similar child records, sort results by max child matches
              comment

              appdev wrote:

               

              I am looking to sit on a parent which contains (3) children records with values ABC, 123 and 456 and be able to find parent records that have child records with those same values.

               

              In addition to your existing relationship:

               

              Parent::ParentID = Child::ParentID

               

              define a self-join of the Child table matching on Value, so that:

               

              Child::Value = Child 2::Value

               

               

              Once you have this in place, you can do:

               

              Go to Related Record [ Child; Show only related records; Match current record ]

              Go to Related Record [ Child 2; Show only related records; Match found set ]
              Go to Related Record [ Parent; Show only related records; Match found set ]

               

              to create the described found set in Parents. This can be shortened to:

               

              Go to Related Record [ Child 2; Show only related records; Match current record ]
              Go to Related Record [ Parent; Show only related records; Match found set ]

               

               

              Note that you need to use a layout of Child when doing the GTRR to Child 2.

               

               

              Another option is to perform a scripted find, creating a request for each child value of the original parent.

               

               

               

              appdev wrote:

               

              Some records may have all the same values in their children, in which case they should be sorted to the top and some records may have only matches on ABC and 456 which should appear lower in the list of "matches"

               

              This part is a little more tricky. You need to start by storing the list of children of the original parent in a global field or variable. Next, you need a calculation field in the Parent table =

               

              ValueCount ( FilterValues ( $$originalList ; List ( Child::Value ) ) )

               

              Then sort by this field, descending.

               

               

               

              Message was edited by: Michael Horak

              • 4. Re: get records of parent table that have a list of similar child records, sort results by max child matches
                appdev

                Thanks Michael - that worked.

                 

                I always forget about that ValueCount function:). A quick loop at th end to get the IDs and then a portal to show them (rather than leave them on found set) and I am good.