Accessing related values for a calculation

[Matthew F]

Does anyone know a way to access related fields in a calculation so values (other than the first matching value) can be retrieved? In a repeating field this can be done with the GetRepetition function. I'd like to be able to, for example, access the 7th related record meeting the relationship criteria without having to create a portal and a script to loop through the portal rows.

I realize this is not a built in feature of filemaker but I'm wondering if there are any new tricks in FM7 or plugins which might enable this.

Thanks...

[transpower]

This sounds like a job for the Choose function.

[Matthew F]

How so? I don't understand how to make that work in this case.

Perhaps I didn't explain clearly what I'm trying to do. Imagine that you have a table called "Teachers" where each record represents a different teacher. Within a layout is a portal which shows the names of Students assigned to a given teacher from a related table .

How can I create a calculation of type text which returns: "Teacher Jones, your students names are Alice, Bob, Charles, ..etc." Or a second calculation which retruns Student #2 = Bob.

Thanks.

[-Queue-]

Create a value list in the Teachers file based on the related field you want to list. If there's a chance there may be duplicate names, add the related serial as the second field in the value list definition and sort by it. Then create an unstored calculation text field of Substitute( ValueListItems( Get(FileName); "yourvaluelist" ); "

[Matthew F]

Eureka! That is brilliant.

Merci.