4 Replies Latest reply on Dec 2, 2014 2:05 PM by philmodjunk

    Need to combine variable number of fields into one

    ChrisWilson

      Title

      Need to combine variable number of fields into one

      Post

      I'm guessing this is an easy issue, but I'm not finding an answer so I thought I would see if someone can help me out.  I am creating reports that need to list the names and ages of children of a given parent.  The children's names and ages are contained in a related table, using the parent's unique ID.  I can build the calculation to tell FMP to compile the names and ages of the children, but I don't know how to tell it to stop when it finds the first blank field (meaning if there are only two kids in the family, how do I tell the calculation to stop when it reaches the field for child3 and it's empty).  Any thoughts?

        • 1. Re: Need to combine variable number of fields into one
          SteveMartino

          Can you show a screen shot of the report in browse mode and layout mode.  Cant decipher what you mean by "...I don't know how to tell it to stop when it finds the first blank field.."

          And post your calculation

          Probably your calculation needs to use list, or an append technique (loop), but hard to tell by guessing

          • 2. Re: Need to combine variable number of fields into one
            philmodjunk

            if

            The children's names and ages are contained in a related table,

            Then there should never need to be such an empty field for the set of related "child" records.

            If you have this relationship:

            Parent-----<Children

            Parent::__pkParentID = Children::_fkParentID

            where you have one related record in children for each child of that parent, then

            List ( Children::Age )

            will produce a list of all the ages of all the children linked to that parent.

            and

            Substitute ( List ( Children::Age ) ; ¶ ; ", " )

            will produce a horizontal list of this information with commas separating the ages.

            And ExecuteSQL could also be used to produce either of these lists.

            • 3. Re: Need to combine variable number of fields into one
              ChrisWilson

              Phil and Steve,

              Your questions and comments helped me clarify my thinking. The children are in a related table, just as Phil suggested, and I was thinking about it in a rather convoluted manner. Basically, I'm writing reports on families and only generating one report per family, meaning I'm creating a form letter, of sorts, except using only one found set of data (meaning, the Smith Family Report includes only the data from the Smith found set, which is only one record). I'm then embedding the fields into a List layout that includes the text of the document. For example:

              Mr. <<FatherLastName>> is the father of...

              In order to provide the names, ages, and gender the of children in this format I decided to write a script that would find all the relevant fields in the related table (name, age, sex) and paste them in the appropriate order with necessary grammar (meaning with commas) into a new field titled "childrendata" so that the contents of the new field can be inserted into the list using <<childrendata>>.

              So the Layout view looks like:

              Mr. <<FatherLastName>> is the father of <<childrendata>>

              And the browse view displays:

              Mr. Smith is the father of daughter Samantha, age 8, daughter Michelle, age 5, and son, Mitchell, age 2.

              The ultimate report is several pages long and to get around issues of formatting headers and footers for the first versus pages 2 on, I am exporting the report as a PDF, converting the PDF to a Word doc and pasting into my own "stationary" doc that has the appropriate headers and footers.

              Am I thinking about this in a way that makes sense?  Is there an easier way to accomplish the task?

              • 4. Re: Need to combine variable number of fields into one
                philmodjunk

                ChildrenData can be a calculation field. There is no need to use a script to paste this data. Even you decide to stick with a script, the script need not use the system clipboard in a copy/paste action that overwrites any data the user may have previously copied to the clipboard. You can use set Variable in place of copy and set field in place of paste.

                It is also possible to add title headers and footers to your layout so you might not need to do all this "post processing" in order to produce the end result that you need.