accessing related records by number

[douglasgifford]

I'd like to walk through a set of related records in a scripted loop. I know I can do this using a portal, but that--though time-honoured--seems clumsy since it requires putting a portal into any layout that uses the script. Queue at one point (#123703 ) suggested using a value list that refers to the related field, which is very clever but a bit subversive and, therefore, may confuse me if I come across it in a later life.

My question is: is there an elegant and 'official' way to get at arbitrary members of a related set of records beyond the first one? Something equivalent to getRepetition(fieldName,n) or (and wouldn't this be sweet) fieldName[n].

[Fenton]

You don't need a portal to access related records. Just Go To Related Record [show, relationship ], specifying a basic layout of the related table. The found set are the related records. If you issue this command outside of a portal, you will be on the 1st record of the relationship (1st according to its sort, if any), in the related table.

You can then Loop through the records, processing whatever field(s) you want, however you want. If you need only a subset of those records, a Constrain find can find them (with Error Capture [On], and test, to see what happened).

If you want only a specific record, you could go to that record number, by calculation. But that would be kind of unusual; because where would you be specifying a particular related record (other than 1st or last) other than in a portal, or via a relationship which exclusively targeted it?

If the above step is attached to a portal row, you will be taken to the related record of that row (in the found set of the related records); as you probably know.

I seldom use portals for complex processing of related records. With Freeze Window in place the user is never going to see where things are happening; could be in several tables.

[Ugo DI LUCA]

Hi Douglas,

There are several approaches to your case in fact, depending on what exactly your goal is.

The straightforward solution would be to go to this set of related records with a Go To Related Records, and then loop through the related set in a specific layout holding the fields you need for the script.

I suspect however that this isn't what you're looking for, so let's give some clues about exactly what the ValueListItems ( ) is all about. If the GTRR solves it, never mind as you may understand how the ValueListItems ( ) works.

The ValueListItems returns the content of a Value List from a given File in your solution, in an unstored calculation. It's result however, as any other functions, doesn't necessarily need to be stored in a field, and can be evaluated at needs within a script command.

As a standard value list, the value returned by the ValueListItems is just a return-separated list of values, and FileMaker offer a bunch of Text functions allowing you to parse and extract any string in this text "field".

FileMaker 7 has a new function, MiddleValues ( ) , which would actually perfectly fit this solution.

MiddleValues(YourText;2;1) will extract the second value of the "YourText" field, including the trailing Carriage return at the end of the line targetted. Involving the Substitute ( ) function, you will easily "trim" this extracted string from this additional "

[douglasgifford]

My, what a lot of work you've gone to! Thank you both very much. For what it's worth, my question was more about 'best practices' than a specific purpose; in fact I am using a portal which is legitimately there in the layout. It's just that the portal approach seems, well, clunky.

Anatole, thanks for the 'going to another layout' suggestion; that makes a third general approach to this problem and one that has a number of advantages (the ability to do a find and sort to start with).

Ugo, if I understand you correctly, then I could write a simple custom function that would do the trick. Something like:

-------------------

Function Name: getRelatedRecordNumber

Parameters: valueListName;theNumber

Calculation:

// uses a value list as a way of getting to the records in a related table

// the value list is sorted alphabetically and will show duplicate values only once

substitute(MiddleValues ( ValueListItems ( Get ( FileName ) ; valueListName ) ; theNumber ; 1 );"