combining repeated fields into a single field (for exporting or publishing)

[b_ike]

I have a database where I have a repeated field called conditions.

The values are pulled down from a value-list.

You can imagine it's an interview and I'm noting a whole

lot of things that I think the person needs to do, as in, say

improve presentation skills

work on english

etc

All of this is fine, except that I also have a need to take this

accumulated list (which may of course be empty for someone

who is very good) and export it as "one entity" separated by

commas. Think of a letter summarising my findings and I

want to say

"Joe Blow needs to improve presentation skills, work on english, blah blah"

OR I want to take all these conditions and place them into a SINGLE excel column (which you can't with normal export).

In summary, "I want a while loop to process the repeating

field entries for as many as there are, and concatenate them

using a field separator ", " and then store in some new field.

I haven't been able to easily see how this can be done.

If I export to HTML it works fine because HTML will

support a table within a table (unlike excel).

I could of course write some script outside of FM to

process things, but that's hardly the point.

Thanks for any advice.

[Tricky]

The quickest way seems to make a value list based on a relationship, so that the list shows the values in the repeating field for one person only. You can check this by assiging the list to a field, just for testing.

Then you either use a scripted set field or a new calculation field that holds all items of the value list. If you need the list to be comma separated, you'd have to include a subsitute. The calculation is shown below. When you use the info in a letter, you can choose to only calculate this when it is needed, using set field and a plain text field, that is included in the merge, or whichever method you use for creating the letter.

Step 1: create Selfjoin relationships: Person ID::Person ID

Step 2: create value list based on this selfjoin to show contents of conditions

Step 3: make the calculation:

Substitute( ValueListItems( Status( CurrentFileName); "New Value List" ); "¶";", ")

I have my copy of FM8 at home, but I am sure you can translate this in version 8-speak. The calculation result, when in a field, can be exported at any time.

If condition repeat 1 is: need to grow up and condition repeat 2 is: more efficiency, the list would look like this:

needs to grow up [¶]

more efficiency

The calculation result will look like:

needs to grow up, more efficiency

Good luck!

[sbg2]

As Tricky mentioned it seems a new value list will work. As this has come up before I have attached a sample file I posted for another user.

ReportImproved2.zip

[b_ike]

Thanks for the responses. There are a few things that I don't understand. The function used in the attachment is

ValueListItems(Get(FileName); "Synonym")

This tells me that it looks for a field called Synonym in the

current file. Synonym would presumably need to be a unique

name to one of the tables in that file. Is that correct?

Curiously, the WordID field displayed in the Entry table

is the one from the Synonyms table (as seen by the : before

the field name). Why? If this is the table for entering

a word, why not display the native next generated word id?

The two are key fields, linked by the relationship diagram.

The other curious thing is that even though we are entering

the data of Synonyms on this field, again it is the Synonym

field from the Synonyms table which is being used on the

Entry table layout. Why? It begs the question, why do you

need an external table at all which is linked to this one

via the WordID, why can't one just use the function

as is, within the one Entry table. That is, you enter your

date in the one repeated field and then, the shadow field

is evaluated via a calculation using

Substitute(ValueListItems(Get(FileName); "Synonym"); ¶; ",")

[b_ike]

Thanks for that. It is a tad confusing. Attached please fine my attempt at doing what I thought was logical based on the advice. There is a data entry table. Then I created an artificial table to collect the conditions. I linked the two tables via their Number. Finally, I included the collected list of conditions in the data entry table to show it had worked.

I've attached my effort and would be appreciative if someone could unlock the mystery of why this doesn't work.

HowTo.fp7.zip

[comment]

Your main problem is using repeating fields for the conditions, instead of related records. While it CAN be done with repeating fields, it is not a good solution.

Once you have your conditions in a related table (linked by InterviewID), you can easily produce a columnar report from the Conditions table.

[b_ike]

Thanks for the advice, although it isn't understandable in the context of my attachment

[comment]

It's not in the context of your attachment - more like a suggestion to change the basic concept.

Interviews.fp7.zip