1 2 Previous Next 28 Replies Latest reply on Jun 20, 2012 1:28 PM by philmodjunk

    Build a list of matching child records (with a parent record header for identification)

    MorkAfur

      Title

      Build a list of matching child records (with a parent record header for identification)

      Post

      Say you have a database of vendors with a child table called appointments.

      I would like to build a layout where I can enter a search term and have it list on some layout, the vendor name and all the matching child records with that search term. That way, I could see a list of all the matching search term in all the child records for all vendors on one layout.

      For example,

      Say you were looking for the term "Dehumidifier". You know you'd purchsed one from a couple vendors. Yet, there would be too many child records to do a regular interactive Find. This find needs to be done programmatically so I can extract just the right child records (with the search term) and the parent record saying who the vendor is.

      Not sure how best to do this in FMP 12.

      Using SQL, it would be like this:

      Select vendor.name, appoinment.details
         where vendor.ven_pk = appointment.ven_FK
             and
          appointment.details LIKE '%Dehumidifer%'

      Look forward to hearing suggestions.

      -m

        • 1. Re: Build a list of matching child records (with a parent record header for identification)
          philmodjunk

          This can certainly be scripted, but a manual script would seem not terribly complex to do. Not sure how the number of child records makes any difference for the method used to search them.

          You you enter "dehumidifier" into a global text field named: globals::gProductSearch

          Go to Layout [Layout based on child table]
          enter Find mode[] //clear the pause check box
          Set field [ChildTable::descriptionfield ; globals::gProductSearch]
          Set Error capture [on]
          Perform Find []
          If [Not Get ( FoundCount ) //no records found ]
             Go to layout [Original layout]
             Show Custom Dialog ["No records with " & globals::gProductSearch & " in the description field were found."]
          End If

          It's also possible to go directly to a 'detail' layout if only one record is found and to present the found records on this layout as a list where clicking one record in the list displays it in the same detail layout.

          • 2. Re: Build a list of matching child records (with a parent record header for identification)
            MorkAfur

            Very cool.

            But, wouldn't I need an additional step to go back to the parent table to pick up the vendor name so I would have a list like this:

            Home Depot

              Bought Demhumidifer on ....

            Amazon

               Demhumidifer purchased on ....

            ----------------------------------------------

            Thanks,

            -- m

            • 3. Re: Build a list of matching child records (with a parent record header for identification)
              philmodjunk

              Nope.

              Assuming that you have a valid relationship between the two tables, you can include the vendor name on your layout and it will automatically display the vendor name. To get the format you show, I'd sort the records by either Vendor Name or vendor ID and put the Vendor name field in a sub summary layout part "when sorted by" the same field I specified for the sort.

              • 4. Re: Build a list of matching child records (with a parent record header for identification)
                MorkAfur

                Very cool.

                Thank you very much, as always. :)

                -m

                • 5. Re: Build a list of matching child records (with a parent record header for identification)

                  Try creating a Report list. I've found that I can create subsummary breaks and only the ones I sort on show up.

                  Once you done your find you can sort on the various subsummary fields to isolate the ones you want to review.

                  You can sort on 
                  Item
                  Vendor

                  Or

                  Item

                  Or

                  Vendor

                  and the report only breaks on the items in question.

                  This isn't the same as searching only for a specific item or specific vendor but its cool and I use it a lot instead of portals.

                  • 6. Re: Build a list of matching child records (with a parent record header for identification)
                    MorkAfur

                    Thanks Jack.


                    BTW, is the use of a global field (per PMJ's reply) typical for a search field where you don't want to store the value and it's just a field used to get user input?

                     

                    thanks,

                     

                    -m

                     

                    • 7. Re: Build a list of matching child records (with a parent record header for identification)
                      philmodjunk

                      Good Idea Jack. I've used that trick for years and incorporated it in this tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

                      @Mork Afur,

                      You don't have to use a global field but it makes your scripts a step or two simpler and the global field can be used to display the criteria used in your most recent search of the database.

                      If the field is not a global field, it becomes blank and it's data is inaccessible when you enter find mode. Thus, your script would need to copy the data into a variable before entering find mode. Since you have to have a field for entering the criteria in the first place, it's usually simpler just to make it a field with global storage.

                      • 8. Re: Build a list of matching child records (with a parent record header for identification)
                        MorkAfur

                        I implemented the script that PMJ suggested, but it doesn't do quite what I was expecting.

                        When I search for "dehumidifier" using PMJ's script, the child layout shows the first matching child record from the parent (vendor) that has "dehumidifer" in one of its work summary records/fields. So, what's shown is the first child record for the matching parent-child where "one of" the child records has "Dehumidifer" in one of its work summary fields. However, usually that first record shown doesn't have Dehumidifer (Dehumidifer is usually in a record further down, and not shown, in the first matching child record shown).

                        I could add a portal to the child view, but that would just duplicate what I already have on the Vendor page where I have a portal showing "appointments" (the child table).

                        What I was trying to do was, given the search term, to only see matching records only in the child layout I created.

                        So you'd see:

                        Amazon

                            Demhuidifier purchased on ....

                        Home Depot

                            Dehumidifer purchased on ...

                        etc..

                        --------------------

                        Here's actual SQL that hits the FMP database that lists only the parent "vendor name" record and any child records that have "DEHMUID" in either of the WORK DONE fields. Exactly correct:

                        select vendor,  WORK_DONE_DETAIL ,WORK_DONE_SUMMARY
                        from vendors v, appointments a
                        where
                              v. VENDOR_ID = a. VENDOR_ID
                         AND
                           (   UPPER(a. WORK_DONE_DETAIL) LIKE '%DEHUMID%'
                         OR
                              UPPER(a. WORK_DONE_SUMMARY) LIKE '%DEHUMID%')

                        ---------------------

                        This is so easy in SQL, but confusing (to me) in FMP.

                        In my main page with Portal -- In FMP, if I enter the search term, yes, it gets the two *parent* records I need, but also ALL the child records for those parents whether or not they have the search term I'm looking for.

                        Separate Layout -- Just see first matching paremt record (may or may not have any matching child records)...

                        -m

                        • 9. Re: Build a list of matching child records (with a parent record header for identification)
                          philmodjunk

                          Your search must be perfomed on a layout based on the child table, not the vendor or parent table.

                          That's why the first line of the example script reads:

                          go to layout  [layout based on child table]

                          • 10. Re: Build a list of matching child records (with a parent record header for identification)
                            MorkAfur

                            Hmmm, I think I did that part correctly. See the attached image and let me know where I went wrong.

                            Also, as a work-around -- when trying out the new ExecuteSQL calculation step, it works fine *if* I hard code the text to find inside the "LIKE". But, if I reference the field on the screen (APPOINTMENTS::gSearchTerm) where I type the result in the ExecuteSQL statement (like shown below), then it doesn't work. Not sure why.

                            ExecuteSQL ( "select vendor,  WORK_DONE_DETAIL ,WORK_DONE_SUMMARY
                            from vendors v, appointments a
                            where
                                  v. VENDOR_ID = a. VENDOR_ID
                             AND
                               (   UPPER(a. WORK_DONE_DETAIL) LIKE UPPER(Trim('%APPOINTMENTS::gSearchTerm%'))
                             OR
                                  UPPER(a. WORK_DONE_SUMMARY) LIKE UPPER(Trim('%APPOINTMENTS::gSearchTerm%')))" ;
                            "|" ; "¶¶")

                            • 11. Re: Build a list of matching child records (with a parent record header for identification)
                              MorkAfur

                              Yes, you were right (as if there were any doubt). I had set up the Layout incorrectly.

                              THANKS! IT WORKS!

                              Still not sure why I can't reference a field to do the SQL, however. Look forward to your suggestions there.

                               

                              Thanks again in advance.

                              -m

                              • 12. Re: Build a list of matching child records (with a parent record header for identification)
                                philmodjunk

                                Vendors is the parent table and I see it in Parenthesis in that first script step.

                                Layout used in this search should be based on Appointments, not vendors.

                                • 13. Re: Build a list of matching child records (with a parent record header for identification)
                                  philmodjunk

                                  ExecuteSQL ( "select vendor,  WORK_DONE_DETAIL ,WORK_DONE_SUMMARY
                                  from vendors v, appointments a
                                  where
                                        v. VENDOR_ID = a. VENDOR_ID
                                   AND
                                     (   UPPER(a. WORK_DONE_DETAIL) LIKE ? )
                                   OR
                                        UPPER(a. WORK_DONE_SUMMARY) LIKE ? )" ;
                                  "|" ; "¶¶" ; UPPER(Trim(APPOINTMENTS::gSearchTerm)) )

                                  Or you can use:

                                  ..." & Upper ( Trim ( Appointments::gSearchTerm)) & "...

                                   

                                  • 14. Re: Build a list of matching child records (with a parent record header for identification)
                                    MorkAfur

                                    These don't work for me. They seem to be missing the '%' syntax as well. I've been playing around with this for a while and haven't gotten it to work yet. I also can't find a lot of examples on how to code LIKE in FMP.


                                    This type of syntax work for you?

                                    When they didn't work, I tried to put in the % syntax, like this (which still doesn't work):

                                    ExecuteSQL ( "select vendor,  WORK_DONE_DETAIL ,WORK_DONE_SUMMARY
                                    from vendors v, appointments a
                                    where
                                          v. VENDOR_ID = a. VENDOR_ID
                                     AND
                                       (   UPPER(a. WORK_DONE_DETAIL) LIKE  '%'"       & Upper ( Trim ( APPOINTMENTS::gSearchTerm)) & "'%'"  &
                                     " OR UPPER(a. WORK_DONE_SUMMARY) LIKE '%'"  &  Upper ( Trim ( APPOINTMENTS::gSearchTerm)) & "'%'" ;
                                       "|" ; "¶¶" )

                                    ---------

                                    In the other way of doing this search (FMP FIND), how would I check two fields for the search term instead of just one? Extend Find? Not sure.

                                    Thanks,

                                    -m

                                    1 2 Previous Next