Using FM6

I have a file that can contain one or more records with the same key. I need to concatenate one of the fields in this file into a single field for display in the related file. I'm not sure exactly how to do this given that the number of records is different in each situation. It doesn't matter which file the concatenation happens in as moving the resulting field to the master file isn't difficult.

Any suggestions on how to loop through a number of iterations when the number of related records varies?

Thanks, any help is appreciated!

kschott said:

I have a file that can contain one or more records with the same key.

Whoah! Hold it right there! Do you mean the primary key for the file or a related key from another file? If you mean the first, then you had better change the way you're doing things. Primary keys have to be unique or they defeat their purpose.

Sorry, mean related key.

Might be overkill but I'll describe the situation in specifics:

Master file contains Design Request / Design Alteration Request forms data.

Every DR/DAR has a unique document number.

The related file is a list of documents to be released with the related DR/DAR.

The two files are related by the DR/DAR document number. The released documents file contains the DR/DAR Number, the released document ID that is associated with the DR/DAR, the revision level and document title.

From the main table the users enter data into the released documents table through a portal. The number of released documents associated with any given DR/DAR is variable, some have none, some have dozens of related documents.

I need to fill a field in the main DR/DAR forms table with a list of all the related released documents, hence the need to concatenate a variable number of fields from a related file.

Hope that helps, sorry for the confusing description the first time.

It sounds like you want "values from a field(s) in a variable number of related records."

This is not the same as "a variable number of fields." Semantics?, perhaps; but very different.

You can use the ValueListItems to get the values from a related field.

Create a Value List, values from a field, only related values, DR/DAR relationship, DocumentTitle field.

Call it DR_Docs.

Create a calculation field, Unstored.

Substitute (ValueListItems (Status(CurrentFileName), "DR_Docs"), "

This works great! But...

I cannot get it to update when a new record is added.

I have a main file with a field "Award" - calculation - Substitute(ValueListItems(Status(CurrentFileName), "Award List"), "

Make sure 'Award' is unstored or it won't update automatically.