9 Replies Latest reply on May 16, 2014 8:59 AM by philmodjunk

    Filtering Related Child Table Records for use in a Report via Merge Field

    DeanChristiansen

      Title

      Filtering Related Child Table Records for use in a Report via Merge Field

      Post

           I have a parent table - Contacts - and a child table - Phone, related through a ContactID. The Phone table has a couple of fields, PhoneID, Phone, and Number. The field "Phone" is a text field description of the phone number, such as "Work", "Home", "Mobile" etc.  Note, this is a one-to-many parent-child relationship, such that I will have possibly unlimited numbers of many descriptions for each contact.

           My problem is that I can't seem to easily filter and report by a merge field specific categories of numbers - such as print a rolodex card that has all the contacts details and say just the Work number. Or the Work, Work Fax, Home, and Mobile numbers, placed in the right way on the report layout.

           Any ideas?

           Thanks in advance,

           Dean

        • 1. Re: Filtering Related Child Table Records for use in a Report via Merge Field
          philmodjunk

               one option (there are others) would be to use a filtered portal to your phone numbers on a contacts layout. Your portal filter expression can limit the related records to just the "work" number or just "Work", "Work Fax", "Home", or "Mobile".

          • 2. Re: Filtering Related Child Table Records for use in a Report via Merge Field
            DeanChristiansen

                 Hi - thanks for the response - I have figured out how to 'portal filter' the individual phone types - this seems to work best on a form/layout page. I have to say, it seems a VERY awkward way to filter a data set.

                 HOWEVER - this does not seem to be possible in a report layout, such as a label page or a form letter. So far as I have been able, the 'portal filter' field does not seem to be possible in line with report text, and therefore, floats around the layout. Isn't there some way to filter the related Child table data set?

                 Thanks again in advance,

                 Dean

                  

            • 3. Re: Filtering Related Child Table Records for use in a Report via Merge Field
              DeanChristiansen

                   Another thing I've tried to 'filter' or limit the data set, was to setup a Child table, in this case called 'PhoneW' and a second, new table called 'PhoneType' that has only two fields, an ID field and a Type field. I then created a data set in this new table with only one (1) entry - in the field Type, an entry 'Work'.

                   I then related the 'PhoneW' table to Contacts by the ContactID, and related by way of a second join, the 'PhoneType' table by relating the Type field to the Phone field. Understanding how these data sets work, that should have limited the data set to only those phone numbers that had 'Work' in the Phone field of the PhoneW table. It did not - it still resulted in showing all of the related types of phone numbers for the related Contact.

                   Just an FYI - can't imagine a crazier way to have to do something....but I tried that as well, which should have been a good work-around - no joy.

                   Dean

              • 4. Re: Filtering Related Child Table Records for use in a Report via Merge Field
                philmodjunk
                     

                          this does not seem to be possible in a report layout,

                     Please describe your report layout. This method should work just fine on either a form or list view layout. It shouldn't "float" anywhere and I'm not sure what you mean by that description.

                     If you defined a calculation field, constHome in Contacts with this expression: "Home".

                     Then you could add another occurrence of the Phones Table Occurrence to your relationship graph to produce this relationship:

                     Contacts::ContactID = Phones|Home::ContactID AND
                     Contacts::constHOme = Phones|Home::Phone

                     Then fields from Phones|Home can be added to a Contacts layout to show only data for the Home phone number. But note that this requires one such calculation field and added table occurrence for each phone type. I suggested filtered portals as an alternative that avoids that clutter in your relationship graph.

                     A third option is to set up a calculation field using ExecuteSQL to reference records in the related table that are only of a specified type or group of types. This option avoids portals and changes to your relationship graph, but requires that you have a decent working knowledge of SQL and how to use it with this function.

                • 5. Re: Filtering Related Child Table Records for use in a Report via Merge Field
                  DeanChristiansen

                       Hi, I've taken a stab at the ExecutSQL approach.

                       I created a calculation field on my contacts table, unstirred, where the calculation is as follows:

                       ExecuteSQL ( "SELECT phone.Number FROM contacts JOIN phone on contacts.ContactID = phone.ContactID WHERE phone.Phone = Work:"; "" ; "" )

                       It returns a ? - which I understand is an error indicator.

                       Could it be my limiting parameter - phone.Phone = Work: - is defined incorrectly. Work: is the actual field entry.

                       Thank again

                  • 6. Re: Filtering Related Child Table Records for use in a Report via Merge Field
                    philmodjunk

                         enclose Work: in single quotes:

                         WHERE phone.Phone = 'Work:' "

                         useful SQL resources for FileMaker:

                         SeedCode's free SQL Explorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree

                         FileMaker's reference Document on SQL (also applies to other uses of SQL in FileMaker): https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                    • 7. Re: Filtering Related Child Table Records for use in a Report via Merge Field
                      DeanChristiansen

                           Thank you....this should work, but, when the layout tries to load, I simply get the 'spinning color wheel' - the database is large - 17,000 contacts - but it seems to be stuck. I've let it run a while, thinking it was calculating 17,000 field calculations - is it possible that this will hang the calculation field?

                           I did a contacts search for one surname, which resulted in 14 contacts and then changed to the report layout where I've inserted the WPhone field, thinking to test whether or not it was a large database calculation issue. It still seems to hang with the spinning color wheel.

                           I may have to go with you second suggestion above  - .......

                            

                      • 8. Re: Filtering Related Child Table Records for use in a Report via Merge Field
                        DeanChristiansen

                             OK -

                             I used your second method above - being to setup a calculated field 'with this expression: "Home"'... etc.

                             You are correct it's a messy outcome for my table schematic, but, it did work!

                             For now I'll use that, and try to debug my SQL approach.

                             Much appreciated,

                             Dean

                        • 9. Re: Filtering Related Child Table Records for use in a Report via Merge Field
                          philmodjunk

                               The filtered portal option would seem much simpler as long as you only need to "display" that data and not access it in scripts or calculations.