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
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::__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.
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.
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?
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.