8 Replies Latest reply on Mar 15, 2012 9:53 AM by philmodjunk

    How to make calculation that returns result from multiple records

    TomWickersham

      Title

      How to make calculation that returns result from multiple records

      Post

      Hello,

      I have two tables, PEOPLE and HOUSEHOLDS.  I would like to create a calcuation that returns a result of the parents' names.  So, if there is only one parent record in the PEOPLE table, the result would be "Nancy Smith."  But if I have two related records (two parents records, but related in that they are both part of the same HOUSEHOLD), the result would read "Jane Doe and Wiliam Small."  Any help would be much appreciated.

      BTW, I'm using FM Pro 7.

      Thank you,

      Tom

        • 1. Re: How to make calculation that returns result from multiple records
          philmodjunk

          Check and see if Filemaker Pro 7 supports the List function.

          If it does, List ( People::FullName )

          will produce a return separated list of names from the related People table if evaluated from Households. If you want a horizontal list of names, it can be:

          Substitute ( List ( People::FullName ) ; ¶ ; ", " )

          • 2. Re: How to make calculation that returns result from multiple records
            TomWickersham

            Thanks, Phil.  Unfortunately, it does not offer a List funtion.  Any other suggestions?

            • 3. Re: How to make calculation that returns result from multiple records
              philmodjunk

              You may want to upgrade. Version 7 is getting pretty old...

              Without the list function you are looking at a script to pull up a found set of the related records in order to either loop through them to build the list of values or to use Copy All Records to copy them to the clipboard for pasting into a text field. (If you use this option, you must copy from a layout that only contains the name fields.)

              Either that or don't use a calculation field; use a portal to list the names.

              • 4. Re: How to make calculation that returns result from multiple records
                TomWickersham

                Thanks again.  A few follow ups...

                1. Let's say I upgrade FM.  With the List function, is it dynamic enough or should I use it in connection with a calculation, to create two possible outcomes -- one where the two parents share the same last name (so the result is "Trisha and Matt Jones" and one where there are two separate names (as in the example above)?

                2. Could you elaborate on how to use the Copy All Records option?  How do I set that up to identify the records relating to a particular household?

                Thank you so much for your help!

                Tom

                • 5. Re: How to make calculation that returns result from multiple records
                  philmodjunk

                  1) That would be a challenge for the list function and also the version 7 alternatives we have discussed. "same last name" gets pretty hairy considering that you could have more than two individuals identified as "parents" even if they are all in the same househould... I think you'd have to pull the names up in a list then loop through them and reprocess them into the combined form to get that. This could be done with a looping script or a recursive custom function. (creating and installing custom functions requires FileMaker Advanced.)

                  2) first create a layout based on People that has only the name or name fields you want to copy. Then use Either Go to Related Records from the Household table or copy data into variables (or fields with global storage if you can't use variables in version 7) from the household table and use them to perform a find. I can't spell out all the details with perfect confidence here as I don't know enough about the two tables to do so. I'd think you would have a third table here that serves as a "join" between people and households so that the same people record (say a child with joint custody) can be linked to more than one household.

                  • 7. Re: How to make calculation that returns result from multiple records
                    TomWickersham

                    Okay, I've upgraded my FileMaker (I'm not at 10), and I've created my List field as you suggested:

                    List ( People::Full Name )

                    This returns a list of all related records in the HOUSEHOLD file.  In other words, I get both parents' names and all kid names (if any).  Is there a way I can have it return just the parents?

                    If not, is there a workaround?  For example, perhaps I can create a field in the PEOPLE file that only populates parents' full names when the field 'parent checkbox' is checked.  Then the List field would look like this:

                    List (People::Parents Full Name )

                    I'm hoping the first option can work, but if not, perhaps this workaround?  (BTW, I've already tried the workaround, and I'm getting Lists like this: John Smith, Mary Smith,  ,  ).  I seem to be populating the Parents Full Name field with spaces/blanks for the kid records.)

                    Again, thanks for your help with this!

                    Tom

                    • 8. Re: How to make calculation that returns result from multiple records
                      philmodjunk

                      How do you distinguish between parents and kids in terms of the data entered into your database? You should be able to set up a calcualtion field such as:

                      If ( People::Type = "parent" ; Full Name )

                      Then set your list to list this calculation field (which will be blank for People records not identified as parents) instead of Full Name.

                      Note: In today's world it's possible for a person to be a member of more than one household. You may want to define a Join table linking People to Houshold with the "type" field part of this join table.